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? Commented Dec 19, 2016 at 21:35
  • The data cover the whole of the United States.
    – kingzing1
    Commented Dec 19, 2016 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
    Commented Dec 20, 2016 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... Commented Dec 20, 2016 at 2:06
  • what about "just trying which is faster"?
    – four-eyes
    Commented Dec 20, 2016 at 21:26

1 Answer 1


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));

  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));

  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 
  TYPE Geometry(Point, 2163) 
  USING ST_Transform(geom, 2163);

CREATE INDEX mytable_geom_x ON mytable USING GIST (geom);

  FROM mytable
  WHERE ST_DWithin(geom, %some2163geom, %radius)
  • Thanks for this Paul. Very helpful - it dramatically speeds up the time to process.
    – kingzing1
    Commented Dec 22, 2016 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? Commented Sep 13, 2018 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?
    – Olshansky
    Commented Oct 15, 2019 at 15:39
  • A lot of information in a small answer, thanks
    – Molochnik
    Commented Jan 18 at 17:17

Your Answer

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

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