Hi Darafei, Thank you so much, I have tested and now using EXISTS it runs in less than a second.
I had avoided using exists because of the recommendation on: https://www.techonthenet.com/postgresql/exists.php 'SQL statements that use the EXISTS condition in PostgreSQL are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query's table. There are more efficient ways to write most queries, that do not use the EXISTS condition.' However, this seems to work appropriately for me. Thank you again, Oliver -----Original Message----- From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of postgis-users-requ...@lists.osgeo.org Sent: 29 January 2018 20:00 To: postgis-users@lists.osgeo.org Subject: [EXTERNAL] postgis-users Digest, Vol 191, Issue 7 External Sender: Use caution with links/attachments. Send postgis-users mailing list submissions to postgis-users@lists.osgeo.org To subscribe or unsubscribe via the World Wide Web, visit https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.osgeo.org_mailman_listinfo_postgis-2Dusers&d=DwIGaQ&c=PskvixtEUDK7wuWU-tIg6oKuGYBRbrMXk2FZvF0UfTo&r=A8IiePeQXxNn87piHJsFIGj9NGqzzZxbQ6jIL0nH8HQ&m=BQ81R13zHy3RXBQgSEYB6ibDq8qY68opCsE6EvC6ctM&s=fdGcanVIfllgOEeIyzfZ5_ST_0uAH8vBN7t9IdR0NFE&e= or, via email, send a message with subject or body 'help' to postgis-users-requ...@lists.osgeo.org You can reach the person managing the list at postgis-users-ow...@lists.osgeo.org When replying, please edit your Subject line so it is more specific than "Re: Contents of postgis-users digest..." Today's Topics: 1. Spatial Intersect if condition is met then go to next step (Oliver Morris) 2. Re: Spatial Intersect if condition is met then go to next step (Darafei Komяpa Praliaskouski) ---------------------------------------------------------------------- Message: 1 Date: Mon, 29 Jan 2018 10:28:57 +0000 From: Oliver Morris <oliver.mor...@halliburton.com> To: "postgis-users@lists.osgeo.org" <postgis-users@lists.osgeo.org> Subject: [postgis-users] Spatial Intersect if condition is met then go to next step Message-ID: <93fb8a2740efb64d82ce13d909334cc0036c5...@ep1exmb001.corp.halliburton.com> Content-Type: text/plain; charset="utf-8" Hi, First post so apologies in advance if this is the wrong place for this question - hopefully I found the right place. I have a bounding box geometry and I would like to find out if any rows in a selection of tables are intersected, each table contains a field (seq) which contains a property that I need to group the results by (for this demo lets day it is days of the week). I don't need to know how many rows but simply require a true/false result for each table and seq. Ideally the script as soon as it has found an intersection would move onto the next table and not spend any further time running intersections, I was hoping the limit 1 clause would do this. I was also hoping that the lateral join would help me to do the grouping but I think it still processes all the rows. Currently my script (for two example tables) looks like: CREATE OR REPLACE FUNCTION tst.f_tst_report( topleftx double precision, toplefty double precision, bottomrightx double precision, bottomrighty double precision, groupidarray text) RETURNS TABLE(seq character) LANGUAGE 'plpgsql' COST 100 VOLATILE ROWS 1000 AS $BODY$ DECLARE envelope geometry; BEGIN envelope := ST_MakeEnvelope(topleftx,toplefty,bottomrightx,bottomrighty,4326); RETURN QUERY WITH cte_gde_polygons as ( select distinct sq.seq as seq1 from tst.seq sq join lateral ( select ss.seq from present_mapnonclipped.gde_polygons ss where ss.seq = sq.seq and ST_Intersects(ss.geom,envelope) ) limit 1 ) p on true), cte_gde_fault as ( select distinct sq.seq as seq1 from tst.seq sq join lateral ( select ss.seq from present_mapnonclipped.gde_fault ss where ss.seq = sq.seq and ST_Intersects(ss.geom,envelope) ) limit 1 ) p on true) select * from cte_gde_polygons union select * from cte_gde_fault END $BODY$; An example output across these tables for when Monday, Wednesday and Thursday intersects with the bounding box would return: Seq Monday Wednesday Thursday In short - if there is no way to stop intersecting after the first match my alternative is to dissolve all the features by seq in each table. The only issue would be that this would need updating on any data change. Many Thanks for your help in advance, Oliver -------------- next part -------------- An HTML attachment was scrubbed... URL: <https://urldefense.proofpoint.com/v2/url?u=http-3A__lists.osgeo.org_pipermail_postgis-2Dusers_attachments_20180129_e90e90d6_attachment-2D0001.html&d=DwIGaQ&c=PskvixtEUDK7wuWU-tIg6oKuGYBRbrMXk2FZvF0UfTo&r=A8IiePeQXxNn87piHJsFIGj9NGqzzZxbQ6jIL0nH8HQ&m=BQ81R13zHy3RXBQgSEYB6ibDq8qY68opCsE6EvC6ctM&s=ymaSU574LN_ghVEbW__bogW3SV96NQ45RCQTK3mlBWs&e=> ------------------------------ Message: 2 Date: Mon, 29 Jan 2018 13:14:35 +0000 From: Darafei "Komяpa" Praliaskouski <m...@komzpa.net> To: PostGIS Users Discussion <postgis-users@lists.osgeo.org> Subject: Re: [postgis-users] Spatial Intersect if condition is met then go to next step Message-ID: <CAC8Q8tKNDhOegFw=ubcqtg0iu2+kggkqkiwbxdzjs3aane2...@mail.gmail.com> Content-Type: text/plain; charset="utf-8" hi, you may employ `where exists()`: select seq from tst sq WHERE EXISTS(select from present_mapnonclipped.gde_polygons ss where ss.seq = sq.seq and ST_Intersects(ss.geom,envelope)) or exists(present_mapnonclipped.gde_polygons ss where ss.seq = sq.seq and ST_Intersects(ss.geom,envelope)) You may also want to debug your query with EXPLAIN (ANALYZE, VERBOSE, BUFFERS) before putting it into stored procedure. пн, 29 янв. 2018 г. в 14:04, Oliver Morris <oliver.mor...@halliburton.com>: > Hi, > > First post so apologies in advance if this is the wrong place for this > question – hopefully I found the right place. > > > > I have a bounding box geometry and I would like to find out if any > rows in a selection of tables are intersected, each table contains a > field (seq) which contains a property that I need to group the results > by (for this demo lets day it is days of the week). I don’t need to > know how many rows but simply require a true/false result for each > table and seq. Ideally the script as soon as it has found an > intersection would move onto the next table and not spend any further > time running intersections, I was hoping the limit 1 clause would do > this. I was also hoping that the lateral join would help me to do the > grouping but I think it still processes all the rows. > > > > Currently my script (for two example tables) looks like: > > > > CREATE OR REPLACE FUNCTION tst.f_tst_report( > > topleftx double precision, > > toplefty double precision, > > bottomrightx double precision, > > bottomrighty double precision, > > groupidarray text) > > > > RETURNS TABLE(seq character) > > LANGUAGE 'plpgsql' > > COST 100 > > VOLATILE > > ROWS 1000 > > > > AS $BODY$ > > DECLARE > > envelope geometry; > > BEGIN > > envelope := > ST_MakeEnvelope(topleftx,toplefty,bottomrightx,bottomrighty,4326); > > RETURN QUERY > > > > WITH > > cte_gde_polygons as ( > > select distinct sq.seq as seq1 from tst.seq sq > > join lateral ( > > select ss.seq from present_mapnonclipped.gde_polygons ss > > where ss.seq = sq.seq and ST_Intersects(ss.geom,envelope) > > ) > > limit 1 > > ) p on true), > > > > cte_gde_fault as ( > > select distinct sq.seq as seq1 from tst.seq sq > > join lateral ( > > select ss.seq from present_mapnonclipped.gde_fault ss > > where ss.seq = sq.seq and ST_Intersects(ss.geom,envelope) > > ) > > limit 1 > > ) p on true) > > > > select * from cte_gde_polygons > > union > > select * from cte_gde_fault > > > > END > > $BODY$; > > > > An example output across these tables for when Monday, Wednesday and > Thursday intersects with the bounding box would return: > > > > Seq > > Monday > > Wednesday > > Thursday > > > > In short - if there is no way to stop intersecting after the first > match my alternative is to dissolve all the features by seq in each > table. The only issue would be that this would need updating on any data > change. > > > > Many Thanks for your help in advance, > > > > Oliver > > > > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.osgeo.org_m > ailman_listinfo_postgis-2Dusers&d=DwIGaQ&c=PskvixtEUDK7wuWU-tIg6oKuGYB > RbrMXk2FZvF0UfTo&r=A8IiePeQXxNn87piHJsFIGj9NGqzzZxbQ6jIL0nH8HQ&m=BQ81R > 13zHy3RXBQgSEYB6ibDq8qY68opCsE6EvC6ctM&s=fdGcanVIfllgOEeIyzfZ5_ST_0uAH > 8vBN7t9IdR0NFE&e= -------------- next part -------------- An HTML attachment was scrubbed... URL: <https://urldefense.proofpoint.com/v2/url?u=http-3A__lists.osgeo.org_pipermail_postgis-2Dusers_attachments_20180129_31e89949_attachment-2D0001.html&d=DwIGaQ&c=PskvixtEUDK7wuWU-tIg6oKuGYBRbrMXk2FZvF0UfTo&r=A8IiePeQXxNn87piHJsFIGj9NGqzzZxbQ6jIL0nH8HQ&m=BQ81R13zHy3RXBQgSEYB6ibDq8qY68opCsE6EvC6ctM&s=_I0bEfa8-BvZCkQEBafghfsqbu7ffyi3RfuXRTQ6uXk&e=> ------------------------------ Subject: Digest Footer _______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.osgeo.org_mailman_listinfo_postgis-2Dusers&d=DwIGaQ&c=PskvixtEUDK7wuWU-tIg6oKuGYBRbrMXk2FZvF0UfTo&r=A8IiePeQXxNn87piHJsFIGj9NGqzzZxbQ6jIL0nH8HQ&m=BQ81R13zHy3RXBQgSEYB6ibDq8qY68opCsE6EvC6ctM&s=fdGcanVIfllgOEeIyzfZ5_ST_0uAH8vBN7t9IdR0NFE&e= ------------------------------ End of postgis-users Digest, Vol 191, Issue 7 ********************************************* _______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
