[postgis-users] 10 closest units

[Pedro Doria Meunier]

Hi All,

 

@Regina:

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)

 

@All

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:

select u.friendly_name, distance(transform(u.curr_location, 32628), 

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!

 

Best regards,

Pedro Doria Meunier.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070402/ea144421/attachment.html>