PostgreSQL + PostGIS count with ST_DWithin multiple distance at once

Question

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;

Answer 1

If the counts are supposed to be cumulative, simply use a filtered aggregate:

SELECT COUNT(*) FILTER (WHERE ST_DWithin(<geom>::GEOGRAPHY, ST_SetSRID(ST_MakePoint(8.398522, 47.000084), 4326)::GEOGRAPHY, 5000)) AS "5km_cnt",
       COUNT(*) FILTER (WHERE ST_DWithin(<geom>::GEOGRAPHY, ST_SetSRID(ST_MakePoint(8.398522, 47.000084), 4326)::GEOGRAPHY, 10000)) AS "10km_cnt",
       COUNT(*) AS "20km_cnt"
FROM   <table>
WHERE  <table>.published
  AND  ST_DWithin(<geom>::GEOGRAPHY, ST_SetSRID(ST_MakePoint(8.398522, 47.000084), 4326)::GEOGRAPHY, 20000)
;

---

Note that I used the cast to GEOGRAPHY here; to utilize an index you want to create one on the CAST:

CREATE INDEX ON <table>
  USING GIST ((<geom>::GEOGRAPHY))
;