Hello Regina,

Txs for replying.

You’re right. I was missing an index for the text fields. I realized this and immediately applied the theory with grim results… L

It turns out (?) that one as to use TSearch2 to have the indices fired up for text fields… Someone please rebate me if I’m wrong.

Anyway I’m still not ready to mess up with my tables’ schema in case someone comes up with a more enlightened solution…

Best regards,

Pedro.

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.

Hi All,

<at> 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)

<at> 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.

Hi All,

<at> 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)

<at> 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.

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.

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,

<at> 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)

<at> 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.

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.

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,

<at> 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)

<at> 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.