Opened 6 years ago

Closed 20 months ago

Last modified 20 months 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 6 years ago.
spatial_subquery2.diff (8.1 KB) - added by milosu 6 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 6 years ago.
improved patch passing GeoDjango and Django test suite (solved SQLEvaluators related problems)

Download all attachments as: .zip

Change History (14)

Changed 6 years ago by milosu

Attachment: spatial_subquery.diff added

Changed 6 years ago by milosu

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 Changed 6 years ago by Russell Keith-Magee

Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset
Triage Stage: UnreviewedDesign decision needed

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

Changed 6 years ago by milosu

Attachment: spatial_subquery3.diff added

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

comment:2 Changed 5 years ago by Julien Phalip

Severity: Normal
Type: New feature

comment:3 Changed 5 years ago by Aymeric Augustin

UI/UX: unset

Change UI/UX from NULL to False.

comment:4 Changed 5 years ago by Aymeric Augustin

Easy pickings: unset

Change Easy pickings from NULL to False.

comment:5 Changed 4 years ago by Aymeric Augustin

Triage Stage: Design decision neededAccepted

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

comment:6 Changed 2 years ago by Tim Graham

Patch needs improvement: set

Patch will need to be updated to apply cleanly.

comment:7 Changed 21 months ago by Claude Paroz

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 20 months ago by Claude Paroz

Patch needs improvement: unset

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

comment:9 Changed 20 months ago by Tim Graham

Triage Stage: AcceptedReady for checkin

comment:10 Changed 20 months ago by Claude Paroz <claude@…>

Resolution: fixed
Status: newclosed

In a0b5f15ea5f1abd91da9a5e4e5b6a0fb5a880c43:

Fixed #14483 -- Allowed using subqueries with GIS lookups

comment:11 Changed 20 months ago by Claude Paroz <claude@…>

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