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
About these ads

, , , , , , , , , , ,

  1. #1 by Linds on January 6, 2011 - 23:26

    Hi,

    This is great but the right side of your table is cutting off code. I’d really like to see the rest of the syntax. Thanks!

    • #2 by Pano Voudouris on January 11, 2011 - 17:36

      Had problems with this template for ever! :-) Which bit though? I thought all code was wrapped in new lines?

  2. #3 by Seth on January 24, 2011 - 16:23

    Hi – very useful summary and comparison of the two databases.

    GDAL/OGR now has a SQL Server driver that can be used to load spatial data.

    If anyone requires linear referencing of segments rather than points, an updated version of SQLSpatialTools can be found at http://geographika.co.uk/linear-referencing-in-sql-server-2008

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 123 other followers

%d bloggers like this: