[postgis-users] [EXTERNAL] postgis-users Digest, Vol 191, Issue 7
Oliver Morris
Oliver.Morris at halliburton.com
Wed Jan 31 03:51:47 PST 2018
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
*********************************************
More information about the postgis-users
mailing list