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
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
- 8394 replies
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.
+22- Contributor
- 2179 replies
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.
+22- Contributor
- 2179 replies
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!,
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;