基于空间数据库的空间分析方法

之前介绍过企业级地理数据库，https://blog.ahtelit.com/blog/article/320。地理空间数据库的使用除了用于项目中的空间数据存储，还能利用空间数据库扩展的空间函数，帮助项目中更好的完成数据查询、统计、分析，即将gis的分析能力前置到后端，更好的实现相关功能。

st_geometry常见用法

1.点、线、面定义

点：st_geometry('point(115.97166453999147 28.716493914230423)',4326)

--经度在前，纬度在后，中间使用空格连接

线：st_geometry('linestring(103.115880 30.494267, 103.115880 30.889056, 103.744557 30.889056, 103.744557 30.494267)',4326)

面：st_geometry('polygon ((103.115880 30.494267, 103.115880 30.889056, 103.744557 30.889056, 103.744557 30.494267, 103.115880 30.494267))', 4326)

4326是坐标系，这里不写时默认使用4326；

在某些场景下，需要使用4490

2.计算两点（两个几何）距离

2.1定义

ST_Distance ：用于返回两个几何之间的距离。这一距离是两个几何的最近折点之间的距离。

--sde.st_distance (geometry1 sde.st_geometry, geometry2 sde.st_geometry)

--sde.st_distance (geometry1 sde.st_geometry, geometry2 sde.st_geometry, unit_name text)

--unit_name 常见可选单位 kilometer  meter

2.2案例

--点与点之间

select st_distance(st_geometry('point(115.97166453999147 28.716493914230423)',4326), st_geometry('point(116.00231199774656 29.719258550486572)',4326),'METER');

--结果：111185.75301852751

select st_distance(st_geometry('point(115.97166453999147 28.716493914230423)',4490), st_geometry('point(116.00231199774656 29.719258550486572)',4490),'METER');

--结果：111185.75301616752

通过上述4326和4490的计算结果对比，可以发现两种差距毫米以下，可以忽略不计。因此两种计算 4326≈4490

--线与点之间

select st_distance(st_geometry('linestring(115.95 28.71 , 115.987 28.7253)',4326), st_geometry('point(116.00231199774656 29.719258550486572)',4326),'meter');

--线与面之间

select st_distance(st_geometry('linestring(115.95 28.71 , 115.987 28.7253)',4326), st_geometry('polygon ((103.115880 30.494267, 103.115880 30.889056, 103.744557 30.889056, 103.744557 30.494267, 103.115880 30.494267))', 4326),'meter');

3.范围查询

3.1定义

将落在给定范围内的数据查询出来。空间关系上是相交，intersect。

sde.st_intersects (geometry1 sde.st_geometry, geometry2 sde.st_geometry):如果两个几何的交集不生成空集，则 ST_Intersects 返回 1（Oracle 和 SQLite）或 t (PostgreSQL)；否则返回 0（Oracle 和 SQLite）或 f (PostgreSQL)

3.2案例

(1)矩形范围查询

SELECT * from (SELECT * from comm_fire_alarm where comm_fire_alarm.longitude<>'' and comm_fire_alarm.latitude<>'') as pnt where sde.st_intersects(sde.st_geometry('point('||pnt.longitude||' '||pnt.latitude||')',4326), sde.st_geometry ('polygon ((103.115880 30.494267, 103.115880 30.889056, 103.744557 30.889056, 103.744557 30.494267, 103.115880 30.494267))', 4326))

矩形范围：sde.st_geometry ('polygon ((103.115880 30.494267, 103.115880 30.889056, 103.744557 30.889056, 103.744557 30.494267, 103.115880 30.494267))', 4326)

这个polygon使用四至点进行计算，即矩形的四至点，由于面状要素需要首尾相连，可以看到起始点和终止点一样。

(2)任意多边形查询

