公司部分新零售等商业咨询类项目中有大量空间查询的需求,便利用 postgresql + postgis 方案构建空间数据库,利用postgis强大的空间查询性能。
现需求为一个点一定范围内的所包含的poi数目,圈选出来后,在此基础上进行分类、统计、分析、展示,广泛应用于公司的商业咨询项目中。
例如:找出经纬度(116.41 39.91)点1000m内的某poi数量。
- 使用&&:
A && B: 当二维A范围覆盖二维B范围时,返回真。但这里注意:这个范围是索引范围,我们知道空间索引索引的是多边形bounding box,这里使用&&返回的缓冲区圆范围的多边形了。如下图所示,本应返回圆形区域的点,却返回了它extent的矩形范围的点。
select name from beijingpoi
where ST_Transform (geom, 32650) && ST_Buffer(ST_Transform(ST_GeomFromText('POINT(116.41 39.91)', 4326), 32650), 1000)
但是,马上想到,其实在多一个选择条件,在这个矩形里的点在做一次过滤就行了,就有了如下sql:
select name from beijingpoi
where ST_Transform (geom, 32650) && ST_Buffer(ST_Transform(ST_GeomFromText('POINT(116.41 39.91)', 4326), 32650), 1000)
and st_within(ST_Transform (geom, 32650), ST_Buffer(ST_Transform(ST_GeomFromText('POINT(116.41 39.91)', 4326), 32650), 1000))
但这样似乎有些多此一举,现在使用ST_DWithin函数。
SELECT name,geom FROM beijingpoi
WHERE ST_DWithin(
ST_Transform(st_geomfromtext('POINT(116.41 39.91)',4326),32650)
,ST_Transform(geom,32650), 1000);
一下子清爽了不是,同样的,我们也可以利用ST_within和ST_Buffer完成这个操作:
select name from beijingpoi
where st_within(ST_Transform (geom, 32650), ST_Buffer(ST_Transform(ST_GeomFromText('POINT(116.41 39.91)', 4326), 32650), 1000))
以上三种方式都可以实现需求,明显后两种更简洁一下。
- 解决了能否的问题后,就该解决效率问题了,现在拿第三种查询方式做对比,首先对被查询的点geometry字段新建了gist空间索引:
CREATE INDEX "beijingpoi_geom_idx" ON "public"."beijingpoi" USING gist ( "geom" "public"."gist_geometry_ops_2d" );
然后执行: