Migrating Tables with Geometry Columns from Oracle to SQL Server Spatial

A colleague asked me the other day how he could move data from a table in an Oracle database with a geometry column to SQL server.

Of course I thought of this as a straight forward data migration exercise using standard database tools, in this case SQL Developer and SQL Server Management Studio (SSMS), where the only other skill one needs is in generating a bit of declarative SQL and using the two database tools.

To execute the migration we need a geometry format that both databases understand, because SQL Server Spatial doesn’t understand SDO_GEOMETRY. In this case we will use Well Known Text (WKT) as that is what it was designed for (one can use Well Known Binary, but WKT gives us better debugging capability).

The object that is being exported in this example could be a table, view or materialized view: it will just be called “object” which you replace with the right name eg CAD. In SQL Server Spatial we will create a table.

The steps to achieve a successful migration are:

1. Select the object’s node in the connection tree such that its Properties Metadata dialog appears; select all columns that are to be migrated.

2. Create a SELECT statement including all columns that are for migration;
If more than one SDO_GEOMETRY column, replace each one with the appropriate SDO_GEOMETRY method “get_wkt()” or the SDO_UTIL function to convert to WKT for example:

SELECT a.geom.Get_WKT() as geom FROM object a;
OR
SELECT SDO_UTIL.TO_WKTGEOMETRY(geom) as geom FROM object;

3. Execute the statement using F9 to get a rowset (table).

You should see WKT (string) under the GEOM heading in the rowset.

4. Select all the rows in the rowset (Ctrl-A), then right mouse click in the selected rowset and select EXPORT from the menu.

5. Make sure the export format is INSERT statements, set the name of the SQL Server target table and the name of the export sql file, then click Next.

Then click Finish:

SQL Developer will save the SQL file and then open it in a new worksheet.

6. Since we will be using this SQL script to create the table in SQL Server (and insert the rows) we need to create an appropriate CREATE TABLE statement.
We can access Oracle’s definition by selecting the CAD table’s Properties Form and selecting the SQL tab at the right.

Select the Create Table statement and the columns for export; execute Ctrl-C to copy selection into memory.

7. Insert (Ctrl-V) the Create Table fragment into the beginning of the INSERT sql file.

8. Reformat the SQL so that the datatypes match SQL Server’s.

Make sure any primary key, constraints and indexes are defined in SQL Server terms. Leave the spatial index until after the rows have been successfully imported in SQL Server.

9. Save your changes: Ctrl-S

10. The next step involves ensuring the INSERT statements will run in SQL Server eg converting the WKT string into something SQL Server Spatial can consume.

First add the target to object ie CAD -> dbo.CAD.

Now we wrap the WKT text with the appropriate SQL Server Spatial WKT constructor eg geometry::STGeomFromText() (or geography equivalent)
So, execute the keyboard shortcut Ctrl-R (Replace). The text to be replaced is the start of the WKT eg ‘POLYGON which you replace with geometry::STGeomFromText(.

Then we need to change the end of the WKT which might look like this ‘);. Again replace this with ‘,)); where is your SRID (eg a.geom.sdo_srid in Oracle).

Assuming you have done this correctly, save the result (Ctrl-S).

11. Close the insert sql file, switch to SSMS and open the file.

12. At the top of the script insert the following to select your database context:

use TESTDB
go

Obviously replace TESTDB with your database name!

Then execute the USE statement.

13. Execute the Create and Alter Table statements.

If an error occurs it is probably to do with the data types.

Correct all errors until the table is created.

Then refresh the tables node in the connection tree for the database to make sure the table name appears.

14. Now select the first INSERT INTO statement and execute.

If an error occurs interpret the result and correct.

Once successful select the rest of the INSERT INTO statements and execute.

15. Count the number of rows inserted by inserting a statement like the following immediately after the last INSERT statement.

SELECT count(*) FROM [dbo].[table “” not found /]
;

16. Create a spatial index using the SSMS tools or manually.

17. As a final check (because we are people who love to see stuff), executed a SELECT statement that includes the geometry column (all or TOP ).

SELECT * FROM dbo.table as a;

Then select the Spatial Results tab.

Conclusion

This migration can be done between any two databases as long as they can create and consume WKT (or WKB) etc.

The take home message here is that pretty well most migrations involving spatial data from one database to another don’t need special spatial software like ogr2ogr etc where one might need to install other software, have access to a command line, require special ports to be opened in firewalls etc.

Additionally, any XML format that includes spatial in an OGC standardized format (WKT, GML, KML etc) also can be handled by pure database tools.

Any questions?

Make a comment against this article or email me directly.