[GIS] Calculating distance of point from arc/polyline using PostGIS

postgisproximity

Using PostgreSQL/PostGIS, I am trying to query the database for the closest arc to a particular GPS point. Each Arc is made up of two nodes that are GPS points on a map. Is there a way I could query this using Postgres and also get the distance of the point from the arc?

I have been trying to find the road to which a GPS point is closest to. I have a postGIS database which i loaded with the map of the road network.
According to a previous question asked here Calculate distance of point from an arc/polyline this was to be done be creating a straight line then look for the shortest distance form this line. The road network is however not straight thus this wont work.

Anybody with a solution?

Best Answer

SELECT arc_id 
FROM road_table 
ORDER BY ST_Distance(road_table.geom, 'gps-point in wkt') 
LIMIT 1;

Since PostGIS 1.5 you also have the option of using ST_ClosestPoint.

Some discussions of how to use it can be found here:

http://blog.jordogskog.no/2010/02/07/how-to-use-the-new-distance-related-functions-in-postgis-part1/

About ST_ClosestPoint you can read more in the PostGIS documentation: http://postgis.net/docs/ST_ClosestPoint.html

As I understand your query correct you don't have any line ready but a list of points in a table.

What you need to do then is writing a query that creates lines from those points and use ST_ClosestPoint on those lines. You do it all in one query. To do that you can design your query in several ways and approaches depending on in more detail what you want to get. If it is just the distance you want, then forget about ST_ClosestPoint then use ST_Distance to get the distance. If you just want the closest Point on the edge and/or the distance, then you can build the whole linestring with ST_MakeLine If you also want to find the points defining the edge that is closest it is probably easiest to make a self join that builds lines from each opint pair in the table instead and use ST_Distance and /or ST_ClosestPoint on that two point lines.

This I guess looks quite messy, but that is because there is a lot of possibilities, what you actually want to get.

I think edge is a more correct word than arc.

Ok, from your comment I guess that what you want is a query that self-joins to create edges and returns the closest edge and distance. You will need a integer field to make it work like this with integer values ordered as the gps-points and without holes in the series. I call id gid here.

Then you can run something like:

SELECT dist, the_edge FROM
(SELECT ST_Distance(e.the_edge, 'PUT YOUR GPS_POINT AS WKT HERE') as dist, the_edge FROM
(SELECT ST_MakeLine(a.geom, b.geom) AS the_edge FROM
point_table a INNER JOIN point_table b ON b.gid=(a.gid+1)) e) s
ORDER BY dist LIMIT 1;

a.geom references geom in the table that I have put the alias a on. I shouldn't have left out the "AS" key-word, then it looks like this:

SELECT dist, the_edge FROM
(SELECT ST_Distance(e.the_edge, 'PUT YOUR GPS_POINT AS WKT HERE') as dist, the_edge FROM
(SELECT ST_MakeLine(a.geom, b.geom) AS the_edge FROM
point_table AS a INNER JOIN point_table AS b ON b.gid=(a.gid+1)) e) s
ORDER BY dist LIMIT 1;

To get a short intro to sql in general you can test the tutorial on PostGISonline

To see some spatial examples you can try: mixed examples

Related Solutions

[GIS] Closest line to a point using PostGIS

You can pass in the point XY to ST_DWithin to return the ID of the closest road, as well as the distance to that road:

SELECT DISTINCT ON (p.id) p.id, r.id, ST_Distance(ST_SetSRID(ST_Makepoint(p.x, p.y),4326), r.geom)
FROM points p
    LEFT JOIN roads r ON ST_DWithin(ST_SetSRID(ST_Makepoint(p.x, p.y),4326), r.geom, 30.48)
ORDER BY p.id, ST_Distance(ST_SetSRID(ST_Makepoint(p.x, p.y),4326), r.geom);

PostGIS pgrouting – How to Calculate Shortest Route by Road from One Origin Point to Multiple Destinations

