<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16674" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=921073309-17072008><FONT face=Arial
color=#0000ff size=2>Pedro,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=921073309-17072008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=921073309-17072008><FONT face=Arial
color=#0000ff size=2>It got thru the first time, I just don't think
people have too many thoughts on it since it is a litte out of the
ordinary. So you always want the closest upper tunnel.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=921073309-17072008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=921073309-17072008><FONT face=Arial
color=#0000ff size=2>One thought that comes to mind is to use the overlap/above
bounding box operator (but that would only work if your lines fit nicely in
non-overlapping bounding boxes.). To get true above below ( I'm actually
not sure that has any meaning with lines vs. right and left which would depend
on the directionality of your line).</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=921073309-17072008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=921073309-17072008><FONT face=Arial
color=#0000ff size=2>So something like below - note I am using the new
ST_DWithin function introduced in 1.3 and the new naming conventions - so you
may need to change back to what you had and add the |&> operator if
you are running an older version of PostGIS.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=921073309-17072008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=921073309-17072008>SELECT name, road_type
</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=921073309-17072008>FROM $road_net
</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=921073309-17072008>WHERE </SPAN></DIV>
<DIV dir=ltr align=left><SPAN
class=921073309-17072008>ST_DWithin(ST_Transform(geometry, $srid) ,
ST_Transform(ST_geomfromtext('POINT($lon $lat)',4326),$srid),
150.1)</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=921073309-17072008>AND road_type = 67 AND
ST_Transform(geometry, $srid) |&>
ST_Transform(ST_geomfromtext('POINT($lon $lat)',4326),$srid)</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=921073309-17072008>ORDER BY</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=921073309-17072008>
ST_Distance(ST_transform(geometry,$srid),
ST_transform(ST_geomfromtext('POINT($lon $lat)',4326),$srid))</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=921073309-17072008>LIMIT
1 ;</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=921073309-17072008></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=921073309-17072008><FONT face=Arial
color=#0000ff size=2>Hope that helps,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=921073309-17072008><FONT face=Arial
color=#0000ff size=2>Regina</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=921073309-17072008><BR></SPAN>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Pedro
Doria Meunier<BR><B>Sent:</B> Thursday, July 17, 2008 4:16 AM<BR><B>To:</B>
postgis-users@postgis.refractions.net<BR><B>Subject:</B> [postgis-users] Need
help with a query - resending<BR><B>Importance:</B> High<BR></FONT><BR></DIV>
<DIV></DIV>(Since I'm not certain that this got through I'm resending. If it
did, please forgive me)<BR><BR>Hi all,<BR><BR>I need help with an unusual query
(at least for me :] )<BR><BR>The objective is to find road segments with a type
defined to tunnel within a given distance.<BR>This is needed because of gps
signal spikes (speed-wise) at tunnel exits...<BR><BR>Now given the following
scenario of road
segments:<BR><BR>
=====*-----------------*==========<BR>====V===*========================*
*======<BR>
=====*-----------------*==========<BR>
|<BR>
|<BR>
|<BR>
|<BR>
|<BR>
|<BR>
|<BR>
|<BR>
|<BR>
|==========-----------------------=======================<BR><BR>= | road<BR>-
tunnel<BR>* nodes<BR>V vehicle<BR><BR>Now when I execute a distance-based query
(for road segments of tunnel type) I get the lower tunnel, which is
closer.<BR>But I want the upper one which is actually the one on the
top.<BR><BR>the actual query is as follows (tunnels (road type=67) within a 150
metres radius) -- the road map is in WGS84:<BR><SPAN
class=921073309-17072008><FONT face=Arial color=#0000ff
size=2> </FONT></SPAN><BR>Already thankful for any tips,<BR><BR>
<TABLE cellSpacing=0 cellPadding=0 width="100%">
<TBODY>
<TR>
<TD>-- <BR>Pedro Doria Meunier <<A
href="mailto:pdoria@netmadeira.com">pdoria@netmadeira.com</A>>
</TD></TR></TBODY></TABLE></BODY></HTML>