I'll try to answer everyone's suggestions:<div><br></div><div>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. . .</div>
<div><br></div><div>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 (<br>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.</div>
<div><br></div><div>Now I combined a lot of the suggestions, so now my Geometry looks like:</div><div><div>select AddGeometryColumn('cities','geom',2163,'POINT',2);</div><div>UPDATE cities SET geom=transform(setsrid(makepoint(longitude, latitude),4326),2163</div>
</div><div><br></div><div>Birmingham to Miami is:</div><div><div>leader=# select distance( (select geom from cities where id=104), (select geom from cities where id=41) )*0.000621371192 as miles;</div><div> miles </div>
<div>------------------</div><div> 656.738138375537</div><div>(1 row)</div></div><div><br></div><div><br></div><div>Which is around 13% off in the other direction.</div><div><br></div><div>But McHenry to Dallas is now off from what I had:</div>
<div><div>leader=# select distance( (select geom from cities where id=26251), (select geom from cities where id=67) )*0.000621371192 as miles;</div><div> miles </div><div>---------------</div><div> 808.949257448</div>
<div>(1 row)</div></div><div><br></div><div>it was 997, which was close to Google Maps. Now it's further off.</div><div><br></div><div>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.</div>
<div><br></div><div>Thanks everyone.</div><div><br><div class="gmail_quote">On Mon, Aug 15, 2011 at 11:04 AM, Paul Ramsey <span dir="ltr"><<a href="mailto:pramsey@opengeo.org">pramsey@opengeo.org</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">You're using polar stereograpic as your projection?<br>
<br>
<a href="http://nsidc.org/data/atlas/epsg_32661.html" target="_blank">http://nsidc.org/data/atlas/epsg_32661.html</a><br>
<br>
Well, yes, the farther south you go, the more over-determined your<br>
distance will be.<br>
<br>
Use the geography type, as suggested below, or the<br>
st_distance_spheroid() function, or just use a projection that is more<br>
conformal for the area of interest (continental USA?<br>
<a href="http://prj2epsg.org/epsg/2163" target="_blank">http://prj2epsg.org/epsg/2163</a>)<br>
<font color="#888888"><br>
P.<br>
</font><div><div></div><div class="h5"><br>
On Mon, Aug 15, 2011 at 3:50 AM, Mike Hostetler<br>
<<a href="mailto:mike@squarepegsystems.com">mike@squarepegsystems.com</a>> wrote:<br>
> Hello,<br>
> I'm somewhat new to GIS and I have a problem that I thought appeared to be<br>
> simply using a wrong projection or datum, but it seems to be a bit more<br>
> subtle than that.<br>
> I have a table of cities in the US and I'm trying to find distances between<br>
> them. When I use a city that is in the northern US, it works fine. When I<br>
> try to find the distance between two cities in the Southern US, the distance<br>
> becomes way off.<br>
> I setup a Geometry in my cities table and populated it like the following:<br>
> select AddGeometryColumn('cities','geom',32661,'POINT',2);<br>
> UPDATE cities SET geom=transform(setsrid(makepoint(longitude,<br>
> latitude),4269), 32661)<br>
> (I find the latitude and longitude from the Yahoo Geocode service)<br>
> A distance calc from McHenry, IL to Dallas, TX is calculated as:<br>
> select distance( (select geom from cities where id=26251), (select geom from<br>
> cities where id=67) )*0.000621371192 as miles;<br>
> miles<br>
> ------------------<br>
> 996.717850542391<br>
> (Google Maps reads as 972, off by 25 miles or off around 4%)<br>
><br>
> But Birmingham, AL, to Miami, FL is calculated as:<br>
> leader=# select distance( (select geom from cities where id=26251), (select<br>
> geom from cities where id=67) )*0.000621371192 as miles;<br>
> miles<br>
> ------------------<br>
> 996.717850542391<br>
> (Google Maps reads as 767, off by 120 files, or 13%).<br>
> I can handle a little error, as long as it's somewhat small (<5%). But this<br>
> is way off.<br>
> Again, it smells to be to be a datum or projection issue to me, but I'm not<br>
> sure how to find the sweet spot to be accurate everywhere.<br>
> Your input is appreciated.<br>
><br>
> --<br>
> Mike Hostetler<br>
> SquarePeg Systems<br>
> <a href="http://www.squarepegsystems.com" target="_blank">http://www.squarepegsystems.com</a><br>
><br>
</div></div><div><div></div><div class="h5">> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
><br>
><br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</div></div></blockquote></div><br><br clear="all"><div><br></div>-- <br>Mike Hostetler<br>SquarePeg Systems<br><a href="http://www.squarepegsystems.com">http://www.squarepegsystems.com</a><br>
</div>