Oracle Spatial and USER_SDO_GEOM_METADATA

Oracle is a very powerful database but at the same time VERY difficult to use. I’m guessing that’s why Oracle DBA’s get all the money and women…hehe. Oracle Spatial is also an incredibly powerful tool, if you know how to use it. I’ve talked before about how to get your spatial data in to the Database, but what happens if the database doesn’t like the data? See, in Oracle you must have an SDO_GEOMETRY type to actually perform spatial queries on your data. What they don’t make so apparent is the fact that you must also Insert a record in to the USER_SDO_GEOM_METADATA table (they call it a view) and also create a Spatial Index to perform the query. SHP2SDO will create the Update/Insert statement for USER_SDO_GEOM_METADATA but if you already Inserted the data and don’t have the original you can use the following to Insert in to your table

1
2
3
4
5
6
7
8
9

INSERT INTO USER_SDO_GEOM_METADATA
VALUES(
'EVENTS','GEOM',
    MDSYS.SDO_DIM_ARRAY(   
    MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.000000005),
    MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.000000005)
     ),
  4326   -- SRID (reserved for future Spatial releases)
);

Once you’ve Inserted that record you can look at it using the following Query:

1
2

 
SELECT * FROM USER_SDO_GEOM_METADATA

The Update query is simple…

1
2
3
4
5
6
7

UPDATE EVENTS S set S.GEOM.SDO_SRID = 4326
 
UPDATE USER_SDO_GEOM_METADATA SET SRID = 4326 WHERE TABLE_NAME='EVENTS'
 
UPDATE USER_SDO_GEOM_METADATA SET DIMINFO = 
MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-180,180,0.000000005),
MDSYS.SDO_DIM_ELEMENT('Y',-90,90,0.000000005)) WHERE TABLE_NAME = 'EVENTS'

If you want to make sure your geometries are valid, you may use Oracle’s ST_IsValid() command.

1
2

 
SELECT s.GEOM.st_isvalid() FROM EVENTS s

Finally, do you want to generate a GEOM column from latitude/longitude fields?

1
2
3
4
5
6

UPDATE EVENTS
SET GEOM = MDSYS.SDO_GEOMETRY(
2001, -- 3-dimensional point or use 2001 for 2-dimensional point
NULL,
MDSYS.SDO_POINT_TYPE(EVENTS.LON, EVENTS.LAT, NULL), -- X,Y,Z
NULL, NULL );

Have fun!

More from Adam Estrada

• GeoNames and GeoRSS
• Microsoft SQL Server 2008 Spatial Examples
• ESRI Flex Training
• Washington DC Mapping Party
• SpatiaLite and Smart Phones

Adam Estrada Recommends

• Adam – The Indian iPad killer (MtaraM)
• Twitter Tools and Scripts (MtaraM)
• Technology and its limits (MtaraM)