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

[Paragon Corporation]

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> 	
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080717/7878b40b/attachment.html>