estimate size of geometry

https://www.oracle.com/technetwork/database/enterprise-edition/spatial-perf-twp-130138.pdf

select sum(k) / count(*) from (
   select (SDO_UTIL.GETNUMVERTICES(geometry)*2*7 + 104) as k from my_spatial
)

find invalid geometries

https://docs.oracle.com/database/121/SPATL/sdo_geom-validate_geometry_with_context.htm#SPATL1130

select * from (
SELECT s.object_id, s.geometry, SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(s.geometry, 0.00000000000000001) res
   FROM nc_spatial s where s.geometry.SDO_GTYPE = 2003
) where res like '13348%'

ORA-13348 is for unclosed polygons

ORA-13019 is for coordinates that out of bound

ORA-13367 is for wrong orientation of interior rings

ORA-13356 is for redundant repeated points

All errors: https://www.oraexcel.com/database-oracle-12cR2--

fins invalid points

If lat = 91 then is is interpreted as 89 (92 -> 88, 180-> 0, 181 - >1). So SDO_UTIL.TO_WKTGEOMETRY return corrected geometry, SDO_INSIDE interprets as corrected. Although VALIDATE_GEOMETRY_WITH_CONTEXT, VALIDATE_GEOMETRY will not find the problem. But this geometries can be found using the script:

select 
  object_id,   
  min(x) minx,  
  max(x) maxx, 
  min(y) miny, 
  max(y) maxy 
FROM (
    SELECT c.object_id, t.x x, t.y y
    FROM   nc_spatial c, TABLE(SDO_UTIL.GETVERTICES(c.geometry)) t
) s
group by s.object_id
having  min(x) < -180 or max(x) > 180 or min(y) < -90 or max(y) > 90

make geoms valid?

SDO_UTIL.RECTIFY_GEOMETRY https://blogs.oracle.com/oraclespatial/post/validating-and-rectifying-spatial-data-in-the-oracle-database

create index

CREATE INDEX myindexnme ON mytable(GEOMETRY) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS('TABLESPACE=NC_INDEXES');

drop index

DROP INDEX myindexname FORCE;

gorshkov-leonid/oracle-spatial-notes.md