[GIS] ST_DWithin Calc Meters – transform or cast

coordinate systempostgisst-dwithin

I want to use ST_DWithin on data which is stored in SRID geometry of 4326 and use meters as the distance parameter. Is it more efficient to do a cast (e.g. data.geom::geography) or a transform to an SRID with units of meters (e.g. ST_Transform(geom, 3857)? Or neither?

Best Answer

As described, the answer is "neither", for the following reasons:

  • Take a spatial table in 4326. Build a spatial index on it. The spatial index is a planar index, consisting of the 2D bounds of the features, in 4326, sorted into a tree structure.
  • (a) run a distance filter query using a cast, like ST_DWithin(geom::geography, %anothergeom, %radius). Because geography is involved, the system will look for a geography index (which is built on a sphere, not on a plane) and will find none. Since it has no index, it will perform the join using full scans of the table(s). It will be slow.
  • (b) run a distance filter query using a transform, like ST_DWithin(ST_Transform(geom, 2163), %anothergeom, %radius). Your tests is not against the indexed column (geom), but against a function applied to the column (ST_Transform(geom,2163)) and so again, your spatial index will not be used. It will be slow.

You need for your query and your index to harmonize. If you do not want to change the projection of your data, you will have to use a functional index, for example, if you create a function geography index, you can use a geography-based query:

CREATE INDEX mytable_geog_x 
  ON mytable USING GIST (geography(geom));

SELECT * 
  FROM mytable 
  WHERE ST_DWithin(geography(geom), %anothergeography, %radius);

Or, in the transform case:

CREATE INDEX mytable_geog_x 
  ON mytable USING GIST (ST_Transform(geom, 2163));

SELECT * 
  FROM mytable 
  WHERE ST_DWithin(ST_Transform(geom, 2163), %another2163geometry, %radius);

The absolute fastest performance will be if you convert the data in your table to a planar projection (like EPSG:2163), create a spatial index, and then use ST_DWithin() on the result.

ALTER TABLE mytable 
  ALTER COLUMN geom 
  TYPE Geometry(Point, 2163) 
  USING ST_Transform(geom, 2163);

CREATE INDEX mytable_geom_x ON mytable USING GIST (geom);

SELECT * 
  FROM mytable
  WHERE ST_DWithin(geom, %some2163geom, %radius)
Related Question