CODEWITHSUNDEEP
postgis
Will Shatford
Will Shatford

Reputation: 21

Best postgis distance query

I'm trying to find the fastest way to get a count for properties with a radius. I've seen some of the other similar question. Does any one know why the third query gives such different results? It is super fast.

Slow

SELECT count(*)
FROM property
WHERE ST_Distance_Sphere(geom_pt, ST_MakePoint(-104.989879,39.736355))<=2000;

count=2665

Very Slow

SELECT count(*)
FROM property
WHERE ST_Distance(geom_pt, ST_SetSRID(ST_MakePoint(-104.989879, 39.736355),4326)::geography)<=2000;

count=2665

Very Fast

SELECT count(*)
FROM property
WHERE ST_Within(geom_pt,ST_Transform(ST_Buffer(ST_Transform(ST_SetSRID(ST_MakePoint(-104.989879, 39.736355), 4326), 3857), 2000), 4326));

count=1794

Upvotes: 2

Views: 231

Answers (1)

Evan Carroll
Evan Carroll

Reputation: 1

Nope, you want ST_DWithin which will use a GIST index.

SELECT count(*)
FROM property
WHERE ST_DWithin(
  geom_pt, -- make sure this is also geography
  ST_MakePoint(-104.989879, 39.736355)::geography,
  2000     -- note distance in meters.
);

You also don't have to set srid to 4326 on geography. It's the default.

If you don't have the index,

CREATE INDEX ON property USING gist(geom_pt);
VACUUM property;

You can CLUSTER on geom_pt too.

Upvotes: 2

Related Questions