You can make use of the dijkstraNear function recently added to pgRouting for one-to-many, many-to-one or many-to-many queries. I have written up a PL/pgSQL function based on some logic of the other thread you already posted. Note: you will have to run all steps listed up top, i.e. a topology will be required. I have commented inline for a better understanding.

CREATE OR REPLACE FUNCTION compute_many_to_many()
RETURNS SETOF RECORD AS $$
DECLARE
  
  route record;
  origins BIGINT[];
  origins_text TEXT[];
  destinations BIGINT[];
  destinations_text TEXT[];
 
BEGIN
  
    -- this finds the closest nodes to your origin points
    -- and saves both the nodes and them to an array
    -- the ids array will be input for the many-to-many query
    SELECT INTO origins_text, origins
    array_agg(origins.gid)::TEXT[] AS origins,
    array_agg(vertices.id)::BIGINT[] as ids
    FROM
      origin AS origins
    CROSS JOIN LATERAL
      (SELECT id, the_geom
        FROM road_segments_vertices_pgr
        ORDER BY origins.geom <-> the_geom
        LIMIT 1) AS vertices;
  
    -- does the same for our destinations and is used in our
    -- many-to-many query
    SELECT
    INTO destinations_text, destinations
    array_agg(destinations.gid)::TEXT[] AS destinations,
    array_agg(vertices.id)::BIGINT[] as ids
    FROM
      destination AS destinations
    CROSS JOIN LATERAL
      (SELECT id, the_geom
        FROM road_segments_vertices_pgr
        ORDER BY destinations.geom <-> the_geom
        LIMIT 1) AS vertices;
  
    -- RAISE NOTICE '% % % %', origins, origins_text, destinations, destinations_text;

    FOR route in
        SELECT 
        SUM(dn.cost)::DOUBLE PRECISION AS cost,
        start_vid::INT AS origin_node,
        -- we want the origin as text again hence the index lookup
        origins_text[array_position(origins, start_vid)] AS origin, 
        end_vid::INT AS destination_node,
        -- we want the destination as text again hence the index lookup
        destinations_text[array_position(destinations, end_vid)] AS destination,  
        -- the edge sequence
        array_agg(edge) AS edge_ids,
        -- the node sequence
        array_agg(node) AS node_ids,
        -- we collect the geometries into a multilinestring
        ST_AsText(
          ST_Collect(et.geom)
        ) AS geometries
        -- https://docs.pgrouting.org/3.2/en/pgr_dijkstraNear.html
        -- we set cap to -1 to get all routes
        FROM pgr_dijkstraNear( 
          'SELECT gid AS id, source, target, ST_Length(geom) AS cost, ST_Length(geom) AS reverse_cost FROM road_segments'::TEXT,
            origins,
            destinations,
            directed => false,
            global => true,
            cap => -1
        ) AS dn
        -- join with the road segments again to derive the geometry
        JOIN road_segments et 
        ON dn.edge = et.gid
        -- we want to group the individual steps of the route together to aggregate
        -- the information required
        GROUP BY end_vid, start_vid
    LOOP
        RETURN NEXT route;
    END LOOP;


END $$ LANGUAGE 'plpgsql';

This function returns a set of records, so you can call it this way:

SELECT * FROM compute_many_to_many() AS (
  cost DOUBLE PRECISION, 
  origin_node INT, 
  origin_text TEXT, 
  destination_node INT, 
  destination_text TEXT, 
  edge_ids BIGINT[], 
  node_ids BIGINT[], 
  geometries TEXT
);

This will output records looking like:

cost             | 0.0018078542184695255
origin_node      | 19
origin_text      | ORIGIN2
destination_node | 1
destination_text | DEST2
edge_ids         | {21,22,23,19,18,16,17}
node_ids         | {19,20,21,17,16,14,15}
geometries       | MULTILINESTRING((-1.147244200155751 51.35313404352985,-1.147034164239104 51.35284477388327),(-1.147034164239104 51.35284477388327,-1.14686086224574 51.352897463819
49),(-1.14686086224574 51.35289746381949,-1.146687559857114 51.352950153497005),(-1.146518014664603 51.35280055232736,-1.146687559857114 51.352950153497005),(-1.146348470573612 51.35
2650950913706,-1.146518014664603 51.35280055232736),(-1.146348470573612 51.352650950913706,-1.146051953184763 51.35276568540412),(-1.146051953184763 51.35276568540412,-1.145755434320
116 51.35288041913489))

