Search code examples
sqloracle-databaseoracle11goracle-spatial

Determining which polygon contains the majority of a line - Oracle Spatial

I have an oracle database (11g spatial) that includes a series of area polygons and water mains. I'm trying to attribute each of these mains to the area in which it is contained and for the most part this is straightforward enough (using the SDO_CONTAINS function) but I'm not sure how to deal with mains that straddle multiple polygons due to errors in digitisation.

In cases like this what I'd ideally like to do is attribute a main to an area polygon if the majority of it's length (>50%) is contained within onit. I know that I can use the SDO_RELATE function to determine every polygon that any given main interacts with, but I don't know how to then go about determining how much of it's length is contained within each area.

Solution

  • The principle is like this:

    1. Correlate mains and areas. Assuming you have many mains and many areas, the most efficient approach is to use SDO_JOIN
    2. For each couple (main/area) returned, compute their intersection (SDO_GEM.SDO_INTERSECTION) and measure the length of that intersection (SDO_GEOM.SDO_LENGTH).
    3. From those results, retain the area for each main where the length is the maximum

    If you want a full SQL example, allow me a bit of time to write that using sample data.