Clarify limitations of MySQL in GeoDjango docs

[Ryan Nowakowski]

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

[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.

[jbronn]

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."

[Ryan Nowakowski]

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?

[jbronn]

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.

[jbronn]

(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.