9

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?

  • What spatial extent is your data? – DPSSpatial Dec 19 '16 at 21:35
  • The data cover the whole of the United States. – kingzing1 Dec 19 '16 at 23:45
  • If the indexed data is not geography then youd be better off approximating with degrees in ST_DWithin, then using a cast with ST_Distance.. – Vince Dec 20 '16 at 1:27
  • I would try to transform to Albers Equal Area Conic for the Continential USA, which I believe is in meters (metres?)... try it on a few features first... that's where I would start... – DPSSpatial Dec 20 '16 at 2:06
  • what about "just trying which is faster"? – Stophface Dec 20 '16 at 21:26
14

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)
  • Thanks for this Paul. Very helpful - it dramatically speeds up the time to process. – kingzing1 Dec 22 '16 at 17:46
  • But won't the last two queries differ from the first one in which the latter calculate planar distances, and the former geodetic ones? – Andre Silva Sep 13 '18 at 14:06
  • This is a great answer, but what's confusing me is that it feels like it's contradicting the docs: postgis.net/docs/manual-dev/PostGIS_FAQ.html#idm1363 Are the docs incorrect or am I misreading them? – Olshansk Oct 15 at 15: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.