# How to find the nearest point projected on the road network?

I would like to know if there is a function at pgRouting that given a point (lon/lat) can give you the projection of that point to the road network .

Thanks in advance john

-
With "projection", do you mean "find the spatial reference" or "find the nearest point projected on the road network"? – Mike T Apr 15 '12 at 21:40
Thanks for your reply Mike .I think the second one ! but what do you mean by spatial reference? – john Apr 16 '12 at 17:48
spatial reference systems are things like UTM vs latitude/longitude vs Google Mercator, etc. Some folks call these "projections" – Mike T Apr 16 '12 at 22:38

There are linear referencing functions in PostGIS to help you out project the location of a point along a line. For example, if you have a road, and a point of interest (POI) that is along the side of the road, you can:

Here is the SQL to extract the interpolated POI on the road:

``````WITH data AS (
SELECT 'LINESTRING (50 40, 40 60, 50 90, 30 140)'::geometry AS road,
'POINT (60 110)'::geometry AS poi)

SELECT ST_AsText(

FROM data;
``````

Returns `POINT(44.4827586206897 103.793103448276)`, which is close to the POI, but projected on the road.

The tricky things you might run into is to locate the closest road LINESTRING and/or, your road network might be a MULTILINESTRING, which needs to be broken down into LINESTRINGs in order to work with the above. Also, linear referencing systems work best with projected (i.e., non-Lat/Lon) data, particularly if your data are far north/south from the equator.

Update An easier function that can use other geometries, including MULTILINESTRINGs is ST_ClosestPoint. For example, the closest POI on a road network is found using:

``````WITH data AS (
SELECT 'MULTILINESTRING ((20 30, 40 70, 40 110),
(40 110, 70 160, 80 190),
(40 110, 25 118, 10 140))'::geometry AS road_network,
'POINT (40 130)'::geometry AS poi)

SELECT ST_AsText(ST_ClosestPoint(road_network, poi)) AS closest_poi

FROM data;
``````

(Also, as a side note, I'm using a CTE or "WITH" query to supply data. Your query only needs to use the "SELECT" part.)

-
Thanks a lot I 'll give it a try .My road network is MULTILINESTRING (i used osm2pgrouting to import my data to the database)is there an easy way to break my MULTILINESTRING to LINESTRING (I will google it but if you know you will spare me some time :) )? – john Apr 18 '12 at 13:38
Can we do the same with stored procedures in pl/pgsql and get the geometry mike? – OPENGISTECH Jun 4 '12 at 21:03
@OpenGIStech you can do pretty much anything with PL/pgSQL – Mike T Jun 4 '12 at 21:53