I have a table of MLRA zones in Iowa, and wish to provide a single point and determine which MLRA contains the point.
I have ingested this geojson into a table IOWA_MLRA
Iowa_MLRA_WGS84.txt (GEOJSON)
CREATE TABLE "IOWA_MLRA" (
"ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
"TYPE" VARCHAR2(50),
"GEOMETRY" CLOB,
"PROPERTIES_LRRSYM" VARCHAR2(1),
"PROPERTIES_MLRA_ID" NUMBER,
"PROPERTIES_LRR_NAME" VARCHAR2(255),
"PROPERTIES_MLRARSYM" VARCHAR2(50),
"PROPERTIES_MLRA_NAME" VARCHAR2(255),
"GEOMETRY_SDO" "SDO_GEOMETRY",
PRIMARY KEY ("ID") USING INDEX ENABLE
)
VARRAY "GEOMETRY_SDO"."SDO_ELEM_INFO" STORE AS SECUREFILE LOB
VARRAY "GEOMETRY_SDO"."SDO_ORDINATES" STORE AS SECUREFILE LOB;
-- populate geometry_sdo
update iowa_mlra m
set m.geometry_sdo = mdsys.sdo_util.from_geojson(m.geometry)
where m.geometry is not null;
-- spatial index
-- note: i actually used apex_spatial to create the index,
-- which i believe is a wrapper for creating the index and inserting the metadata
-- https://docs.oracle.com/en/database/oracle/apex/24.1/aeapi/INSERT_GEOM_METADATA-Procedure.html
create index "IOWA_MLRA_SX"
on "IOWA_MLRA" ( "GEOMETRY_SDO" )
indextype is mdsys.spatial_index_v2;
-- insert metadata for index
insert into user_sdo_geom_metadata (
table_name,
column_name,
diminfo,
srid
) values (
'iowa_mlra',
'geometry_sdo',
sdo_dim_array(
sdo_dim_element('x', -97.0, -90.0, 0.00001), -- Longitude (slightly expanded for Iowa)
sdo_dim_element('Y', 40.0, 44.0, 0.00001) -- Latitude (slightly expanded for Iowa)
),
4326 -- SRID for WGS84
);
-- query a specific point
select m.properties_mlra_name, m.properties_mlrarsym, m.properties_lrr_name
from wksp_isuini.iowa_mlra m
where sdo_contains(
m.geometry_sdo,
mdsys.sdo_geometry(
2001, -- Point type
4326, -- WGS84 SRID
mdsys.sdo_point_type(-92.024034880625, 41.86820437625, null),
null,
null
)
) = 'TRUE';
The output of this last query is 2 MLRAs. I've checked the geometry, and the shapes don't overlap. Why would I be getting two results?
PROPERTIES_MLRARSYM PROPERTIES_MLRA_NAME
104 Eastern Iowa and...
105 Upper Mississ..
This issue is causing the flagged point to show like it's included in this red zone
