[postgis-users] Spatial Intersect if condition is met then go to next step

[Darafei "Komяpa" Praliaskouski]

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://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20180129/31e89949/attachment.html>