Opened 7 years ago

Closed 7 years ago

#13430 closed (fixed)

Clarify limitations of MySQL in GeoDjango docs

Reported by: Ryan Nowakowski 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 Changed 7 years ago by Alex Gaynor

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 7 years ago by jbronn

Resolution: invalid
Status: newclosed

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 Changed 7 years ago by Ryan Nowakowski

Resolution: invalid
Status: closedreopened

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 7 years ago by jbronn

Keywords: gis mysql docs added
Owner: changed from nobody to jbronn
Status: reopenednew
Summary: GeoDjango query 'contains' returns incorrect resultsClarify 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 7 years ago by Russell Keith-Magee

Component: GISDocumentation
Triage Stage: UnreviewedAccepted

comment:6 Changed 7 years ago by jbronn

Resolution: fixed
Status: newclosed

(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