[postgis-users] [EXTERNAL] postgis-users Digest, Vol 191, Issue 7

Wed Jan 31 05:33:16 PST 2018

exists() is optimized in a manner similar to join, so there's usually no
penalty using it, when it's contextually a right metaphor.

ср, 31 янв. 2018 г. в 14:51, Oliver Morris <Oliver.Morris at halliburton.com>:

> 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-bounces at lists.osgeo.org] On
> Behalf Of postgis-users-request at lists.osgeo.org
> Sent: 29 January 2018 20:00
> To: postgis-users at 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 at 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-request at lists.osgeo.org
>
> You can reach the person managing the list at
>         postgis-users-owner at 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.Morris at halliburton.com>
> To: "postgis-users at lists.osgeo.org" <postgis-users at lists.osgeo.org>
> Subject: [postgis-users] Spatial Intersect if condition is met then go
>         to      next step
> Message-ID:
>         <
> 93FB8A2740EFB64D82CE13D909334CC0036C54DE at 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 <me at komzpa.net>
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] Spatial Intersect if condition is met
>         then go to next step
> Message-ID:
>         <CAC8Q8tKNDhOegFw=
> UBCQtG0iU2+KGgkQkiwbXDzjS3aane2u0w at 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.Morris at 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 at 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 at 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 at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20180131/5692c7a1/attachment.html>