4

I'm trying to count the total points outside of a buffer zone using PostgreSQL. The intention is to find all of the schools that do not fall within a 2 mile radius to libraries.

I've either gotten results that return the total number of schools or nothing at all.

What is wrong with my query? 

SELECT ST_Buffer(mdlibrary.geom, 3218, 'endcap = round join = round') AS librarybuffer, mdlibrary.anchorname AS libraryname, count(mdschool.*) AS totalschools
FROM mdlibrary, mdschool
WHERE mdschool.geom IS NULL
GROUP BY mdlibrary.geom, mdlibrary.anchorname
| improve this question | |
6

Try to tackle you task in a bit different way. This question was already explained here Postgis – Opposite of ST_Within.

Let's assume there are two layers "schools" (orange) and "libraries" (purple), see image below.

input

So, your query can look like

SELECT s.*
FROM schools AS s
WHERE s."osm_id" NOT IN (
    SELECT s."osm_id"
    FROM schools AS s, libraries AS l
    WHERE st_within(s.geometry, st_buffer(l.geometry, 20))
 )

Find the output below (pink circles), including the schools that do not fall within a radius to libraries.

output

To count those schools, simply use COUNT()

SELECT COUNT(*) AS "schools_outside"
FROM schools AS s
WHERE s."osm_id" NOT IN (
    SELECT s."osm_id"
    FROM schools AS s, libraries AS l
    WHERE st_within(s.geometry, st_buffer(l.geometry, 20))
)

Additionally you may try using slightly adjusted query

SELECT s.*
FROM schools AS s
WHERE s."osm_id" NOT IN (
    SELECT s."osm_id"
    FROM schools AS s, libraries AS l
    WHERE st_disjoint(s.geometry, st_buffer(l.geometry, 20)) IS FALSE
)

P.S. Mind the CRS (SRID) for your area.

| improve this answer | |
  • 1
    But Beware! for it is written in the holy book of computational effectivity: Never shallt thou create or use buffers on-the-fly for proximity searches!, and furthermore: Nor is there need for true containment checks, for it is either in proximity, or not! - best use WHERE NOT EXISTS (SELECT 1 FROM libraries AS l WHERE ST_DWithin(geom, l.geom) as it terminates the subquery once a truthy (1) tuple is identified. Powered by the index. – geozelot May 13 at 8:57
  • Thank you for info! Shall I use alias for Buffer instead of on-the-fly? – Taras May 13 at 9:12
  • 1
    Don't use buffers at all. Note the usage of ST_DWithin in my comment (note also that I forgot the distance parameter in the function...). – geozelot May 13 at 9:32
  • Thank you. Can you adjust my answer? Or shall I delete it complete then you can write a correct one? Do not want to litter. – Taras May 13 at 9:39
  • 1
    No! The most important part of it all is what you covered elegantly; tests for non-intersection are tricky, and can only be solved by checking against a set of actual intersections to avoid a cartesian product. Your queries will work just fine, but I suggest to dive into why and how indexes kick in, and check on the benefits of [NOT] EXISTS in many cases (search e.g. "NOT IN vs ǸOT EXISTS") – geozelot May 13 at 9:50

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.