Exporting a Database in 12c , the SRID is set to null , is there a way that FME can update all SRID to correct coordinate system within FME , would it be Feature class by Feature class or can it be doen for the whole Schema ?
Solved
Updating SRID from null to SRID 27700
Best answer by mark_f
@mantmania definitely a task for SQL in Oracle and have done exactly this in recent months.
- Drop the Spatial Index
- Update USER_SDO_GEOM_METADATA with SRID = 27700
- Update the SRID for each row in each table, but check it has a geometry.I would use:
update<YOUR_TABLE> t set t.<YOUR_GEOMETRY_COLUMN>.sdo_srid = 27700 where t.<YOUR_GEOMETRY_COLUMN> is not null;
- Rebuild the Spatial Index
Tricky parts include making sure any tablespace parameters specified for the Spatial Index are maintained during the Drop/Create index. Remember to manage the commit through the update of the various tables.
To check what tables to update:
select table_name, column_name from user_tab_cols where data_type = 'SDO_GEOMETRY';To check index names and tablespaces:
select SDO_INDEX_NAME,SDO_TABLESPACE from user_sdo_index_info i, user_sdo_index_metadata m where i.index_name = m.sdo_index_name and i.table_name = 'FIELDIM' and i.column_name = 'GEOMETRY';You can wrap it up in a PL/SQL block
You can specify the coordinate system on the writer, this will affect all the features that exit that particular writer. Example:
You can also leave it blank on the writer and use the CoordinateSystemSetter transformer instead, then you can specify the coordinate system per feature of feature class.
+17- Contributor
And you can do it by using SQL in the Oracle database. After creating the table you can populate the MDSYS.USER_SDO_GEOM_METADATA using the SRID. FME will detect this record and use it to populate the SRID on the geometries.
+17- Contributor
And if you loaded data into the table and want to use SQL to add the SRID this will be the SQL statement:
Update table t set t.geom.sdo_srid = 27700;
Where table is your table name and geom is your geometry column name.
You have to use the table name alias (I used t) for this to work.
+2@mantmania definitely a task for SQL in Oracle and have done exactly this in recent months.
- Drop the Spatial Index
- Update USER_SDO_GEOM_METADATA with SRID = 27700
- Update the SRID for each row in each table, but check it has a geometry.I would use:
update<YOUR_TABLE> t set t.<YOUR_GEOMETRY_COLUMN>.sdo_srid = 27700 where t.<YOUR_GEOMETRY_COLUMN> is not null;
- Rebuild the Spatial Index
Tricky parts include making sure any tablespace parameters specified for the Spatial Index are maintained during the Drop/Create index. Remember to manage the commit through the update of the various tables.
To check what tables to update:
select table_name, column_name from user_tab_cols where data_type = 'SDO_GEOMETRY';To check index names and tablespaces:
select SDO_INDEX_NAME,SDO_TABLESPACE from user_sdo_index_info i, user_sdo_index_metadata m where i.index_name = m.sdo_index_name and i.table_name = 'FIELDIM' and i.column_name = 'GEOMETRY';You can wrap it up in a PL/SQL block
Hi Mark @mark_1spatial
When trying to update SRID to 27700 by running the below SQL query, oracle is throwing
atomic null violation error. Could you please suggest how to update SRID to 27700 when SRID is NULL currently.
update<YOUR_TABLE> t set t.<YOUR_GEOMETRY_COLUMN>.sdo_srid = 27700 where t.<
YOUR_GEOMETRY_COLUMN> is null;
+2Hi @anilkb1105
Do you have any geometries with a null value?
select count(*) from my_table where geometry is null;In my original answer the update is where geometry is NOT null. Your reply looks like you are trying IS null!,
mark_f wrote:
Hi @anilkb1105
Do you have any geometries with a null value?
select count(*) from my_table where geometry is null;In my original answer the update is where geometry is NOT null. Your reply looks like you are trying IS null!,
Yes, out of 7 million records, i got some 31 records as GEOMETRY NULL(which is a bug in our utility data model, i'll try to fix it) and this was the reason update query was throwing atomic null violation error.
Your below query has worked absolutely fine, thanks for your help.
update<YOUR_TABLE> t set t.<YOUR_GEOMETRY_COLUMN>.sdo_srid = 27700 where t.<YOUR_GEOMETRY_COLUMN> is not null;
Reply
Rich Text Editor, editor1
Editor toolbars
Press ALT 0 for help
Helpful Members This Week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.