CODEWITHSUNDEEP
djangopostgisgeodjango
Kevin Parker
Kevin Parker

Reputation: 17206

Django + GeoDjango and Postrgresql (postGis extension): Wrong distance returned from the database

So we're having a seemingly straight forward problem with Django, GeoDjango and Postgis. There's a lot of mention online that you need to include the SRID both on the point you're trying to query and on the database field.

The problem is we do have the SRID on the database field and yet the distance returned is incorrect. Normally I wouldn't care so much and just do the calculation in python for the returned rows, but in this case we need to filter on distance and without it being accurate I'm not comfortable moving forward.

Model:

class LocationStore(BaseLocation, LocationStoreFunctions):
    street = models.CharField(max_length=64)
    extras = models.CharField(max_length=64, blank=True, null=True)
    town = models.CharField(max_length=64)
    zip_postal = models.CharField(max_length=32)
    region = models.ForeignKey(Region)
    country = models.ForeignKey(Country)
    coordinates = PointField(srid=4326, db_index=True)
    timezone = TimeZoneField(default='US/Eastern', blank=True)
    phone_number = models.CharField(max_length=32)
    is_closed = models.BooleanField(default=False)
    features = MultiSelectField(choices=LOCATION_FEATURE_CHOICES, default=[])

Query Set:

locations = LocationStore.objects.filter(coordinates__distance_lte=(self.client_point, D(m=distance))).annotate(distance=Distance('coordinates', self.client_point, sphereoid=True)).order_by('distance')

Resulting Query:

SELECT "retailers_baselocation"."id", "retailers_baselocation"."polymorphic_ctype_id", "retailers_baselocation"."retailer_id", "retailers_baselocation"."identifier", "retailers_baselocation"."name", "retailers_baselocation"."date_created", "retailers_baselocation"."date_updated", "retailers_baselocation"."rating", "retailers_locationstore"."baselocation_ptr_id", "retailers_locationstore"."street", "retailers_locationstore"."extras", "retailers_locationstore"."town", "retailers_locationstore"."zip_postal", "retailers_locationstore"."region_id", "retailers_locationstore"."country_id", "retailers_locationstore"."coordinates", "retailers_locationstore"."timezone", "retailers_locationstore"."phone_number", "retailers_locationstore"."is_closed", "retailers_locationstore"."features", ST_DistanceSphere("retailers_locationstore"."coordinates", ST_GeomFromEWKB('\001\001\000\000 \346\020\000\000s\242]\205\224\037F@\353\016s\223\342!S\300'::bytea)) AS "distance" FROM "retailers_locationstore" INNER JOIN "retailers_baselocation" ON ("retailers_locationstore"."baselocation_ptr_id" = "retailers_baselocation"."id") WHERE ST_DistanceSphere("retailers_locationstore"."coordinates", ST_GeomFromEWKB('\001\001\000\000 \346\020\000\000s\242]\205\224\037F@\353\016s\223\342!S\300'::bytea)) <= 10000.0 ORDER BY "distance" ASC;

Resulting Results:

**NOTICE:  Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY**
 id | polymorphic_ctype_id | retailer_id | identifier |         name          |         date_created          |         date_updated          | rating | baselocation_ptr_id |        street        | extras |
   town   | zip_postal | region_id | country_id |                    coordinates                     |  timezone  | phone_number | is_closed | features |   distance    
----+----------------------+-------------+------------+-----------------------+-------------------------------+-------------------------------+--------+---------------------+----------------------+--------+
----------+------------+-----------+------------+----------------------------------------------------+------------+--------------+-----------+----------+---------------
  2 |                   80 |           1 | 0195       | Kingston              | 2019-10-11 18:41:12.548573-04 | 2019-10-11 18:41:12.548592-04 |   0.00 |                   2 | 59 Bath Road         |        |
 Kingston | K7L 5G3    |        16 |          2 | 0101000020E61000000B26FE28EA1E464080D4264EEE2053C0 | US/Eastern | 6135494200   | f         |          | 1663.28505119
  3 |                   80 |           1 | 0694       | Kingston Division St. | 2019-10-11 18:41:12.570061-04 | 2019-10-11 18:41:12.57008-04  |   0.00 |                   3 | 1040 Division Street |        |
 Kingston | K7K 0C3    |        16 |          2 | 0101000020E61000008F705AF0A22146408AE942ACFE1F53C0 | US/Eastern | 6135461922   | f         |          | 3310.47164328
  1 |                   80 |           1 | 0417       | Kingston Township     | 2019-10-11 18:41:12.527034-04 | 2019-10-11 18:41:12.527053-04 |   0.00 |                   1 | 2560 Princess Street |        |
 Kingston | K7P 2S8    |        16 |          2 | 0101000020E61000006B7EFCA5452146405B26C3F17C2453C0 | US/Eastern | 6133840011   | f         |          | 4535.41823264

Debug information:
 Client test point: SRID:4326 44.24672 -76.5294541
 Returned location's coordinates, as defined in the database: SRID: 4326 44.259938 -76.570126
 Every online calculator I can think of returns this distance as 3.561131 km
 The database however returns this distance as 1.66328 km (I'm assuming these are KM units)

Any help would be appreciated :)

Upvotes: 2

Views: 178

Answers (1)

Kevin Parker
Kevin Parker

Reputation: 17206

So figured this out to be ST_Distance takes a Longitude and Latitude, not a Latitude and Longitude like every other geo spatial library in the world... Not really sure why this is, but it was the case. All Point objects or PointFields should be specified in Longitude & Latitude.

http://postgis.net/docs/manual-1.4/ST_Distance_Spheroid.html

Upvotes: 2

Related Questions