Opened 5 years ago

Closed 4 years ago

#17884 closed Bug (invalid)

Distance lookups using ST_Distance_Sphere fails with PostGis 1.5.3

Reported by: ogirardot@… Owned by: nobody
Component: GIS Version: master
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Karen Tracey)

i've worked a lot to make this work, and i just can't figure it out right now, so i'm opening a ticket. This is related to #16778 and my point of view right now is that it's a problem about how the EWKB Binary object gets created.
My problem is that, i've CommentAddress objects with PointField (srid=4326) and an Address with a PointField (srid=4326 too), i do a simple distance lookup request :


And it fails with :

  File ".../django/db/backends/postgresql_psycopg2/", line 53, in execute
    return self.cursor.execute(query, args)
DatabaseError: geometry_distance_spheroid: Operation on two GEOMETRIES with different SRIDs

But both are the proper srids, the problem is that the query that gets created is :

 select St_Distance_Sphere(geopoint, ST_GeomFromEWKB('\x01010000001f7932dea0670240486469f173704840'::bytea)) from frontend_commentaddress;

But when i do myself the ST_AsEWKB on the same precise object i get a very different EWKB and the query - works :

select St_Distance_Sphere(geopoint, ST_GeomFromEWKB('\001\001\000\000 \346\020\000\000\037y1\336\240g\002@Hdi\361spH@'::bytea)) from frontend_commentaddress;

I tried to use self._adapter.adapted (which looks really better with a H@ at the end) instead of self._adapter.getquoted() into the postgis adapter but it doesn't work as it's unicode translated into bytes... Anyway i'm missing something there and can't get it working.

This is a real blocker, and it seems that this is crashing my local runserver (no stacktrace no log, just crashed).
Thank you for your help.

Here are my versions and what i tried :

Python 2.7.1

Django 1.4 trunk (as of today)

"POSTGIS="1.5.3" GEOS="3.3.2-CAPI-1.7.2" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.7.3" USE_STATS (procs from 1.5 r5976 need upgrade)"

"PostgreSQL 9.1.3 on x86_64-apple-darwin10.8.0, compiled by i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664), 64-bit"

both installed with homebrew. (on mac os lion)

Change History (9)

comment:1 Changed 5 years ago by anonymous

Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset

extracted from postgresql logs the query :

STATEMENT:  SELECT (ST_distance_sphere("frontend_commentaddress"."geopoint",ST_GeomFromEWKB(E'\\x01010000001f7931dea0670240486469f173704840'::bytea))) AS "distance", "frontend_commentaddress"."id", "frontend_commentaddress"."address_id", "frontend_commentaddress"."user_id", "frontend_commentaddress"."positive_comment", "frontend_commentaddress"."negative_comment", "frontend_commentaddress"."role_name", "frontend_commentaddress"."date", "frontend_commentaddress"."is_visible", "frontend_commentaddress"."created_at", "frontend_commentaddress"."modified_at", "frontend_commentaddress"."geopoint" FROM "frontend_commentaddress" LIMIT 21

comment:2 Changed 5 years ago by Karen Tracey

Description: modified (diff)

Fixed formatting, please use WikiFormatting and preview before submitting.

I don't see evidence that this is something that is a regression in Django code and therefore should be a release blocker?

comment:3 Changed 5 years ago by anonymous

it makes at least 1.3 and the trunk of 1.4 fail (and crash in my case but that may be something else) at distance lookups for Postgresql 9.1 and Postgis 1.5.3 (latest version).

As a matter of fact i can't say that i will be able to put django 1.4 in production without a proper interaction with postgis...

But i'm not that much familiar with what tags should be on what tickets in django's trac, if you think that may not be a suitable tag, i can understand that i just put the tag according to what i think.

But you're right, i don't think it's a regression in Django code, but evidence points to the fact that the ewkb used and transmitted to ST_Distance_Sphere does not contain SRID informations. So the oldest versions of ST_Distance_Sphere (according to the documentation) just assumed the SRID was 4326, but it seems now it's enforcing it.
So it may not be a regression as the change of behavior comes from Postgis, but it's not that innocent either to create an EWKB without the SRID infos.

Regards and sorry for the formatting i couldn't fix it afterwards,


comment:4 Changed 5 years ago by Karen Tracey

If it used to work in 1.3 but now fails with current trunk Django code, the release blocker label is appropriate. I'm still not sure if that is the case based on what's been said so far.

comment:5 Changed 5 years ago by Karen Tracey

Actually reading closer I think above it says the same problem exists in both 1.3 and 1.4? That would mean it's not a release blocker.

comment:6 Changed 5 years ago by ogirardot@…

yes, i have a production machine, and a dev machine (debian/mac os) that used to work properly with the same code, using django 1.3.1,

PostgreSQL 9.0.5 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit


POSTGIS="1.5.2" GEOS="3.2.0-CAPI-1.6.0" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.7.8" USE_STATS

The problem for me seems to come form whether Postgresql 9.1 or Postgis 1.5.3

comment:7 Changed 5 years ago by Jannis Leidel

Severity: Release blockerNormal

Removing the release blocker label, in that case.

comment:8 Changed 4 years ago by Aymeric Augustin

Triage Stage: UnreviewedAccepted

Accepting because the description is sufficiently detailed to convince me that the problem exists. I can't tell how to resolve it, though.

comment:9 in reply to:  description Changed 4 years ago by jbronn

Resolution: invalid
Status: newclosed

both installed with homebrew. (on mac os lion)

This is probably your problem. Develop on a VM in linux, or install manually by source on a Mac -- do NOT use homebrew. It causes a significant amount of frustration on my part because it can use wrong linker flags, use incompatible libraries, and in general causes crazy issues such as this.

Regardless, I tried the following query using models from GeoDjango's test suite (which mimics the example you did, two models with PointFields that have srid=4326):

>>> from django.contrib.gis.tests.geoapp.models import City
>>> from django.contrib.gis.tests.distapp.models import AustraliaCity
>>> qs = City.objects.distance(AustraliaCity.objects.get(name='Thirroul').point)
>>> c = qs[4]
>>> print, c.distance
Pueblo 8342.02642135

Thus, I cannot reproduce -- I'm using PostGIS 1.5.5. Please provide a concrete test case that demonstrates this to reopen.

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