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

[Paul Ramsey]

Rob, the coordinate order in PostGIS is always (x y), so your example  
coordinates make no sense (y value < -90). Try reversing your  
ordinates and try again.

P

On 13-Jan-07, at 12:26 PM, Rob Young 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users