Code

Opened 2 years ago

Closed 22 months 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 kmtracey)

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)

Attachments (0)

Change History (9)

comment:1 Changed 2 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 2 years ago by kmtracey

  • 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 2 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,

Olivier.

comment:4 Changed 2 years ago by kmtracey

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 2 years ago by kmtracey

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 2 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

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

comment:7 Changed 2 years ago by jezdez

  • Severity changed from Release blocker to Normal

Removing the release blocker label, in that case.

comment:8 Changed 2 years ago by aaugustin

  • Triage Stage changed from Unreviewed to Accepted

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 22 months ago by jbronn

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

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.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.