I have point in my db (Postgres postgis):
CREATE TABLE geometries (name varchar, geom geometry);
With example data:
INSERT INTO geometries VALUES ('Point', 'POINT(0 0)');
INSERT INTO geometries VALUES ('Point', 'POINT(10 0)');
I would like to select one point closest to input - for example (1, 1)
It must be geographics distance using haversine formula (https://www.movable-type.co.uk/scripts/latlong.html).
What is the best way (fastest by performance) how can I do it?
Use the nearest neighbour operator
SELECT * FROM geometries
ORDER BY geom <-> ST_GeogFromText('POINT(1, 1)')
LIMIT 1
<-> operator measures distances based on a sphere for GEOGRAPHY; if 'using haversine' (i.e. spheroidal geometric algebra) is mandatory, one will need to ORDER BY ST_Distance(<geography_a>, <geography_b>)
ST_DISTANCE was horribly slow and inefficient. Wouldn't it be enough to cast geometry to geography? Ie, ORDER BY geom::geography <-> ST_GeomFromText('POINT(1, 1)')::geography ?
<-> operator is the implicit check of the index to consider only geometries in the actual neighborhood; adding sth. like WHERE geom && ST_Expand('POINT(1 1)'::GEOMETRY, <threshold_in_degree_eg_0.001>) would help to limit actual calculations to nearby points (but obviously doesn't guarantee to find a neighbor). casting to GEOGRAPHY is definitely what you want, but the <-> operator is hardcoded to 'only' measure on a sphere, wheras ST_Distance uses a spheroid for GEOGRAPHY by default.
GEOGRAPHY precision for nearest neighbor searches; for 99.5% of cases, topology is completely independent from the impact of longitudinal measurement discrepancy or great circle distance differences when using 2D LatLon 'projections'!