Timeline for Postgres/PostGIS skip intersect on features already intersected
Current License: CC BY-SA 4.0
16 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Sep 27, 2018 at 16:21 | comment | added | Francis | The Build_polys are circles but the Flood_poly's are complex polygons with multiple vertices, most if not all have 10 or more vertices | |
| Sep 27, 2018 at 15:50 | comment | added | jpmc26 | @Yjelza How complex are your geometries? In number of vertices. | |
| Sep 27, 2018 at 9:03 | history | edited | geozelot | CC BY-SA 4.0 |
added 170 characters in body
|
| Sep 27, 2018 at 8:46 | comment | added | Francis | I do, I indexed it using CREATE INDEX id_idx on id USING btree (id) | |
| Sep 27, 2018 at 8:45 | comment | added | geozelot |
@Yjelza just to be sure: do you have an index on the <id> column?
|
|
| Sep 27, 2018 at 8:44 | history | edited | geozelot | CC BY-SA 4.0 |
added 1198 characters in body
|
| Sep 27, 2018 at 8:00 | comment | added | geozelot |
@Yjelza haha...damn. try with the EXISTS query mentioned above
|
|
| Sep 27, 2018 at 7:59 | comment | added | Francis | So I let your suggeston run overnight as I had to leave, it took 121m55s to run compared to my original query which ran in 23m47s | |
| Sep 27, 2018 at 7:35 | comment | added | geozelot |
+1 @jpmc26: that runs the same theoretical concept, and would probably be the more specialized design! I still can seem to get a speed benefit compared to the LATERAL JOIN, though; while the condition fulfills at the first match, the non-matching cases will still crawl the whole table...pretty much what the above structure does. anyways, I think it's worth an answer since the EXISTS design and purpose is pretty much the direct answer to OPs question
|
|
| Sep 26, 2018 at 22:14 | comment | added | jpmc26 |
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.
|
|
| Sep 26, 2018 at 16:08 | comment | added | geozelot | @JohnPowell and thx to point out! I see you got rid of that...very demanding other half of your name ,) | |
| Sep 26, 2018 at 15:00 | comment | added | geozelot | @Yjelza uh oh...my bad, I corrected the closing bracket. | |
| Sep 26, 2018 at 15:00 | history | edited | geozelot | CC BY-SA 4.0 |
added 8 characters in body
|
| Sep 26, 2018 at 14:58 | comment | added | John Powell | There is a missing ), to close out the WITH lim as ( statement. The answer is spot on, though. | |
| Sep 26, 2018 at 14:28 | comment | added | Francis | 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 | |
| Sep 26, 2018 at 12:42 | history | answered | geozelot | CC BY-SA 4.0 |