Opened 13 years ago

Closed 12 years ago

Last modified 7 years ago

#17448 closed Bug (fixed)

Error reading PointField in objects.raw(sql) query

Reported by: oluckyman Owned by: David Eklund
Component: GIS Version: 1.3
Severity: Normal Keywords: raw sql gis
Cc: daek@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (17)

comment:1 by Aymeric Augustin, 13 years ago

Triage Stage: UnreviewedAccepted

comment:2 by oluckyman <elias.logins@…>, 13 years ago

Resolution: worksforme
Status: newclosed

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.

comment:3 by Julien Phalip, 13 years ago

Resolution: worksforme
Status: closedreopened

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

comment:4 by David Eklund, 12 years ago

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>

comment:5 by David Eklund, 12 years ago

Cc: daek@… added

comment:6 by Claude Paroz, 12 years ago

Owner: nobody removed
Status: reopenednew

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.

in reply to:  6 comment:7 by David Eklund, 12 years ago

Owner: set to David Eklund
Status: newassigned

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.

comment:8 by David Eklund, 12 years ago

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.

Version 0, edited 12 years ago by David Eklund (next)

comment:9 by David Eklund, 12 years ago

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.

comment:10 by David Eklund, 12 years ago

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

comment:11 by Claude Paroz <claude@…>, 12 years ago

In [d7a2e816a15a121c50b85c4470c91a676cbc827b]:

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

Thanks David Eklund for the initial patch.

comment:12 by Claude Paroz, 12 years ago

Resolution: fixed
Status: assignedclosed

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

comment:13 by Claude Paroz, 12 years ago

Resolution: fixed
Status: closedreopened

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.

comment:14 by Claude Paroz <claude@…>, 12 years ago

Resolution: fixed
Status: reopenedclosed

In [62e1c5a441d957e44d7527a6d901587b40203a51]:

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

in reply to:  14 comment:15 by David Eklund, 12 years ago

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.

comment:16 by Tim Graham <timograham@…>, 7 years ago

In 69922d3:

Refs #17448 -- Fixed GeoModelTest.test_raw_sql_query.

The test was a false positive.

comment:17 by Tim Graham <timograham@…>, 7 years ago

In 9e12e02a:

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

The test was a false positive.

Backport of 69922d303dbe8e83952f769caff205abbe100bba from master

Note: See TracTickets for help on using tickets.
Back to Top