[postgis-users] Locating road segments with a start and end point

[Charles Galpin]

Hi Folks

New to the list (as well as post{gres,gis}).

I wanted to get some feedback on my approach in case i'm doing it a really really bad way :). 

I have data (happens to be navteq street data) that consists of a bunch of road segments stored in postgis as MULTILINESTRINGs.  I have other data sets that identify road segments by a start and end point, but don't match my data cleanly. So one of these other links could 

a) match some percentage of one of my segments.
b) overlap two or more of my segments

And their start and end points don't necessarily fall cleanly onto my MULTILINESTRINGs either.

My goal is to figure out the mapping of their segments to ours and build an index so when I get data referencing their segments it's easy to map it to ours.

I can do this in multiple steps right now with a minimum of 2 queries per segment to lookup, and possibly up to N queries where N is the number of my segments theirs spans + 1. So all in all not the end of the world but something tells me someone more experienced with these GIS queries will know of a faster/simpler way. Let me know what you think

-- Test case of two points POINT(-74.5973587 40.1928978) POINT(-74.60466766 40.19513702)

-- Q1
-- find nearest line to the start point and percent along the line (maybe change to ST_closestPoint now that postgis 1.5 is out)
-- st_name is used later to limit results and speed up the query
-- dist gets me the closest segment in case I get more than one match
-- percent is for later weighting of the match to that segment
-- end_point might be needed to walk the segments
select link_id, st_name, 
st_distance(the_geom, ST_GeomFromText('POINT(-74.5973587 40.1928978)',4269)) as dist, 
ST_Line_Locate_Point(ST_LineMerge(the_geom), ST_GeomFromText('POINT(-74.5973587 40.1928978)',4269))*100 as percent,
ST_AsText(ST_endPoint(the_geom)) as end_point from streets 
where the_geom && ST_GeomFromText('POINT(-74.5973587 40.1928978)',4269)
order by dist asc limit 1;

-- Q2
-- do the same for the end point
-- start_point might be needed to walk the segments
select link_id, st_name, st_distance(the_geom, ST_GeomFromText('POINT(-74.60466766 40.19513702)',4269)) as dist, 
ST_Line_Locate_Point(ST_LineMerge(the_geom), ST_GeomFromText('POINT(-74.60466766 40.19513702)',4269))*100 as percent,
ST_AsText(ST_startPoint(the_geom)) as start_point from streets 
where the_geom && ST_GeomFromText('POINT(-74.60466766 40.19513702)',4269)
order by dist asc limit 1;

At this point if the two links returned are the same, then my foreign segment is a subset of my native segment so I'm done.

But if not, and the end_point of the first link is not equal to the start_point of the second link, then I have one or more links inbetween these two links.

While the next link does not start with Q2.start_point
-- get next link (starts with the end of the last link)
-- limiting to a segment with the same name speeds up the query and afaik is guaranteed to be the same 
-- for all segments on the same road in my case. query is dog slow otherwise
select link_id, st_name, ST_AsText(ST_startPoint(the_geom)) as start_point, ST_AsText(ST_endPoint(the_geom)) as end_point
from streets 
where st_name = <Q1.st_name>
and ST_startPoint(the_geom) = <last_end_point>

compare start and end points and keep going until we get to the last segment (the segment result of Q2). Seems like there should be an easier way to do this.

I'll be looking to see if I can do this in a stored procedure otherwise I'll be doing it in java.

tia,
charles

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100204/237d1429/attachment.html>