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
- SpatiaLite 2.4.0
- Customizing config.xml in the ESRI FlexViewer
- ESRI FlexViewer and Error 1065
- SpatiaLite and Smart Phones
- GeoDC Kickoff…
Adam Estrada Recommends
- Generic Host Process for Win32 Services and internet stops (MtaraM)
- Jailbreaking and unlocking your iphone for T-mobile (tretre)
- Replace IPhone Carrier T-Mobile and AT&T with SKYPE 2010 (tretre)
Adam,
I found your site and this post after much head scratching trying to work with the USER_SDO_GEOM_METADATA table. The issue I have is trying to define the extent, or bounding box, for datasets that I am loading into Oracle 10g. I am using FME with a standardised workflow to load the data but what I can’t get right is how to define the spatial extent automatically for each dataset. Some of the datasets cover the whole of Scotland while others might just be a single county or town. If I use an extent big enough to hold any dataset I am likely to use then I need a bounding box for Scotland. However, within ArcGIS the “zoom to layer” command then always zooms out to that extent even if the dataset only covers a small area.
Does the shp2sdo automatically calculate the correct bounding box extent? Looking at your other post (http://www.adamestrada.com/2009/06/14/how-to-load-a-shapefile-in-to-oracle-spatial/) it appears that you have to define the extent yourself as parameters for the command.
Any ideas?
Regards
Ross
Ross,
I am pretty sure that shp2sdo grabs the bounding box of the input shapefile automatically. I am off on another project now and don’t have an Oracle instance to test against. Otherwise, I would love to help you! I suggest running shp2sdo on a couple data sets to see if you get the results you are looking for.
Adam