[postgis-users] How to get the distance between geometry inmeters?

[Kevin Neufeld]

Ah.  So your first attempt is incorrect.  When you first create your 
geometries, you need to set the srid of your new geometries to the 
projection they are currently in. Since your input is lat/long, you need 
to set the srid of your new geometries to the lat/long projection. 
*Then* you can transform your geometries into 26912 and query for things 
like distance.

Try this:
select AddGeometryColumn('table','geocode', 26912, 'POINT', 2);
update table
set geocode = transform(PointFromText('POINT(' || longitude || ' ' || 
latitude || ')', 4269), 26912);

-- or better, since going through the text representation introduces 
coordinate drifting.
select AddGeometryColumn('table','geocode', 26912, 'POINT', 2);
update table
set geocode = transform(setsrid(MakePoint(longitude, latitude), 4269), 
26912);

then distance(geocode, geocode) will return meters.

I hope this clarifies things.
Cheers,
Kevin

R H wrote:
> My explanation wasn't that great.  Below is what I did.
>  
> 26912 projection is in meters (right?)
> select AddGeometryColumn('table','geocode', 26912, 'POINT', 2);
> update table
> set geocode = PointFromText('POINT(' || longitude || ' ' || latitude 
> || ')', 26912);
> distance(geocode, geocode) returns degrees
>  
> ALTER TABLE property DROP COLUMN geocode
>  
> 4269 projection in degrees
> select AddGeometryColumn('table','geocode', 4269, 'POINT', 2);
> update table
> set geocode = PointFromText('POINT(' || longitude || ' ' || latitude 
> || ')', 4269);
> distance(transform(geocode, 26912), transform(geocode,26912)) returns 
> meters
>
>  
> I would prefer not to do the transform() if don't have to speed and 
> accuracy reasons.  Thanks for help. : }
>