Opened 4 years ago

Closed 3 years ago

Last modified 3 years ago

#17448 closed Bug (fixed)

Error reading PointField in objects.raw(sql) query

Reported by: oluckyman Owned by: DavidEklund
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 (15)

comment:1 Changed 4 years ago by aaugustin

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

comment:2 Changed 4 years ago by oluckyman <elias.logins@…>

  • Resolution set to worksforme
  • Status changed from new to closed

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 Changed 4 years ago by julien

  • Resolution worksforme deleted
  • Status changed from closed to reopened

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 Changed 3 years ago by DavidEklund

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 Changed 3 years ago by DavidEklund

  • Cc daek@… added

comment:6 follow-up: Changed 3 years ago by claudep

  • Owner nobody deleted
  • Status changed from reopened to new

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.

comment:7 in reply to: ↑ 6 Changed 3 years ago by DavidEklund

  • Owner set to DavidEklund
  • Status changed from new to assigned

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 Changed 3 years ago by DavidEklund

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, here:

https://github.com/django/django/pull/235

Last edited 3 years ago by DavidEklund (previous) (diff)

comment:9 Changed 3 years ago by DavidEklund

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 Changed 3 years ago by DavidEklund

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

comment:11 Changed 3 years ago by Claude Paroz <claude@…>

In [d7a2e816a15a121c50b85c4470c91a676cbc827b]:

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

Thanks David Eklund for the initial patch.

comment:12 Changed 3 years ago by claudep

  • Resolution set to fixed
  • Status changed from assigned to closed

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

comment:13 Changed 3 years ago by claudep

  • Resolution fixed deleted
  • Status changed from closed to reopened

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 follow-up: Changed 3 years ago by Claude Paroz <claude@…>

  • Resolution set to fixed
  • Status changed from reopened to closed

In [62e1c5a441d957e44d7527a6d901587b40203a51]:

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

comment:15 in reply to: ↑ 14 Changed 3 years ago by DavidEklund

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.

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