Opened 5 years ago

Closed 5 years ago

#13430 closed (fixed)

Clarify limitations of MySQL in GeoDjango docs

Reported by: tubaman Owned by: jbronn
Component: Documentation Version: master
Severity: Keywords: gis mysql docs
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

Following the GeoDjango tutorial, I get different results querying each border individually than I do when using filter.

>>> from django.contrib.gis.geos import Point
>>> from world.models import WorldBorders
>>> 
>>> pnt = Point(-97.7695999145507812, 30.3036994934082031)
>>> countries_from_iteration = []
>>> for country in WorldBorders.objects.all():
...         if country.geom.contains(pnt):
...             countries_from_iteration.append(country)
... 
>>> countries_from_iteration
[<WorldBorders: United States>]
>>> countries_from_filter = WorldBorders.objects.filter(geom__contains=pnt)
>>> countries_from_filter
[<WorldBorders: United States>, <WorldBorders: Mexico>]

Notice that the filter query results contain Mexico while the results from interating do not. I'm using mysql and Django SVN r13027

Change History (6)

comment:1 follow-up: Changed 5 years ago by Alex

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

I'm pretty sure that's because MySQL only does bounding box queries and therefore it tends to be... wrong. I'll wait for justin to chime in.

comment:2 in reply to: ↑ 1 Changed 5 years ago by jbronn

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

Replying to Alex:

I'm pretty sure that's because MySQL only does bounding box queries and therefore it tends to be... wrong. I'll wait for justin to chime in.

Yup. This is exactly the problem -- MySQL only supports MBR (minimum bounding rectangle, aka bounding box) queries. This is noted in the GeoDjango db-api, and the contains lookup docs (MBRContains is the function used).

The MySQL spatial extension docs also mention this limitation: "MySQL does not implement these functions according to the specification. Those that are implemented return the same result as the corresponding MBR-based functions."

comment:3 follow-up: Changed 5 years ago by tubaman

  • Resolution invalid deleted
  • Status changed from closed to reopened

Can we link from the http://docs.djangoproject.com/en/dev/ref/contrib/gis/install/#id4 where it says, "Not OGC-compliant; limited functionality" to the db-api guide? That'll make it easier to find the details.

Also, does it make sense to have contains() raise a NotImplementedError("MySQL only supports bbcontains") since it doesn't really do a contains query?

comment:4 in reply to: ↑ 3 Changed 5 years ago by jbronn

  • Keywords gis mysql docs added
  • Owner changed from nobody to jbronn
  • Status changed from reopened to new
  • Summary changed from GeoDjango query 'contains' returns incorrect results to Clarify limitations of MySQL in GeoDjango docs

Replying to tubaman:

Can we link from the http://docs.djangoproject.com/en/dev/ref/contrib/gis/install/#id4 where it says, "Not OGC-compliant; limited functionality" to the db-api guide? That'll make it easier to find the details.

This is good feedback, and the ticket is now re-purposed for clarifying the documentation. Now that I think about it, I also need to include a warning about how you can only use spatial indexes with MyISAM tables (e.g., no transactions on tables with spatial data).

Also, does it make sense to have contains() raise a NotImplementedError("MySQL only supports bbcontains") since it doesn't really do a contains query?

One important reason is backwards-compatibility, as this proposed change would break people's existing code. In previous versions, the internal machinery to introspect and raise a NotImplementedError for an arbitrary lookup based on the version of the backend simply did not exist.

Another reason is that MySQL itself includes a Contains function because it hopes to return the correct spatial results at some release in the future (6.0, I believe). When that happens GeoDjango will call Contains on those versions. I don't do it now because of a long-standing bug in the MySQL beta GIS builds in which merely calling Contains would crash the entire MySQL database.

comment:5 Changed 5 years ago by russellm

  • Component changed from GIS to Documentation
  • Triage Stage changed from Unreviewed to Accepted

comment:6 Changed 5 years ago by jbronn

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

(In [13097]) Fixed #13315, #13430 -- Recreated django.contrib.gis.db.backend module with SpatialBackend alias and added Adaptor alias for backwards-compatibility purposes; added GeoDjango 1.2 backwards-incompatibility documentation and release notes; added a section in the docs about how MySQL is a crippled spatial database; updated versions in install docs.

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