Points which are beyond certain distance from multiple points

Question

I have two tables - first is a list of positions (points) and second is a list of cities (cities). I am using PostGIS and can find all points outside 10nm of New York by using the SQL statement -

SELECT * FROM points, cities 
    WHERE ST_Distance(points.position, cities.geom) > 1 AND cities.city = 'New York'

However, if I want to find all points which are outside both New York and Washington, the query with an AND clause gives zero results..

SELECT * FROM points, cities 
    WHERE (ST_Distance(points.position, cities.geom) > 1 AND cities.city = 'New York') 
      AND (ST_Distance(points.position, cities.geom) > 1 AND cities.city = 'Washington')

Can you help on constructing an SQL statement which will find all points outside a certain range of multiple cities?

I am also using SQLAlchemy.

Answer 1

Geometric non-predicates are tricky, and your attempt in particular cannot benefit from a spatial index.

Consider this approach, which essentially inverts the filter and excludes matches, while being fully index driven (either on cities.city or cities.geom):

SELECT *
FROM   points AS pt
WHERE NOT EXISTS (
  SELECT 1
  FROM   cities AS ct
  WHERE  ST_DWithin(ct.geom, pt.position, 1)
    AND  city IN ('New York', 'Washington')
);

Answer 2

Just adding to the solution by @geozelot for posterity. Anyone wanting to use the above SQL statement in Python / SQLAlchemy, here is the statement -

stmt = ~select([1]).where(func.ST_DWithin(points.c.geom.cast(Geography), cities.c.geom.cast(Geography), DISTANCE_IN_METRES)). \
                    where(cities.c.city_id.in_(CITY_ID_TUPLE)).exists()

Answer 3

Sufficient answers have been given, so let me explain the (or a part of the) problem with your query:

SELECT * FROM points, cities 

creates a kartesian product from points and cities, with points.position, cities.geom, cities.city...

Now only the filter

WHERE cities.city = 'New York' AND cities.city = 'Washington'

will elimiante all rows, since cities.city can only have one value in a row