在Oracle中查询拆分后多边形的指定点坐标

Got it, you've split your original polygon 0204 into two smaller ones (1003 and 1004) and need the coordinates of the two shared vertices marked as 1 and 2. Based on the geometry data you provided, these points are the common vertices between the two split polygons. Here's a straightforward Oracle Spatial query to retrieve them:

WITH polygons AS (
    SELECT '1003' poly_id, 
           MDSYS.SDO_GEOMETRY(2003, 2400000, null, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1), MDSYS.SDO_ORDINATE_ARRAY(8464488.695278989151120185852050781250, 4444303.411238789558410644531250, 8464488.781260209158062934875488281250, 4444284.2415307201445102691650390625, 8464503.001509850844740867614746093750, 4444284.1077903099358081817626953125, 8464502.874620059505105018615722656250, 4444305.03532961010932922363281250, 8464488.695278989151120185852050781250, 4444303.411238789558410644531250)) geom
    FROM dual
    UNION ALL
    SELECT '1004' poly_id, 
           MDSYS.SDO_GEOMETRY(2003, 2400000, null, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1), MDSYS.SDO_ORDINATE_ARRAY(8464488.62343735992908477783203125, 4444319.428492070175707340240478515625, 8464488.695278989151120185852050781250, 4444303.411238789558410644531250, 8464502.874620059505105018615722656250, 4444305.03532961010932922363281250, 8464502.7877615205943584442138671875, 4444319.36063767969608306884765625, 8464488.62343735992908477783203125, 4444319.428492070175707340240478515625)) geom
    FROM dual
),
shared_edge AS (
    SELECT SDO_GEOM.SDO_INTERSECTION(a.geom, b.geom, 0.0001) AS edge_geom
    FROM polygons a
    JOIN polygons b ON a.poly_id < b.poly_id
),
ordinate_pairs AS (
    SELECT 
        rownum AS point_id,
        vertex.x AS x_coord,
        vertex.y AS y_coord
    FROM shared_edge,
         TABLE(SDO_UTIL.GET_VERTICES(edge_geom)) vertex
)
SELECT point_id, x_coord, y_coord
FROM ordinate_pairs;

How this works: #

• polygons CTE: We first define the two split polygons (1003 and 1004) using your provided SDO_GEOMETRY data.
• shared_edge CTE: The SDO_GEOM.SDO_INTERSECTION function computes the geometric overlap between the two polygons. Since they're adjacent, this returns a line string representing their shared edge. The 0.0001 parameter is the tolerance value—adjust it if you need higher/lower precision matching.
• ordinate_pairs CTE: SDO_UTIL.GET_VERTICES extracts all vertices from the shared edge line string, and TABLE() converts this into individual rows. We use rownum to assign a unique ID (1 and 2) to each vertex, matching your marked points.

Expected Output: #

内容的提问来源于stack exchange，提问作者Meqenaneri Vacharq