Count number of points outside of Buffer?

Question

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

Answer 1

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.

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.

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.