geometry vs geography for distance measurement and large dataset

Question

I'm working on a project where I have clusters of GPS coordinates to store, let's say ~20k points per cluster. The clusters overlay with big cities, like London or New York. I have many clusters around the globe, so the data isn't local (this could be important for the SRID).

I'm wondering what PostGIS type I should use to store this data, geometry or geography?

Once this data is stored, I'll mainly do only two kinds of computation (but I'll have to do them many many times so speed matters here):

• "Give me the points that are within 5 km of this particular point"
• "Is this point within 2-5 meters of this other point?"

My assumption is that geometry with the EPSG:4326 SRID should be good enough for this, since my distance measurements won't cover long distances. For the second point, I really need the accuracy though.

Is my assumption right?

Answer 1

Unfortunately, in order to work with metric units of measurement, a GEOMETRY(4326) by itself is not applicable - any function called on this type will use planar Cartesian algebra based on the primary unit of the CRS - here degree - for both input parameters and any resulting measurement value.

While there are a few functions that execute geodesic calculations on the GEOMETRY(4326) type (e.g. ST_DistanceSpher[e|oid]), they have close to no advantage over using the GEOGRAPHY type in the first place, except the obvious shortcut.

You are most likely bound to the comparably more expensive geodesic calculations here, so the question remains how to set up the data-set.

---

Personally I would always initially set up my data-set as GEOMETRY(4326), add functional indexes for the GEOGRAPHY type:

CREATE INDEX [name]
  ON <table>
  USING GIST ( (<geom_column>::GEOGRAPHY) )
;

and work with a cast at query-time when needed:

SELECT
  t.*
FROM
  <table> AS t
WHERE
  ST_DWithin(
    t.<geom_column>::GEOGRAPHY,
    <reference_geom>::GEOGRAPHY,
    <threshold_in_meter>
  )
;

This way you keep the full flexibility of the PostGIS functional environment as well as data interoperability with most client systems, while adding only a type cast to the execution time of your queries.

---

Start thinking about performance (or storage space) hunting only after validating the actual application - but I suspect that a) for the mentioned data sizes you won't see any significant performance bottlenecks and b) if this setup turns out to be too slow, you would want to invest in result caching methods rather than switching data types.