google mapslinepostgisrouting
I have a set of road conditions reported for small segments of road, and I am hoping to determine the specific subset of each segment which intersect with a route along the road so that I can draw the intersection of the route and road conditions on a map.
Is there a way to determine the specific points of intersection of two polylines in PostGIS in a way suitable for plotting on a Google Map?
I am new to PostGIS so please accept my apologize if I am getting any terminology wrong.
This is a PostGIS query that I just tried on our data set so it may work for what you need, and I'm not much of a PostGIS expert yet so I'm 100% sure it could be done better but here goes anyway:
select DISTINCT ON (p."land_no") a."Road_asset_ID", p."land_no", p."SP_GEOMETRY"
from "roads_old_centre_lines" a
INNER JOIN "Property" p on st_intersects(st_buffer(a."SP_GEOMETRY",50), p."SP_GEOMETRY")
where a."Road_asset_ID" = 1500
OR a."Road_asset_ID" = 1502
It should create a buffer around the road and select the property geometry that intersects the buffer, the property land no, and the road id that was used for the buffer. It should also only select one property using DISTINCT ON (p."land_no") even if the property touches two road buffers.
This query uses a buffer of 50m but you will just need to adjust that so that cover the road reserve area.
This is what the result in QGIS looks like:
EDIT: This seems to do the count right, it's a bit slow due to having to do two runs but I'm sure someone might be able to optimize it.
SELECT road, Count(land_no) FROM
(select DISTINCT ON (p."land_no") a."Road_asset_ID" as road, p.land_no ,p."SP_GEOMETRY"
from "roads_old_centre_lines" a
INNER JOIN "Property" p on st_intersects(st_buffer(a."SP_GEOMETRY",50), p."SP_GEOMETRY")
where a."Road_asset_ID" = 1500 OR a."Road_asset_ID" = 1502) as Test
GROUP BY road
Using Google Maps API v3 Directions you can achieve this road segment length using waypoints.
http://code.google.com/apis/maps/documentation/javascript/examples/directions-waypoints.html
To work out line length http://groups.google.com/group/google-maps-js-api-v3/browse_thread/thread/3a9f3b83b941a2d7/06792073b2529413?lnk=gst&q=polyline+length#06792073b2529413
Best Answer
St_Intersection function will give you the a point/collection of points that are the intersection of two linestrings.
ST_Intersectscan be used to find linestrings that intersect.