Opened 5 years ago

Closed 4 months ago

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

Download all attachments as: .zip

Change History (14)

Changed 5 years ago by milosu

Changed 5 years ago by milosu

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

comment:1 Changed 4 years ago by russellm

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Design decision needed

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

Changed 4 years ago by milosu

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

comment:2 Changed 4 years ago by julien

  • Severity set to Normal
  • Type set to New feature

comment:3 Changed 3 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:4 Changed 3 years ago by aaugustin

  • Easy pickings unset

Change Easy pickings from NULL to False.

comment:5 Changed 2 years ago by aaugustin

  • Triage Stage changed from Design decision needed to Accepted

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

comment:6 Changed 12 months ago by timo

  • Patch needs improvement set

Patch will need to be updated to apply cleanly.

comment:7 Changed 4 months ago by claudep

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 4 months ago by claudep

  • Patch needs improvement unset

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

comment:9 Changed 4 months ago by timgraham

  • Triage Stage changed from Accepted to Ready for checkin

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

  • Resolution set to fixed
  • Status changed from new to closed

In a0b5f15ea5f1abd91da9a5e4e5b6a0fb5a880c43:

Fixed #14483 -- Allowed using subqueries with GIS lookups

comment:11 Changed 4 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