# geometry vs geography for distance measurement and large dataset

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?

• Commented Oct 12, 2022 at 12:55

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.

• Are you absolutely certain that `ST_DWithin` won't work on 4326 geometries? I'm a bit confused, I'm reading `From the 2nd edition of PostGIS in Action` and they show a query with this function, using meters and 4326 geometries: ibb.co/NmVRGg1. Commented Oct 12, 2022 at 9:37
• I am absolutely certain. That is, of course it works on `GEOMETRY(4326)`, but distance parameter and return value will be in degree! From the docs: "For geometry: The distance is specified in units defined by the spatial reference system of the geometries. Commented Oct 12, 2022 at 10:03
• What you're saying seems to make sense, but what about the example in the book (see image in previous comment)? The tables in the DB store coordinates as long/lat geometries, but the `ST_DWithin` function uses meters as the distance threshold. Commented Oct 12, 2022 at 10:13
• Oooooops, the SRID is 2163 after a couple of transforms Commented Oct 12, 2022 at 10:40
• Pretty sure they transform all exemplary data after insert to the (now deprecated, btw.) `EPSG:2163` Equal Area projection, which has metre as unit! Commented Oct 12, 2022 at 10:56