5

I am running a PostgreSQL 9.1 Database with PostGIS 1.5 extension. Several coordinates are stored in there with type GEOMETRY (SRID 4326). I'm building a web-application with Google Maps Javascript API v3 to display those points. For any point on the map and a definable distance, I now want to highlight markers if they are within the radius of that point.

This query returns the IDs of all those points:

WITH chosen AS (SELECT * FROM ST_SetSRID (ST_MakePoint ($1, $2), 4326))
SELECT mypoints.id FROM mypoints, chosen
WHERE ST_DWithin (mypoints.location::geography, chosen.ST_SetSRID::geography, $3);

The ::geography casts are needed here to use meters as measurement unit instead of degrees. $1,$2 is lon,lat, $3 is the chosen distance in meters.

Clientside, only the markers resulting from the query receive full opacity and I also display a google.maps.Circle with the chosen distance as radius. As you can see on the picture below, some markers that should be highlighted if you trust the red circle are not returned by my query. The difference also only seems to occur on the X-axis.


Now what is wrong here?

Is the circle shape from the GMaps JS API inaccurate for my region (Germany)?

Or am I missing something in my query?


So far, i really think that martin_f is right about the distortion. There are multiple coordinate systems involved here: https://stackoverflow.com/questions/15015946/google-map-api-v3-projection

Can anybody confirm that every Shape I draw in Google Maps Javascript API is in SRID 3857 and every Marker I add with Lat/Lon Values in SRID 4326 are internally converted to 3857 before they are shown on the map?

  • 3
    It would be useful if you could provide actual coordinates for the red marker, the distance, one of the correct blue ones and an incorrect blue one. – John Powell Dec 7 '14 at 9:09
2

I suspect the issue relates to the very real difference existing between geometry objects, existing on a plane map surface, and geography objects, existing on a curved globe surface.

It is always more accurate (but slower) to do calculations with geography types on a globe. Projecting features from a globe onto a plane always incurs distortion.

Looking at your SQL code, on the 3rd line you do cast to geography but for the "incomplete" reasons. You do it to use meters instead of degrees for the distance measure, and that is good. You should also do it because you get more accurate results.

Now, in the 1st line of your SQL code, you make a point out of coordinate parameters, but it is by default a geometry and so you end up not comparing like with like. Try casting the point you make to geography and see what happens.

Like this, i think:

ST_SetSRID(ST_MakePoint($1, $2),4326)::geography

If you draw circles using GMaps, they are probably not representing true circles on the globe. If "GMaps" (not sure what you mean, exactly) uses the Web Mercator projection, then a drawn circle really represents an "egg" shape on the globe, with the pointed end up north. That's how the Mercator projection distorts things -- things get hugely stretched as one moves away from the equator.

  • I guess you are right about the projection distorsion, so that means the query is correct and the circle is inaccurate - but i do already cast the point to geography in line 3, line 1 is just preparation. – Therion Dec 6 '14 at 23:46
  • Yes, i now see that i missed that in your SQL. I've now struck out the parts that are no longer relevant to your case -- but left them there because they could be applicable to similar problems -- and expanded and corrected the actual relevant part. (If anyone thinks it's a bad idea to leave in the struck out text, i'm open to deleting them completely.) – Martin F Dec 7 '14 at 18:21
  • 1
    Now i'm even having second thoughts about my "correct" answer: it would be relevant if your region were country or continental in size, but you have just a town neighborhood, too small to have that egg-circle effect. – Martin F Dec 7 '14 at 18:29
2

I finally figured it out:

The real problem was that I stored the coordinates with latitude as the first value. That is the way Google Earth or Google Maps need the values, but it is WRONG for PostGIS. The coordinates need to be stored as (lon,lat). The way I did it, distances were calculated for the sea near Somalia. :P

Now the distance calculations finally make sense and the red circle does not seem wrong at all. So if you draw a shape in Google Maps Javascript, i guess it is drawn for SRID 4326.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.