[postgis-users] Re: Working with point data from google maps in postgis

[Rob Young]

Paul, thanks for your reply, I should have checked that!  Works perfectly now.

Rob

On 1/14/07, Rob Young <bobbotron at gmail.com> wrote:
> Thanks for the reply Paul!  That makes everything make a bit more sense.
> I have one more question.  I created a small table to test this out
> and seem to be getting some funny results.  I inserted three points,
> (35 -110), (36 -110) and (37 -111).  I ran the following query on the
> table to see what the distance and distance_sphere functions returned
> when comparing each point in the table to the point (35, -110).
>
> select name,   --- I added a name field so
>                     --- I could label the points I included
>       AsText(point),
>       distance_sphere( point, GeomFromText( 'POINT( 35 -110)', 4326)
> ) as sphere_distance,
>       distance( point, GeomFromText( 'POINT( 35 -110)', 4326) ) as distance
> from test;
>
> Which returned
> "a";"POINT(35 -110)";3.14288420845923e-010;0
> "b";"POINT(36 -110)";38030.4002184108;1
> "c";"POINT(37 -111)";135742.15137218;2.23606797749979
>
> These results don't make sense to me though.  I just measured these
> distances in google earth (which I assumed is reasonably correct, and
> got the results below.
>
> point   distance (in km to the point (35 -110))
> a         0
> b         110
> c         240
>
> These values don't match up with those returned by postgis.  Any
> suggestions on what I've done wrong?
>
> Thanks again,
> Robert
>
> On 1/13/07, Rob Young <bobbotron at gmail.com> wrote:
> >
> > I'm looking to make a small application with google maps and postgis, where I would enter spatial data into postgis via an interface that would use a (google) map to define lat/long points.  I'm pretty familiar with databases, and know just enough about projection systems to know I don't know that much.  ;)  I am concerned about getting the following right, as I know if I don't I will be introducing errors into any calculations I attempt with postigs queries.
> >
> > I'm not completely sure which SRID to use with Google maps point data.  I have found the following helpful message, but I was hoping to confirm that.
> >
> > http://postgis.refractions.net/pipermail/postgis-users/2005-November/009920.html
> >
> > If google maps does use the GRS 80 projection and a mercator projection, which SRID should I use when creating postgis tables to store said data?  There are quite a few GRS 80 types in the spatial_ref_sys table.  I know enough to know that choosing the correct one is important, but am really in the dark as to which one I should actually choose!  :)
> >
> > Also, I will be interested in doing distance based queries (like everyone) on this data once it's in the database.  The postgis function length uses the project you associate with the geometry data, correct?  And the units of the number it returns is specified by the SRID?
> >
> > Thanks for the help,
> >
> > Robert Y
>