Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question
    
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
add comment

1 Answer

up vote 5 down vote accepted

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

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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