postgresql – PostGIS – 路由匹配解决方案

概述我们正在构建一个应用程序,我是一名司机,我从A点到B点旅行.在我的路上,我可以找到以相同路线旅行的乘客. 我们正在使用PostgreSQL和PostGIS扩展. 经过谷歌搜索后我发现我们可以使用线串来实现这一点.我不完全确定这种方法是否会成功. 假设我有源和目的地的坐标. var RouteCoordinates = [ {lat: 37.772, lng: -122.214}, 我们正在构建一个应用程序,我是一名司机,我从A点到B点旅行.在我的路上,我可以找到以相同路线旅行的乘客.

我们正在使用Postgresql和PostGIS扩展.

经过谷歌搜索后我发现我们可以使用线串来实现这一点.我不完全确定这种方法是否会成功.

假设我有源和目的地的坐标.

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}    ];

我需要将它存储为我的数据库中的线串.

如果一名乘客也在这条路线上行驶,但我们知道他的来源和目的地不会完全在我的线路中,但他们会在附近.
例如,半径约1km

正如您所看到的那样,我的源和目的地就是那条线.当我旅行时,我想挑选所有那些(源和目的地)靠近我的路线(特定半径范围内)的人

如果我想在特定半径范围内找到我的数据库中的特定位置,我将会这样查询

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

现在我可以实现我的解决方案,因为我刚接触postGIS有点令人困惑

>如何在DB中存储我的所有源和目标点

ANS：我需要使用此函数ST_Makeline转换为linestring,然后存储,对吧？

>如何根据我的要求查询,如上所述

能否请您了解如何实现这一目标.非常感谢您的帮助.谢谢

你的问题有几个问题：

I need to convert in to linestring using this function ST_Makeline and
then store,right?

是的,要将多个点合并为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)

How to query that based on my requirement as I have mentioned above

在linesTRING的每个点上创建一个缓冲区,并检查其中的其他几何形状.

首先,你必须使用ST_DumpPoints将你的路径linesTRING分成POINT …

db=# SELECT ST_AsText((ST_DumpPoints('linesTRING(-122.21 37.77,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))

如果你想知道这个缓冲区的形状,请阅读answer.

使用此查询,您可以检查另一个几何是否在此缓冲区内(ST_Within)：

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

要把它们放在一起,你可以使用CTE(又名WITH子句)并写下这样的东西：

WITH j AS (  SELECT    (ST_DumpPoints('linesTRING(-122.21 37.77,153.02 -27.46)'::GEOMETRY)).geom AS g)SELECT ID,geomFROM Seattle_StarbucksWHERE ST_Within(geom,ST_Buffer(j.g,'quad_segs=16'))

总结

以上是内存溢出为你收集整理的postgresql – PostGIS – 路由匹配解决方案全部内容，希望文章能够帮你解决postgresql – PostGIS – 路由匹配解决方案所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。