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

[Rob Young]

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