[postgis-users] 10 closest units

[Pedro Doria Meunier]

Regina,

 

You're simply an angel! Thank you!

 

Just one question:

The expand(rc.agg_geometry, 0.5) uses *degrees*, right?

 

All the best,

Pedro.

 

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Obe,
Regina
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

 

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/eb6a9928/attachment.html>