Shp2ora and Ora2shp: Utilities for importing and exporting shapefiles to Oracle

Yes, another one of those. Its not exactly a novel idea as there are a few converters around for moving shapefile data to and from Oracle:

  • If you are an ESRI/ArcSDE user you have the sde2shp and shp2sde commands.
  • Oracle provides two options: a command line program, shp2sdo to load shapefiles into Oracle, and its java equivalent through Mapbuilder
  • Most (all?) GIS’ that can read Oracle Spatial data, include options to save the Oracle layer to a shapefile.

Plenty of choice you probably think. Well, I was still not happy. First of all I didn’t always have ArcSDE on all machines I was working on, secondly installing Mapbuilder means you need to go through the 18.2MB download, plus it will only LOAD shapefiles into Oracle and not the other way around and thirdly, I didn’t want to go through the whole process of downloading a fully-blown desktop GIS (say QGIS) just for the sake of converting a spatial table into a shapefile. Not to mention that you have to configure the client GIS first in order to connect to Oracle BEFORE you can even display the layer – so you can then export it. Too much hassle for a relative easy task.

So I decided to create my own little command-line utilities to do just that. You can download the full source code and executables from the Box.Net widget on the left side of this post (ora2shp.rar).

After compiling the project, you will find two executables under the Debug folders of Ora2shp and shape2ora projects: Ora2shp.exe and shp2ora.exe respectively,

Ora2shp syntax is: ora2shp <username/password>@dbalias> <spatial_table_name> <PK_col> <shape_col> <shapefile> ["optional_where_clause"]

If you use the where clause you should NOT put the WHERE keyword.

Shp2orashp2ora <username/password>@dbalias> <spatial_table_name> <shape_col> <shapefile> <srid>

If you don’t specify a SRID it will default to null. If the spatial table already exists records will get appended. If it doesn’t it will get created and will also create the oracle metadata records record (USER_SDO_GEOM_METADATA table) and spatial index.

It should deal with all shapes (multipart, Z, M) APART from multipatch.

Note that you will need Oracle client 10.2 installed to compile the programs as-is. Otherwise change the reference to the Oracle client of your choice. You will know if you have the wrong Oracle client version if you get an error about Oracle.DataAccess not being the same version as Oracle Client.

When running the programs any errors should appear on the console and the full stack trace will be written to orashp_err.log file, located at the same folder as the executables.

Note that  performance is not great- especially when creating the shapefile. It took something less than 15mins to create a point shapefile of around 66K records. (around 5M of shapefiles). But feel free to improve it and I would very much appreciate to get back to me if you do!

Advertisements

SQL Server 2008 Spatial and Oracle Spatial comparison and cheat sheet

This is a brief comparison between SQL Server 2008 and Oracle’s spatial functions along with relevant links and simple examples. It is by no means exhaustive but it should help people when migrating.

Note that all Oracle examples and links here refer to the 10g R2 release.

UPDATED: Added link for updated version of SQLSpatialTools from Geographica.

Spatial Data

Feature Oracle 10g SQL Server 2008
Spatial Data types

Defined by implementing the SDO_GEOMETRY type:

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);

Defines two types of spatial data depending whether they contain geographic (round-earth coordinate system) or geometric (Euclidean/flat coordinate system) data: Geometry and Geography. Both types are implemented as a .NET common language runtime (CLR) data types.

Spatial Tables Statement to create a spatial table:
CREATE TABLE sp_table(sp_id NUMBER PRIMARY KEY,
  shape SDO_GEOMETRY);

Statement to create a spatial table (geometry):

CREATE TABLE sp_table 
(sp_id int IDENTITY (1,1), shape geometry);

Statement to create a spatial table (geography):

CREATE TABLE sp_table 
(sp_id int IDENTITY (1,1),shape geography);

Geometry Metadata For spatial tables to perform correctly, Oracle requires the update of the geometry metadata views. These views describe the dimensions, lower and upper bounds, and tolerance in each dimension for each spatial table and are stored in a global table owned by the MDSYS user.

The main view is USER_SDO_GEOM_METADATA which contains metadata information for all spatial tables owned by the user (schema).

Example statement to insert data into the USER_SDO_GEOM_METADATA view:

