基于空间数据库的空间分析方法
之前介绍过企业级地理数据库,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秒,耗时较长,针对大数据量的空间查询,查询结果后续还需缓存存储。
注:此处的面状要素表可以是行政区划、网格、林长责任区等