# How to use St_intersects with different geometry type

I try to run this really simple spatial query with PostgreSQL+Postgis extension.

CASE 1:

``````SELECT a.id, b.id
FROM PolygonLayer1 a, PolygonLayer2 b
WHERE St_intersects(a.geom,st_centroid(b.geom))
``````

CASE 2:

``````SELECT a.id, b.id
FROM PolygonLayer1 a, PolygonLayer2 b
WHERE St_intersects(st_centroid(a.geom),b.geom) -- This time I get the centroid of PolygonLayer1
``````

Both PolygonLayer1 (~8000 features) and PolygonLayer2 (~10000 features) are polygons (or Multipolygons).

• The case 1 run in less than 10 seconds
• The case 2 never ends

I really don't understand the difference between the 2 cases.

So the question: What is the difference between the 2 cases ? How can I get a working query for both cases ?

Specification:

• If I run: `EXPLAIN CASE1`, I get a cost of ~20'000 "operations"
```"Nested Loop  (cost=0.00..21878.84 rows=196 width=8)"
"  Join Filter: _st_intersects(st_centroid(a.geom), b.geom)"
"  ->  Seq Scan on superposition_bruit a  (cost=0.00..6089.04 rows=8804 width=728)"
"  ->  Index Scan using affectation_com_old_geom_gist on affectation_com_old b  (cost=0.00..1.53 rows=1 width=591)"
"        Index Cond: (st_centroid(a.geom) && geom)"
```

========================================================================

• If I run: `EXPLAIN CASE2`, I get a cost of ~40'000'000 "operations"
```"Nested Loop  (cost=0.00..40530509.25 rows=196 width=8)"
"  Join Filter: ((a.geom && st_centroid(b.geom)) AND _st_intersects(a.geom, st_centroid(b.geom)))"
"  ->  Seq Scan on superposition_bruit a  (cost=0.00..6089.04 rows=8804 width=728)"
"  ->  Materialize  (cost=0.00..3813.98 rows=13332 width=591)"
"        ->  Seq Scan on affectation_com_old b  (cost=0.00..2744.32 rows=13332 width=591)"
```
• I suppose that on-the-fly generated centroids can't utilize spatial index. Create a new temporary table from the centroids, create spatial index and try again. – user30184 Sep 8 '16 at 10:08
• @user30184, ok but why it work in the first case and not in the second ? Because I can also add a second condition to the case 2 `WHERE St_intersects(st_centroid(a.geom),b.geom) AND St_intersects(a.geom,b.geom)` and it will work. – obchardon Sep 8 '16 at 10:48
• This on the verge of being a simple database question, without significant GIS content. If you had included full EXPLAIN plans, the metamorphosis would have been complete. Sequential scan of N rows is O (N/2): figuring out how the cost is calculated is left as an exercise. You should try putting the indexed column in the first position in case 2 (as case 3). – Vince Sep 8 '16 at 10:59

Fast query results for `ST_Intersects` hinge on the fact that not every pair of inputs needs to be tested. PostGIS avoids testing every pair of geometries by implicitly testing the arguments to `ST_Intersects` with the bounding box intersection operator `&&`, so that only geometries whose bounding boxes intersect need to be passed to `ST_Intersects`. When your geometry columns are indexed, PostgreSQL can use the index to fetch only geometries that pass the `&&` filter, significantly reducing the number of comparisons.

Here's the problem. The index provides the bounding boxes of `a.geom` and `b.geom`, but not `ST_Centroid(a.geom)`. You and I know that whenever `a.geom && b.geom` is true, then `ST_Centroid(a.geom) && b.geom` must also be true, but PostgreSQL has no way to know this.

You can fix this by manually forcing an `a.geom && b.geom` comparison, which can take advantage of the index.

``````SELECT a.id, b.id
FROM PolygonLayer1 a, PolygonLayer2 b
WHERE a.geom && b.geom AND ST_Intersects(ST_Centroid(a.geom), b.geom)
``````

This doesn't explain why you're getting good performance in Case 1, because I have no idea.

• I guess that in case 1 PostGIS can utilize the index because it gets fast answer to which polygons in b fulfill && with polygon in a, and compute centroids only for those candidate polygons in b. – user30184 Sep 8 '16 at 11:59
• Centroids can very well (and often) lie outside their polygons! – bugmenot123 Sep 9 '16 at 7:56
• @bugmenot123 Yes, but that doesn't come into play here. The `&&` operator compares bounding boxes. A polygon's centroid can't lie outside of its bounding box. – dbaston Sep 9 '16 at 11:25
• Sorry, case 1 is OP's case 1 query for me. They did not say if adding your solution made both queries run similarly fast. – bugmenot123 Sep 9 '16 at 13:45