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).

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;
/

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?