5

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 .

3
  • With "projection", do you mean "find the spatial reference" or "find the nearest point projected on the road network"?
    – Mike T
    Apr 15, 2012 at 21:40
  • Thanks for your reply Mike .I think the second one ! but what do you mean by spatial reference?
    – john
    Apr 16, 2012 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, 2012 at 22:38

1 Answer 1

9

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(
    ST_Line_Interpolate_Point(road, ST_Line_Locate_Point(road, poi))) AS projected_poi

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

3
  • 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, 2012 at 13:38
  • Can we do the same with stored procedures in pl/pgsql and get the geometry mike? Jun 4, 2012 at 21:03
  • @OpenGIStech you can do pretty much anything with PL/pgSQL
    – Mike T
    Jun 4, 2012 at 21:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.