3

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?

  • 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 '18 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 '18 at 14:47
  • Seems to require also PostGIS version 2.2. – user30184 Aug 6 '18 at 16:05
5

<-> 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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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