Projecting GPS point to point in closest pgrouting road (map matching)?

Question

I'm quite new to PostGIS and pgRouting.

I'm interested in doing a simple map matching. Precisely, finding the closest point in a given OSM road network.

I’ve successfully imported the OSM network with osm2pgrouting. After some googling and lots of read I was able to find how to get the closest road vertex from a given point P (-49.239099 -16.667802) using the following query:

SELECT * FROM ways_vertices_pgr ORDER BY the_geom <->
ST_GeometryFromText('POINT(-49.239099 -16.667802)',4326) LIMIT 1

However, what I'm interested in on the project point of P that should be located on the closest edge in the road network.

How can I do that?

Answer 1

Use your query structure to get the closest edge to P and project a closest point P' from P on the edge with ST_ClosestPoint:

SELECT edge.id,
       ST_ClosestPoint(edge.geom, <P>) AS "P'"
FROM (
    SELECT <edges_id> AS id,
           <edges_geom> AS geom
    FROM <edges_table>
    ORDER BY ST_Distance(<P>, <edges_geom>)
    LIMIT 1
) AS edge

Note: there might be better solutions if you are actually interested in the distance along the line to the line's node you need as an entry point for routing.

UPDATE:
ST_Distance in conjunction with LatLon (e.g. EPSG:4326) is a tricky business. While it might work here, with sufficiently small distances, do at least not use it to derive metric distances (this does equally apply to the KNN operators, e.g. <->). For a more robust and precise solution, consider casting to geography prior to the calculations (e.g. ST_Distance(<P>::geography, <edges_geom>::geography))

Answer 2

I've recently worked on a similar problem as this.

My methodology was as follows:

1. Find nearest line to the input point(s)
2. Get a small subsection of this line
3. Rotate the subsection 90 degrees around a point on the line closest to the input point (allow us to cut and or create point at intersection)

To this methodology we can add a way to cut get a point when these two lines intersect... For that we can use some thing like this:

ST_CollectionExtract(ST_Intersection(a.geom, b.geom), 1)

I was working with a table of many input points but it should be fine for just one point (we need a geom and id column in the netowrk and point tables).

Full code here:

--1 Closest line to point(s)
CREATE TABLE schema.test_closestline AS
            SELECT pid ,lid, distance_m, a.geom 
            FROM
                (SELECT q.id pid, a.id lid, ROUND(ST_Distance(q.geom, a.geom)::NUMERIC, 2) as distance_m, 
            ROW_NUMBER() OVER (PARTITION BY q.id ORDER BY ST_DISTANCE(a.geom,q.geom)) as row_number,
            a.geom
                FROM schema.road_network a
                INNER JOIN schema.input_points q ON
                ST_DWITHIN(a.geom,q.geom,100) -- use a sensible distance to restrict the result set without losing records 
                ) a
            WHERE a.row_number = 1

DROP TABLE IF EXISTS schema.test_blade_rotate;

--2/3 get Small line segment at right 90 degress to line, crossing at closest point to input point
CREATE TABLE schema.test_blade_rotate AS   
SELECT
line.id, ST_Rotate(line.geom, 1.5708, ST_Centroid(line.geom)) geom --1.5708 radians = 90 degrees
FROM
   (
   SELECT paired.pid id, 
CASE 
    WHEN  ( round((ST_LineLocatePoint(paired.lgeom,paired.pgeom))::numeric,2) + 1 = 1 ) THEN  ST_LineSubstring(paired.lgeom, round((ST_LineLocatePoint(paired.lgeom,paired.pgeom))::numeric,2), round((ST_LineLocatePoint(paired.lgeom,paired.pgeom))::numeric,2)+ 0.01)
    WHEN  ( round((ST_LineLocatePoint(paired.lgeom,paired.pgeom))::numeric,2) + 1 = 2) THEN  ST_LineSubstring(paired.lgeom, round((ST_LineLocatePoint(paired.lgeom,paired.pgeom))::numeric,2) - 0.01, round((ST_LineLocatePoint(paired.lgeom,paired.pgeom))::numeric,2))
    ELSE ST_LineSubstring(paired.lgeom,(round((ST_LineLocatePoint(paired.lgeom,paired.pgeom))::numeric,2) - 0.01), (round((ST_LineLocatePoint(paired.lgeom,paired.pgeom))::numeric,2)+0.01))
END AS geom 
   FROM 
        (SELECT line.pid, (ST_DUMP(line.geom)).geom lgeom, (ST_DUMP(point.geom)).geom pgeom
        FROM 
        schema.test_closestline as line,
        schema.input_points as point
        WHERE line.pid = point.id) as paired
   ) as line;

--4 POINT FROM INTERSECT OF TWO LINES:
CREATE TABLE schema.closest_point AS   
SELECT 
b.id, ST_CollectionExtract(ST_Intersection(a.geom, b.geom), 1) as geom        
FROM  schema.road_network a, schema.test_blade_rotate b

Answer 3

Use a Buffer to find nearest line string point of intersect. The point on the line path is then easily identified.