AnsweredAssumed Answered

Optimize SQL query using ST_GEOMETRY

Question asked by jeff.pace on Aug 8, 2018
Latest reply on Aug 9, 2018 by bixb0012

Looking for help to optimize a SQL query in ORACLE.  What this does is

 

Take two polygon layers, overlay/intersect them, and where they intersect, summarize the attributes of the overlay to a aggregate list if the area of intersection is more than 15% of the source poly.  

 

SELECT

hashid,

--this is an aggregate function that sorts and groups overlayed floodzones by parcel
LISTAGG (TO_CHAR (fld_zone), '','') WITHIN GROUP (ORDER BY fld_zone) FLOODZONE
FROM MCGIS.FEMA_2014_FLOOD_ZONES e, parcel.parcels_mv p
WHERE

--get matching polygons

st_intersects (p.shape, e.shape) = 1
AND

--use polygon if more than 15% overlay

(st_area (st_intersection (p.shape, e.shape))) / st_area (p.shape) >.15

--summarize by hashid
GROUP BY hashid

 

It works perfectly, but takes like 3 hours.  i have 30 of these to do every night.  Anything i can do to speed it up? Both layers have spatial indexes, and there are attribute indexes on both HASHID and FLD_ZONE fields.

 

This produces results like

 

HASHID    FLOODZONE

124345      AE,X

124346      X

124347      AE,B,X

 

instead of 

 

HASHID    FLOODZONE

124345      AE

124345      X

124346      X

124347      AE

124347      B

124347      X

Outcomes