Creating a Land Parcel Management System with Topology In Oracle Spatial

 Creating a Land Parcel Management System with Topology In Oracle Spatial

First, what is Topology In Oracle Spatial?

In below article, you can find good information about Topology In Oracle spatial

https://lidagholizadeh.blogspot.com/2024/05/topology-in-oracle-spatial.html

We'll create a topology to manage land parcels in a city. This topology ensures:

• Parcels (polygons) don’t overlap.
• Roads (lines) connect without gaps.
• Landmarks (points) are inside parcels.

1. Prerequisites

Ensure Oracle Spatial is installed and your database schema has the required privileges.

2. Create a Topology Workspace

A topology workspace acts as a container for managing spatial relationships.

BEGIN
  SDO_TOPO.CREATE_TOPOLOGY(
    'CITY_PARCEL_TOPOLOGY',  -- Topology name
    'MY_SCHEMA',             -- Schema name
    0.001,                   -- Tolerance for operations
    'GEOMETRY_COLUMN');      -- Optional geometry column table
END;

3. Define Topology Layers

Topology layers correspond to spatial feature types (e.g., points, lines, polygons).

BEGIN
  SDO_TOPO.ADD_TOPOLOGY_LAYER(
    'CITY_PARCEL_TOPOLOGY', -- Topology name
    'PARCELS',              -- Table for parcels (polygons)
    'GEOMETRY',             -- Geometry column
    'SDO_TOPO_GEOMETRY',    -- Data type
    'SDO_POLYGON_LAYER'     -- Layer type
  );
END;

BEGIN
  SDO_TOPO.ADD_TOPOLOGY_LAYER(
    'CITY_PARCEL_TOPOLOGY',
    'ROADS', 
    'GEOMETRY',
    'SDO_TOPO_GEOMETRY',
    'SDO_LINE_LAYER'
  );
END;

BEGIN
  SDO_TOPO.ADD_TOPOLOGY_LAYER(
    'CITY_PARCEL_TOPOLOGY',
    'LANDMARKS', 
    'GEOMETRY',
    'SDO_TOPO_GEOMETRY',
    'SDO_POINT_LAYER'
  );
END;

4. Create and Populate Spatial Tables

Parcels Table (Polygons):

CREATE TABLE PARCELS (
  PARCEL_ID NUMBER PRIMARY KEY,
  GEOMETRY SDO_GEOMETRY
);

INSERT INTO PARCELS VALUES (
  1,
  SDO_GEOMETRY(2003, 4326, NULL, 
               SDO_ELEM_INFO_ARRAY(1, 1003, 1),
               SDO_ORDINATE_ARRAY(10, 10, 20, 10, 20, 20, 10, 20, 10, 10))
);

Roads Table (Lines):

CREATE TABLE ROADS (
  ROAD_ID NUMBER PRIMARY KEY,
  GEOMETRY SDO_GEOMETRY
);

INSERT INTO ROADS VALUES (
  1,
  SDO_GEOMETRY(2002, 4326, NULL, 
               SDO_ELEM_INFO_ARRAY(1, 2, 1),
               SDO_ORDINATE_ARRAY(10, 15, 20, 15))
);

Landmarks Table (Points):

CREATE TABLE LANDMARKS (
  LANDMARK_ID NUMBER PRIMARY KEY,
  GEOMETRY SDO_GEOMETRY
);

INSERT INTO LANDMARKS VALUES (
  1,
  SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(15, 15, NULL), NULL, NULL)
);

5. Build the Topology

Compile the spatial data into the topology workspace.

BEGIN
  SDO_TOPO.LOAD_TOPOLOGY_LAYER(
    'CITY_PARCEL_TOPOLOGY',
    'PARCELS'
  );
END;

BEGIN
  SDO_TOPO.LOAD_TOPOLOGY_LAYER(
    'CITY_PARCEL_TOPOLOGY',
    'ROADS'
  );
END;

BEGIN
  SDO_TOPO.LOAD_TOPOLOGY_LAYER(
    'CITY_PARCEL_TOPOLOGY',
    'LANDMARKS'
  );
END;

6. Perform Topological Operations

some common spatial queries:

Check if Roads Intersect Parcels:

SELECT r.ROAD_ID, p.PARCEL_ID
FROM ROADS r, PARCELS p
WHERE SDO_RELATE(r.GEOMETRY, p.GEOMETRY, 'MASK=ANYINTERACT') = 'TRUE';

Find Landmarks Inside Parcels:

SELECT lm.LANDMARK_ID, p.PARCEL_ID
FROM LANDMARKS lm, PARCELS p
WHERE SDO_INSIDE(lm.GEOMETRY, p.GEOMETRY) = 'TRUE';

You can use Oracle Map Viewer, ArcGIS, or QGIS to display and verify spatial relationships visually.