1

I'd like to begin by saying that I have very little knowledge and understanding when it comes to GIS (despite extensive experience with Google Maps API). PostGIS appears to be a whole new ball game on its own.

I've recently installed PostGIS (v2.1.5) with my PostgreSQL (v9.4) instance in order to begin storing and working with GIS data. I'm currently using Google Maps API v3's DrawingManager to draw polygons, circles, rectangles and markers which need to be stored in the database, and later retrieved to display the polygons again on Google Maps; These shapes will also be used for various aggregate queries.

I currently have two tables. The one is called "locations" which will store the polygons, circles and rectangles created with Google Maps' DrawingManager. The second, called "rawdata" will contain a set of records with a POINT (lat, long) inside of it.

First and foremost, I do not entirely understand the concept of projections and how they work, but, based on what I've read online is that calculations and accuracy may vary, significantly. I understand that Google Maps works with the Web Mercator projection which I believe is officially known as the EPSG:3857 projection (or CRS), while the preferred method for storing and working with geometry() shapes in PostGIS is in the EPSG:4326. As such and in reference to my create table statements below, I have chosen to store my geometry data in EPSG:4326 in the tables.

An easier method would to be store my geometry in the newer geographic format, but I do not wish to impose those performance and lack of functionality drawbacks as I will be working with a significant amount of data (hundreds of thousands, if not millions of records).

I am trying to test the ST_Point_Inside_Circle() function but this appears to be inaccurate and I can only assume so because the function expects degrees as the radius parameter, yet Google Maps supplies me with a radius in meters. In reference to http://resources.arcgis.com/en/help/main/10.1/index.html#//009t0000004v000000, I have tried converting meters to degrees by multiplying meters by 0.00001. However, the greater the radius in meters, the more inaccurate it appears to be. What can I do to achieve an accurate result?

SELECT ST_Point_Inside_Circle(ST_SetSRID(ST_MakePoint(28.239418677985668, -25.75443387513752), 4326), 28.241967111825943, -25.759674774140656, (583 * 0.00001));

Google Maps reports the distance in meters between the two points (center of circle and point) as 637 meters, yet ST_Point_Inside_Circle returns true when trying to convert 583 to degrees. This leads me to the assumption that one cannot convert meters to degrees using 0.00001.

What is the correct way to determine the radius with meters?

2

Without reading all the details I think:

You should use ST_DWithin instead

You should cast to geography type, or just use the geography type instead of geoemtry type which makes ST_DWithin work with meters instead of degrees

So your query will be something like:

SELECT ST_DWithin(ST_GeogFromText('POINT(28.239418677985668 -25.75443387513752)'),
ST_GeogFromText('POINT(28.241967111825943 -25.759674774140656)'), 583);

You can also check the distance like:

SELECT ST_Distance(ST_GeogFromText('POINT(28.239418677985668 -25.75443387513752)'),
ST_GeogFromText('POINT(28.241967111825943 -25.759674774140656)'));

Degrees can not be transformed to meters by just using a factor. 1 lon degree is very accurate 0 meters at the poles, but about 40000/360 kilometers by the equator.

  • Thanks for the help Nicklas. As per my original post, I do not wish to store the data in a geography type as this will impose performance and loss of functionality drawbacks. Therefore, as per your answer, the alternative then is to cast to geography type in my query. How does this casting affect performance in the queries though? – Paradox Mar 9 '15 at 5:54

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.