Error reading PointField in objects.raw(sql) query

[oluckyman]

There is a model:

class Place(models.Model):
    ...
    location = models.PointField(srid=4326)
    objects = models.GeoManager()
    ...

Expecting result of Place.objects.raw('select * from places_place')[0].location is the same as Place.objects.all()[0].location

Test it in the shell:

In [1]: Place.objects.all()[0].location
Out[1]: <Point object at 0x1042e9400>

In [2]: Place.objects.raw('select * from places_place')[0].location
ERROR: An unexpected error occurred while tokenizing input
The following traceback may be corrupted or invalid
The error message is: ('EOF in multi-line statement', (140, 0))

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
/usr/local/lib/python2.7/site-packages/django_extensions/management/commands/shell_plus.pyc in <module>()
----> 1 Place.objects.raw(u'select * from places_place')[0].location

/usr/local/lib/python2.7/site-packages/django/contrib/gis/db/models/proxy.pyc in __get__(self, obj, type)
     36             # Otherwise, a Geometry object is built using the field's contents,

     37             # and the model's corresponding attribute is set.

---> 38             geom = self._klass(geom_value)
     39             setattr(obj, self._field.attname, geom)
     40         return geom

/usr/local/lib/python2.7/site-packages/django/contrib/gis/geos/geometry.pyc in __init__(self, geo_input, srid)
     72                 g = wkb_r().read(gdal.OGRGeometry(geo_input).wkb)
     73             else:
---> 74                 raise ValueError('String or unicode input unrecognized as WKT EWKT, and HEXEWKB.')
     75         elif isinstance(geo_input, GEOM_PTR):
     76             # When the input is a pointer to a geomtry (GEOM_PTR).


ValueError: String or unicode input unrecognized as WKT EWKT, and HEXEWKB.

[oluckyman <elias.logins@…>]

There is a workaround:

# to expect error wrap any geometry field in raw sql by `asText()` function
In [12]: Place.objects.raw('select id, asText(location) from places_place')[0].location
Out[12]: <Point object at 0x103507ac0>

I have mark this ticket as 'worksforme', but the bug with select * from geometry fields is still not fixed.

[Julien Phalip]

Thanks oluckyman for mentioning your workaround. However, please do not close a ticket if it hasn't been fixed. "Worksforme" is for when the bug cannot be replicated; see: ​https://docs.djangoproject.com/en/dev/internals/contributing/triaging-tickets/#closing-tickets

[David Eklund]

This works for me, I can't reproduce this bug.

Which database was used when coming across the bug?

I'm using:

OS: Ubuntu 12.04.

Database backend: Postgis.

My setup is an app named placeapp with the following model in it:

from django.contrib.gis.db import models

class Place(models.Model):
    location = models.PointField(srid=4326)
    objects = models.GeoManager()

Then I get the following in a shell:

>>> from placeapp.models import Place
>>> from django.contrib.gis.geos import Point
>>> q = Point(1,2)
>>> p = Place()
>>> p.location = q
>>> p.save()
>>> Place.objects.raw('select * from placeapp_place')[0].location
<Point object at 0x3526f40>

And also:

>>> Place.objects.raw(u'select * from placeapp_place')[0].location
<Point object at 0x33ae0a0>

[Claude Paroz]

Even if the problem cannot be reproduced, I suggest to add a test with a raw query in django/contrib/gis/tests/geoapp before closing this ticket.

[David Eklund]

Replying to claudep:

Even if the problem cannot be reproduced, I suggest to add a test with a raw query in django/contrib/gis/tests/geoapp before closing this ticket.

Ok, that sounds like a good idea. I will do that.

[David Eklund]

I have added a test for raw SQL queries to the GeoDjango test suite.

The branch on my github is called ticket_17448 and is found here:

​https://github.com/davideklund/django/tree/ticket_17448

I also created a pull request on github.

[David Eklund]

Note though that I have only run the test using the postgis spatial database backend. This is beacuse I have some general problems with running GeoDjango's test suite with the other backends. I would like to confirm that it works using mysql, oracle and spatialite as well and I'll be back when this is done.

[David Eklund]

This seems to work fine with MySQL. It remains to test Oracle and SpatiaLite.

[Claude Paroz <claude@…>]

In [d7a2e816a15a121c50b85c4470c91a676cbc827b]:

Added a GeoDjango test with a raw query (Refs #17448)

Thanks David Eklund for the initial patch.

[Claude Paroz]

I committed a test anyway. Let's create a new ticket if this is not working in any other backend. Thanks again.

[Claude Paroz]

This is failing with MySQL, see ​http://ci.django-cms.org/job/Django/1571/

As far as I understand, the default return value from a geometry field in PostGis is returned as a HEXEWKB string which is recognized by the GEOSGeometry constructor. However, with MySQL, the default return value is an internal representation not understood by GEOSGeometry. Hence the required asText() or asBinary() function around the geometry field in the SQL query string. This should be documented.

[Claude Paroz <claude@…>]

In [62e1c5a441d957e44d7527a6d901587b40203a51]:

Fixed #17448 -- Improved test and documented raw-sql gis query

[David Eklund]

Replying to Claude Paroz <claude@…>:

In [62e1c5a441d957e44d7527a6d901587b40203a51]:

Fixed #17448 -- Improved test and documented raw-sql gis query

Ok, thanks for finishing this!

I'm a bit surprised that the test failed for MySQL as it went right through when I ran it using MySQL (5.5.24) on Ubuntu.

Anyway, with the documentation that you added this should be ok now.

[Tim Graham <timograham@…>]

In 69922d3:

Refs #17448 -- Fixed GeoModelTest.test_raw_sql_query.

The test was a false positive.

[Tim Graham <timograham@…>]

In 9e12e02a:

[2.0.x] Refs #17448 -- Fixed GeoModelTest.test_raw_sql_query.

The test was a false positive.

Backport of 69922d303dbe8e83952f769caff205abbe100bba from master