So, apparently all the data in our database is missing the 5th coordinate of the polygon to close the object. Everything comes back as 0 when I do a
select bd.title, bd.chart,
SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT
(bd.sdo_window, 0.005)
from boundaries_data bd;
For example,
Montauk Harbor
--Chart 13209
--MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(41.06444,-71.94139,41.06444,-71.92528,41.08583,-71.92528,41.08583,-71.94139))
select bd.sdo_window.get_wkt() from boundaries_data bd where bd.chart like 13209;
POLYGON ((41.06444 -71.94139, 41.06444 -71.92528, 41.08583 -71.92528, 41.08583 -71.94139))
POLYGON ((40.96656 -72.28611, 40.96656 -71.74944, 41.25825 -71.74944, 41.25825 -72.28625))
I've been trying to figure out how to add a 5th ordinate that is equal to the 1st ordinate and I can't figure out how to do that. I've tried to manually do that and it doesn't recognize that it has been changed.
I want to do something like that, but something is not correct. Any help is appreciated. Thanks!!
************************
CREATE OR REPLACE FUNCTION update_5th_ordinate(geom IN SDO_GEOMETRY)
RETURN SDO_GEOMETRY DETERMINISTIC IS
new_geom SDO_GEOMETRY := geom;
BEGIN
IF exists (select geom.sdo_ordinates(5) from cp_ocsprd.boundaries_data) THEN
new_geom.sdo_ordinates(5) := geom.sdo_ordinates(1);
ELSE
new_geom.sdo_ordinates(5) := geom.sdo_ordinates(1);
END IF;
RETURN new_geom;
END update_5th_ordinate;
UPDATE cp_ocsprd.boundaries_data bd
SET bd.sdo_window = update_5th_ordinate(bd.sdo_window);
**************
After all updated, then what do I do? just drop spatial index and then recreate it? I've never done that before. Thanks!