### Update to accommodate OP expectation of mappings

Different to the above approach, this will conduct a set of one-to-many calls as not all combinations are required. It basically uses the same logic as before but adds one extra loop iterating over all origin points.

SELECT * FROM compute_many_one_to_many() AS (
  cost DOUBLE PRECISION, 
  origin_node INT, 
  origin_text TEXT, 
  destination_node INT, 
  destination_text TEXT, 
  edge_ids BIGINT[], 
  node_ids BIGINT[], 
  geometries TEXT
);

CREATE OR REPLACE FUNCTION compute_many_one_to_many()
RETURNS SETOF RECORD AS $$
DECLARE
  route record;
  
  ogn_id TEXT;
  ogn_nn_node_id BIGINT;

  destinations BIGINT[];
  destinations_text TEXT[];
  
BEGIN
    
  -- in a loop of distinct origins
  -- this finds the closest node to each
  -- this is the "one"
  FOR ogn_id, ogn_nn_node_id IN 
    SELECT 
      DISTINCT(gid),
      vertices.id::BIGINT   
    FROM origin
    CROSS JOIN LATERAL
      (SELECT id, the_geom
        FROM road_segments_vertices_pgr
        ORDER BY origin.geom <-> the_geom
        LIMIT 1) AS vertices

  LOOP

    -- does the same for our destinations 
    -- and is used as the "many"
    SELECT
    INTO destinations_text, destinations
    array_agg(d.gid)::TEXT[] AS destinations,
    array_agg(vertices.id)::BIGINT[] as ids
    FROM
      destination AS d
    CROSS JOIN LATERAL
      (SELECT id, the_geom
        FROM road_segments_vertices_pgr
        ORDER BY d.geom <-> the_geom
        LIMIT 1) AS vertices
    -- this makes sure of the mappings
    WHERE d.origin_id = ogn_id;


    -- RAISE NOTICE '% % % %', ogn_id, ogn_nn_node_id, destinations, destinations_text;

    FOR route IN
        SELECT 
        SUM(dn.cost)::DOUBLE PRECISION AS cost,
        start_vid::INT AS origin_node,
        ogn_id AS origin, 
        end_vid::INT AS destination_node,
        -- we want the destination as text again hence the index lookup
        destinations_text[array_position(destinations, end_vid)] AS destination,  
        -- the edge sequence
        array_agg(edge) AS edge_ids,
        -- the node sequence
        array_agg(node) AS node_ids,
        -- we collect the geometries into a multilinestring
        ST_AsText(
          ST_Collect(et.geom)
        ) AS geometries
        -- https://docs.pgrouting.org/3.2/en/pgr_dijkstraNear.html
        -- we set cap to -1 to get all routes
        FROM pgr_dijkstranear( 
          'SELECT gid AS id, source, target, ST_Length(geom) AS cost, ST_Length(geom) AS reverse_cost FROM road_segments'::TEXT,
            ogn_nn_node_id,
            destinations,
            directed => false,
            cap => -1
        ) AS dn
        -- join with the road segments again to derive the geometry
        JOIN road_segments et 
        ON dn.edge = et.gid
        -- we want to group the individual steps of the route together to aggregate
        -- the information required
        GROUP BY end_vid, start_vid
    LOOP
        RETURN NEXT route;
    END LOOP;

  END LOOP;

END $$ LANGUAGE 'plpgsql';

Best Answer

Related Solutions

[GIS] Closest line to a point using PostGIS

PostGIS pgrouting – How to Calculate Shortest Route by Road from One Origin Point to Multiple Destinations

Related Question