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.

3 Answers 3


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):

FROM   points AS pt
  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)). \

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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