[postgis-users] 10 closest units

[Obe, Regina]

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
 
select u.friendly_name, distance(transform(u.curr_location, 32628), 

transform(rc.agg_geometry,32628)) as thedistance 

from (SELECT collect(r.geometry) as agg_geometry FROM pt_mainland_roads
r WHERE r.name = 'N17') rc, units as u 

where 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

 

expand(rc.agg_geometry, <somevalue>) && u.curr_location 

 

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: Sunday, April 01, 2007 9:50 PM
To: 'PostGIS Users Discussion'
Subject: [postgis-users] 10 closest units
Importance: High



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.




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