CODEWITHSUNDEEP
pythondjangopostgisgeodjango
user7141836
user7141836

Reputation:

How to get intersecting geometries with subquery in GeoDjango?

I'm trying to get all the records from a table whose geometry intersects with a buffered geometry from the same table, which I get with a subquery.

My working plain SQL statement is:

SELECT id FROM table 
WHERE ST_INTERSECTS(geom, (
    SELECT ST_BUFFER(geom, 10) FROM table WHERE id = 1)
);

How can I achieve this with GeoDjango?

Edit:

Got it working with the following:

records = table.objects.filter(
    geom__intersects = table.objects.filter(pk=1).values('geom')
)

But I am still missing the buffer.
Any suggestions?

Upvotes: 2

Views: 1100

Answers (2)

user7141836
user7141836

Reputation:

After some research I solved it the following way:

# First get the geometry i tried to get in the subquery
tempgeom = table.objects.get(pk=1)
# Apply the buffer
tempgeom = tempgeom.geom.buffer(10)
# Use the buffered geometry to get the records
records = table.objects.filter(geom__intersects = tempgeom)

I think there are better ways but for now, it works.

Upvotes: 1

John Moutafis
John Moutafis

Reputation: 23134

Your solution is correct @Chris.

The only addition could be for the Django version 1.11 where the Subquery was added.

Essentially you can translate your PostgreSQL query to the following:

records = table.objects.filter(
    geom__intersects=Subquery(
        table.objects.get(pk=1).geom.buffer(10)
    )
)

and avoid the middle steps.

Upvotes: 0

Related Questions