# Points which are beyond certain distance from multiple points

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.

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')
);
``````

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()
``````

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