<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" class="">The first GIS system I ever used had an “intersection area” function that was distinct from, and faster than, the full-on “intersection” function that output a new geometry. I wonder if there is some possibility there…<div class=""><br class=""></div><div class="">P<br class=""><div><br class=""><blockquote type="cite" class=""><div class="">On Oct 23, 2018, at 6:01 PM, Darafei Komяpa Praliaskouski <<a href="mailto:me@komzpa.net" class="">me@komzpa.net</a>> wrote:</div><br class="Apple-interchange-newline"><div class=""><div dir="ltr" class="">Hi,<div class=""><br class=""></div><div class="">I have two polygonal layers to intersect and need to measure area of intersections.</div><div class=""><br class=""></div><div class="">ST_Area(ST_Intersection(a.geom, b.geom)) works fine, but is rather slow. Typical polygon on first side has <10 edges, on second 200..1000 edges.</div><div class=""><br class=""></div><div class="">What are my options to make it faster? Any ideas, links, experiences to share are welcome.</div><div class=""><br class=""></div><div class="">For starters, this seems to work faster than vanilla ST_Intersection in my case:</div><div class=""><pre style="font-family:"Courier New"" class=""><span style="color:rgb(0,0,128);font-weight:bold" class="">create or replace function </span><span style="font-style:italic" class="">clipped_st_intersection</span>(geom1 <span style="color:rgb(0,0,128);font-weight:bold" class="">geometry</span>, geom2 <span style="color:rgb(0,0,128);font-weight:bold" class="">geometry</span>)<br class=""> <span style="color:rgb(0,0,128);font-weight:bold" class="">returns geometry<br class=""></span><span style="color:rgb(0,0,128);font-weight:bold" class="">as </span><span style="color:rgb(0,128,0);font-weight:bold" class="">$$</span><span style="color:rgb(0,0,128);font-weight:bold" class="">select </span><span style="font-style:italic" class="">ST_Intersection</span>(<br class=""> <span style="color:rgb(0,0,128);font-weight:bold" class="">case<br class=""></span><span style="color:rgb(0,0,128);font-weight:bold" class=""> when </span><span style="font-style:italic" class="">ST_MemSize</span>(geom1) > <span style="color:rgb(0,0,255)" class="">160 </span><span style="color:rgb(0,0,128);font-weight:bold" class="">then </span><span style="font-style:italic" class="">ST_ClipByBox2D</span>(geom1, geom2)<br class=""> <span style="color:rgb(0,0,128);font-weight:bold" class="">else </span>geom1 <span style="color:rgb(0,0,128);font-weight:bold" class="">end</span>, <span style="color:rgb(0,0,128);font-weight:bold" class="">case<br class=""></span><span style="color:rgb(0,0,128);font-weight:bold" class=""> when </span><span style="font-style:italic" class="">ST_MemSize</span>(geom2) > <span style="color:rgb(0,0,255)" class="">160 </span><span style="color:rgb(0,0,128);font-weight:bold" class="">then </span><span style="font-style:italic" class="">ST_ClipByBox2D</span>(geom2, geom1)<br class=""> <span style="color:rgb(0,0,128);font-weight:bold" class="">else </span>geom2 <span style="color:rgb(0,0,128);font-weight:bold" class="">end</span>)<span style="color:rgb(0,128,0);font-weight:bold" class="">$$<br class=""></span><span style="color:rgb(0,0,128);font-weight:bold" class="">language sql<br class=""></span><span style="color:rgb(0,0,128);font-weight:bold" class="">immutable<br class=""></span><span style="color:rgb(0,0,128);font-weight:bold" class="">strict<br class=""></span><span style="color:rgb(0,0,128);font-weight:bold" class="">parallel safe</span>;</pre></div></div>-- <br class=""><div dir="ltr" class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr" class="">Darafei Praliaskouski<br class="">Support me: <a href="http://patreon.com/komzpa" class="">http://patreon.com/komzpa</a></div></div>
_______________________________________________<br class="">postgis-devel mailing list<br class=""><a href="mailto:postgis-devel@lists.osgeo.org" class="">postgis-devel@lists.osgeo.org</a><br class="">https://lists.osgeo.org/mailman/listinfo/postgis-devel</div></blockquote></div><br class=""></div></body></html>