I'll try to answer everyone's suggestions:
I didn't know that Yahoo's service was in 4326. That did change things up a
bit. And, yes, I did copy the value from Birmingham to Miami twice. Sorry
about that. . .
My customer was comparing my results to a different source but it seems
close to Google Maps. I know that I was using the road miles in Birmingham
to Miami (
and other southern cites) but my other mileage to the northern cities was
>5% off from the Google Maps was. So that I was kinda close.
Now I combined a lot of the suggestions, so now my Geometry looks like:
select AddGeometryColumn('cities','geom',2163,'POINT',2);
UPDATE cities SET geom=transform(setsrid(makepoint(longitude,
latitude),4326),2163
Birmingham to Miami is:
leader=# select distance( (select geom from cities where id=104), (select
geom from cities where id=41) )*0.000621371192 as miles;
miles
------------------
656.738138375537
(1 row)
Which is around 13% off in the other direction.
But McHenry to Dallas is now off from what I had:
leader=# select distance( (select geom from cities where id=26251), (select
geom from cities where id=67) )*0.000621371192 as miles;
miles
---------------
808.949257448
(1 row)
it was 997, which was close to Google Maps. Now it's further off.
But I understand what everyone is saying, and makes sense that flying
distance would be shorter than the road distance. I think I'm comparing
apples to oranges and it was by chance that some random samples I tested
with were close. I think I need to find some interstate shapefiles, put
them in, and have PostGIS calculate the distance between those points on
that route.
Thanks everyone.
On Mon, Aug 15, 2011 at 11:04 AM, Paul Ramsey <pram...@opengeo.org> wrote:
> You're using polar stereograpic as your projection?
>
> http://nsidc.org/data/atlas/epsg_32661.html
>
> Well, yes, the farther south you go, the more over-determined your
> distance will be.
>
> Use the geography type, as suggested below, or the
> st_distance_spheroid() function, or just use a projection that is more
> conformal for the area of interest (continental USA?
> http://prj2epsg.org/epsg/2163)
>
> P.
>
> On Mon, Aug 15, 2011 at 3:50 AM, 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.
> >
> > --
> > Mike Hostetler
> > SquarePeg Systems
> > http://www.squarepegsystems.com
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users@postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
> _______________________________________________
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
--
Mike Hostetler
SquarePeg Systems
http://www.squarepegsystems.com
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
