Opened 6 years ago

Closed 6 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 6 years ago by Alex Gaynor

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

Component: GISDocumentation
Triage Stage: UnreviewedAccepted

comment:6 Changed 6 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