Skip to Main Content
  • Questions
  • Migrating spatial data from SQL Server to Oracle

Breadcrumb

May 4th

Question and Answer

Thanks for the question.

Asked: October 02, 2018 - 6:14 pm UTC

Last updated: October 02, 2018 - 6:14 pm UTC

Version:

Viewed 10K+ times! This question is

You Asked

Hi,

I would want to migrate spatial columns of data from SQL server to Oracle database server. Can you please provide how to begin. The sql developer tool does not support migration of spatial data. Could you provide appropriate tool/steps necessary. Can you assist in starting off.

and we said...

Hi Angelica

(just to be clear: your question is not related to the topic of the AskTOM session: the session is specifically about Property Graphs and the PGQL graph query language)

There are generally three possible approaches when it comes to moving spatial data from one storage structure to another:

1) Commercial tools: the most common tool available and in use by the majority of GIS-style users is FME (Feature Manipulation Engine) available from Safe Software ( https://www.safe.com ). Note that it does much more than converting spatial data: it is a complete ETL for geospatial data and can do all sorts of transformations.

2) Open source tools: that will be GDAL ( http://gdal.org ). It is a library that isolates applications from the specifics of spatial data stores, based on a "plugin" architecture: a common API and "plugins" that are specific to different data stores. Think "jdbc" for spatial data. The distribution comes with ready for use command-line tools that you can use to convert data from one storage structure to another, including SQL Server and Oracle. See  https://www.gdal.org/drv_mssqlspatial.html for the SQL Server plugin and  https://www.gdal.org/drv_oci.html for the Oracle Spatial plugin. 

3) Exporting the data from the SQL Server database into a well-known format and importing into Oracle from that format. The most common file format that all spatial stores understand in some way is the ESRI Shape File format. If the applications/tools you use with your SQL Server installation can do that, then you are all set: export the data as shape files, import that into Oracle Spatial using the Oracle Mapbuilder tool (see  https://www.oracle.com/technetwork/middleware/mapviewer/downloads/index-100641.html ). It is a GUI stand-alone java tool. In the Tools menu we provide an "Import Shapefile" function.

Which solution is best depends obviously of your use case. Is this a one-off migration ? Is this something that repeats ? How often ? 

Albert


Is this answer out of date? If it is, please let us know via a Comment