ST_Distance increases when comparing with more points

[Jan]

I'm using `spatialite` from Ubuntu's spatialite-bin package, version 5.1.0a-1, to calculate the distance between a point and a set of points.

I have a table called `civilization` with a `coords` column containing coordinates of points. I collect these into a single MULTIPOINT object using `ST_Collect`, then use `ST_Distance` to get the distance between these points and my point:

```
SELECT ST_Distance(
    (SELECT ST_Collect(coords) civ FROM civilization),
    GeomFromText("POINT(6.4 54.95)", 4326),
    true);
```

This returns 152254.457894344 meters. If I limit the inner query to a smaller set of points by adding a WHERE clause:

```
SELECT ST_Distance(
    (SELECT ST_Collect(coords) civ FROM civilization WHERE id<81081856),
    GeomFromText("POINT(6.4 54.95)", 4326),
    true);
```

I get a result of 122798.197600431 meters (which I believe to be roughly correct).

My understanding is that the function should return the distance between my point and the closest point from the multipoint collection, and thus having a bigger set of points to choose from should never increase the distance.

Am I mistaken and this is expected, correct behavior (e.g. because some of the points are closer by some other metric and the function is allowed to choose to compare those), or is this a bug in spatialite?

Cheers,

Jan

[a.fu...@lqt.it]

On Fri, 23 Feb 2024 14:05:12 -0800 (PST), Jan wrote:
> I'm using `spatialite` from Ubuntu's spatialite-bin package, version
> 5.1.0a-1, to calculate the distance between a point and a set of
> points.
>
> I have a table called `civilization` with a `coords` column
> containing
> coordinates of points. I collect these into a single MULTIPOINT
> object
> using `ST_Collect`, then use `ST_Distance` to get the distance
> between
> these points and my point:
>
> ```
> SELECT ST_Distance(
>     (SELECT ST_Collect(coords) civ FROM civilization),
>     GeomFromText("POINT(6.4 54.95)", 4326),
>     true);
> ```
>
> This returns 152254.457894344 meters. If I limit the inner query to a
> smaller set of points by adding a WHERE clause:
>
> ```
> SELECT ST_Distance(
>     (SELECT ST_Collect(coords) civ FROM civilization WHERE id
>

hi Jan,

a little preliminary explanation is needed to explain
what is happening to your distances.

In the most common case SpatiaLite directly delegates
the calculation of distances to the GEOS library, the
same one also used by PostGIS, QGIS and many other
geospatial SWs.

But you are using the special form of ST_Distance(),
the one requiring via a further argument to calculate
a distance expressed in meters also in the case of
latitude and longitude coordinates.

In this special case it's not possible to use the
optimized support of GEOS, and instead is requires
using complex geodetic formulas that are particularly
heavy to calculate.
Unfortunately in this particular mode the calculation
of distances risks becoming intolerably slow when very
complex geometries with many thousands of points are
encountered.

To mitigate the problem SpatiaLite tries to apply a
small optimization.
First the closest points of the two geometries are
identified using the support of GEOS, and finally the
geodesic distance expressed in metres between these
two points is measured.

Obviously it's a non-rigorous empirical method, because
GEOS identifies the two closest points by measuring the
distances in the natural reference system units, which
in the case of lat/long coordinates implies that the
distances are angles measured in degrees.
Unfortunately nothing ensures that the minimum angular
distance coincides with the minimum geodesic distance,
because they are two completely unrelated metrics, and
it's something that becomes more evident the closer we
get to the poles.

That's exactly what's happening in your Query; your
reference point is located in the North Sea at a fairly
high latitude where the discrepancy between angular
distances and geodetic distances is particularly
evident.
What you are really obtaining is the smaller of
the angular distances measured in meters, but it's
not necessarily the true minimum geodesic distance
between the two geometries.

best regards,
Sandro