2

We have geometries in a Postgis database with SRID WGS84 and we have found lookups directly in degrees to be much faster than in kilometres, because the database can skip the projections I would think.

So, having a reference point in coordinates and a distance threshold in kilometres, is there a somewhat precise way to convert the distance to degrees for the lookup?

Example: Finding all locations within 10 Km of POINT(-3, 40). How do I convert the 10 Km to a degree value?

  • 1
    It depends on your definition of "somewhat precise" (and how far it bends to "not particularly precise") and the latitude, and the orientation, but you'd probably be better off breaking down and doing spheroidal calclations. – Vince Aug 11 '15 at 12:12
  • 1
    can you add the actual query you are trying. DWithin on Geographies should be reasonably fast with an index, – Ian Turton Aug 11 '15 at 12:16
  • @iant yes, DWithin is fast and that's the point. I cant use kilometres as an argument for DWithin with this SRID. – Adrián Aug 11 '15 at 12:19
  • 1
    You could if you used a geography type instead of geometry postgis.net/docs/ST_DWithin.html and postgis.net/docs/manual-2.0/…. – user30184 Aug 11 '15 at 12:23
  • @Vince The queries at the moment are to identify similar locations nearby and merge duplicates, and for calculating areas of relevance for events. Identifying if they are approximately in the same part of a city should be enough. – Adrián Aug 11 '15 at 12:23
4

The length of degree in north-south is about the same so you could use 1/110574 degree/meter as a factor. However, the farther to south or north you go the bigger the error is in east-west direction.

For example, take these two shapes which have a 1 degree buffer in EPSG:4326 transformed into EPSG:32630 (UTM zone 30N). First one is from 40°N and the second from higher north at 70°N.

SELECT ST_Transform(ST_Buffer(ST_GeomFromText('POINT (-3 40)',4326),1),32630);
SELECT ST_Transform(ST_Buffer(ST_GeomFromText('POINT (-3 70)',4326),1),32630);

Draw them side by side and it is easy to see that in UTM projection the area of the degree buffer is oval and it gets quite narrow at 70°N. The height of both ovals is about 220 km and widths at 40° and 70° are about 170 km and 80 km, respectively. I wouldn't call even the oval at 40°N as somewhat precise. For more accurate results you can use this calculator http://www.csgnetwork.com/degreelenllavcalc.html

enter image description here

  • So no way to increase precision with the reference point? Like, taking a different conversion factor according to the latitude of reference? What about using the UTM zone that the point falls into? – Adrián Aug 13 '15 at 8:23
  • 1
    One possibility is to take the lon-lat coordinates of the EPSG:4326 point -> transform into UTM zone of the longitude -> compute a circle polygon in UTM by using buffer -> tranform the circle back to EPSG:4326. But it might be accurate enough for you to stay with EPSG:4326 and use a rectangle for selection. Length factor of one degree E-W at certain latitude (in degrees) is length of latitude N-S * COS(PI)/(180/latitude)) Play a bit with Excel or calculator and you will see how to use the formula. Idea is to make the selection rectangle wider when you travel towards the poles. – user30184 Aug 13 '15 at 9:13
  • I found a relevant chapter on Postgis in Action (3.2.4) that suggests partial indexes on each of the projections to UTM zones and a simple function to match lon/lat and zone. I've been doing some tests with an index on the geography cast (geography(geom)) (seems easier to me) and getting good results so far. Any idea why that would not be the recommendation by the book? – Adrián Aug 14 '15 at 11:43
  • Writers have very long career with PostGIS. Perhaps they have simply used the UTM zone method for years before cast to geography was even possible. – user30184 Aug 14 '15 at 12:27
  • I made some tests and here is a query that should returns an elliptic polygon in EPSG:4326 with 10 km buffer: select cast (ST_Buffer(cast(ST_GeomFromText('POINT (-3 70)',4326) as geography),10000) as geometry) . Feels very simple and useful indeed, please check if it looks correct to you too. – user30184 Aug 14 '15 at 12:39

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.