postgis中的缓冲区圈选查询研究_postgis缓冲区查询-优快云博客

本文链接：https://blog.youkuaiyun.com/mutouren123xixi/article/details/133886976

参考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" );
然后执行：explain (analyze,verbose,timing,costs,buffers) 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))
结果如下：

Seq Scan on public.beijingpoi (cost=0.00..408530.77 rows=82643 width=28) (actual time=0.741..4577.254 rows=8192 loops=1)
Output: name
Filter: ((st_transform(beijingpoi.geom, 32650) && '01030000208A7F00000100000021000000552D003F2E801B4156DFC5B063DA50411DB92063E17F1B415809FEEA32DA50410459A4C3FD7E1B413C84080504DA50417A39E21F8C7D1B41B32C47CCD8D95041753958AC9A7B1B41945EF0E9B2D950417E57EB863C791B4194BEB7D293D95041F9DED5FA88761B419B9C7BB87CD950413B8D7D9B9A731B4199D6837E6ED95041552D003F8E701B4156DFC5B069D9504170CD82E2816D1B4199D6837E6ED95041B17B2A83936A1B419B9C7BB87CD950412C0315F7DF671B4194BEB7D293D950413521A8D181651B41945EF0E9B2D9504130211E5E90631B41B32C47CCD8D95041A6015CBA1E621B413C84080504DA50418DA1DF1A3B611B415809FEEA32DA5041552D003FEE601B4156DFC5B063DA50418DA1DF1A3B611B4154B58D7694DA5041A6015CBA1E621B41703A835CC3DA504130211E5E90631B41F9914495EEDA50413521A8D181651B4118609B7714DB50412C0315F7DF671B411800D48E33DB5041B17B2A83936A1B41112210A94ADB50416FCD82E2816D1B4113E807E358DB5041552D003F8E701B4156DFC5B05DDB50413A8D7D9B9A731B4113E807E358DB5041F9DED5FA88761B41112210A94ADB50417E57EB863C791B411800D48E33DB5041753958AC9A7B1B4118609B7714DB50417A39E21F8C7D1B41F9914495EEDA50410459A4C3FD7E1B41703A835CC3DA50411DB92063E17F1B4154B58D7694DA5041552D003F2E801B4156DFC5B063DA5041'::geometry) AND _st_contains('01030000208A7F00000100000021000000552D003F2E801B4156DFC5B063DA50411DB92063E17F1B415809FEEA32DA50410459A4C3FD7E1B413C84080504DA50417A39E21F8C7D1B41B32C47CCD8D95041753958AC9A7B1B41945EF0E9B2D950417E57EB863C791B4194BEB7D293D95041F9DED5FA88761B419B9C7BB87CD950413B8D7D9B9A731B4199D6837E6ED95041552D003F8E701B4156DFC5B069D9504170CD82E2816D1B4199D6837E6ED95041B17B2A83936A1B419B9C7BB87CD950412C0315F7DF671B4194BEB7D293D950413521A8D181651B41945EF0E9B2D9504130211E5E90631B41B32C47CCD8D95041A6015CBA1E621B413C84080504DA50418DA1DF1A3B611B415809FEEA32DA5041552D003FEE601B4156DFC5B063DA50418DA1DF1A3B611B4154B58D7694DA5041A6015CBA1E621B41703A835CC3DA504130211E5E90631B41F9914495EEDA50413521A8D181651B4118609B7714DB50412C0315F7DF671B411800D48E33DB5041B17B2A83936A1B41112210A94ADB50416FCD82E2816D1B4113E807E358DB5041552D003F8E701B4156DFC5B05DDB50413A8D7D9B9A731B4113E807E358DB5041F9DED5FA88761B41112210A94ADB50417E57EB863C791B411800D48E33DB5041753958AC9A7B1B4118609B7714DB50417A39E21F8C7D1B41F9914495EEDA50410459A4C3FD7E1B41703A835CC3DA50411DB92063E17F1B4154B58D7694DA5041552D003F2E801B4156DFC5B063DA5041'::geometry, st_transform(beijingpoi.geom, 32650)))
Rows Removed by Filter: 1231452
Buffers: shared hit=3592 read=73346
Total runtime: 4578.392 ms

可以看到，查询规划器遍历扫描了一遍，过滤了1231452条记录。可以尝试一下，加不加这个索引其实效果是一样的。想要使用索引查询，我们这里可以添加一个函数索引，也就是对st_transform添加索引，执行：

CREATE INDEX "idx1" ON "public"."beijingpoi" USING gist (st_transform(geom, 32650) "public"."gist_geometry_ops_2d");
1
再次执行查询：

