[postgis-users] Distance query not working after transfrom?

Obe, Regina robe.dnd at cityofboston.gov
Thu Jul 19 12:57:21 PDT 2007


You can also use distance_sphere if you don't want to transform.  Its
not as accurate as transforming to a meter based system though
 
select distance_sphere(a.the_geom, b.the_geom)
 
For a little bit more accuracy but trade-off of less speed, you can use
distance_spheroid
 
http://postgis.refractions.net/pipermail/postgis-users/2007-January/0143
02.html
 
 

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Burgholzer,Robert
Sent: Thursday, July 19, 2007 3:20 PM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] Distance query not working after transfrom?



What I typically do, is use the transform function in my distance query
to bring it to something that uses meters, so it goes like:

 

Select distance(transform(a.the_geom, 27700), transform(b.the_geom,
27700)) 

....

 

I may be totally off with this approach, there may be some better way of
telling PostGIS what units you want, but this works for me, and is
verified by comparison to the original shapefiles (if ArcFoo can be
considered as verification:-) )

 

HTH,

r.b.

 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Alan
Cunnane
Sent: Thursday, July 19, 2007 2:59 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Distance query not working after transfrom?

 

Could you suggest to me how I can the get the result in meters and not
degrees?  Thanks very much for your help

----- Original Message ----
From: "Burgholzer,Robert" <rwburgholzer at deq.virginia.gov>
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Sent: Thursday, 19 July, 2007 7:55:07 PM
Subject: RE: [postgis-users] Distance query not working after transfrom?

I think that this is an entirely correct result, as you are now getting
it in the new units, which are degrees, rather than meters.

 

r.b.

 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Alan
Cunnane
Sent: Thursday, July 19, 2007 2:51 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Distance query not working after transfrom?

 

Im having a problem with a distance query after I transformed a set of
points to another SRID. I transformed from easting northing (SRID 27700)
to longitude lattitude (SRID 4326).  The problem that I am having is
that when I do the same distance queries using both geom columns I get a
completely different result? I know that the distance query using the
first easting northing column is correct. Here is an example of the
different returns I am getting:

SRID (27700):

SELECT a.stop_reference, b.stop_reference, distance(a.east_north,
b.east_north) FROM bus_stops1 a, bus_stops1 b WHERE a.stop_reference =
6200206290 AND b.stop_reference = 6200244450;

stop_reference | stop_reference |     distance
----------------+----------------+------------------
 6200206290     | 6200244450     | 4320.88972319359

SRID (4326):

SELECT a.stop_reference, b.stop_reference, distance(a.googlemap,
b.googlemap) FROM bus_stops1 a, bus_stops1 b WHERE a.stop_reference =
6200206290 AND b.stop_reference = 6200244450;

stop_reference | stop_reference |     distance
----------------+----------------+-------------------
 6200206290     | 6200244450     | 0.041147965100606

Here is the table I am selecting from:


            Table "s0679212.bus_stops1"
     Column     |         Type          | Modifiers
----------------+-----------------------+-----------
 stop_reference | character varying(12) | not null
 easting        | integer               | not null
 northing       | integer               | not null
 full_location  | character varying(50) |
 gazetteer_code | character varying(1)  |
 point_type     | character varying(1)  |
 nat_gazetteer  | character varying(7)  |
 district_name  | character varying(24) |
 town_name      | character varying(24) |
 east_north     | geometry              | not null
 lat            | double precision      |
 lon            | double precision      |
 googlemap      | geometry              |
Indexes:
    "bus_stops1_pkey" PRIMARY KEY, btree (stop_reference)
    "stops_distance1" gist (east_north)
Check constraints:
    "enforce_dims_east_north" CHECK (ndims(east_north) = 2)
    "enforce_dims_googlemap" CHECK (ndims(googlemap) = 2)
    "enforce_geotype_east_north" CHECK (geometrytype(east_north) =
'POINT'::text OR east_north IS NULL)
    "enforce_geotype_googlemap" CHECK (geometrytype(googlemap) =
'POINT'::text OR googlemap IS NULL)
    "enforce_srid_east_north" CHECK (srid(east_north) = 27700)
    "enforce_srid_googlemap" CHECK (srid(googlemap) = 4326)

I created the googlemap column using these commands:

SELECT AddGeometryColumn( 'bus_stops1', 'googlemap', 4326, 'POINT', 2 );
UPDATE bus_stops1 SET googlemap = transform(setsrid(makepoint(easting,
northing),27700), 4326);


Can anyone suggest what on earth could be wrong?





 

________________________________

Yahoo! Mail is the world's favourite email. Don't settle for less, sign
up for your free account today
<http://uk.rd.yahoo.com/evt=44106/*http:/uk.docs.yahoo.com/mail/winter07
..html> .

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

 

 

________________________________

Yahoo! Mail is the world's favourite email. Don't settle for less, sign
up for your free account today
<http://uk.rd.yahoo.com/evt=44106/*http:/uk.docs.yahoo.com/mail/winter07
..html> .




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


More information about the postgis-users mailing list