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