fixing the geometric metadata and spatial indexes

When storing spatial data into Oracle, there are a few steps one needs to complete (as shown here too):

• insert the data (obviously)
• update the USER_SDO_GEOM_METADATA table. This table specifies for each column the bounding box and the SRID (coordinate system).
• create a spatial index I have created a script to this automatically for me, once all tables are filled with their data (e.g. after import or after using FME to import your data). [sourcecode language="sql"] set serverout on DECLARE schema_orig varchar2(100) := upper('&user_orig'); CURSOR ctab is select TABLE_NAME from sys.dba_tables where owner = schema_orig; tn sys.dba_tab_columns.TABLE_NAME%TYPE ; CURSOR ctabcol is select column_name,DATA_TYPE from sys.dba_tab_columns where owner = schema_orig and table_name = tn ; collist varchar2(2000) ; query varchar2(2000) ; column_name varchar2(200); has_geometric_column boolean; col_count number; BEGIN -- dbms_output.enable(1000000); FOR ctabrec IN ctab LOOP tn := ctabrec.table_name ; has_geometric_column := false; col_count := 0; for ctabcolrec IN ctabcol LOOP if ctabcolrec.data_type = 'SDO_GEOMETRY' then has_geometric_column := true; col_count := col_count + 1; column_name := ctabcolrec.column_name; -- insert data into user_sdo_geom query := 'INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ('''||tn|| ''' , '''|| COLUMN_NAME ||''', MDSYS.SDO_DIM_ARRAY( MDSYS.SDO_DIM_ELEMENT(''X'', 0, 10000000, 0.000005),MDSYS.SDO_DIM_ELEMENT(''Y'', 0, 10000000, 0.000005), MDSYS.SDO_DIM_ELEMENT(''Z'', 0,10000000, 0.000005)), &srid)'; dbms_output.put_line(query); BEGIN execute immediate query; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('**** failed to insert row into user_sdo_geom_metadata for '||tn||'('||column_name||')'); --rollback; ignore any errors! there will be existing columns END; -- create spatial index query := 'CREATE INDEX '||tn||'_'||to_char(col_count)||'_SX ON '||tn||'('|| COLUMN_NAME ||') INDEXTYPE IS MDSYS.SPATIAL_INDEX'; dbms_output.put_line(query); BEGIN execute immediate query; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('**** failed to create spatial index for '||tn||'('||column_name||')'); --rollback; ignore any errors END; end if; END LOOP; END LOOP; commit; END; / [/sourcecode] The script has two parameters: the schema from where to scan all tables, and the srid (coordinate system) which needs to be filled in. Any thoughts on this? suggestions?

Recent comments

• sonsun on Removing VBA Password in AutoCAD 04/05/23
• cccc on [Bootstrap 4] Closing a modal programmatically 28/06/22
• Rollins Duke on Removing VBA Password in AutoCAD 06/05/22
• Greg Tarsa on [rails 4.1] when the rails command just hangs 12/03/22
• gfbh tg on starting a new rails3 site using rspec2 and jquery 01/02/22

Tags