<html><head></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">Hi Folks<div><br></div><div>New to the list (as well as post{gres,gis}).</div><div><br></div><div>I wanted to get some feedback on my approach in case i'm doing it a really really bad way :). </div><div><br></div><div>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 </div><div><br></div><div>a) match some percentage of one of my segments.</div><div>b) overlap two or more of my segments</div><div><br></div><div>And their start and end points don't necessarily fall cleanly onto my MULTILINESTRINGs either.</div><div><br></div><div>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.</div><div><br></div><div>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</div><div><br></div><div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Monaco; color: rgb(146, 146, 146); ">-- Test case of two points POINT(-74.5973587 40.1928978) POINT(-74.60466766 40.19513702)</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; ">-- Q1</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Monaco; color: rgb(146, 146, 146); ">-- find nearest line to the start point and percent along the line (maybe change to ST_closestPoint now that postgis 1.5 is out)</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Monaco; color: rgb(146, 146, 146); ">-- st_name is used later to limit results and speed up the query</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Monaco; color: rgb(146, 146, 146); ">-- dist gets me the closest segment in case I get more than one match</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Monaco; color: rgb(146, 146, 146); ">-- percent is for later weighting of the match to that segment</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Monaco; color: rgb(146, 146, 146); ">-- end_point might be needed to walk the segments</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Helvetica; "><span style="color: #4300ff"><b>select </b></span>link_id, st_name, </div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Helvetica; ">st_distance(the_geom, ST_GeomFromText(<span style="color: #ff0000">'POINT(-74.5973587 40.1928978)'</span>,<span style="color: #ff00ff">4269</span>)) <span style="color: #4300ff"><b>as </b></span>dist, </div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Helvetica; ">ST_Line_Locate_Point(ST_LineMerge(the_geom), ST_GeomFromText(<span style="color: #ff0000">'POINT(-74.5973587 40.1928978)'</span>,<span style="color: #ff00ff">4269</span>))*<span style="color: #ff00ff">100 </span><span style="color: #4300ff"><b>as </b></span>percent,</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Helvetica; ">ST_AsText(ST_endPoint(the_geom)) <span style="color: #4300ff"><b>as </b></span>end_point <span class="Apple-style-span" style="color: rgb(67, 0, 255); "><b>from </b><span style="color: #000000">streets </span></span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Helvetica; "><span class="Apple-style-span" style="color: rgb(67, 0, 255); "><span style="color: #000000"></span><b>where <span class="Apple-style-span" style="color: rgb(255, 0, 0); font-weight: normal; "><span style="color: #000000">the_geom && ST_GeomFromText(</span>'POINT(-74.5973587 40.1928978)'<span style="color: #000000">,</span><span style="color: #ff00ff">4269</span><span style="color: #000000">)</span></span></b></span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Helvetica; color: rgb(67, 0, 255); "><b>order by </b><span style="color: #000000">dist </span><b>asc limit </b><span style="color: #ff00ff">1</span><span style="color: #000000">;</span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; ">-- Q2</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Monaco; color: rgb(146, 146, 146); ">-- do the same for the end point</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Monaco; color: rgb(146, 146, 146); ">-- start_point might be needed to walk the segments</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Helvetica; "><span style="color: #4300ff"><b>select </b></span>link_id, st_name, st_distance(the_geom, ST_GeomFromText(<span style="color: #ff0000">'POINT(-74.60466766 40.19513702)'</span>,<span style="color: #ff00ff">4269</span>)) <span style="color: #4300ff"><b>as </b></span>dist, </div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Helvetica; ">ST_Line_Locate_Point(ST_LineMerge(the_geom), ST_GeomFromText(<span style="color: #ff0000">'POINT(-74.60466766 40.19513702)'</span>,<span style="color: #ff00ff">4269</span>))*<span style="color: #ff00ff">100 </span><span style="color: #4300ff"><b>as </b></span>percent,</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Helvetica; ">ST_AsText(ST_startPoint(the_geom)) <span style="color: #4300ff"><b>as </b></span>start_point <span class="Apple-style-span" style="color: rgb(67, 0, 255); "><b>from </b><span style="color: #000000">streets </span></span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Helvetica; "><span class="Apple-style-span" style="color: rgb(67, 0, 255); "><span style="color: #000000"></span><b>where <span class="Apple-style-span" style="color: rgb(255, 0, 0); font-weight: normal; "><span style="color: #000000">the_geom && ST_GeomFromText(</span>'POINT(-74.60466766 40.19513702)'<span style="color: #000000">,</span><span style="color: #ff00ff">4269</span><span style="color: #000000">)</span></span></b></span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Helvetica; color: rgb(67, 0, 255); "><b>order by </b><span style="color: #000000">dist </span><b>asc limit </b><span style="color: #ff00ff">1</span><span style="color: #000000">;</span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Helvetica; color: rgb(67, 0, 255); "><font class="Apple-style-span" color="#000000"><br></font></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Helvetica; color: rgb(67, 0, 255); "><font class="Apple-style-span" color="#000000">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.</font></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Helvetica; color: rgb(67, 0, 255); "><font class="Apple-style-span" color="#000000"><br></font></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Helvetica; color: rgb(67, 0, 255); "><font class="Apple-style-span" color="#000000">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.</font></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; ">While the next link does not start with Q2.start_point</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Monaco; color: rgb(146, 146, 146); ">-- get next link (starts with the end of the last link)</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Monaco; color: rgb(146, 146, 146); ">-- limiting to a segment with the same name speeds up the query and afaik is guaranteed to be the same </div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Monaco; color: rgb(146, 146, 146); ">-- for all segments on the same road in my case. query is dog slow otherwise</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Helvetica; "><span style="color: #4300ff"><b>select </b></span>link_id, st_name, ST_AsText(ST_startPoint(the_geom)) <span style="color: #4300ff"><b>as </b></span>start_point, ST_AsText(ST_endPoint(the_geom)) <span style="color: #4300ff"><b>as </b></span>end_point</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Helvetica; color: rgb(67, 0, 255); "><b>from </b><span style="color: #000000">streets </span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Helvetica; color: rgb(67, 0, 255); "><span style="color: #000000"></span><b>where <span class="Apple-style-span" style="color: rgb(0, 0, 0); font-weight: normal; ">st_name = <span style="color: #ff0000"><Q1.st_name></span></span></b></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 10px/normal Helvetica; "><span style="color: #4300ff"><b>and </b></span>ST_startPoint(the_geom) = <last_end_point></div></div><div><br></div><div>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.</div><div><br></div><div>I'll be looking to see if I can do this in a stored procedure otherwise I'll be doing it in java.</div><div><br></div><div>tia,</div><div>charles</div><div><br></div></body></html>