5

I have a requirement where I need to store lat/long coordinates for locations all around the world. I then need to query for those locations that are within a specified distance (in meters) from another location. These distances are usually within the 500-1000 meter range so I don't need crazy precision.

Following various examples I've found, I've been using SRID 4326 with my geometry column in a PostGIS database. The column simply holds lat/long coordinates.

The ST_DWithin method takes a distance in meters for geography types, but I don't know what the unit is for 4326. It doesn't seem to be meters with some queries that I tried. I'm assuming it's degrees.

I've read that for performing Spatial queries on small areas, it's best to use geometries since operations are quicker at the price of precision. How can I use these methods to provide meters for either ST_DWithin or ST_Distance?

Improve this question

1 Answer 1

Reset to default
10

The definition of SRID 4326 is in fact IN DEGREES. ALso as it says in the ST_DWithin page:

Returns true if the geometries are within the specified distance of one another. For geometry units are in those of spatial reference and For geography units are in meters and measurement is defaulted to use_spheroid=true (measure around spheroid),

Since you're are using the 4326 SRID, you can cast from geometry to geography and vice versa like this :

select st_distance(<geom_column>::geography) from myTable;

Most of the functions in postgis are overloaded, meaning, they return values and run a different procedure based on the input data type.

eg:

boolean ST_DWithin(geometry g1, geometry g2, double precision distance_of_srid);

boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters);

they have the same name, but the first function returns T/F based on units of the srid, and the second returns T/F based on meters.

If you plug into the function geog types, for its calculation, will take into account the curve of the earth so the algorithm 'costs' a bit more.

If you want to check how much more it costs, you can run for each query an EXPLAIN-ANALYSE

Improve this answer
4
  • Thanks! Two questions: 1) Will ::geography work on the ST_MakePoint and other constructors? 2) Would you recommend postgis' spatial indexing for my requirement on 100000 such locations?
    – Sotirios Delimanolis
    Jul 7, 2013 at 1:48
  • One more: Is there a point in storing my coordinates as geometries if I'm just going to cast them?
    – Sotirios Delimanolis
    Jul 7, 2013 at 1:55
  • 1
    @SotiriosDelimanolis . 1) The ST_MakePoint function definition expects at the very least a (double, double) and not (geog, geog). So no. 2) If you want to make most of postgis capabilities you must ALWAYS create a the spatial index of its corresponding geom/geog column. 3) Casting from geom <-> geog at the moment only works for SRID 4326. Also the functions that support geog are not (atm) that many as those that work with geom.Also you can have both geom/geog columns at the same time.
    – nickves
    Jul 7, 2013 at 8:36
  • What I meant for 1) was: can I do ST_SetSRID(ST_MakePoint(?, ?), 4326)::geography?
    – Sotirios Delimanolis
    Jul 7, 2013 at 13:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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