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

[Alan Cunnane]

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 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070719/72d3bc9e/attachment.html>