3

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;
2
  • If the query was more complex, you could use a CTE, but you just need to use three subqueries here: 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 a geography cast would eliminate the need to do a ST_Transform (which is inaccurate if the geographic points are in WGS84)
    – Vince
    Jan 30 at 16:43
  • Also, EPSG:26986 is completely inappropriate for 8E,47N
    – Vince
    Jan 30 at 17:27
5

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))
;
5
  • thanks for the answer sir, but I have created the index already so do I need to case geography here or using ST_Transform will do the trick? As it is already using my index Jan 30 at 19:22
  • @AlwaysSunny "Also, EPSG:26986 is completely inappropriate for 8E,47N - Vince" - you can use ST_Transform, but you want to be extra careful.
    – geozelot
    Jan 30 at 19:30
  • 1
    Your ST_Transform SRID is very incorrect. You should not be using it. Using a geography cast is the correct solution here.
    – Vince
    Jan 30 at 19:31
  • Okay sir thanks for the reply I will change then. Jan 30 at 19:42
  • I have used this example while using ST_Transform 26896 from here postgis.net/docs/ST_Transform.html Jan 31 at 11:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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