# ST_DWithin Calc Meters - transform or cast?

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

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
``````

Or, in the transform case:

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

SELECT *
FROM mytable
``````

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