[postgis-users] Distance query

[Obe, Regina]

distance I think always measures in the metrics of the spatial reference
system which in case of 4326 I think is some variant of degrees and is
the minimum distance between 2 geometries.  
 
Are you sure there is only one geometry in your toponimia table with
name = 'Se'?  If there are more, you are picking an arbitrary one so
could be the distance calcs are right and the one you are measuring
against is not the one you think you are getting. Are looking for
minimum distance against any with that name.  In that case you may want
to aggregate your 'Se' into one geometry with geomunion or something
like that.
 
 
Try for example
 
SELECT u.id, u.curr_location, t.name, t.someid,
distance(u.curr_location, t.geometry) as thedistance
FROM units as u, (SELECT topoimia.name, toponimia.someid,
toponima.geometry from toponimia where name='Se') t
WHERE u.id = XXXXXXX
ORDER BY distance(u.curr_location, t.geometry)
LIMIT 10
 
The above should return only one point for your u.id and if you have
more than one with name='Se' then will return the top 10 closest 'Se'
geometries to your
u.id.
 
if you leave out the u.id it will give you the closest 10 points, but
the query may be really slow depending on how large your tables are. 
 
If you want to aggregate your geometries like you have an 'Se' path of
some sort, then I think you may want to do
 
  
SELECT u.id, u.curr_location, distance(u.curr_location,
t.thecombinedgeom) as thedistance
FROM units as u, (SELECT geomunion(toponima.geometry) as thecombinedgeom
from toponimia where name='Se') t
ORDER BY distance(u.curr_location,  t.thecombinedgeom)
LIMIT 10
 
Again this will be very slow since they are not indexable. You may want
to try substituting geomunion with extent which will be much faster, but
less precise
 
Hope that helps,
Regina
 

________________________________

From: postgis-users-bounces at postgis.refractions.net on behalf of Pedro
Doria Meunier
Sent: Mon 2/5/2007 2:45 PM
To: 'PostGIS Users Discussion'
Subject: [postgis-users] Distance query



Hi All,

 

In the sequence of my last email with the same subject this is the query
I tried (only for the localities layer)

 

select min(distance(u.curr_location, (select geometry from toponimia
where name='Se' limit 1))) as dist 

from units as u 

where u.id=XXXXXXXX;

 

the results aren't quite the expected as:

unit 11111111 is almost over the point with the name 'Se'

unit 80000135 is the second on the list

unit 99999999 is the farthest....

 

These are the result that I got:

80000135 = 3665.52978684708

11111111 = 3666.92134092059

99999999 = 3665.79245878804

 

To my surprise unit 11111111's distance is the greatest....

What is distance() returning anyway?

Btw: both tables have srid=4326

 

Any help would be greatly appreciated since I really have to finish this
task (please see last email with the same subject)

 

Thank you.

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 --------------
A non-text attachment was scrubbed...
Name: winmail.dat
Type: application/ms-tnef
Size: 8367 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070205/8ad088b5/attachment.bin>