INSERT INTO user_sdo_geom_metadata
(TABLE_NAME,COLUMN_NAME,
  DIMINFO, SRID)
VALUES (‘SP_TABLE’, ‘SHAPE’, 
SDO_DIM_ARRAY(    SDO_DIM_ELEMENT(‘X’, 0, 20, 0.005), 
SDO_DIM_ELEMENT(‘Y’, 0, 20, 0.005)), NULL);

SQL Server does not have an equivalent of Oracle’s geometry metadata views
Spatial Indexes Supports R-tree and Quadtree indexing. However, Oracle states that:

the use of quadtree indexes is discouraged, and you are strongly encouraged to use R-tree indexing

For more information about Oracle’s spatial indexing click here.

Creating a spatial (R-tree) index:

CREATE INDEX sp_table_spidx   ON sp_table(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Uses B-tree indexing. For more information on how spatial indexing is handled, click here.

Creating spatial indexing syntax varies depending whether the column is of type Geometry or Geography.

Creating a spatial index on a Geography column:

CREATE SPATIAL INDEX sp_table_spidx    ON sp_table(shape);

Creating a spatial index on a Geometry column:

CREATE SPATIAL INDEX sp_table_spidx ON sp_table(shape) WITH (
BOUNDING_BOX = ( 0, 0, 500, 200 ), 
GRIDS = ( LEVEL_4 = HIGH, LEVEL_3 = MEDIUM ) );

More examples can be found here

OGG Compliance Conforms to the Open Geospatial Consortium (OGC) Simple Features for SQL Specification version 1.1.0. Conforms to the Open Geospatial Consortium (OGC) Simple Features for SQL Specification version 1.1.0

Spatial object

Constructors

Examples
More geometry examples here
Examples
       Points

(Notice that 4326 is the SRID)

1. Using the SDO_GEOMETRY type:

insert into sdo_points (sp_id, shape)
values (1, SDO_GEOMETRY(
      2001,
      4326,
      SDO_POINT_TYPE(40, 22, NULL),  NULL,   NULL));

2. Using the Well-Known text (WKT) syntax:

SELECT SDO_GEOMETRY(‘POINT(-79 37)’) FROM DUAL;

(Notice that 4326 is the SRID)

1. Using the Well-Known text (WKT) syntax (STPointFromText function):

INSERT INTO GeomTable (geom)

VALUES (geography::STPointFromText(‘POINT(40 22)’, 4326))

2. Using the geometry::Point or geography::Point syntax:

INSERT INTO GeogTable (geog) VALUES (geography::Point(40, 22, 4326))

3. Using the GML syntax (GeomFromGml function)

INSERT INTO GeomTable (geom)
     VALUES (geography::GeomFromGml(
‘<Point xmlns="
http://www.opengis.net/gml">
<pos>40 22</pos></Point>’
, 4326))

       Simple Lines

Using the SDO_GEOMETRY type:

INSERT INTO sdo_lines VALUES(
  1, SDO_GEOMETRY(
    2002,
    NULL, — NULL SRID
    NULL,
    SDO_ELEM_INFO_ARRAY(1,2,1),
    SDO_ORDINATE_ARRAY(411392.088118, 4493608.698554, 411436.431582, 4493649.698236)));

(You can also create line geometries using the WKT syntax as above)

Using the Well-Known text (WKT) syntax (STLineFromText function):

INSERT INTO GeogTable (geog)
VALUES (geography::STLineFromText(‘LINESTRING(-122.360 47.656,
-122.343 47.656 )’, 4326));

(You can also create line geometries using the WKT and GML syntax as above)

       Simple Polygons

Using the SDO_GEOMETRY type:

INSERT INTO sdo_polys VALUES(
2,
MDSYS.SDO_GEOMETRY(
2003, — 2-dimensional polygon
NULL,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), — one polygon (exterior polygon ring)
MDSYS.SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1)));

(You can also create polygon geometries using the WKT syntax as above)

Using the Well-Known text (WKT) syntax (STPolyFromText function):

INSERT INTO GeogTable (geog) VALUES (geography::STPolyFromText(‘POLYGON((-122.358 47.653,
-122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))’
, 4326))

(You can also create polygon geometries using the WKT and GML syntax as above)

3D/4D support

Both DBs support 3D and 4D (X,Y,Z,M) shapes

