Distance lookups using ST_Distance_Sphere fails with PostGis 1.5.3

[ogirardot@…]

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

CommentAddress.objects.distance(Address.objects.get(pk=1111).geopoint)

And it fails with :

  File ".../django/db/backends/postgresql_psycopg2/base.py", 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)

[anonymous]

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

[Karen Tracey]

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?

[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,

Olivier.

[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.

[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.

[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

and

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

[Jannis Leidel]

Removing the release blocker label, in that case.

[Aymeric Augustin]

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

[jbronn]

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.name, 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.