PostGIS - Route matching solution

Question

We are building an application where I am a driver and i travel from point A to point B.On my way i can find passengers who travel in the same route.

We are using PostgreSQL with PostGIS extension.

After googling a lot i have found out that we can use linestring to achieve this.I am not fully sure whether this approach will work out.

Suppose I have co-ordinates of my source and destination.

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

I need to store this as linestring in my DB.

After stroring if a passenger is also going in this route but as we know his source and destination wont be exactly in my line string but they will be near. For example around 1km radius

As you can see my source and destination is that line. And as I am travelling I want to pick all those whose (source and destination) are near to my routes (within particular radius)

If I want to find particular location in my DB within particular radius I will be querying like this

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

Now I can achieve my solution as I am new to postGIS its little bit confusing

How to store all all my source and destination point in DB

ANS: I need to convert in to linestring using this function ST_MakeLine and then store, right?

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

Can you please give me the insight on how to achieve this. Your help is greatly appreciated. Thanks

How this relates to Elixir and Ecto? Do you have raw SQL query to convert to Ecto? You should probably split this question to two: 1: raw SQL query and 2: converting it into Ecto format. — denis.peplin, Sep 21 '17 at 08:09
Have you looked into pgrouting? If I your understand question correctly, you would not only have to create a line once, but that line will have to get updated once you pick another customer which is going to be very inefficient. I'm curious, why are you working with points instead of road-network to begin with? — timmur, Sep 26 '17 at 04:45
How did you later solve this issue if you don't mind sharing? — Paul Okeke, Feb 26 '20 at 19:07

Jim Jones · Answer 1 · 2018-05-09T09:25:23.377

A few thougths on your question:

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

Yes, to merge multiple points into a LINESTRING you can use 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

Create a buffer on each point of your LINESTRING and check wether other geometries are inside of it.

First, you have to split your rout LINESTRING into POINTs using ST_DumpPoints ...

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)

.. and then use ST_Buffer to create a buffer around each point. ST_Buffer returns a geometry with the area surrounding your point (or any other geometry type). For instance, taking the first point of your route (if I didn't switch x and y, it's somewhere in San Francisco) POINT(-122.21 37.76):

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

Returns this geometry:

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))

If you're wondering about the shape of this buffer, please read this answer.

And using this query you can check if another geometry is inside of this buffer (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)

To put it all together, you can use a CTE (aka WITH clause) and write something like this:

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'))

PostGIS - Route matching solution

1 Answers1