Insert a Point whose longitude is over date line at Oracle

Question

I am using Oracle Spatial

table structure

CREATE TABLE "MINTAKA"."GRID_POINT_BOUNDARY"
      (
        "BOUNDARY_ID" NUMBER(19,0),
        "BOUNDARY_DEF" MDSYS.SDO_GEOMETRY NOT NULL ENABLE,
        CONSTRAINT "GRID_POINT_BOUNDARY_PK" PRIMARY KEY ("BOUNDARY_ID") ENABLE
     )

configure the longitude and latitude range

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES ('GRID_POINT_BOUNDARY',
        'BOUNDARY_DEF',
        SDO_DIM_ARRAY( -- in our case, we only have 2 dimensions, latitude and longitude
                SDO_DIM_ELEMENT('Longitude', -180, 180, 0.0001),
                SDO_DIM_ELEMENT('Latitude',   -90,  90, 0.0001)
            ),
        4326 
       );

create the index at the spatial column

CREATE INDEX GRID_POINT_BOUNDARY_DEF_IDX ON MINTAKA.GRID_POINT_BOUNDARY (BOUNDARY_DEF) INDEXTYPE IS MDSYS.SPATIAL_INDEX

so why I still can successfully register a point whose longtitue is over 180 at the Oracle db??

INSERT INTO GRID_POINT_BOUNDARY (BOUNDARY_ID, BOUNDARY_DEF)
    VALUES (1234, MDSYS.SDO_GEOMETRY(2001, 4326, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1, 1), MDSYS.SDO_ORDINATE_ARRAY(185.0, -42.001))

Answer 1

Contrary to other datatypes (dates, numbers, ...), Oracle does not automatically verify that the spatial data is correct (except of course for obvious syntax errors, or if shapes are inserted with an SRID that does not match that of the spatial index).

In particular, it does not verify that the shapes are structurally or geometrically correct according to the OGC rules (polygon orientation, etc) or that the coordinates are consistent with the coordinate system used. This is because of the high cost of doing those validations, and the fact that the spatial data you use does not appear out of the blue: it is coming from some GIS tool, that is expected to do all validation at the point of capture.

But it does offer a function SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT() you can use to verify the geometric shapes. If you want, you can add a trigger onto your table(s) that will verify that any insertion or update produces valid spatial data (= the INSERT will fail if it is not valid, allowing your application to take the proper action).

In this particular case, the fact that some points are outside the bounds of a geodetic coordinate system ((-180,180), (-90,90)) is an error.