Timeline for Calculating distance (lat, lon) for geom point using PostGIS
Current License: CC BY-SA 4.0
7 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Oct 27, 2019 at 12:39 | comment | added | geozelot |
@hunter your answer is still +1! ...actually, there is almost no need at all to use 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'!
|
|
| Oct 27, 2019 at 12:31 | comment | added | geozelot |
@hunter it's as efficient as ordering in general. the advantage of the <-> 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.
|
|
| Oct 27, 2019 at 11:53 | comment | added | hunter |
@ThingumaBob I was under the impression that ordering by 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 ?
|
|
| Oct 27, 2019 at 8:56 | vote | accept | jimodefax | ||
| Oct 27, 2019 at 8:13 | comment | added | geozelot |
note that the <-> 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>)
|
|
| Oct 26, 2019 at 18:35 | review | First posts | |||
| Oct 26, 2019 at 20:54 | |||||
| Oct 26, 2019 at 18:31 | history | answered | hunter | CC BY-SA 4.0 |