Opened 13 years ago

Closed 9 years ago

Last modified 9 years ago

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

spatial_subquery.diff (6.5 KB ) - added by milosu 13 years ago.
spatial_subquery2.diff (8.1 KB ) - added by milosu 13 years ago.
performance improvement for postgis, that allows postgres to efficiently replace subselect with its result while preparing the query plan
spatial_subquery3.diff (8.5 KB ) - added by milosu 13 years ago.
improved patch passing GeoDjango and Django test suite (solved SQLEvaluators related problems)

Download all attachments as: .zip

Change History (14)

by milosu, 13 years ago

Attachment: spatial_subquery.diff added

by milosu, 13 years ago

Attachment: spatial_subquery2.diff added

performance improvement for postgis, that allows postgres to efficiently replace subselect with its result while preparing the query plan

comment:1 by Russell Keith-Magee, 13 years ago

Triage Stage: UnreviewedDesign decision needed

Seems reasonable, but I'll let a GeoDjango expert make the final call.

by milosu, 13 years ago

Attachment: spatial_subquery3.diff added

improved patch passing GeoDjango and Django test suite (solved SQLEvaluators related problems)

comment:2 by Julien Phalip, 13 years ago

Severity: Normal
Type: New feature

comment:3 by Aymeric Augustin, 12 years ago

UI/UX: unset

Change UI/UX from NULL to False.

comment:4 by Aymeric Augustin, 12 years ago

Easy pickings: unset

Change Easy pickings from NULL to False.

comment:5 by Aymeric Augustin, 11 years ago

Triage Stage: Design decision neededAccepted

No GeoDjango expert made the call, and it still seems reasonable.

comment:6 by Tim Graham, 10 years ago

Patch needs improvement: set

Patch will need to be updated to apply cleanly.

comment:7 by Claude Paroz, 9 years ago

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 by Claude Paroz, 9 years ago

Patch needs improvement: unset

#24164 resolution (thanks Tim!) allows now the current PR to pass on MySQL/Spatialite also.

comment:9 by Tim Graham, 9 years ago

Triage Stage: AcceptedReady for checkin

comment:10 by Claude Paroz <claude@…>, 9 years ago

Resolution: fixed
Status: newclosed

In a0b5f15ea5f1abd91da9a5e4e5b6a0fb5a880c43:

Fixed #14483 -- Allowed using subqueries with GIS lookups

comment:11 by Claude Paroz <claude@…>, 9 years ago

In c77dd64402e64e3e53b49634175f1e010a480b67:

[1.8.x] Fixed #14483 -- Allowed using subqueries with GIS lookups

Backport of a0b5f15ea5f from master.

Note: See TracTickets for help on using tickets.
Back to Top