Adding support for stored procedures to GIS back-ends?
Thomas Sutton
Justin Bronn
> Hi, I'm working on a project that needs to order by distance between a
> point column and a fixed point (on MySQL). I've have my MySQL stored
> procedure working but haven't been able to determine how to convince
> GeoDjango to actually use it. As far as I've been able to determine with
> code inspection, I need to pass a distance kwarg to the constructor for
> BaseSpatialBackend in django.contrib.gis.db.backend.mysql.
That's part of it -- that's the method name for the `distance`
GeoQuerySet method, e.g., `GeoModel.objects.distance(geom)`. Distance
lookups, like `GeoModel.objects.filter(geom__distance_lte=(D(mi=5),
geom)`, depend on other attributes on the SpatialBackend instance,
specifically they must be in the `gis_terms` and `distance_functions`
dictionary attributes.
Moreover, the `get_geo_where_clause` routine needs to be modified for
distance queries (it's in gis/db/backend/mysql/query.py). Read the
implementations for the other spatial backends to get an idea of what
you'll have to.
> If I do so (`distance='distance', `), then I get past the
> ImproperlyConfigured exception raised when a method is unsupported, but
> it instead fails in gis.measure with "'NoneType' object has no attribute
> 'lower'" called from gis.db.models.query on line 684 (when it finds that
> my field is not geodetic and tries to determine the units). If geodetic
> means "uses degrees as its metric" then I believe my SRID is geodetic.
The real cause of this error is in the `get_srid_info`
(gis/db/models/fields/__init__.py). MySQL is not an OGC-compliant
database. One consequence of this is that it has no notion of spatial
reference systems -- compliant databases, such as PostGIS, use another
database table (`spatial_ref_sys`) to hold projection information
associated with an SRID value. In other words, for GeoDjango to get
distance calculations right it needs to know information about the
coordinate system which it obtains from that table -- which MySQL lacks.
A possible solution for MySQL would be to get this information instead
from GDAL (via gis.gdal.SpatialReference).
More specifically, PostGIS has to use different distance calculation
methods on geometries in a geodetic coordinate system
(ST_distance_sphere, ST_distance_spheroid) vs. a projected one
(ST_Distance). What this means for MySQL is that you'll also have to
implement a linear calculation method as well.
> I've run into a blank wall. Is there any information on extending the
> GeoDjango back-end to support additional custom stored procedures for
> such features as distance()? Can anyone suggest the correct way to do this?
>
Other than the suggestions I've given above, it would be to read the
source code -- that will be as close as you'll get to an extension
reference.
Once you have a patch, open up a ticket in the Django trac, specifying
the component as 'GIS'. Now's a good time to get it on the docket for 1.2.
Regards,
-Justin
Thomas Sutton
Thanks for the reply.
On 30/04/2009, at 7:29 AM, Justin Bronn wrote:
> Thomas Sutton wrote:
>> Hi, I'm working on a project that needs to order by distance
>> between a
>> point column and a fixed point (on MySQL). I've have my MySQL stored
>> procedure working but haven't been able to determine how to convince
>> GeoDjango to actually use it. As far as I've been able to determine
>> with
>> code inspection, I need to pass a distance kwarg to the constructor
>> for
>> BaseSpatialBackend in django.contrib.gis.db.backend.mysql.
>
> That's part of it -- that's the method name for the `distance`
> GeoQuerySet method, e.g., `GeoModel.objects.distance(geom)`. Distance
> lookups, like `GeoModel.objects.filter(geom__distance_lte=(D(mi=5),
> geom)`, depend on other attributes on the SpatialBackend instance,
> specifically they must be in the `gis_terms` and `distance_functions`
> dictionary attributes.
I saw that distance was mentioned in those places too, but couldn't
figure out if they were necessary. The indirection made it a bit
tricky to trace through, and the different back-ends seemed to differ
quite a bit which made it hard for me to compare them...
Thanks for the suggestions, but getting it to work the Right Way(TM)
will take too long on a project that's already late.
I've decided to just call the stored procedures directly use extra()
and pass the WKT for the point to measure from in select_params. This
is rather inelegant, but it does what I want.
I'll have to investigate using PostgreSQL on future projects instead.
Thanks again,
Thomas Sutton