SELECT * from (SELECT * from comm_fire_alarm where comm_fire_alarm.longitude<>'' and comm_fire_alarm.latitude<>'') as pnt where sde.st_intersects(sde.st_geometry('point('||pnt.longitude||' '||pnt.latitude||')',4326), sde.st_geometry ('polygon ((104.1225110571206 30.561369823136484,104.4272499880601 30.649374019296115,104.64577987932594 30.452548474719897,104.33101664144762 30.333223616132923,104.16461314626355 30.326301765206306,104.0924381363042 30.440449699746136,104.1225110571206 30.561369823136484))', 4326));

多边形范围：

sde.st_geometry ('polygon ((104.1225110571206 30.561369823136484,104.4272499880601 30.649374019296115,104.64577987932594 30.452548474719897,104.33101664144762 30.333223616132923,104.16461314626355 30.326301765206306,104.0924381363042 30.440449699746136,104.1225110571206 30.561369823136484))', 4326)

(3)缓冲区范围查询

--点缓冲区：

方法1：

SELECT * from (SELECT * from comm_fire_alarm where comm_fire_alarm.longitude<>'' and comm_fire_alarm.latitude<>'') as pnt where sde.st_intersects(sde.st_geometry('point('||pnt.longitude||' '||pnt.latitude||')',4326), sde.st_buffer(sde.st_geometry('point(103.115880 30.889056)',4326),55000,'meter'));

ST_Buffer 获取几何对象和距离，然后返回表示围绕源对象的缓冲区的几何对象。

sde.st_buffer (geometry sde.st_geometry, distance double, text unit_name)

unit_name:'meter'、'kilometer'

方法2：防火组目前在用。通过遍历比较距离，小于给定距离的数据筛选出来。

SELECT * from (SELECT * from comm_fire_alarm where comm_fire_alarm.longitude<>'' and comm_fire_alarm.latitude<>'') as pnt where sde.st_distance(sde.st_geometry('point('||pnt.longitude||' '||pnt.latitude||')',4326), sde.st_geometry('point(103.115880 30.889056)',4326),'kilometer')<50;

对比后发现，使用st_buffer效率更高。

--线缓冲区查询

SELECT * from (SELECT * from comm_fire_alarm where comm_fire_alarm.longitude<>'' and comm_fire_alarm.latitude<>'') as pnt where sde.st_intersects(sde.st_geometry('point('||pnt.longitude||' '||pnt.latitude||')',4326), sde.st_buffer(sde.st_geometry('linestring(104.1225110571206 30.561369823136484,104.4272499880601 30.649374019296115)',4326),10,'kilometer'));

--面缓冲区查询

SELECT count(*) from (SELECT * from comm_fire_alarm where comm_fire_alarm.longitude<>'' and comm_fire_alarm.latitude<>'') as pnt where sde.st_intersects(sde.st_geometry('point('||pnt.longitude||' '||pnt.latitude||')',4326), sde.st_buffer(sde.st_geometry('polygon ((104.1225110571206 30.561369823136484,104.4272499880601 30.649374019296115,104.64577987932594 30.452548474719897,104.33101664144762 30.333223616132923,104.16461314626355 30.326301765206306,104.0924381363042 30.440449699746136,104.1225110571206 30.561369823136484))',4326),5,'kilometer'));

这里查询落在给定范围内的火点数量，结果如下。

注：点、线、面的缓冲区分析均能使用，即传入的geometry可以支持点、线、面。

4.基于面状空间数据范围查询

上述查询范围除了支持自定义的范围，还能通过空间数据库中的面状要素表对应查询；比如查询某网格下的防火物资数量。

--网格下的防火物资数量查询

SELECT count(*),plyo."责任单位" from (SELECT * from comm_config_qushuidian where comm_config_qushuidian.longitude<>'' and comm_config_qushuidian.latitude<>'') as pnt,sde.geo_xzqh_grids as plyo where sde.st_intersects(sde.st_geometry('point('||pnt.longitude||' '||pnt.latitude||')',4490), plyo.shape)='t' group BY plyo."责任单位";

查询各个网格范围的散坟数量，15万条记录，查询时间1200秒，耗时较长，针对大数据量的空间查询，查询结果后续还需缓存存储。

注：此处的面状要素表可以是行政区划、网格、林长责任区等