On 15 August 2011 12:50, Mike Hostetler <m...@squarepegsystems.com> wrote:
> Hello,
> I'm somewhat new to GIS and I have a problem that I thought appeared to be
> simply using a wrong projection or datum, but it seems to be a bit more
> subtle than that.
> I have a table of cities in the US and I'm trying to find distances between
> them. When I use a city that is in the northern US, it works fine. When I
> try to find the distance between two cities in the Southern US, the distance
> becomes way off.
> I setup a Geometry in my cities table and populated it like the following:
> select AddGeometryColumn('cities','geom',32661,'POINT',2);
> UPDATE cities SET geom=transform(setsrid(makepoint(longitude,
> latitude),4269), 32661)
> (I find the latitude and longitude from the Yahoo Geocode service)
> A distance calc from McHenry, IL to Dallas, TX is calculated as:
> select distance( (select geom from cities where id=26251), (select geom from
> cities where id=67) )*0.000621371192 as miles;
> miles
> ------------------
> 996.717850542391
> (Google Maps reads as 972, off by 25 miles or off around 4%)
>
> But Birmingham, AL, to Miami, FL is calculated as:
> leader=# select distance( (select geom from cities where id=26251), (select
> geom from cities where id=67) )*0.000621371192 as miles;
> miles
> ------------------
> 996.717850542391
> (Google Maps reads as 767, off by 120 files, or 13%).
> I can handle a little error, as long as it's somewhat small (<5%). But this
> is way off.
> Again, it smells to be to be a datum or projection issue to me, but I'm not
> sure how to find the sweet spot to be accurate everywhere.
> Your input is appreciated.
>
Hi Mike,
Some remarks:
• Are you sure Yahoo! Geocoder Service returns degree expressed on
4269 coordinate system ? I read it is 4326.
• How did you get the distances with Google Maps ? Did you use travel
directions ? If so, the 972 miles is by driving on roads, not by
flying the shortest distance (great circle).
The following service:
http://www.geobytes.com/CityDistanceTool.htm?loadpage
gave me a direct distance of 808 miles or 1300km for McHenry - Dallas
and a distance of 658 miles or 1059 km for Birmingham - Miami.
Then, by running these queries the distance between cities seems to be good:
select foo.city, bar.city, st_distance(foo.geom,
bar.geom)*0.000621371192 as miles
from
(select 'McHenry' as city, 'IL' as state, 'POINT(-88.267314
42.326215)'::geography as geom) as foo,
(select 'Dallas' as city, 'TX' as state, 'POINT(-96.795404
32.778155)'::geography as geom) as bar;
city | city | miles
---------+--------+------------------
McHenry | Dallas | 807.029700174124
select foo.city, bar.city, st_distance(foo.geom,
bar.geom)*0.000621371192 as miles
from
(select 'Birmingham' as city, 'AL' as state, 'POINT(-86.811504
33.520295)'::geography as geom) as foo,
(select 'Miami' as city, 'FL' as state, 'POINT(-80.237419
25.728985)'::geography as geom) as bar
city | city | miles
------------+-------+------------------
Birmingham | Miami | 666.318599210394
The slight differences between CityDistanceTool and Yahoo! services
come from the cities coordinates: the two services do not position
cities at the same coordinates.
Also please note I'm using the new GEOGRAPHY Postgis type that allows
direct distance computation: no need to transform data back to a
planar coordinate system.
If you use geometry type with long/lat coordinates, you may have a
look at st_distanceSphere and st_distanceSpheroid.
Nicolas
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
