• oracle空间索引


    1、索引创建

    • 添加元数据
      • INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
        VALUES ('BEIJING_LINK', 'GEOMETRY',
        MDSYS.SDO_DIM_ARRAY
        (MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.00000000050),
        MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.00000000050)),
        8307);

    • 创建索引
      • create index idx_beijing_link on beijing_link(geometry)
        indextype is mdsys.spatial_index;

    • 查询索引

        select * from user_sdo_index_info where index_name='IDX_BEIJING_LINK' ;

    2、空间操作符使用

    1、sdo_filter相交

    select count(*)
    from beijing_link a,
    (select link_pid, geometry from beijing_link where rownum = 1) b
    where sdo_filter(a.geometry, b.geometry) = 'TRUE'
    and a.link_pid != b.link_pid;

    2、sdo_anyinteract 和sdo_filter相似

    select count(*)
    from beijing_link a,
    (select link_pid, geometry from beijing_link where rownum = 1) b
    where sdo_anyinteract(a.geometry, b.geometry) = 'TRUE'
    and a.link_pid != b.link_pid;

    3、sdo_nn(geometry a,geometry b,tol c,unit)返回距离最近的前N个

    select sdo_geom.sdo_distance(a.geometry, b.geometry, 0.1, 'unit=meter')
    from beijing_link a,
    (select link_pid, geometry from beijing_link where rownum = 1) b
    where sdo_nn(a.geometry, b.geometry, 'sdo_num_res=6') = 'TRUE';

    4、sdo_nn_distance返回距离

    select a.link_pid,b.link_pid,sdo_nn_distance(1) distance
    from beijing_link a,
    (select link_pid, geometry from beijing_link where rownum = 1) b
    where sdo_nn(a.geometry, b.geometry, 'sdo_num_res=10',1) = 'TRUE'
    order by distance;

    5、sdo_relate 判断两个几何关系

    select count(*)
    from beijing_link a,
    (select link_pid, geometry from beijing_link where rownum = 1) b
    where sdo_relate(a.geometry, b.geometry,'mask=touch') = 'TRUE'
    and a.link_pid != b.link_pid;

    select count(*)
    from beijing_link a,
    (select link_pid, geometry from beijing_link where rownum = 1) b
    where sdo_relate(a.geometry, b.geometry,'mask=anyinteract') = 'TRUE'
    and a.link_pid != b.link_pid;

    select count(*)
    from beijing_link a,
    (select link_pid, geometry from beijing_link where rownum = 1) b
    where sdo_relate(a.geometry, b.geometry,'mask=coveredby') = 'TRUE'
    and a.link_pid != b.link_pid;

     6、sdo_within_distance两个对象距离在某范围内

    select count(*)
    from beijing_link a,
    (select link_pid, geometry from beijing_link where rownum = 1) b
    where sdo_within_distance(a.geometry, b.geometry,'distance=10,unit=km') = 'TRUE'
    and a.link_pid != b.link_pid;

  • 相关阅读:
    关闭防火墙,仍然无法访问80端口 centos
    apache添加虚拟主机(windows下)
    PHP实现文件下载
    chmod 777 修改权限之后,文件夹颜色变绿:解决方案
    element ui table(表格)点击一行展开
    vue中eventbus 多次触发的问题
    console.log、toString方法与js判断变量类型
    另一个维度:cocos-2d VS vue
    浏览器内置的base64方法
    H5网页涂鸦canvas
  • 原文地址:https://www.cnblogs.com/lilei2blog/p/8376758.html
Copyright © 2020-2023  润新知