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

[Oliver Morris]

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: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20180129/e90e90d6/attachment.html>