What's the difference between using PostGIS functions and operators?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
3
down vote

favorite

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?

asked Aug 6 at 14:25

karpfen

1,4671023

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 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.
â€“Â RoVo
Aug 6 at 14:47

Seems to require also PostGIS version 2.2.
â€“Â user30184
Aug 6 at 16:05

add a commentÂ |Â

up vote
3
down vote

favorite

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?

asked Aug 6 at 14:25

karpfen

1,4671023

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 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.
â€“Â RoVo
Aug 6 at 14:47

Seems to require also PostGIS version 2.2.
â€“Â user30184
Aug 6 at 16:05

add a commentÂ |Â

up vote
3
down vote

favorite

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?

asked Aug 6 at 14:25

karpfen

1,4671023

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?

asked Aug 6 at 14:25

karpfen

1,4671023

asked Aug 6 at 14:25

karpfen

1,4671023

asked Aug 6 at 14:25

karpfen

1,4671023

asked Aug 6 at 14:25

karpfen

1,4671023

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 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.
â€“Â RoVo
Aug 6 at 14:47

Seems to require also PostGIS version 2.2.
â€“Â user30184
Aug 6 at 16:05

add a commentÂ |Â

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 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.
â€“Â RoVo
Aug 6 at 14:47

Seems to require also PostGIS version 2.2.
â€“Â user30184
Aug 6 at 16:05

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

â€“Â RoVo
Aug 6 at 14:47

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.

â€“Â RoVo
Aug 6 at 14:47

Seems to require also PostGIS version 2.2.
â€“Â user30184
Aug 6 at 16:05

add a commentÂ |Â

1 Answer
1

active

oldest

votes

up vote
5
down vote

accepted

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

edited Aug 6 at 14:51

answered Aug 6 at 14:46

RoVo

4,324629

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "79"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f291869%2fwhats-the-difference-between-using-postgis-functions-and-operators%23new-answer', 'question_page');

);

Post as a guest

Name

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
5
down vote

accepted

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

edited Aug 6 at 14:51

answered Aug 6 at 14:46

RoVo

4,324629

add a commentÂ |Â

up vote
5
down vote

accepted

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

edited Aug 6 at 14:51

answered Aug 6 at 14:46

RoVo

4,324629

add a commentÂ |Â

up vote
5
down vote

accepted

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

edited Aug 6 at 14:51

answered Aug 6 at 14:46

RoVo

4,324629

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

edited Aug 6 at 14:51

answered Aug 6 at 14:46

RoVo

4,324629

edited Aug 6 at 14:51

answered Aug 6 at 14:46

RoVo

4,324629

answered Aug 6 at 14:46

RoVo

4,324629

answered Aug 6 at 14:46

RoVo

4,324629

add a commentÂ |Â

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky