<div dir="ltr">hi,<div><br></div><div>you may employ `where exists()`:</div><div><br></div><div>select seq from tst sq</div><div> WHERE EXISTS(select from present_mapnonclipped.gde_polygons ss</div><div>where ss.seq = sq.seq and ST_Intersects(ss.geom,envelope)) or exists(present_mapnonclipped.gde_polygons ss</div><div>where ss.seq = sq.seq and ST_Intersects(ss.geom,envelope))</div><div><br></div><div>You may also want to debug your query with EXPLAIN (ANALYZE, VERBOSE, BUFFERS) before putting it into stored procedure.</div></div><br><div class="gmail_quote"><div dir="ltr">пн, 29 янв. 2018 г. в 14:04, Oliver Morris <<a href="mailto:Oliver.Morris@halliburton.com">Oliver.Morris@halliburton.com</a>>:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div lang="EN-GB" link="blue" vlink="purple">
<div class="m_3432239626756810785WordSection1">
<p class="MsoNormal">Hi,<u></u><u></u></p>
<p class="MsoNormal">First post so apologies in advance if this is the wrong place for this question – hopefully I found the right place.<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">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.
<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">Currently my script (for two example tables) looks like:<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">CREATE OR REPLACE FUNCTION tst.f_tst_report(<u></u><u></u></p>
<p class="MsoNormal">topleftx double precision,<u></u><u></u></p>
<p class="MsoNormal">toplefty double precision,<u></u><u></u></p>
<p class="MsoNormal">bottomrightx double precision,<u></u><u></u></p>
<p class="MsoNormal">bottomrighty double precision,<u></u><u></u></p>
<p class="MsoNormal">groupidarray text)<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">RETURNS TABLE(seq character)<u></u><u></u></p>
<p class="MsoNormal">LANGUAGE 'plpgsql'<u></u><u></u></p>
<p class="MsoNormal">COST 100<u></u><u></u></p>
<p class="MsoNormal">VOLATILE <u></u><u></u></p>
<p class="MsoNormal">ROWS 1000<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">AS $BODY$<u></u><u></u></p>
<p class="MsoNormal">DECLARE<u></u><u></u></p>
<p class="MsoNormal">envelope geometry;<u></u><u></u></p>
<p class="MsoNormal">BEGIN<u></u><u></u></p>
<p class="MsoNormal">envelope := ST_MakeEnvelope(topleftx,toplefty,bottomrightx,bottomrighty,4326);<u></u><u></u></p>
<p class="MsoNormal">RETURN QUERY<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">WITH<u></u><u></u></p>
<p class="MsoNormal">cte_gde_polygons as (<u></u><u></u></p>
<p class="MsoNormal">select distinct sq.seq as seq1 from tst.seq sq<u></u><u></u></p>
<p class="MsoNormal">join lateral (<u></u><u></u></p>
<p class="MsoNormal">select ss.seq from present_mapnonclipped.gde_polygons ss<u></u><u></u></p>
<p class="MsoNormal">where ss.seq = sq.seq and ST_Intersects(ss.geom,envelope)<u></u><u></u></p>
<p class="MsoNormal">)<u></u><u></u></p>
<p class="MsoNormal">limit 1<u></u><u></u></p>
<p class="MsoNormal">) p on true),<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">cte_gde_fault as (<u></u><u></u></p>
<p class="MsoNormal">select distinct sq.seq as seq1 from tst.seq sq<u></u><u></u></p>
<p class="MsoNormal">join lateral (<u></u><u></u></p>
<p class="MsoNormal">select ss.seq from present_mapnonclipped.gde_fault ss<u></u><u></u></p>
<p class="MsoNormal">where ss.seq = sq.seq and ST_Intersects(ss.geom,envelope)<u></u><u></u></p>
<p class="MsoNormal">)<u></u><u></u></p>
<p class="MsoNormal">limit 1<u></u><u></u></p>
<p class="MsoNormal">) p on true)<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">select * from cte_gde_polygons<u></u><u></u></p>
<p class="MsoNormal">union<u></u><u></u></p>
<p class="MsoNormal">select * from cte_gde_fault<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">END<u></u><u></u></p>
<p class="MsoNormal">$BODY$;<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">An example output across these tables for when Monday, Wednesday and Thursday intersects with the bounding box would return:<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">Seq<u></u><u></u></p>
<p class="MsoNormal">Monday<u></u><u></u></p>
<p class="MsoNormal">Wednesday<u></u><u></u></p>
<p class="MsoNormal">Thursday<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">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.<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">Many Thanks for your help in advance,<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">Oliver<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
</div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>