<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
<META NAME="GENERATOR" CONTENT="GtkHTML/3.16.3">
</HEAD>
<BODY>
Dear Regina,<BR>
<BR>
As always you come to the rescue... <IMG SRC="cid:1216290056.3996.0.camel@213-190-214-90-cmts01.netmadeira.com" ALIGN="middle" ALT=":)" BORDER="0"><BR>
Thank you for your thoughts. This is actually a *tad* out of the ordinary...<BR>
<BR>
Anyway it's just occurred to me that I was trying to kill two birds with the same pellet ...<BR>
This has to have a two step approach, as the problem is related to having several tunnels in range:<BR>
<BR>
- get all the tunnels in that range (thus eliminating the LIMIT 1)<BR>
- check if the current location (road segment) *touches* one of the returned tunnels (this involved a quick fix on the road map... <IMG SRC="cid:1216290416.3996.7.camel@213-190-214-90-cmts01.netmadeira.com" ALIGN="middle" ALT=";-)" BORDER="0"> )<BR>
<BR>
Best regards,<BR>
Pedro.<BR>
<BR>
<TABLE CELLSPACING="0" CELLPADDING="0" WIDTH="100%">
<TR>
<TD>
<BR>
</TD>
</TR>
</TABLE>
<BR>
On Thu, 2008-07-17 at 05:49 -0400, Paragon Corporation wrote:
<BLOCKQUOTE TYPE=CITE>
<FONT SIZE="2"><FONT COLOR="#0000ff">Pedro,</FONT></FONT>
</BLOCKQUOTE>
<BLOCKQUOTE TYPE=CITE>
<FONT COLOR="#000000"> </FONT>
</BLOCKQUOTE>
<BLOCKQUOTE TYPE=CITE>
<FONT SIZE="2"><FONT COLOR="#0000ff">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></FONT>
</BLOCKQUOTE>
<BLOCKQUOTE TYPE=CITE>
<FONT COLOR="#000000"> </FONT>
</BLOCKQUOTE>
<BLOCKQUOTE TYPE=CITE>
<FONT SIZE="2"><FONT COLOR="#0000ff">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></FONT>
</BLOCKQUOTE>
<BLOCKQUOTE TYPE=CITE>
<FONT COLOR="#000000"> </FONT>
</BLOCKQUOTE>
<BLOCKQUOTE TYPE=CITE>
<FONT SIZE="2"><FONT COLOR="#0000ff">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></FONT>
</BLOCKQUOTE>
<BLOCKQUOTE TYPE=CITE>
<FONT COLOR="#000000"> </FONT>
</BLOCKQUOTE>
<BLOCKQUOTE TYPE=CITE>
<FONT COLOR="#000000">SELECT name, road_type </FONT>
</BLOCKQUOTE>
<BLOCKQUOTE TYPE=CITE>
<FONT COLOR="#000000">FROM $road_net </FONT>
</BLOCKQUOTE>
<BLOCKQUOTE TYPE=CITE>
<FONT COLOR="#000000">WHERE </FONT>
</BLOCKQUOTE>
<BLOCKQUOTE TYPE=CITE>
<FONT COLOR="#000000">ST_DWithin(ST_Transform(geometry, $srid) , ST_Transform(ST_geomfromtext('POINT($lon $lat)',4326),$srid), 150.1)</FONT>
</BLOCKQUOTE>
<BLOCKQUOTE TYPE=CITE>
<FONT COLOR="#000000">AND road_type = 67 AND ST_Transform(geometry, $srid) |&> ST_Transform(ST_geomfromtext('POINT($lon $lat)',4326),$srid)</FONT>
</BLOCKQUOTE>
<BLOCKQUOTE TYPE=CITE>
<FONT COLOR="#000000">ORDER BY</FONT>
</BLOCKQUOTE>
<BLOCKQUOTE TYPE=CITE>
<FONT COLOR="#000000"> ST_Distance(ST_transform(geometry,$srid), ST_transform(ST_geomfromtext('POINT($lon $lat)',4326),$srid))</FONT>
</BLOCKQUOTE>
<BLOCKQUOTE TYPE=CITE>
<FONT COLOR="#000000">LIMIT 1 ;</FONT>
</BLOCKQUOTE>
<BLOCKQUOTE TYPE=CITE>
<FONT COLOR="#000000"> </FONT>
</BLOCKQUOTE>
<BLOCKQUOTE TYPE=CITE>
<FONT SIZE="2"><FONT COLOR="#0000ff">Hope that helps,</FONT></FONT>
</BLOCKQUOTE>
<BLOCKQUOTE TYPE=CITE>
<FONT SIZE="2"><FONT COLOR="#0000ff">Regina</FONT></FONT>
</BLOCKQUOTE>
<BLOCKQUOTE TYPE=CITE>
<BR>
<HR>
<BR>
<B><FONT SIZE="2"><FONT COLOR="#000000">From:</FONT></FONT></B><FONT COLOR="#000000"><FONT SIZE="2"> postgis-users-bounces@postgis.refractions.net [mailto:postgis-users-bounces@postgis.refractions.net] </FONT></FONT><FONT COLOR="#000000"><B><FONT SIZE="2">On Behalf Of </FONT></B></FONT><FONT COLOR="#000000"><FONT SIZE="2">Pedro Doria Meunier</FONT></FONT><BR>
<B><FONT SIZE="2"><FONT COLOR="#000000">Sent:</FONT></FONT></B><FONT COLOR="#000000"><FONT SIZE="2"> Thursday, July 17, 2008 4:16 AM</FONT></FONT><BR>
<B><FONT SIZE="2"><FONT COLOR="#000000">To:</FONT></FONT></B><FONT COLOR="#000000"><FONT SIZE="2"> postgis-users@postgis.refractions.net</FONT></FONT><BR>
<B><FONT SIZE="2"><FONT COLOR="#000000">Subject:</FONT></FONT></B><FONT COLOR="#000000"><FONT SIZE="2"> [postgis-users] Need help with a query - resending</FONT></FONT><BR>
<B><FONT SIZE="2"><FONT COLOR="#000000">Importance:</FONT></FONT></B><FONT COLOR="#000000"><FONT SIZE="2"> High</FONT></FONT><BR>
<BR>
<BR>
</BLOCKQUOTE>
<BLOCKQUOTE TYPE=CITE>
<BR>
</BLOCKQUOTE>
<BLOCKQUOTE TYPE=CITE>
<FONT COLOR="#000000">(Since I'm not certain that this got through I'm resending. If it did, please forgive me)</FONT><BR>
<BR>
<FONT COLOR="#000000">Hi all,</FONT><BR>
<BR>
<FONT COLOR="#000000">I need help with an unusual query (at least for me :] )</FONT><BR>
<BR>
<FONT COLOR="#000000">The objective is to find road segments with a type defined to tunnel within a given distance.</FONT><BR>
<FONT COLOR="#000000">This is needed because of gps signal spikes (speed-wise) at tunnel exits...</FONT><BR>
<BR>
<FONT COLOR="#000000">Now given the following scenario of road segments:</FONT><BR>
<BR>
<FONT COLOR="#000000"> =====*-----------------*==========</FONT><BR>
<FONT COLOR="#000000">====V===*========================* *======</FONT><BR>
<FONT COLOR="#000000"> =====*-----------------*==========</FONT><BR>
<FONT COLOR="#000000"> |</FONT><BR>
<FONT COLOR="#000000"> |</FONT><BR>
<FONT COLOR="#000000"> |</FONT><BR>
<FONT COLOR="#000000"> |</FONT><BR>
<FONT COLOR="#000000"> |</FONT><BR>
<FONT COLOR="#000000"> |</FONT><BR>
<FONT COLOR="#000000"> |</FONT><BR>
<FONT COLOR="#000000"> |</FONT><BR>
<FONT COLOR="#000000"> |</FONT><BR>
<FONT COLOR="#000000"> |==========-----------------------=======================</FONT><BR>
<BR>
<FONT COLOR="#000000">= | road</FONT><BR>
<FONT COLOR="#000000">- tunnel</FONT><BR>
<FONT COLOR="#000000">* nodes</FONT><BR>
<FONT COLOR="#000000">V vehicle</FONT><BR>
<BR>
<FONT COLOR="#000000">Now when I execute a distance-based query (for road segments of tunnel type) I get the lower tunnel, which is closer.</FONT><BR>
<FONT COLOR="#000000">But I want the upper one which is actually the one on the top.</FONT><BR>
<BR>
<FONT COLOR="#000000">the actual query is as follows (tunnels (road type=67) within a 150 metres radius) -- the road map is in WGS84:</FONT><BR>
<FONT SIZE="2"><FONT COLOR="#0000ff"> </FONT></FONT><BR>
<FONT COLOR="#000000">Already thankful for any tips,</FONT><BR>
<BR>
<TABLE CELLSPACING="0" CELLPADDING="0" WIDTH="100%">
<TR>
<TD>
-- <BR>
Pedro Doria Meunier <<A HREF="mailto:pdoria@netmadeira.com">pdoria@netmadeira.com</A>>
</TD>
</TR>
</TABLE>
<PRE>
<FONT COLOR="#000000">_______________________________________________</FONT>
<FONT COLOR="#000000">postgis-users mailing list</FONT>
<FONT COLOR="#000000"><A HREF="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A></FONT>
<FONT COLOR="#000000"><A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A></FONT>
</PRE>
</BLOCKQUOTE>
</BODY>
</HTML>