ST_ClosestPoint 2X slower than ST_Distance?

26 views
Skip to first unread message

Jason Williams

unread,
Aug 14, 2022, 4:14:03 PMAug 14
to SpatiaLite Users
I have 2 quries:

SELECT ST_Distance(geometry, src.srcpt) as dist
                       FROM main.coastline, (SELECT ST_Transform(SetSRID(MakePoint(<lon>, <lat>), 4326), 26917) as srcpt) as src
                       LIMIT 1
=====================================================
select X(nearestpt) as x1, Y(nearestpt) as y1, X(srcpt) as x2, Y(srcpt) as y2
from
(SELECT ST_ClosestPoint(geometry, src.srcpt) as nearestpt, src.srcpt as srcpt
                       FROM main.coastline , (SELECT ST_Transform(SetSRID(MakePoint(<lon>, <lat>), 4326), 26917) as srcpt) as src
                       LIMIT 1)

I thought ST_Distance would internally call ST_ClosestPoint function then will calculate the distance between the 2 points. But the first query using ST_Distance is 2X faster than the second query using ST_ClosestPoint. Why is ST_ClosestPoint so slow? 

Thank you in advance!

a.fu...@lqt.it

unread,
Aug 15, 2022, 2:36:28 AMAug 15
to spatiali...@googlegroups.com
On Sun, 14 Aug 2022 13:14:02 -0700 (PDT), Jason Williams wrote:
> I have 2 quries:
>
> SELECT ST_Distance(geometry, src.srcpt) as dist
> FROM main.coastline, (SELECT ST_Transform(SetSRID(MakePoint(, ),
> 4326), 26917) as srcpt) as src
> LIMIT 1
> =====================================================
> select X(nearestpt) as x1, Y(nearestpt) as y1, X(srcpt) as x2,
> Y(srcpt) as y2
> from
> (SELECT ST_ClosestPoint(geometry, src.srcpt) as nearestpt, src.srcpt
> as srcpt
> FROM main.coastline , (SELECT ST_Transform(SetSRID(MakePoint(, ),
> 4326), 26917) as srcpt) as src
> LIMIT 1)
>
> I thought ST_Distance would internally call ST_ClosestPoint function
> then will calculate the distance between the 2 points. But the first
> query using ST_Distance is 2X faster than the second query using
> ST_ClosestPoint. Why is ST_ClosestPoint so slow?
>

Hi Jason,

a first very important clarification.

all geometry-related SQL functions as ST_IsValid(), ST_Length(),
ST_Area(),
ST_Centroid(), ST_Union(), ST_Intersects(), ST_Touches, ST_Differece(),
ST_Intersection(), ST_Overlaps(), ST_Crosses() and alike are not
directly
implemented by SpatiaLite itself.

all them are provided by the well-known open source library GEOS
(Geometry
Engine Open Source), that in turn is the result of porting JTS (Java
Topology Suite) in C++

https://github.com/libgeos/geos
https://en.wikipedia.org/wiki/JTS_Topology_Suite

GEOS is universally adopted by all O.S. Spatial/GIS projects, so the
results
you can obtain on SpatiaLite are always the same you'll get using
PostGIS,
GrassGis, QGIS, MapServer, GeoServer (... and many others).

Short conclusion: SpatiaLite has no direct control on the computational
geometry algorithms adopted by GEOS. If you have specific questions
about these topic you should post a message on the GEOS own mailing
list.

-----------------

that said, I strongly doubt that ST_Distance() internally calls
ST_ClosestPoint() for a very elementary reason.
ST_Distance() was supported since the very first version of
GEOS, whilst ST_ClosestPoint() was introduced only in later
times (several years later if I remember well).
In other words: ST_Distance() is part of the very basic core
of GEOS, but ST_ClosestPoint() belongs to a further set of
"advanced" capabilities that were implemented only in a
second time.

------------------

final remark:

there is no reason to write complex SQL expressions such as:

SetSRID(MakePoint(<lon>, <lat>), 4326)

when you can directly write:

MakePoint(<lon>, <lat>, 4326)

that is a shorter, simpler and clearer notation.

bye Sandro

Jason Williams

unread,
Aug 15, 2022, 4:59:30 PMAug 15
to SpatiaLite Users
Thank you very much, Sandro!
Reply all
Reply to author
Forward
0 new messages