How to get most accurate Point-Point distance queries in Europe with Spatialite

Question

I have a GeoDjango app, currently on PostGIS, which I am converting to use Spatialite db instead. I have no specialised knowledge about GIS systems :(

The locations table has a Geography column storing a single point in SRID 4326 per row, originally derived from lat/long data.

It's a long time since I built the original app but I believe I had read advice like: https://docs.djangoproject.com/en/3.1/ref/contrib/gis/model-api/#geography-type https://postgis.net/docs/using_postgis_dbmanagement.html#PostGIS_GeographyVSGeometry

...and this led me to believe that I would get more accurate point-to-point distance measurements with out-of-the-box GeoDjango queries by using the Geography type.

I have loaded the data in Spatialite, the same table now has a Point column. The data was dumped and loaded via GeoDjango and the values look the same.

When I do the distance queries I get similar but different results between PostGIS and Spatialite. The discrepancy gets larger with larger distances.

If I inspect the method Django is using in PostGIS:

SELECT ...
  ST_Distance("locations"."geopoint", ST_GeogFromWKB(\'\\x0101000020e6100000c3bb5cc47762c0bf1893fe5e0ac14940\'::bytea))
    AS "distance"
  FROM locations
  WHERE ...

vs in Spatialite:

SELECT ...
  COALESCE(Distance("locations"."geopoint", GeomFromText(\'POINT (-0.128005 51.508129)\',4326), 0), 0)
    AS "distance"
  FROM locations
  WHERE ...

...this seems to fit with what I read about "Geography" vs "Geometry".

Would it be correct to say that the PostGIS Geography query is more accurate?

And reading here https://www.gaia-gis.it/spatialite-3.0.0-BETA/spatialite-cookbook/html/srid.html it sounds like the key to getting accurate distances in Geometry queries is to use an appropriate projection, would that be correct?

If so, is there a way to get more accurate distance queries in Spatialite by re-projecting my points in a different SRID?

My use case would be "Select all rows where distance from <some point> is less than X kilometres". All the points are in Europe.

Answer 1

The SpatiaLite function for distance measurement is more advanced nowadays. It's a pity that search engines do not find the up-to-date documentation about the SQL functions of SpatiaLite www.gaia-gis.it/gaia-sins/spatialite-sql-5.0.0.html.

ST_Distance( geom1 Geometry , geom2 Geometry , use_ellipsoid Boolean ) : Double precision

Summary: return the distance between geom1 and geom2 (measured in meters). If the use_ellipsoid argument is set to TRUE the precise (but slower) distance will be computed on the Ellipsoid, otherwise will be computed on the Great Circle (approximative, but faster). This function only supports Long/Lat coordinates, and will return NULL for any planar CRS

The ST_Distance function should fit extremely well for your EPSG:4326 data. Notice the third parameter "use_ellipsoid". It must appear in the SQL query or otherwise the distance function falls to use the traditional method "return the distance between geom1 and geom2 (always measured in CRS units)".