Bitmap Heap Scan on public.beijingpoi (cost=9040.74..152287.75 rows=82643 width=28) (actual time=6.122..175.143 rows=8192 loops=1)
Output: name
Recheck Cond: (st_transform(beijingpoi.geom, 32650) && '01030000208A7F00000100000021000000552D003F2E801B4156DFC5B063DA50411DB92063E17F1B415809FEEA32DA50410459A4C3FD7E1B413C84080504DA50417A39E21F8C7D1B41B32C47CCD8D95041753958AC9A7B1B41945EF0E9B2D950417E57EB863C791B4194BEB7D293D95041F9DED5FA88761B419B9C7BB87CD950413B8D7D9B9A731B4199D6837E6ED95041552D003F8E701B4156DFC5B069D9504170CD82E2816D1B4199D6837E6ED95041B17B2A83936A1B419B9C7BB87CD950412C0315F7DF671B4194BEB7D293D950413521A8D181651B41945EF0E9B2D9504130211E5E90631B41B32C47CCD8D95041A6015CBA1E621B413C84080504DA50418DA1DF1A3B611B415809FEEA32DA5041552D003FEE601B4156DFC5B063DA50418DA1DF1A3B611B4154B58D7694DA5041A6015CBA1E621B41703A835CC3DA504130211E5E90631B41F9914495EEDA50413521A8D181651B4118609B7714DB50412C0315F7DF671B411800D48E33DB5041B17B2A83936A1B41112210A94ADB50416FCD82E2816D1B4113E807E358DB5041552D003F8E701B4156DFC5B05DDB50413A8D7D9B9A731B4113E807E358DB5041F9DED5FA88761B41112210A94ADB50417E57EB863C791B411800D48E33DB5041753958AC9A7B1B4118609B7714DB50417A39E21F8C7D1B41F9914495EEDA50410459A4C3FD7E1B41703A835CC3DA50411DB92063E17F1B4154B58D7694DA5041552D003F2E801B4156DFC5B063DA5041'::geometry)
Filter: _st_contains('01030000208A7F00000100000021000000552D003F2E801B4156DFC5B063DA50411DB92063E17F1B415809FEEA32DA50410459A4C3FD7E1B413C84080504DA50417A39E21F8C7D1B41B32C47CCD8D95041753958AC9A7B1B41945EF0E9B2D950417E57EB863C791B4194BEB7D293D95041F9DED5FA88761B419B9C7BB87CD950413B8D7D9B9A731B4199D6837E6ED95041552D003F8E701B4156DFC5B069D9504170CD82E2816D1B4199D6837E6ED95041B17B2A83936A1B419B9C7BB87CD950412C0315F7DF671B4194BEB7D293D950413521A8D181651B41945EF0E9B2D9504130211E5E90631B41B32C47CCD8D95041A6015CBA1E621B413C84080504DA50418DA1DF1A3B611B415809FEEA32DA5041552D003FEE601B4156DFC5B063DA50418DA1DF1A3B611B4154B58D7694DA5041A6015CBA1E621B41703A835CC3DA504130211E5E90631B41F9914495EEDA50413521A8D181651B4118609B7714DB50412C0315F7DF671B411800D48E33DB5041B17B2A83936A1B41112210A94ADB50416FCD82E2816D1B4113E807E358DB5041552D003F8E701B4156DFC5B05DDB50413A8D7D9B9A731B4113E807E358DB5041F9DED5FA88761B41112210A94ADB50417E57EB863C791B411800D48E33DB5041753958AC9A7B1B4118609B7714DB50417A39E21F8C7D1B41F9914495EEDA50410459A4C3FD7E1B41703A835CC3DA50411DB92063E17F1B4154B58D7694DA5041552D003F2E801B4156DFC5B063DA5041'::geometry, st_transform(beijingpoi.geom, 32650))
Rows Removed by Filter: 1254
Buffers: shared hit=50 read=7974 written=2972
-> Bitmap Index Scan on idx1 (cost=0.00..9020.08 rows=247929 width=0) (actual time=3.898..3.898 rows=9446 loops=1)
Index Cond: (st_transform(beijingpoi.geom, 32650) && '01030000208A7F00000100000021000000552D003F2E801B4156DFC5B063DA50411DB92063E17F1B415809FEEA32DA50410459A4C3FD7E1B413C84080504DA50417A39E21F8C7D1B41B32C47CCD8D95041753958AC9A7B1B41945EF0E9B2D950417E57EB863C791B4194BEB7D293D95041F9DED5FA88761B419B9C7BB87CD950413B8D7D9B9A731B4199D6837E6ED95041552D003F8E701B4156DFC5B069D9504170CD82E2816D1B4199D6837E6ED95041B17B2A83936A1B419B9C7BB87CD950412C0315F7DF671B4194BEB7D293D950413521A8D181651B41945EF0E9B2D9504130211E5E90631B41B32C47CCD8D95041A6015CBA1E621B413C84080504DA50418DA1DF1A3B611B415809FEEA32DA5041552D003FEE601B4156DFC5B063DA50418DA1DF1A3B611B4154B58D7694DA5041A6015CBA1E621B41703A835CC3DA504130211E5E90631B41F9914495EEDA50413521A8D181651B4118609B7714DB50412C0315F7DF671B411800D48E33DB5041B17B2A83936A1B41112210A94ADB50416FCD82E2816D1B4113E807E358DB5041552D003F8E701B4156DFC5B05DDB50413A8D7D9B9A731B4113E807E358DB5041F9DED5FA88761B41112210A94ADB50417E57EB863C791B411800D48E33DB5041753958AC9A7B1B4118609B7714DB50417A39E21F8C7D1B41F9914495EEDA50410459A4C3FD7E1B41703A835CC3DA50411DB92063E17F1B4154B58D7694DA5041552D003F2E801B4156DFC5B063DA5041'::geometry)
Buffers: shared hit=44 read=57
Total runtime: 175.893 ms

可以看到，使用了索引扫描，只过滤了1254条记录，耗时仅仅0.17秒，性能大幅提升了。可以看到Recheck里有一个&&，其实此时就是只对索引的bounding box范围进行了过滤，自然可以大幅减少过滤条数了。
————————————————
版权声明：本文为优快云博主「mmll0927」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.youkuaiyun.com/mmll0927/article/details/89574971