在指定的距离限制和顺序中沿线串找到最近的点 [英] Find the nearest points along the linestring in specified distance limit and order

问题描述

我有这样的问题，如果有人可以帮助我，我会很好. 我有带有GIST索引的积分表.这些点不会随时间变化.

I have such problem and I would be nice If somebody can help me. I have points table with GIST index. Those points don't change in time.

我想获取一些给定线串附近的点. 示例:想象一下，线串是道路，点是沿道路的poi. 我想获取距指定道路5公里的poi.我想以正确的顺序(沿道路的行驶顺序)获取这些pois.看图片:

I would like to fetch points that are near some given linestring. Example: Imagine that linestring is the road and points are poi along the road. I would like to fetch poi's that are in 5 km distance from the given road. I would like to fetch those pois in correct order (driving order along the road). Look at the image:

对于从1点到5点的给定道路，我想获取距道路最大5 km的POI，并按顺序从1点到5点沿道路.所以结果应该是:

For given road from point 1 to 5 i would like to fetch POIs that is in 5 km max from the road and in order from point 1 to 5 along the road. So the result should be:

POI_ID
1
5
6
8
9
10
12
13

这应该告诉我在沿着道路旅行期间可以以最低的费用访问哪些POI.

This should tell me what POI I can visit during traveling along the road with minimum cost.

有人对Postgres和Postgis怎么做有想法吗?

Does anybody have some ideas how to do it with postgres and postgis?

推荐答案

假设您有几何列geom，它们在表road(LINESTRING)和poi(POINT)中使用仪表的预计SRID，您的查询在距离公路5公里(id = 123)5公里之内的所有POI都应为:

Assuming you have geometry columns geom that use a projected SRID of meters in tables road (LINESTRING) and poi (POINT), your query to find all POIs within 5 km of road (where id = 123) should be something like:

SELECT poi.*, ST_Distance(road.geom, poi.geom)/1000.0 AS distance_km
FROM road, poi
WHERE road.id = 123 AND ST_DWithin(road.geom, poi.geom, 5000.0)
ORDER BY ST_LineLocatePoint(road.geom, poi.geom),
         ST_Distance(road.geom, poi.geom);

带有ST_LineLocatePoint的第一个ORDER部分使用0.0到1.0之间的分数，具体取决于该点沿LINESTRING的位置.如果道路的方向是错误的方向"，请附加DESC以颠倒顺序.第二个ORDER部分基于距离，如果该点稍微超过LINESTRING的起点/终点(ST_LineLocatePoint分别返回0.0或1.0)，则可以使用该距离.

The first ORDER part with ST_LineLocatePoint uses a fraction between 0.0 and 1.0, depending where the point is along the LINESTRING. If the direction of the road is going "the wrong way", then append DESC to reverse the order. The second ORDER part is based on distance, which could be used if the point is slightly past the start/end of the LINESTRING (where ST_LineLocatePoint would return 0.0 or 1.0, respectively).

如果您将geography类型与经度/纬度值配合使用，此查询也可能会起作用，因为它会自动计算米-而不是度.查看文档以了解更多信息:

This query might also work if you are using the geography type with Long/Latitude values, since it automagically calculates metres -- not degrees. Check out docs for more:

• ST_Distance
• ST_DWithin
• ST_LineLocatePoint (或对于早期版本的PostGIS，则为ST_Line_Locate_Point)

• ST_Distance
• ST_DWithin
• ST_LineLocatePoint (or ST_Line_Locate_Point for older versions of PostGIS)

这篇关于在指定的距离限制和顺序中沿线串找到最近的点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！