PostGIS - 路线匹配解决方案

Question

我们正在开发一个应用程序，我是司机，从A点到B点旅行。在路上，我可以找到同路的乘客。

我们使用带有PostGIS扩展的PostgreSQL。

经过大量搜索后，我发现我们可以使用linestring来实现这一目标。但我不完全确定这种方法是否可行。

假设我拥有起点和终点的坐标。

    var RouteCoordinates = [
      {lat: 37.772, lng: -122.214},
      {lat: 21.291, lng: -157.821},
      {lat: -18.142, lng: 178.431},
      {lat: -27.467, lng: 153.027}
    ];

我需要将这个存储为linestring在我的数据库中。

存储后，如果乘客也沿着这条路线前往，但我们知道他的起点和终点不会恰好在我的线路上，但它们将在附近。例如，在1公里半径内

如您所见，我的起点和终点就是那条线。当我旅行时，我想挑选所有那些（起点和终点）靠近我的路线（在特定半径内）的人

如果我想在我的DB中找到特定半径内的特定位置，我会查询如下：

SELECT id, name, address, geom  
FROM Seattle_Starbucks  
WHERE ST_DWithin(geom, ST_MakePoint(-122.325959,47.625138)::geography, 1000);

现在我可以实现我的解决方案，因为我对postGIS很陌生，所以有点困惑

1. 如何在数据库中存储所有的源点和目标点

答案：我需要使用ST_MakeLine函数将其转换为linestring，然后进行存储，对吗？

2. 如何基于我上面提到的要求进行查询

请问您能给我一些见解吗？非常感谢您的帮助。谢谢。

- sdg

---

这与 Elixir 和 Ecto 有什么关系？你有原始的 SQL 查询可以转换成 Ecto 吗？你应该把这个问题分成两部分：1. 原始的 SQL 查询；2. 将它转换成 Ecto 格式。 - denis.peplin

你有研究过pgrouting吗？如果我理解你的问题正确，你不仅需要创建一条线路，而且当你选择另一个客户时，该线路还必须得到更新，这将非常低效。我很好奇，为什么你一开始就要使用点而不是道路网络呢？ - jimmu

如果您不介意分享，您后来是如何解决这个问题的？ - Paul Okeke

Answer 1

关于你的问题，我的一些想法：

我需要使用函数ST_MakeLine将其转换为linestring并存储，是吗？

是的，要将多个点合并成LINESTRING，可以使用ST_MakeLine函数：

SELECT ST_AsText( 
       ST_MakeLine(ARRAY[ST_MakePoint(-122.21,37.77),
                         ST_MakePoint(-157.82,21.29),
                         ST_MakePoint(178.43,-18.14),
                         ST_MakePoint(153.02,-27.46)]));

                              st_astext                              
---------------------------------------------------------------------
 LINESTRING(-122.21 37.77,-157.82 21.29,178.43 -18.14,153.02 -27.46)
(1 Zeile)

如何根据我上面提到的要求进行查询

在您的LINESTRING每个点上创建一个缓冲区，并检查其他几何图形是否在其内部。

首先，您必须使用ST_DumpPoints将您的路由LINESTRING分割成POINT。

db=# SELECT ST_AsText((ST_DumpPoints('LINESTRING(-122.21 37.77,-157.82 21.29,178.43 -18.14,153.02 -27.46)'::GEOMETRY)).geom);
      st_astext       
----------------------
 POINT(-122.21 37.77)
 POINT(-157.82 21.29)
 POINT(178.43 -18.14)
 POINT(153.02 -27.46)
(4 Zeilen)

然后使用ST_Buffer创建每个点周围的缓冲区。ST_Buffer返回一个几何图形，表示点（或任何其他几何类型）周围的区域。例如，如果我没有交换x和y，则将路线的第一个点（位于旧金山某个地方）POINT(-122.21 37.76):

db=# SELECT ST_AsText(                                                         
       ST_Buffer('POINT(-122.21 37.76)'::GEOMETRY,0.0001, 'quad_segs=16'));

返回此几何图形：

POLYGON((-122.2099 37.76,-122.209900481527 37.759990198286,-122.209901921472 37.7599804909678,-122.209904305966 37.7599709715323,-122.209907612047 37.7599617316568,-122.209911807874 37.7599528603263,-122.209916853039 37.7599444429767,-122.209922698955 37.7599365606716,-122.209929289322 37.7599292893219,-122.209936560672 37.7599226989547,-122.209944442977 37.7599168530388,-122.209952860326 37.7599118078736,-122.209961731657 37.7599076120467,-122.209970971532 37.7599043059664,-122.209980490968 37.759901921472,-122.209990198286 37.7599004815273,-122.21 37.7599,-122.210009801714 37.7599004815273,-122.210019509032 37.759901921472,-122.210029028468 37.7599043059664,-122.210038268343 37.7599076120467,-122.210047139674 37.7599118078736,-122.210055557023 37.7599168530388,-122.210063439328 37.7599226989547,-122.210070710678 37.7599292893219,-122.210077301045 37.7599365606716,-122.210083146961 37.7599444429767,-122.210088192126 37.7599528603263,-122.210092387953 37.7599617316568,-122.210095694034 37.7599709715323,-122.210098078528 37.7599804909678,-122.210099518473 37.759990198286,-122.2101 37.76,-122.210099518473 37.760009801714,-122.210098078528 37.7600195090322,-122.210095694034 37.7600290284677,-122.210092387953 37.7600382683432,-122.210088192126 37.7600471396737,-122.210083146961 37.7600555570233,-122.210077301045 37.7600634393284,-122.210070710678 37.7600707106781,-122.210063439328 37.7600773010453,-122.210055557023 37.7600831469612,-122.210047139674 37.7600881921264,-122.210038268343 37.7600923879533,-122.210029028468 37.7600956940336,-122.210019509032 37.760098078528,-122.210009801714 37.7600995184727,-122.21 37.7601,-122.209990198286 37.7600995184727,-122.209980490968 37.760098078528,-122.209970971532 37.7600956940336,-122.209961731657 37.7600923879533,-122.209952860326 37.7600881921264,-122.209944442977 37.7600831469612,-122.209936560672 37.7600773010453,-122.209929289322 37.7600707106781,-122.209922698955 37.7600634393284,-122.209916853039 37.7600555570233,-122.209911807874 37.7600471396737,-122.209907612047 37.7600382683432,-122.209904305966 37.7600290284677,-122.209901921472 37.7600195090322,-122.209900481527 37.760009801714,-122.2099 37.76))

如果你想了解这个缓冲区的形状，请阅读答案。

使用此查询，您可以检查另一个几何对象是否在此缓冲区内（ST_Within）：

db=# SELECT
       ST_Within('POINT(-122.21 37.76)'::GEOMETRY,
          ST_Buffer('POINT(-122.21 37.76)'::GEOMETRY,0.0001, 'quad_segs=16'));
 st_within 
-----------
 t
(1 Zeile)

为了将所有内容整合在一起，您可以使用 CTE（也称为 WITH 子句），并编写以下内容：

WITH j AS (
  SELECT
    (ST_DumpPoints('LINESTRING(-122.21 37.77,-157.82 21.29,178.43 -18.14,153.02 -27.46)'::GEOMETRY)).geom AS g)
SELECT id, name, address, geom
FROM Seattle_Starbucks
WHERE ST_Within(geom,ST_Buffer(j.g,0.0001, 'quad_segs=16'))

- Jim Jones