[postgis-users] Distance query

Wed Feb 7 01:57:35 PST 2007

Dear Regina,

As always your input is highly appreciated as well as useful. Thank you.

Distance() is still not returning the expected values. Please see the
results below.

All geometries SRID=4326

'toponimia' Layer:

Query:

select toponimia.name, toponimia.oid, toponimia.geometry from toponimia
where name='Se';

Result:

"Se";41616;"0101000020E6100000AEC352666CE830C075DE2CE301534040"

Point 'Se' = "POINT(-16.9079040481626 32.6484950989806)"

(Point 'Se' is unique)

'units' Layer:

Unit 80000135 curr_location = "POINT(3239.3462 -1650.4789)"

Unit 11111111 curr_location = "POINT(3238.82678 -1654.51)"

Unit 99999999 curr_location = "POINT(3241.00088 -1647.84688)"

Distance from 'Se', measured in OpenJump:

11111111= 0,00150

80000135= 0,06677

99999999= 0,11574

Query:

select u.id, u.curr_location, t.name, t.oid, distance(u.curr_location,
t.geometry) as thedistance 

from units as u, (select toponimia.name, toponimia.oid, toponimia.geometry
from toponimia where name='Se') as t 

order by thedistance 

limit 10;

the results:

+---------------------------------------------------------------------------
-------------------------------------------------+

+ u.id        | curr_location
|name|oid |thedistance              +

+---------------------------------------------------------------------------
-------------------------------------------------+

80000135;"0101000020E6100000C05B2041B14EA94038F8C264EAC999C0";"Se";41616;366
5.52978684708

99999999;"0101000020E610000071E657730052A9408CBE823463BF99C0";"Se";41616;366
5.79245878804

11111111;"0101000020E6100000F949B54FA74DA940D7A3703D0ADA99C0";"Se";41616;366
6.92134092059

There's something really 'fishy' about these results. I comprehend the query
fully; just don't get the results! :S

Btw: this is postgis_full_version() output if it helps any:

"POSTGIS="1.1.5" GEOS="3.0.0rc2-CAPI-1.3.0" PROJ="Rel. 4.5.0, 22 Oct 2006"
USE_STATS"

Best regards,

Pedro Doria Meunier

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Obe,
Regina
Sent: segunda-feira, 5 de Fevereiro de 2007 22:25
To: PostGIS Users Discussion
Subject: RE: [postgis-users] Distance query

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

-------------- next part --------------
A non-text attachment was scrubbed...
Name: winmail.dat
Type: application/ms-tnef
Size: 14658 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070207/386e48e9/attachment.bin>