Loader and conversion  Tools Oracle makes available for download from its website two utilities to convert shapefile to oracle spatial tables. The first one is a command line utility shp2sdo.exe,and the second a Java shapefile converter. You can find more information about these utilities here. There is a free utility called Shape2Sql available from the SharpGIS website

Spatial Functions

The table list some commonly used spatial functions.

Function Description Oracle 10g

Spatial Functions see also the
SDO_GEOM package
SQL Server 2008

All Geography functions
All Geometry functions

Retrieve the spatial extent of a given table(Minimum Bounding Rectangle (MBR)) Use the SDO_AGGR_MBR function, e.g.:

SELECT SDO_AGGR_MBR(shape) FROM cola_markets;

Does not contain a similar function in the standard install. You will have to use the GeographyUnionAggregate and GeometryEnvelopeAggregate functions available in the SQL Server Spatial Tools CRL package available from Codeplex.
e.g.

select state, dbo.
GeometryEnvelopeAggregate(shape_geom) from zipcodes

Identify objects within a distance

Use the SDO_WITHIN_DISTANCE function e.g.:

SELECT c.name FROM cola_markets c WHERE SDO_WITHIN_DISTANCE(c.shape,
  SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3),
    SDO_ORDINATE_ARRAY(4,6, 8,8)),
  ‘distance=10’) = ‘TRUE’;

Use the STDistance (Geography) or STDistance (Geometry) e.g.:

DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STGeomFromText(‘POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))’, 0);
SET @h = geometry::STGeomFromText(‘POINT(10 10)’, 0);
SELECT @g.STDistance(@h);

Create a buffer around a spatial object Use the SDO_GEOM.SDO_BUFFER function e.g.:

SELECT c.name, SDO_GEOM.SDO_BUFFER(c.shape, m.diminfo, 1)
  FROM cola_markets c, user_sdo_geom_metadata m
  WHERE m.table_name = ‘COLA_MARKETS’  AND m.column_name = ‘SHAPE’
  AND c.name = ‘cola_a’;

Use the STBuffer (Geography) or STBuffer (geometry) e.g.:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText(‘LINESTRING(0 0, 4 0)’, 0);
SELECT @g.STBuffer(1).ToString();

Get the length of an object Use the SDO_GEOM.SDO_LENGTH function, e.g.:

SELECT c.name, SDO_GEOM.SDO_LENGTH(c.shape, m.diminfo)
  FROM cola_markets c, user_sdo_geom_metadata m
  WHERE m.table_name = ‘COLA_MARKETS’ AND m.column_name = ‘SHAPE’;

Use the STLength (Geography) or STLength (Geometry) e.g.:

 

DECLARE @g geography;
SET @g = geography::STGeomFromText(‘LINESTRING(-122.360 47.656, -122.343 47.656)’, 4326);
SELECT @g.STLength();

Get the area of a polygon   Use the STArea (Geography) or STArea (Geometry) e.g.:

DECLARE @g geography;
SET @g = geography::STGeomFromText(‘POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))’, 4326);
SELECT @g.STArea();

Check the validity of an object Use the SDO_GEOM. VALIDATE_GEOMETRY_WITH_CONTEXT function e.g.:

SELECT c.name, SDO_GEOM. VALIDATE_GEOMETRY_WITH_CONTEXT(c.shape, 0.005)
   FROM cola_markets c WHERE c.name = ‘cola_invalid_geom’;

Use the STIsValid (Geometry) e.g.:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText(‘LINESTRING(0 0, 2 2, 1 0)’, 0);
SELECT @g.STIsValid();

The Geography data type does not –for reasons unknown to me- include a STIsValid function.

Linear Referencing Contains a large number of functions for linear referencing. Lots of examples can be found here SQL Server does not support any true Linear referencing functions. SQL Server Spatial Tools however, provides some rudimentary linear referencing functions such as LocateAlongGeog and LocateAlongGeom which will return the point at a given instance along a linear object.
UPDATED: Geographica provides an updated version of SQLSpatialTools which includes a new function to display a linear event as well (a “LocateLineAlongGeom” function but called CreateLinearReferenceFeature. More details can be found at this address: http://geographika.co.uk/linear-referencing-in-sql-server-2008