Skip to main content
edited tags
Source Link
Evan Carroll
  • 64.7k
  • 49
  • 251
  • 496

Here is an example which is direct copy from && documentation, ST_Intersects adaptation:

# SELECT tbl1.column1, tbl2.column1, tbl1.column2 && tbl2.column2 AS overlaps
FROM ( VALUES
(1, 'LINESTRING(0 0, 3 3)'::geometry),
(2, 'LINESTRING(0 1, 0 5)'::geometry))t1.id AS tbl1t1,
( VALUES
(3, 'LINESTRING(1 2, 4 6)'::geometry)) AS tbl2;
 column1 | column1 |t2.id overlapsAS 
---------+---------+----------t2,
       1 |     t1.ln && 3t2.ln |AS t"&&",
       2 |       3 | f
(2 rows)

Below is ST_Intersects adaptation:

# SELECT tbl1.column1, tbl2.column1, ST_Intersects(tbl1t1.column2ln,tbl2t2.column2ln) AS overlaps
FROM ( VALUES
        (1, 'LINESTRING(0 0, 3 3)'::geometry),
        (2, 'LINESTRING(0 1, 0 5)'::geometry) 
) AS tbl1t1(id,ln)
(CROSS JOIN (VALUES
        (3, 'LINESTRING(1 2, 4 6)'::geometry) 
) AS tbl2;t2(id,ln);

 column1t1 | column1t2 | overlaps&& | st_intersects 
-----+----+----+-----+----------
       1 |    3 | t 3 | f
       2 |    3 | f 3 | f
(2 rows)

To summarize:

Question 1 is if &&&& is significantly faster than ST_IntersectsST_Intersects when using with ST_MakeEnvelopeST_MakeEnvelope (a rectangular boundary), when finding points inside a rectangular bounding box.

Question 2 is Also am I understanding correctly that when checking points inside a rectangular boundary &&&& does exactly same thing as ST_IntersectsST_Intersects?

Here is an example which is direct copy from && documentation

# SELECT tbl1.column1, tbl2.column1, tbl1.column2 && tbl2.column2 AS overlaps
FROM ( VALUES
(1, 'LINESTRING(0 0, 3 3)'::geometry),
(2, 'LINESTRING(0 1, 0 5)'::geometry)) AS tbl1,
( VALUES
(3, 'LINESTRING(1 2, 4 6)'::geometry)) AS tbl2;
 column1 | column1 | overlaps 
---------+---------+----------
       1 |       3 | t
       2 |       3 | f
(2 rows)

Below is ST_Intersects adaptation:

# SELECT tbl1.column1, tbl2.column1, ST_Intersects(tbl1.column2,tbl2.column2) AS overlaps
FROM ( VALUES
(1, 'LINESTRING(0 0, 3 3)'::geometry),
(2, 'LINESTRING(0 1, 0 5)'::geometry)) AS tbl1,
( VALUES
(3, 'LINESTRING(1 2, 4 6)'::geometry)) AS tbl2;
 column1 | column1 | overlaps 
---------+---------+----------
       1 |       3 | f
       2 |       3 | f
(2 rows)

To summarize:

Question 1 is if && is significantly faster than ST_Intersects when using with ST_MakeEnvelope (a rectangular boundary), when finding points inside a rectangular bounding box.

Question 2 is Also am I understanding correctly that when checking points inside a rectangular boundary && does exactly same thing as ST_Intersects?

Here is an example which is direct copy from &&, ST_Intersects adaptation:

SELECT
        t1.id AS t1,
        t2.id AS t2,
        t1.ln && t2.ln AS "&&",
        ST_Intersects(t1.ln,t2.ln)
FROM ( VALUES
        (1, 'LINESTRING(0 0, 3 3)'::geometry),
        (2, 'LINESTRING(0 1, 0 5)'::geometry) 
) AS t1(id,ln)
CROSS JOIN (VALUES
        (3, 'LINESTRING(1 2, 4 6)'::geometry) 
) AS t2(id,ln);

 t1 | t2 | && | st_intersects 
----+----+----+---------------
  1 |  3 | t  | f
  2 |  3 | f  | f
(2 rows)

Question 1 is if && is significantly faster than ST_Intersects when using with ST_MakeEnvelope (a rectangular boundary), when finding points inside a rectangular bounding box.

Question 2 is Also am I understanding correctly that when checking points inside a rectangular boundary && does exactly same thing as ST_Intersects?

edited tags
Link
Evan Carroll
  • 64.7k
  • 49
  • 251
  • 496
deleted 358 characters in body; edited title
Source Link
Evan Carroll
  • 64.7k
  • 49
  • 251
  • 496

&& , ST_Overlaps ,vs ST_Intersects performancesperformance

I am trying to figure out points reside inside an rectangular area (envelope). I am having a little bit difficulty understanding performance implications of using &&&& operator compared to ST_IntersectST_Intersect.

Returns TRUE if the Geometries/Geography "spatially intersect in 2D" - (share any portion of space) and FALSE if they don't (they are Disjoint). For geography -- tolerance is 0.00001 meters (so any points that close are considered to intersect)

The manual for ST_Overlaps says:

ST_Overlaps — Returns TRUE if the Geometries share space, are of the same dimension, but are not completely contained by each other.

So, clearly ST_Overlaps return false if one object is completely inside another. Which is unsuitable for my objective. Meanwhile ST_Intersects will return true in such cases. For my objective && operator does almost same thing since I am using a rectangular bounding box in either case. I am wondering if && is the fastest operator for my purpose? It feels like && has to do less checks so it must be much more efficient.

&& , ST_Overlaps , ST_Intersects performances

I am trying to figure out points reside inside an rectangular area (envelope). I am having a little bit difficulty understanding performance implications of using && operator compared to ST_Intersect.

Returns TRUE if the Geometries/Geography "spatially intersect in 2D" - (share any portion of space) and FALSE if they don't (they are Disjoint). For geography -- tolerance is 0.00001 meters (so any points that close are considered to intersect)

The manual for ST_Overlaps says:

ST_Overlaps — Returns TRUE if the Geometries share space, are of the same dimension, but are not completely contained by each other.

So, clearly ST_Overlaps return false if one object is completely inside another. Which is unsuitable for my objective. Meanwhile ST_Intersects will return true in such cases. For my objective && operator does almost same thing since I am using a rectangular bounding box in either case. I am wondering if && is the fastest operator for my purpose? It feels like && has to do less checks so it must be much more efficient.

&& vs ST_Intersects performance

I am trying to figure out points reside inside an rectangular area (envelope). I am having a little bit difficulty understanding performance implications of using && operator compared to ST_Intersect.

Returns TRUE if the Geometries/Geography "spatially intersect in 2D" - (share any portion of space) and FALSE if they don't (they are Disjoint). For geography -- tolerance is 0.00001 meters (so any points that close are considered to intersect)

ST_Intersects will return true in such cases. For my objective && operator does almost same thing since I am using a rectangular bounding box in either case. I am wondering if && is the fastest operator for my purpose? It feels like && has to do less checks so it must be much more efficient.

added more info
Source Link
yurtesen
  • 237
  • 4
  • 11
Loading
edited body
Source Link
Evan Carroll
  • 64.7k
  • 49
  • 251
  • 496
Loading
Source Link
yurtesen
  • 237
  • 4
  • 11
Loading