6

PostGIS offers a list of operators implementing spatial relations. Some of them have a direct function equivalent, for example <-> and ST_Distance.

EXPLAIN
SELECT
st_distance(st_setsrid(st_makepoint(0, 0), 4326),
             st_setsrid(st_makepoint(10, 10), 4326))

and

EXPLAIN
SELECT
st_setsrid(st_makepoint (0, 0), 4326) <->
st_setsrid(st_makepoint (10, 10), 4326)

yield the exact same query plan. Are there any scenarios in which one method is preferable to the other?

3
  • 1
    Operators seem to utilize bounding boxes and thus <-> and ST_Distance are not equivalent for other geometries than points. Operators suit well for fast, spatial index spaced filtering. Typical use case is to make a fast pre-selection with && and run the slower but accurate ST_Intersects for the result set if it is important to know if the geometries really intersect.
    – user30184
    Aug 6, 2018 at 14:45
  • that is not true for postgreSQL > 9.5: For PostgreSQL below 9.5 only gives centroid distance of bounding boxes and for PostgreSQL 9.5+, does true KNN distance search giving true distance between geometries, and distance sphere for geographies.
    – pLumo
    Aug 6, 2018 at 14:47
  • Seems to require also PostGIS version 2.2.
    – user30184
    Aug 6, 2018 at 16:05

1 Answer 1

6

<-> and <#> can make use of indexes when used in SORT BY clause.

Used in the "ORDER BY" clause provides index-assisted nearest-neighbor result sets.

e.g.:

SELECT *
FROM table1, table2
SORT BY table1.geom <-> table2.geom
LIMIT 1

See the example on the docs for <->.

Another difference:

<-> always uses spherical distance for geography type, while st_distance uses distance calculated on a spheroid (slower) as default with an option to change it (use_spheroid=false).

2
  • What is the reasoning for <-> to not use the spheroid?
    – No_name
    Apr 4, 2020 at 9:18
  • @No_name -- Speed
    – Martin F
    Jan 15 at 22:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.