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 :)