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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> To: PostGIS Users Discussion <[email protected]> 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Alan Cunnane Sent: Thursday, July 19, 2007 2:51 PM To: [email protected] 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 [email protected] 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.
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
