SDO_GEOM.SDO_INTERSECTION Between Line and Polygon – Oracle Spatial Geometry

geometryoraclespatial

I'm confused as to what exactly the SDO_GEOM.SDO_INTERSECTION returns in Oracle. Documentation is here, but only gives examples of two intersecting polygons.

What if I have many lines, some of them are contained fully within the polygon and some intersect the polygon. Assume that I need the geometry of any line fully contained within the polygon (i.e. same as SDO_CONTAINS) AND the geometry of those parts of intersecting lines, which are inside the polygon.

Example image below. Assume I only need the geometries of the red lines, but not the green ones. Will SDO_INTERSECTION return all of the red ones or only those red ones, which also go outside of the polygon and have some green bits?

Best Answer

Someone answered this question for me and it is as I suspected. SDO_INTERSECTION will return any lines fully contained within a polygon, as well as those parts of intersecting lines, which are within the polygon. In other words, it will return the geometry of everything in red in the example image.

Related Solutions

Mysql – the SQL command to get 20 closest points from some center location

If a SPATIAL INDEX does not work for you, see http://mysql.rjweb.org/doc.php/latlng It is a description of how to efficiently "find the nearest" on the globe.

Sql-server – the fastest way to compare polygon geometry data

Building on the comments provided by @John M, I've contrived the following example of how to build a geometric index, and then query the index using the STEquals function:

USE tempdb;

DROP TABLE dbo.G1;
DROP TABLE dbo.G2;

CREATE TABLE dbo.G1
(
    G1ID INT NOT NULL CONSTRAINT PK_G1 PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , Shape1 GEOMETRY
);

CREATE SPATIAL INDEX SIndx_G1_Shape1 
ON dbo.G1(Shape1)
WITH (BOUNDING_BOX = (0, 0, 9999, 9999));

CREATE TABLE dbo.G2
(
    G2ID INT NOT NULL CONSTRAINT PK_G2 PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , Shape2 GEOMETRY
);

CREATE SPATIAL INDEX SIndx_G2_Shape2 
ON dbo.G2(Shape2)
WITH (BOUNDING_BOX = (0, 0, 9999, 9999));

DECLARE @c NVARCHAR(50);
SET @c = '';
DECLARE @LoopCount INT;
SET @LoopCount = 0;
WHILE @LoopCount < 5000
BEGIN

    SET @c = 'LINESTRING(2 ' + CONVERT(NVARCHAR(10), @LoopCount) + ', ' 
        + CONVERT(NVARCHAR(10), @LoopCount) + ' 2, 4 ' 
        + CONVERT(NVARCHAR(10), @LoopCount) + ', 2 4, 2 2)';
    IF @LoopCount < 10 PRINT (@c);
    INSERT INTO dbo.G1 (Shape1)
    SELECT geometry::Parse(@c);

    SET @c = 'LINESTRING(' + CONVERT(NVARCHAR(10), @LoopCount) + ' ' 
        + CONVERT(NVARCHAR(10), @LoopCount) + ', ' 
        + CONVERT(NVARCHAR(10), @LoopCount) + ' 2, ' 
        + CONVERT(NVARCHAR(10), @LoopCount + 2) + ' ' 
        + CONVERT(NVARCHAR(10), @LoopCount) + ', 2 4, 2 2)';
    IF @LoopCount < 10 PRINT (@c);
    INSERT INTO dbo.G2 (Shape2)
    SELECT geometry::Parse(@c);

    SET @LoopCount = @LoopCount + 1;
END

SELECT TOP(10) *
FROM dbo.G1;
SELECT TOP(10) *
FROM dbo.G2;

SELECT *
FROM dbo.G1
    INNER JOIN dbo.G2 ON G1.Shape1.STEquals(G2.Shape2) = 1;

Best Answer

Related Solutions

Mysql – the SQL command to get 20 closest points from some center location

Sql-server – the fastest way to compare polygon geometry data

Related Question