5

I have a collection of polygons(building outlines called "Build_poly") that I am intersecting with another table of flood defenses (also polygons called Flood_poly). When ever one of the building polygons intersect with a flood defense polygon I am updating the "defense" field of building polygon from 0 to 1.

What I have been trying to do to speed up my query is put in a condition where: if a building_poly has successfully intersected with a flood polygon for it to skip trying to intersect with any other flood defense polygons. This is what I've got so far, but I haven't managed to get any form of skip to work as this is my first trying to use postgresql.

UPDATE Build_poly
SET Defenses = 1
FROM Flood_poly
WHERE ST_Intersects(Build_poly.geom,Flood_poly.geom)"
  • Interesting... the doc mentions that only one of the join rows will be used to update the target row, but which one will be used is not readily predictable, but it doesn't specify if all matching rows are evaluated or if the planner limits the match to 1 row – JGH Sep 26 '18 at 11:28
  • one could update from a CTE, where a LATERAL JOIN limits the matches to 1 for each building. not sure if you can sqeeze time out of your UPDATE with any other construct, though... – geozelot Sep 26 '18 at 12:08
  • I wouldn't worry about skipping unless you're talking about millions of intersections. It's probably a better idea to build up a spatial index on your two tables and then run a normal query. I'd also set the defences column to a boolean (if you're only interested in True/False cases): CREATE INDEX build_poly_geo_index ON Build_poly USING GIST (geom); CREATE INDEX flood_poly_geo_index ON Flood_poly USING GIST (geom); ALTER TABLE build_poly ADD COLUMN defences boolean; UPDATE build_poly set defenses = ST_INTERSECTS(build_poly.geom, flood_p – RedM Sep 26 '18 at 12:54
  • I already have build a spatial index and we are talking about millions! There are approximately 6m buildings and 10k flood defenses. – Yjelza Sep 26 '18 at 13:33
  • 1
    Highly recommend against this design. It's not normalized, which will make keeping it correct error prone and a pain in the rear. – jpmc26 Sep 26 '18 at 16:28
4

Following my comment I run a simple test on OSM data (osm2pgsql import; 1.7M polygons on 1M points; 340k updates);

Running (the equivalent to)

WITH
  lim AS (
    SELECT <id>
    FROM Build_poly AS bp
    JOIN LATERAL (
      SELECT 1
      FROM Flood_poly AS fp
      WHERE ST_Intersects(fp.geom, bp.geom)
      LIMIT 1
    ) AS q
    ON true
  )

UPDATE Build_poly AS bp
  SET Defenses = 1
  FROM lim
  WHERE bp.<id> = lim.<id>

finished about 3 times faster.

Here, the LATERAL JOIN finds only one match per row in Build_poly, since you can use LIMIT 1 in the inner query. The UPDATE will then be executed on comparing the <id> column (make sure that column of yours has an index in place).


Update:

You mentioned in the comments this approach is 5 times slower than yours and I think, at this point, it's no matter of query design, it's the resource demand that's slowing down; an UPDATE in general is a resource heavy operation, and adding a LATERAL JOIN, with a sub-query execution for each row in memory, will enlarge the resource footprint quickly to a point where PG will need to work with inefficient intermediate/temporary files on disk (this might as well happen with your query, albeit not as extensive maybe). From my experience and following a suggestion I read years ago, creating a new table with the updates and dropping the old one instead usually is the better idea.

Some thoughts:

  • leave it be, let it run overnight and put a trigger in place to update any new inserts on the spot

  • create a new table with a foreign key each on the primary key of both your polygon tables and the Defense column only - it's a relational database after all...

  • create a separate table with centroids (ST_Centroid or ST_PointOnSurface, depending on your polygons) of your Flood-poly table to run the intersection against
  • try optimizing your PostgreSQL setup, e.g. work_mem etc. - this is where I usually head over to DBA.SE (you could even migrate this question)
| improve this answer | |
  • I've been trying to use this script but I keep getting the error: ERROR: syntax error at or near "UPDATE" LINE 13: UPDATE Build_poly AS bp – Yjelza Sep 26 '18 at 14:28
  • There is a missing ), to close out the WITH lim as ( statement. The answer is spot on, though. – John Powell Sep 26 '18 at 14:58
  • @Yjelza uh oh...my bad, I corrected the closing bracket. – geozelot Sep 26 '18 at 15:00
  • @JohnPowell and thx to point out! I see you got rid of that...very demanding other half of your name ,) – geozelot Sep 26 '18 at 16:08
  • 1
    I believe your CTE is equivalent to SELECT id FROM build_poly AS bp WHERE EXISTS (SELECT 1 FROM Flood_poly AS fp WHERE ST_Intersects(fp.geom, bp.geom)). In which case, you could just drop the CTE and put the EXISTS check directly on the UPDATE's WHERE clause. Should speed things up even more. – jpmc26 Sep 26 '18 at 22:14
2

Why not only test buildings that doesn't already have Defenses = 1

UPDATE Build_poly
SET Defenses = 1
FROM Flood_poly
WHERE Build_poly.Defenses != 1 and ST_Intersects(Build_poly.geom,Flood_poly.geom);

The planner will check the integer test first since that test has a lower cost than the intersects check.

Also be sure to have working indexed both on Build_poly.Defenses and on the geometry columns.

If your flood poly table is static you can also add a field that keeps information about if the test already is done. That way you will not have to recheck buildings outside flood_polygons again either.

alter table Build_poly add column already_checked int default 0;
     UPDATE Build_poly
    SET Defenses = 1
    FROM Flood_poly
    WHERE Build_poly.already_checked = 0 and ST_Intersects(Build_poly.geom,Flood_poly.geom);

A third way might be to add a trigger to the Build_poly table which only fires on the row that gets inserted or updated. Then put the test in a row level trigger and do the update from there.

| improve this answer | |
  • Hi thanks for your reply, I did try to run this, it took approximately the same time to run as my original query – Yjelza Sep 27 '18 at 8:03

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.