Auto: Nearest chargers are not correctly sorted by distance (OCM, OSM, Nobil)

[robho]

When using Android Auto there's a list of nearest chargers shown on screen. This list is not sorted properly for data sources OCM, OSM and Nobil. GoingElectric seems ok.

It's doesn't seem completely unordered. The first few matches are near and seem to be sorted, but scrolling down further there are other nearby chargers that should have been higher up in the list.

Here's an example when using OSM:
Charger 1 - 2,5 km
Charger 2 - 2,8 km
Charger 3 - 2,5 km
Charger 4 - 2,3 km
Charger 5 - 2,8 km
Charger 6 - 3,2 km
Charger 7 - 3,8 km
Charger 8 - 3,6 km
Charger 9 - 2,1 km
Charger 10 - 3,7 km
Charger 11 - 3,1 km
Charger 12 - 3,1 km
Charger 13 - 3,6 km
Charger 14 - 3,2 km
Charger 15 - 4,4 km
Charger 16 - 4,1 km
Charger 17 - 4,2 km
Charger 18 - 4,4 km
Charger 19 - 4,5 km
Charger 20 - 4,8 km
Charger 21 - 4,9 km
Charger 22 - 4,6 km
Charger 23 - 4,0 km
Charger 24 - 4,4 km
Charger 25 - 4,3 km

(tested without filters, with legacy map screen and not using "chargers along route")

[robho]

It looks like the list of chargers returned by getChargeLocationsRadius is incorrectly sorted.

I made this change and it seems to fix the problem:

--- a/app/src/main/java/net/vonforst/evmap/storage/ChargeLocationsDao.kt
+++ b/app/src/main/java/net/vonforst/evmap/storage/ChargeLocationsDao.kt
@@ -101,7 +102,7 @@ abstract class ChargeLocationsDao {
     ): List<ChargeLocation>
 
     @SkipQueryVerification
-    @Query("SELECT * FROM chargelocation WHERE dataSource == :dataSource AND PtDistWithin(coordinates, MakePoint(:lng, :lat, 4326), :radius) AND timeRetrieved > :after AND ROWID IN (SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'ChargeLocation' AND f_geometry_column = 'coordinates' AND search_frame = BuildCircleMbr(:lng, :lat, :radius)) ORDER BY Distance(coordinates, MakePoint(:lng, :lat, 4326))")
+    @Query("SELECT * FROM chargelocation WHERE dataSource == :dataSource AND PtDistWithin(coordinates, MakePoint(:lng, :lat, 4326), :radius) AND timeRetrieved > :after AND ROWID IN (SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'ChargeLocation' AND f_geometry_column = 'coordinates' AND search_frame = BuildCircleMbr(:lng, :lat, :radius)) ORDER BY Distance(coordinates, MakePoint(:lng, :lat, 4326), 1)")
     abstract suspend fun getChargeLocationsRadius(
         lat: Double,
         lng: Double,

Ie.. I'm setting use_ellipsoid=TRUE in call to Distance.

But, I don't understand this.. The distances shown in Android Auto are calculated by distanceBetween which is a Haversine implementation that uses great-circle calculations. With the change above I'm no longer using great-circle calculation in the database query. It would make sense to me to use the same calculations in both the database query and when calculating the distance for the UI.

But on the other hand.. If we were using great-circle calculations consistently before, why are the distances returned by the database query and distanceBetween not the same?

[robho]

It wasn't the switch to use_ellipsoid=TRUE that fixed this. The return value of SpatiaLite function Distance() changes when a third argument is added. 2 arguments -> CRS units. 3 arguments -> meters

So, sorting by meters instead of CRS units seems to fix this.