[postgis-users] Distances off in the Southern US

[Nicolas Ribot]

On 15 August 2011 12:50, Mike Hostetler <mike at 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