You’re simply an
angel! Thank you!
Just one question:
0.5) uses *degrees*, right?
All the best,
postgis-users-bounces <at> postgis.refractions.net
[mailto:postgis-users-bounces <at> postgis.refractions.net] On Behalf Of Obe,
Sent: segunda-feira, 2 de Abril de 2007 13:43
To: PostGIS Users Discussion
Subject: RE: [postgis-users] 10 closest units
I'm not sure how slow this would be depending on how many line
segments you have by name, but it seems the most speedy to write.
Basically I think you want to collect all your roads of N17 into a single
geometry so that you can then apply a single distance check call.
So something like
collect(r.geometry) as agg_geometry FROM pt_mainland_roads r WHERE r.name =
'N17') rc, units as u
rc.agg_geometry && u.curr_location order by thedistance LIMIT 10;
If you have some units
that are not in the bounding box of a road that would be in the top 10, then
you may want to change your where clause to
Hope that helps,
postgis-users-bounces <at> postgis.refractions.net [mailto:postgis-users-bounces <at> postgis.refractions.net]
On Behalf Of Pedro Doria Meunier
Sent: Sunday, April 01, 2007 9:50 PM
To: 'PostGIS Users Discussion'
Subject: [postgis-users] 10 closest units
Remember you helped me with the 10 closest
units to a given point? This was it:
(original layers srid==4326 – I want *meters*
so the transform at play)
SELECT u.id, u.friendly_name,
u.curr_location, t.oid, distance(transform(u.curr_location,32628), transform(t.geometry,
32628)) AS thedistance, u.mobile FROM units AS u, (SELECT roads.oid,
roads.geometry FROM roads WHERE name=’N17’ LIMIT 1) AS t
ORDER BY thedistance LIMIT 10;
(this query returns the 1st
found line segment labelled ‘N17’ which is 68Kms away when the unit is actually
1.8 meters away from the closest line segment of the same label)
This actually returns the FIRST occurrence
of ‘N17’ (the sample).
This is *NOT* necessarily the
closest road to the unit’s current location.
I’ve melted half-a-dozen neurons (mainly
due to tiredness at the time of this writing :] )trying to figure out how can I
implement the distance bit in the sub-query…
Given example for ONE unit:
transform(geometry,32628)) as thedistance
from pt_mainland_roads as r, units as u
where name='N17' AND u.curr_location
&& r.geometry order by thedistance LIMIT 1;
(this returns the correct result)
So to summarize things:
I need help with a query that returns the *FIRST
TEN* units to the *closest given* road.
(Keep in mind that multiple line segments
with the same name exist – I need the closest)
Will try again tomorrow with a clear head
but in the meantime:
Any help would be most appreciated!
Pedro Doria Meunier.
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended solely for the
addressee. If you received this in error, please contact the sender
and delete the material from any computer.