[postgis-users] Need help with a query - resending

[Pedro Doria Meunier]

Dear Regina,

As always you come to the rescue... :)
Thank you for your thoughts. This is actually a *tad* out of the
ordinary...

Anyway it's just occurred to me that I was trying to kill two birds with
the same pellet ...
This has to have a two step approach, as the problem is related to
having several tunnels in range:

- get all the tunnels in that range (thus eliminating the LIMIT 1)
- check if the current location (road segment) *touches* one of the
returned tunnels (this involved a quick fix on the road map... ;-) )

Best regards,
Pedro.



On Thu, 2008-07-17 at 05:49 -0400, Paragon Corporation wrote:
> Pedro,
>  
> 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.
>  
> 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).
>  
> 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.
>  
> SELECT name, road_type 
> FROM $road_net 
> WHERE 
> ST_DWithin(ST_Transform(geometry, $srid) ,
> ST_Transform(ST_geomfromtext('POINT($lon $lat)',4326),$srid), 150.1)
> AND road_type = 67 AND ST_Transform(geometry, $srid) |&>
> ST_Transform(ST_geomfromtext('POINT($lon $lat)',4326),$srid)
> ORDER BY
>     ST_Distance(ST_transform(geometry,$srid),
> ST_transform(ST_geomfromtext('POINT($lon $lat)',4326),$srid))
> LIMIT 1 ;
>  
> Hope that helps,
> Regina
> 
> 
> ______________________________________________________________________
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> Pedro Doria Meunier
> Sent: Thursday, July 17, 2008 4:16 AM
> To: postgis-users at postgis.refractions.net
> Subject: [postgis-users] Need help with a query - resending
> Importance: High
> 
> 
> 
> 
> (Since I'm not certain that this got through I'm resending. If it did,
> please forgive me)
> 
> Hi all,
> 
> I need help with an unusual query (at least for me :] )
> 
> The objective is to find road segments with a type defined to tunnel
> within a given distance.
> This is needed because of gps signal spikes (speed-wise) at tunnel
> exits...
> 
> Now given the following scenario of road segments:
> 
> 
> =====*-----------------*==========
> ====V===*========================*
> *======
> 
> =====*-----------------*==========
>                     |
>                     |
>                     |
>                     |
>                     |
>                     |
>                     |
>                     |
>                     |
>                     |
> ==========-----------------------=======================
> 
> = | road
> - tunnel
> * nodes
> V vehicle
> 
> Now when I execute a distance-based query (for road segments of tunnel
> type) I get the lower tunnel, which is closer.
> But I want the upper one which is actually the one on the top.
> 
> the actual query is as follows (tunnels (road type=67) within a 150
> metres radius) -- the road map is in WGS84:
>   
> Already thankful for any tips,
> 
> -- 
> Pedro Doria Meunier <pdoria at netmadeira.com> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080717/e7b7c82b/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: stock_smiley-3.png
Type: image/png
Size: 876 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080717/e7b7c82b/attachment.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: stock_smiley-1.png
Type: image/png
Size: 873 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080717/e7b7c82b/attachment-0001.png>