3

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)"
  • 2
    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
  • 1
    @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
  • 1
    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
8

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

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.