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

Creating spatial indexes programmatically in SQL Server 2008

Whilst I keep trying to teach myself the finer points of of SQL Server 2008 spatial functionality, I was presented with my first “real” task: To create a spatial index for a table containing some point features. So what’s the big deal, I hear you ask?  Well, first of all, I didn’t even realize that the table DID NOT have a spatial index! Duh! Being used to Oracle that complains if you try to do almost anything without a spatial index, it didn’t even occur to me. And then, I found out that in order to create the index you have to first find the table’s extent/envelope. In SQL Server there doesn’t seem to be anything equivalent to Oracle’s USER_SDO_GEOM_METADATA table (For a short and sweet explanation of this view see Adam Estrada’s post here). Then again even in Oracle you have to define the spatial extent at some point in USER_SDO_GEOM_METADATA. (Should really write a post comparing Oracle and SQLServer’s spatial functions…at some point)

So, on to the task at hand: rather than defining the extent and creating the spatial index by hand, I wrote a couple of procedures to do this automatically. I only tested these on this one table but should work for any. I used some default values for defining the Grid Density, however it should be easy enough to change this based on your data. In any case, creating spatial indexes on ANY database, is –from my experience- more art than science!

The first procedure is spgGetExtentDimension which will return a point geometry representing a corner of the table’s envelope or Minimum Bounding Rectangle (MBR) (which is, as the name suggests, a rectangle). The procedure will accept as input parameters the table name, the spatial column name and the vertex number of the rectangle (1 to 4).

CREATE PROCEDURE spgGetExtentDimension (@spatial_tab nvarchar(20)
                                      ,@spatial_col nvarchar(10)
                                      ,@vertex int
                                      ,@point geometry OUTPUT)
AS
BEGIN
    -- Declare variables
    DECLARE @extent geometry;
    DECLARE @sqlString nvarchar(500);
    DECLARE @paramDefinition nvarchar(500);
    -- The extent calculation sql string
    SET @sqlString=N'SELECT @extentOUT = dbo.GeometryEnvelopeAggregate('
                                                       +@spatial_col+')
                    FROM dbo.pois'
    -- Parameter definition string
    SET @paramDefinition=N'@extentOUT geometry OUTPUT';
    EXECUTE sp_executesql @sqlString, @paramDefinition
                                    , @extentOUT=@extent OUTPUT
    SELECT @extent;
    -- Now that we have the extent return its min or max point (1 or 3)
    SELECT @point = @extent.STPointN(@vertex);
END
GO

The main procedure to create the spatial index is the imaginatively named CreateSPIndex. The procedure accepts the table and spatial column names as input parameters, calls the spgGetExtentDimension procedure to retrieve the upper and lower corners of the extent (vertices 1 and 3) and executes a bit of dynamic sql to create the index.

CREATE PROCEDURE CreateSPIndex (@spatial_tab nvarchar(20)
                              ,@spatial_col nvarchar(10))
AS
BEGIN
    DECLARE @sqlCommand nvarchar(1000);
    DECLARE @minPoint geometry;
    DECLARE @maxPoint geometry;
    DECLARE @minx float;
    DECLARE @miny float;
    DECLARE @maxx float;
    DECLARE @maxy float;

    EXEC [dbo].[spgGetExtentDimension]
         @spatial_tab = @spatial_tab,
         @spatial_col = @spatial_col,
         @vertex = 1,
         @point = @minPoint OUTPUT;
    --SELECT @minPoint;

    EXEC [dbo].[spgGetExtentDimension]
         @spatial_tab = @spatial_tab,
         @spatial_col = @spatial_col,
         @vertex = 3,
         @point = @maxPoint OUTPUT;
    --SELECT @maxPoint;
    select @minx= @minPoint.STX;
    select @miny= @minPoint.STY;
    select @maxx= @maxPoint.STX;
    select @maxy= @maxPoint.STY;
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Spatial index creation statement
    SELECT @sqlCommand = 'CREATE SPATIAL INDEX POIS_SPIDX  ON '
                                + @spatial_tab+'(' + @spatial_col +')
                          WITH (BOUNDING_BOX = ('
                                + CAST(@minx as varchar(20)) +','
                                + CAST(@miny as varchar(20)) +','
                                + CAST(@maxx as varchar(20)) +','
                                + CAST (@maxy as varchar(20))
                        +' ) ,GRIDS = ( LEVEL_4 = HIGH, LEVEL_3 = MEDIUM ) )';
    EXEC sp_executesql @sqlCommand;
END
GO

Ok, back to my sandbox now…

Create point geometries dynamically using dynamic segmentation in SQLServer 2008

UPDATED: Change trigger to allow for multiple linear geometries

I have started experimenting with the new spatial functionality in SQL Server 2008. Having used Oracle Spatial for a quite a few years this is my first attempt with SQL Server so my apologies in advance if there is an easier way of doing this! 🙂

So the scenario is as follows:- Consider you have a layer containing A SINGLE multiple linear geometries with the following structure called ROUTES:

image

The structure can be different- for now all we care about is the [Shape] and [ID] fields.

We also have another table called EVENTS- this is the table we will be updating using the trigger. The table structure is:

image

Where ROUTE_ID is the ID the Route the event is  located along, (as a FK to ID field in the ROUTES table) and  START_MP is the distance from the start of the Route.

The task at hand was to create a trigger so any changes in the START_MP value would update the shape column in the EVENTS table, creating a Point geometry at that location.

That was easy enough using the SQL Server Spatial tools available from http://sqlspatialtools.codeplex.com. After registering the SQLSpatialTools.dll in my database I was able to use the LocateAlongGeom function in a trigger which would deliver the point geometries. The trigger source is:

ALTER TRIGGER [dbo].[upd_shape_trg]
   ON  [dbo].[EVENTS]
   AFTER UPDATE
AS

BEGIN 
  DECLARE @lineshape geometry
    DECLARE @routeid int
    --SELECT @lineshape = r.shape from marathonia_diadromi_spatial r
    --, events e
    --where r.id= e.route_id
    SELECT @routeid=route_id from deleted
    SELECT @lineshape = shape from marathonia_diadromi_spatial where id=@routeid
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
   UPDATE [mpoint].[dbo].[EVENTS]
   SET [SHAPE] = [mpoint].[dbo].[LocateAlongGeom](@lineshape, start_mp).ToString()

END

Note that right now this would only work if the ROUTES table contains a single linear geometry. As said- first attempt only this! More later…

Want to (just) display data from Oracle or SQLServer in ArcMap? You have to pay for the privilege!

I was just reading Abe Gillespie’s post about the decision to release zigGIS, the Arcmap plugin to access PostGIS data  as open source (again) and it got me thinking:- If you want to display oracle or sql server spatial data in ArcMap you have to also pay for ArcSDE. And it doesn’t come cheap. Bit cheeky this from ESRI I think. Especially since you did NOT have to do this back in ArcGIS 8.x days. The sde libraries were part of the ArcMap product and you could use “direct connect” to simply display the data. (You still had to create the sde schema and owner in your database which was another major pain, but will leave that for now…)

Compare this with the $279 you have to pay now for ziGIS to access PostGIS data (and soon to be $0) and the $0 you pay to display Oracle or SQLServer data using MapServer or QGIS. And none of them requires you to tinker with your database!

Is it just me who thinks that ESRI (“ezree”) got it wrong somewhere along the line? Why do I have to go for a fully-blown ESRI solution? Why should my customers pay for something they will never use? Still though, since ESRI’s ArcMap IS a good product and it IS used by a lot of people, are there  any other brave souls out there who are working for Oracle or SQLServer plugins?