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.
--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
--get matching polygons
st_intersects (p.shape, e.shape) = 1
--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