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

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?

• sidenote: allthough I also haven't covered that in my answer, for large tables with proper index to be distance ordered performantly, it is advisable to include a radius search (i.e. `WHERE ST_DWithin(...)`) with a decent value to preselect only those geometries that are within range to the point. Feb 22, 2018 at 11:42

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

• This worked like a charm. Feb 23, 2018 at 4:09
• @MarcosRorizJunior as a sidenote: this works only for one pre-selected point <P>. if you want that query dynamically create respective projected points on multiple lines from an input point table, you could use a `LATERAL JOIN`. Mar 2, 2018 at 12:47

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

--2/3 get Small line segment at right 90 degress to line, crossing at closest point to input point
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