Thanks Nicklas and Charles for your help. I'm a babe in the woods with this even after reading the postgis book. Lots of new terminology.<br><br>I'll check your information today and report back to the list. <br><br>
ST_Distance(geog,geog) is supposed to report only in meters according to the documentation.<br><br><a href="http://www.postgis.org/documentation/manual-svn/ST_Distance.html">http://www.postgis.org/documentation/manual-svn/ST_Distance.html</a><br>
<br>"For geography type defaults to return the minimum distance around WGS 84 spheroid between two geographies in meters. "<br><br>I'm thinking it' a planar vs spheriod thing. I'll do the same as Nicklas and check on Google Earth. <br>
<br>The trick with GIS is that it's not like picking up a new API set, you've got a whole world of scientific terms to start understanding!<br><br>Again, thanks very much for your help. Great list!<br><br><div class="gmail_quote">
On Tue, Mar 13, 2012 at 11:26 PM, Nicklas Avén <span dir="ltr"><<a href="mailto:nicklas.aven@jordogskog.no">nicklas.aven@jordogskog.no</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Hallo again<br>
<br>
Stuart, what makes you say that you know the distance is 1800 meters?<br>
I just measured by hand in google earth and I get the same answer as<br>
PostGIS there about 1180 meters.<br>
<br>
I think you have been tricked by some application using web mercator<br>
projection in the first.<br>
<br>
Google earth, like PostGIS geography knows how to calculate distances on<br>
a sphere or spheroid which for instance google maps doesn't do since it<br>
is a flattened globe.<br>
<br>
HTH<br>
<span class="HOEnZb"><font color="#888888"><br>
Nicklas<br>
</font></span><div class="HOEnZb"><div class="h5"><br>
<br>
<br>
<br>
<br>
<br>
On Tue, 2012-03-13 at 12:50 +0100, Nicklas Avén wrote:<br>
> Sorry, wrong from me<br>
><br>
> Something is wrong of course if the answer is 1179 compared to 1800. My<br>
> brain was on vaccation<br>
><br>
> /Nicklas<br>
><br>
><br>
> On Tue, 2012-03-13 at 12:48 +0100, Nicklas Avén wrote:<br>
> > The geography type distance calculations gives answers in meters from<br>
> > unprojected data (4326).<br>
> ><br>
> > I see no erason to belive that the postgis answer is wrong, especially<br>
> > not when Stuart says he knows the distance is 1800 meters and he gets<br>
> > 1179.61 meters from PostGIS.<br>
> ><br>
> > I don't know anything about that draft-logic site but I would definitly<br>
> > rely more on the PostGIS answer.<br>
> ><br>
> > The measurments on Bing, Google maps and so on will be wrong since the<br>
> > webmercator projection 913900 is a flattened globe. How could it be<br>
> > correct?<br>
> ><br>
> > /Nicklas<br>
> ><br>
> ><br>
> > On Tue, 2012-03-13 at 07:37 -0400, Charles Galpin wrote:<br>
> > > The distance should be in the projection units, which for 4326 is<br>
> > > decimal degrees, and if you want meters you'll need to convert the<br>
> > > results to meters.<br>
> > ><br>
> > ><br>
> > > But first, I suspect your data is not in 4326 then if you get 1179.<br>
> > > Make sure you are using the SRID when inserting them<br>
> > ><br>
> > ><br>
> > > > update address set geog=ST_GeographyFromText('POINT('||<br>
> > > > lon||' '||lat||')',4326) where id=?;<br>
> > > Also see<br>
> > > <a href="http://postgis.refractions.net/docs/ST_Distance.html" target="_blank">http://postgis.refractions.net/docs/ST_Distance.html</a><br>
> > > hth<br>
> > > charles<br>
> > ><br>
> > > On Mar 13, 2012, at 2:58 AM, Stuart Guthrie wrote:<br>
> > ><br>
> > > > Could it be returning a distance in decimal miles? I'm out by around<br>
> > > > 1.6 - 1.6km = 1mile?<br>
> > > ><br>
> > > > On Tue, Mar 13, 2012 at 5:43 PM, Stuart Guthrie<br>
> > > > <<a href="mailto:stuartguthrie@gmail.com">stuartguthrie@gmail.com</a>> wrote:<br>
> > > > OK, I'm a newbie to postgis but an old-timer with open<br>
> > > > source in general.<br>
> > > ><br>
> > > > Sorry to bother everyone but this problem is driving me a<br>
> > > > little nuts.<br>
> > > ><br>
> > > > I've bought the 'postgis in action' book and now I think I<br>
> > > > still know nothing.<br>
> > > ><br>
> > > > I'm trying to store long lats and do queries on them to<br>
> > > > establish distance and use that for reporting.<br>
> > > ><br>
> > > > I'm using this to update the table 'address'<br>
> > > ><br>
> > > > update address set geog=ST_GeographyFromText('POINT('||<br>
> > > > lon||' '||lat||')') where id=?;<br>
> > > ><br>
> > > > the address table field 'geog' has this definition:<br>
> > > ><br>
> > > > geog geography(Point,4326)<br>
> > > ><br>
> > > > When I compare two addresses that I know are around 1800m<br>
> > > > apart I get this:<br>
> > > ><br>
> > > > select a.addressline2, a.suburb, ST_Y(a.geog::geometry),<br>
> > > > ST_X(a.geog::geometry),<br>
> > > > b.addressline2,b.suburb,ST_Y(b.geog::geometry),<br>
> > > > ST_X(b.geog::geometry), ST_distance(a.geog,b.geog ) from<br>
> > > > address a join address b on <a href="http://b.id" target="_blank">b.id</a>=41491 where <a href="http://a.id" target="_blank">a.id</a>=1238 ;<br>
> > > ><br>
> > > > addressline2 | suburb | st_y | st_x |<br>
> > > > addressline2 | suburb | st_y | st_x |<br>
> > > > st_distance<br>
> > > > ---------------+--------+-------------+-------------+----------------+-------------+-------------+-------------+------------------<br>
> > > > 140 Sussex St | Sydney | -33.8692429 | 151.2036312 | 1<br>
> > > > Riley Street | Surry Hills | -33.8726426 | 151.2157115 |<br>
> > > > 1179.61881540556<br>
> > > ><br>
> > > > Postgis says they are 1179.61m apart.<br>
> > > ><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>
> ><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>
> ><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>
><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"><br>-- <br>Best regards<br><br>Stuart Guthrie<br>(m) 0403 470 123<br><br><br>