I spent many years on spatial data modeling and migration on ESRI ArcGIS and ArcSDE, served nature resources sector. Spatial data migration is common when we get involved in map-related applications.
I worked on a project which requested spatial data migration from Oracle to SQL Server. I would like to give you a quick walk-through on how it can be done correctly.
First, let’s introduce some basic concepts:
- WKT & WKB: Well-known Text & Well-known Binary, text markup language defined by Open Geospatial Consortium (OGC), supported by Oracle 9i+, MS SQL Server 2008 R2+, PostgreSQL PostGIS M2, etc.
- Geometry & Geography: Geometry: Euclidean (flat) coordinate system; Geography: Round-earth coordinate system.
- Spatial Data Types: consist of simple types and collection types as illustrated below.
Second, let’s see what Oracle Spatial offers. Oracle Spatial uses SDO_GEOMETRY object type to contain spatial data. The definition of SDO_GEOMETRY is:
CREATE TYPE sdo_geometry AS OBJECT (
SDO_GTYPE NUMBER,
SDO_SRID NUMBER,
SDO_POINT SDO_POINT_TYPE,
SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY,
SDO_ORDINATES SDO_ORDINATE_ARRAY);
SDO_GEOMERY methods:
| Name | Returns | Description |
| Get_Dims | Number | Returns the number of dimensions of a geometry object |
| Get_Gtype | Number | Returns the geometry type of a geometry object |
| Get_LRS_Dim | Number | Returns the measure dimension of an LRS geometry object |
| Get_WKB | BLOB | Returns the well-known binary (WKB) format of a geometry object |
| Get_WKT | CLOB | Returns the well-known text (WKT) format of a geometry object. |
| ST_CoordDim | Number | Returns the coordinate dimension |
| ST_IsValid | Number | Returns 0 if a geometry object is invalid or 1 if it is valid. |
There are many SDO packages available, e.g. SDO_UTIL, SDO_GEOM, etc.
Now let’s see how we handle the spatial data migration from Oracle to SQL Server manually:
- Query Oracle spatial table using SDO_UTIL.To_WKTGeometry:
Select pid, SDO_UTIL.To_WKTGeometry(SHAPE) from TFM_BLOCK_GEOMETRY;2. Export as Insert statement, e.g.:
Insert into TFM_BLOCK_GEOMETRY (PID, SHAPE) values ('007505850','POLYGON ((509339.299974334 5439971.90996378, 509339.419974334 5440002.32996378, 509241.249974334 5440002.39996378, 509241.040074334 5439971.96006378, 509339.299974334 5439971.90996378))');
3. Get SRID:
Select SRID from ALL_SDO_GEOM_METADATA where table_name = 'TFM_BLOCK_GEOMETRY';4. Replace ‘POLYGON()’ with geometry::STGeomFromText(‘POLYGON…’, SRID) in above insert statement;
5. Use TFM_TSTDB; Execute Insert statement;
6. Create spatial index;
7. Review results via executing select statement and view [Spatial results] tab in SSMS.
Lionsgatesoft.com consultants have rich experience in spatial data architecture design, development and spatial data migration. Should you have any questions, please feel free to contact us.
