#14483 closed New feature (fixed)
Implement subqueries in geodjango spatial lookup
Reported by: | milosu | Owned by: | nobody |
---|---|---|---|
Component: | GIS | Version: | 1.2 |
Severity: | Normal | Keywords: | geodjango, subquery |
Cc: | Triage Stage: | Ready for checkin | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
Currently only GEOS geometry objects can be used in the GeoQuerySet spatial field lookups. Sometimes the geometry objects are very large geometries on its own and the resulting SQL statement that includes EWKB of the geometry is very large (lets say 30 mega bytes in my case).
Attached patch extends GeoDjango ORM to support subqueries in spatial lookups, so that it is possible to make a query like:
qset = Address.objects.filter(location__within = District.objects.filter(name='Boston area').values('area'))
so that the resulting spatial SQL looks like:
SELECT "spatial_subquery_address"."id", "spatial_subquery_address"."city", "spatial_subquery_address"."location" FROM "spatial_subquery_address" WHERE ST_Within("spatial_subquery_address"."location", (SELECT U0."area" FROM "spatial_subquery_district" U0 WHERE U0."name" = Boston area ))
See attached regression test for this enhancement.
Patch was built against slightly modified Django 1.2.3 so that it will not apply cleanly, but I hope jbronn or someone else should get the idea from my attached version.
Attachments (3)
Change History (14)
Changed 12 years ago by
Attachment: | spatial_subquery.diff added |
---|
Changed 12 years ago by
Attachment: | spatial_subquery2.diff added |
---|
comment:1 Changed 12 years ago by
Triage Stage: | Unreviewed → Design decision needed |
---|
Seems reasonable, but I'll let a GeoDjango expert make the final call.
Changed 11 years ago by
Attachment: | spatial_subquery3.diff added |
---|
improved patch passing GeoDjango and Django test suite (solved SQLEvaluators related problems)
comment:2 Changed 11 years ago by
Severity: | → Normal |
---|---|
Type: | → New feature |
comment:5 Changed 9 years ago by
Triage Stage: | Design decision needed → Accepted |
---|
No GeoDjango expert made the call, and it still seems reasonable.
comment:6 Changed 8 years ago by
Patch needs improvement: | set |
---|
Patch will need to be updated to apply cleanly.
comment:7 Changed 7 years ago by
I have a branch where this is working with PostGIS.
https://github.com/claudep/django/commit/53783c93a551fe769d5395e6ee54d01729b23722
However, it still fails on MySQL and Spatialite because of the ops.select
format (AsText()) which is applied to the subquery select. I don't know if there is a mean to prevent that special formatting for subqueries. ORM master needed :-)
comment:8 Changed 7 years ago by
Patch needs improvement: | unset |
---|
#24164 resolution (thanks Tim!) allows now the current PR to pass on MySQL/Spatialite also.
comment:9 Changed 7 years ago by
Triage Stage: | Accepted → Ready for checkin |
---|
comment:10 Changed 7 years ago by
Resolution: | → fixed |
---|---|
Status: | new → closed |
performance improvement for postgis, that allows postgres to efficiently replace subselect with its result while preparing the query plan