Which SRID works with adding and sorting in SpatiaLite db?

Question

I'm trying to make geodjango app. I'm using SQLite and SpatialLite. I want to add shops, and be able to sort them from closest to furthest from my location.

When in my model I have:

location = gis_models.PointField(srid=4326, blank=True, null=True)

then adding works, but sorting by distance does not work, and I get:

SQLite does not support linear distance calculations on geodetic coordinate systems.

When I have:

location = gis_models.PointField(srid=3857, blank=True, null=True)

than adding does not work, and sorting works, and I get:

geo_shop.location violates Geometry constraint [geom-type or SRID not allowed]

What do I do to make them both work at the same time?

Answer 1

Error while adding location is related to srid mismatch.

Use srid=3857 for sorting but when you are adding locations then convert them from 4326 to 3857 using the following method (from this answer):

>>> from django.contrib.gis.gdal import SpatialReference, CoordTransform
>>> from django.contrib.gis.geos import Point
>>> gcoord = SpatialReference(4326)
>>> mycoord = SpatialReference(22186)
>>> trans = CoordTransform(gcoord, mycoord)

>>> pnt = Point(30, 50, srid=4326)
>>> print 'x: %s; y: %s; srid: %s' % (pnt.x, pnt.y, pnt.srid)
x: 30.0; y: 50.0; srid: 4326
>>> pnt.transform(trans)
>>> print 'x: %s; y: %s; srid: %s' % (pnt.x, pnt.y, pnt.srid)
x: 11160773.5712; y: 19724623.9117; srid: 22186