I am using ST_DWithin
to count rows that are within 5KM, What I want is count on multiple distance at once with single query i.e count for 5KM, 10KM, 20M
Current Query
SELECT count(*) AS cnt FROM table_name
WHERE table_name.published IS TRUE AND
ST_DWithin(ST_Transform(table_name.geo_point, 26986),
ST_Transform(ST_SetSRID(ST_MakePoint(8.398522, 47.000084), 4326), 26986), 5000)
Here, 5000 = 5KM
What I Want
KM_5 KM_10 KM_20
100 215 300
Created Index
CREATE INDEX properties_geo_point_transform_idx ON
properties
USING gist (ST_Transform(geo_point, 26986)) WHERE
geo_point IS NOT NULL;
SELECT (...) as cnt_5km, (...) as cnt_10km, (...) as cnt_20km
with the (modified) query above in place of the ellipsis. Note that '5km_cnt' is not a legal SQL name (leading numeric), none of these queries are using indexes, and that ageography
cast would eliminate the need to do aST_Transform
(which is inaccurate if the geographic points are in WGS84) – Vince Jan 30 at 16:43