GeoAlchemy2: find a set of Geometry items that doesn't intersect with a separate set

Question

I have a postgis database table called tasks, mapped to a python class Task using geoalchemy2/sqlalchemy - each entry has a MultiPolygon geometry and an integer state. Collectively, entries in my database cover a geographic region. I want to select a random entry of state=0 which is not geographically adjacent to any entry of state=1.

Here's code which selects a random entry of state=0:

class Task(Base):
    __tablename__ = "tasks"
    id = Column(Integer, primary_key=True, index=True)
    geometry = Column(Geometry('MultiPolygon', srid=4326))
    state = Column(Integer, default=0)

session = DBSession()
taskgetter = session.query(Task).filter_by(state=0)
count = taskgetter.count()
if count != 0:
    atask = taskgetter.offset(random.randint(0, count-1)).first()

So far so good. But now, how to make sure that they are not adjacent to another set of entries?

Geoalchemy has a function ST_Union which can unify geometries, and ST_Disjoint which detects if they intersect or not. SO it seems I should be able to select items of state=1, union them into a single geometry, and then filter down my original query (above) to only keep the items that are disjoint to it. But I can't find a way to express this in geoalchemy. Here's one way I tried:

session = DBSession()
taskgetter = session.query(Task).filter_by(state=0) \
    .filter(Task.geometry.ST_Disjoint(session.query( \
            Task.geometry.ST_Union()).filter_by(state=1)))
count = taskgetter.count()
if count != 0:
    atask = taskgetter.offset(random.randint(0, count-1)).first()

and it yields an error like this:

ProgrammingError: (ProgrammingError) subquery in FROM must have an alias
LINE 3: FROM tasks, (SELECT ST_Union(tasks.geometry) AS "ST_Union_1"...
                    ^
HINT:  For example, FROM (SELECT ...) [AS] foo.
 'SELECT count(*) AS count_1 
FROM (SELECT tasks.id AS tasks_id
FROM tasks, (SELECT ST_Union(tasks.geometry) AS "ST_Union_1" 
FROM tasks 
WHERE tasks.state = %(state_1)s) 
WHERE tasks.state = %(state_2)s AND ST_Disjoint(tasks.geometry, (SELECT ST_Union(tasks.geometry) AS "ST_Union_1" 
FROM tasks 
WHERE tasks.state = %(state_1)s))) AS anon_1' {'state_1': 1, 'state_2': 0}

ddelemeny · Accepted Answer · 2014-03-09T22:34:31.623

1

A shot in the dark as I don't have the setup to test it :

This seems to be related to SQLAlchemy's subqueries more than GeoAlchemy, try to add .subquery() at the end of your subquery to generate an alias (cf : http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#using-subqueries)

Edit : Still using info from the linked tutorial, I think this may work :

state1 = session.query(
        Task.geometry.ST_Union().label('taskunion')
        ).filter_by(state=1).subquery()

taskgetter = session.query(Task)\
        .filter_by(state=0)
        .filter(Task.geometry.ST_Disjoint(state1.c.taskunion))

Add a label to the column you're creating on your subquery to reference it in your super-query.

edited Mar 09 '14 at 22:34

answered Mar 09 '14 at 20:24

ddelemeny

1,891
13
18

Thanks - I think that's definitely part of the answer, but it doesn't get all the way there. By adding `.subquery()` to the inner session.query, I end up with `(ProgrammingError) syntax error at or near "SELECT"` which is due to the resulting SQL containing `AND ST_Disjoint(tasks_1.geometry, ST_Union(SELECT tas...`. This suggests I'm not combining subquery with ST_Union in the right way. – Dan Stowell Mar 09 '14 at 21:21
It works! As-is. Thanks for the quality guesswork. The one thing that's particularly unfamiliar to me is the use of `.c` since I guess I hadn't been thinking of the unified geometry as being a column, though of course it is when you go down to the SQL. – Dan Stowell Mar 10 '14 at 09:04
Np, glad I could help ! :) – ddelemeny Mar 10 '14 at 09:41

GeoAlchemy2: find a set of Geometry items that doesn't intersect with a separate set

1 Answers1