我试图解决使用PostGIS查找n个最近邻居的问题:
起点:
- 表geoname和geonames(来自
- 添加了GeometryColumn geom和
- 用值填充geom:更新geoname
- 创建geom的主要索引(创建
- 为geonameid创建了主键唯一的BTREE索引
问题:
可能的解决方案:
受http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor的启发,我try 了以下问题:
"SELECT start.asciiname, ende.asciiname, distance_sphere(start.geom, ende.geom) as distance " +
"FROM geoname As start, geoname As ende WHERE start.geonameid = 2950159 AND start.geonameid <> ende.geonameid " +
"AND ST_DWithin(start.geom, ende.geom, 300) order by distance limit 5"
处理时间:约60秒
还try 了一种基于扩展的方法:
"SELECT start.asciiname, ende.asciiname, distance_sphere(start.geom, ende.geom) as distance " +
"FROM geoname As start, geoname As ende WHERE start.geonameid = 2950159 AND start.geonameid <> ende.geonameid AND expand(start.geom, 300) && ende.geom " +
"order by distance limit 5"
处理时间:约120秒
The intended application is some kind of autocomplete. So, any approach taking longer than >1s is not applicable. Is it generally possible to achieve a response time of <1s with PostGIS?