Postgres/PostGIS skip intersect on features already intersected

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

up vote
2
down vote

favorite

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

asked 3 hours ago

Yjelza

1459

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
1 hour ago

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...
â€“Â ThingumaBob
1 hour ago

add a commentÂ |Â

up vote
2
down vote

favorite

UPDATE Build_poly
SET Defenses = 1
FROM Flood_poly
WHERE ST_Intersects(Build_poly.geom,Flood_poly.geom)"

asked 3 hours ago

Yjelza

1459

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
1 hour ago

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...
â€“Â ThingumaBob
1 hour ago

add a commentÂ |Â

up vote
2
down vote

favorite

UPDATE Build_poly
SET Defenses = 1
FROM Flood_poly
WHERE ST_Intersects(Build_poly.geom,Flood_poly.geom)"

asked 3 hours ago

Yjelza

1459

UPDATE Build_poly
SET Defenses = 1
FROM Flood_poly
WHERE ST_Intersects(Build_poly.geom,Flood_poly.geom)"

postgis postgresql st-intersects

asked 3 hours ago

Yjelza

1459

asked 3 hours ago

Yjelza

1459

asked 3 hours ago

Yjelza

1459

asked 3 hours ago

Yjelza

1459

asked 3 hours ago

Yjelza

1459

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
1 hour ago

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...
â€“Â ThingumaBob
1 hour ago

add a commentÂ |Â

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
1 hour ago

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...
â€“Â ThingumaBob
1 hour ago

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
1 hour ago

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...
â€“Â ThingumaBob
1 hour ago

add a commentÂ |Â

3 Answers
3

active

oldest

votes

up vote
1
down vote

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

answered 45 mins ago

ThingumaBob

3,8731219

add a commentÂ |Â

up vote
1
down vote

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_poly.geom)
FROM flood_poly

answered 33 mins ago

RedM

16618

add a commentÂ |Â

up vote
0
down vote

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.

edited 26 mins ago

answered 32 mins ago

Nicklas AvÃ©n

11.3k12539

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%2f297090%2fpostgres-postgis-skip-intersect-on-features-already-intersected%23new-answer', 'question_page');

);

Post as a guest

Name

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

up vote
1
down vote

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.

answered 45 mins ago

ThingumaBob

3,8731219

add a commentÂ |Â

up vote
1
down vote

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.

answered 45 mins ago

ThingumaBob

3,8731219

add a commentÂ |Â

up vote
1
down vote

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.

answered 45 mins ago

ThingumaBob

3,8731219

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.

answered 45 mins ago

ThingumaBob

3,8731219

answered 45 mins ago

ThingumaBob

3,8731219

answered 45 mins ago

ThingumaBob

3,8731219

answered 45 mins ago

ThingumaBob

3,8731219

add a commentÂ |Â

up vote
1
down vote

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_poly.geom)
FROM flood_poly

answered 33 mins ago

RedM

16618

add a commentÂ |Â

up vote
1
down vote

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_poly.geom)
FROM flood_poly

answered 33 mins ago

RedM

16618

add a commentÂ |Â

up vote
1
down vote

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_poly.geom)
FROM flood_poly

answered 33 mins ago

RedM

16618

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_poly.geom)
FROM flood_poly

answered 33 mins ago

RedM

16618

answered 33 mins ago

RedM

16618

answered 33 mins ago

RedM

16618

answered 33 mins ago

RedM

16618

add a commentÂ |Â

up vote
0
down vote

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.

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.

edited 26 mins ago

answered 32 mins ago

Nicklas AvÃ©n

11.3k12539

add a commentÂ |Â

up vote
0
down vote

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.

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.

edited 26 mins ago

answered 32 mins ago

Nicklas AvÃ©n

11.3k12539

add a commentÂ |Â

up vote
0
down vote

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.

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.

edited 26 mins ago

answered 32 mins ago

Nicklas AvÃ©n

11.3k12539

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.

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.

edited 26 mins ago

answered 32 mins ago

Nicklas AvÃ©n

11.3k12539

edited 26 mins ago

answered 32 mins ago

Nicklas AvÃ©n

11.3k12539

answered 32 mins ago

Nicklas AvÃ©n

11.3k12539

answered 32 mins ago

Nicklas AvÃ©n

11.3k12539

add a commentÂ |Â

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky