Oracle Spatial Examples

Get project information (note table alias required).
select c.[GEOMETRY FIELD].sdo_srid
from [TABLE] c;

Get the geometry type
select c.[GEOMETRY_FIELD].sdo_gtype
from [TABLE_NAME] c;

Interpret results with:
*00 UNKNOWN_GEOMETRY
*01 POINT
*02 LINE or CURVE
*03 POLYGON
*04 COLLECTION
*05 MULTIPOINT
*06 MULTILINE or MULTICURVE
*07 MULTIPOLYGON

Ensure table is registered in Oracle Spatial

select *
from mdsys.sdo_geom_metadata_table;

Get spatial tolerance of table
SELECT sdo_dimname, sdo_tolerance
FROM TABLE(
select DIMINFO
from user_sdo_geom_metadata
WHERE table_name = ‘[TABLE_NAME]’
) ;

Create point geometry from x/y values
select
mdsys.SDO_GEOMETRY(
2001, — point data
[SRID],
mdsys.SDO_point_type([X Ordinate], [Y Ordinate], NULL),
NULL,
NULL )
from dual;

Create Spatial Index
CREATE INDEX “[Index Name]” ON “[Table Name]” (“[Geometry Field]”)
INDEXTYPE IS “MDSYS”.”SPATIAL_INDEX”;

Add Geometry Metadata

Insert into MDSYS.USER_SDO_GEOM_METADATA (TABLE_NAME,COLUMN_NAME,DIMINFO,SRID) values (‘[Table_Name]’,'[Geometry_Field_Name]’,MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT(NULL,-180,180,0.001),MDSYS.SDO_DIM_ELEMENT(NULL,-90,90,0.001)),4269);

Determine Spatial Relationship Between Geometries
SELECT [ID_Field]
, sdo_geom.relate(
a.[Geometry_Field]
, ‘determine’
, b.[Geometry_Field]
, [Tolerance]
)
FROM
[Table_Name_1] a, [Table_Name_2] b;

Leave a Reply

Your email address will not be published.

− 3 = 1