Introduction

One of the great things about Oracle and PostgreSQL is that they provide flexible array data types which provide the stored procedure developer great power.

Unfortunately SQL Server has no array datatype.

This makes it difficult (not impossible) for developers to implement functionality that require such capability.

I have implemented two different types of “array” processing.

The first uses GeometryCollections as the host datatype and so is a pure spatial (geometry) implementation that allows only one geometry object per element.

The other, more flexible approach, uses XML as the host datatype.

Data Structure

The follows shows a TSQL pseudo declaration in comparison to sample Oracle and PostgreSQL/PostGIS declarations.

 
-- PostgreSQL/PostGIS
DECLARE
  v_saved_geometries Geometry[];

-- Oracle Locator/Spatial
DECLARE 
  v_saved_geometries IS TABLE OF sdo_geometry;

-- TSQL
DECLARE
  v_saved_geometries geometry; -- ie Array of geometry

As already described, a GeometryCollection, geometry object is used as the base array with each geometry element within the collection accessed by its implied position (implied because an index is not stored in the array that a user can access).

Accessors And Setters

To turn the GeometryCollecton in to an “array” we will need some accessors and setters.

Fairly obviously, no “read” accessors are required as the SQL/ OGC Standards provide:

STIsEmpty()
STNumGeometries()
STGeometryN(i)

However, to insert, update and delete setting actions we need some functions. The following are made available in my TSQL functions:

STCollectionAppend()
STCollectionInsertN()
STCollectionUpdateN()
STCollectionDeleteN()

With some additional utility functions:

STCollectionExtract() — Elements of a specific type eg points
STCollectionForce() — Converts the geometry into a GEOMETRYCOLLECTION.
STCollectionDeduplicate() — Removes from GeometryCollection all geometries that are equal and of the desired type.
STCollectionPrint() — Prints geometries in supplied geometry collection eg to SSMS Text Console
STCollectionToLine() — Creates a (multi)linestring from supplied GeometryCollection geometry’s (multi)linestring objects.

Example Collection and its Array “view”

Here is a GeometryCollection which we would like to process as an array.

select gid as i, geom.AsTextZM() as element
from [dbo].[STExtract](geometry::STGeomFromText(
'GEOMETRYCOLLECTION( 
  POINT(2 3 4), 
  POINT(4 5), 
  MULTIPOINT((1 1),(2 2),(3 3)), 
  CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778),
  LINESTRING(2 3 4,3 4 5), 
  MULTILINESTRING((2 3 4,3 4 5),(1 1,2 2)), 
  POLYGON((0 0 0, 100 0 1, 100 100 2, 0 100 3, 0 0 4)), 
  MULTIPOLYGON(((200 200, 400 200, 400 400, 200 400, 200 200)), ((0 0, 100 0, 100 100, 0 100, 0 0), (40 40,60 40,60   60,40 60,40 40))),
  CURVEPOLYGON( COMPOUNDCURVE( (0 -23.43778, 0 23.43778), CIRCULARSTRING(0 23.43778, -45 23.43778, -90 23.43778), (-90 23.43778, -90 -23.43778), CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778))), 
  COMPOUNDCURVE(CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778), (0 -23.43778, 0 23.43778))
)',0),1
) as f;

The GeometryCollection can be visualised as an “Array” as follows (with implied array index i):

Geometry Array Visualisation

i	Element
0	{Beginning Placeholder}
1	POINT (2 3 4)
2	POINT (4 5)
3	MULTIPOINT ((1 1), (2 2), (3 3))
4	CIRCULARSTRING (-90 -23.43778, -45 -23.43778, 0 -23.43778)
5	LINESTRING (2 3 4, 3 4 5)
6	MULTILINESTRING ((2 3 4, 3 4 5), (1 1, 2 2))
7	POLYGON ((0 0 0, 100 0 1, 100 100 2, 0 100 3, 0 0 4))
8	MULTIPOLYGON (((200 200, 400 200, 400 400, 200 400, 200 200)), ((0 0, 100 0, 100 100, 0 100, 0 0), (40 40, 60 40, 60 60, 40 60, 40 40)))
9	CURVEPOLYGON (COMPOUNDCURVE ((0 -23.43778, 0 23.43778), CIRCULARSTRING (0 23.43778, -45 23.43778, -90 23.43778), (-90 23.43778, -90 -23.43778), CIRCULARSTRING (-90 -23.43778, -45 -23.43778, 0 -23.43778)))
10	COMPOUNDCURVE (CIRCULARSTRING (-90 -23.43778, -45 -23.43778, 0 -23.43778), (0 -23.43778, 0 23.43778))
-1	{End Placeholder}

This table "visualizes" the use of a GeometryCollection object as an Array.

(To support geography objects, a geography GeometryCollection needs to be used, with all the support/access functions described duplicated.)

Example Processing

USE DEVDB
GO

-- Steps
-- Create empty array / GC

DECLARE @gArray geometry;
SET @gArray = geometry::STGeomFromText('GEOMETRYCOLLECTION EMPTY',0);
exec dbo.STCollectionPrint @gArray;

EMPTY

-- Add single element (cannot mix srids) at beginning
SET @gArray = dbo.STCollectionAppend(@gArray,geometry::STGeomFromText('POINT(0 0)',0),1,1);
exec dbo.STCollectionPrint @gArray;

1,POINT (0 0)

-- Add again but allow duplicates
SET @gArray = dbo.STCollectionAppend(@gArray,geometry::STGeomFromText('POINT(0 0)',0),1,0);
exec dbo.STCollectionPrint @gArray;

1,POINT (0 0)
2,POINT (0 0)

-- Note can add collection's elements at end
SET @gArray = [dbo].[STCollectionInsertN] (@gArray,geometry::STGeomFromText('GEOMETRYCOLLECTION(POINT(0.5 0.5))',0),-1);
exec dbo.STCollectionPrint @gArray;

1,POINT (0 0)
2,POINT (0 0)
3,POINT (0.5 0.5)

-- Delete Duplicate Point
SET @gArray = dbo.STCollectionDeleteN(@gArray,1);
exec dbo.STCollectionPrint @gArray;

1,POINT (0 0)
2,POINT (0.5 0.5)

-- Add more data from a GeometryCollection including a duplicate
SET @gArray = dbo.STCollectionAppend(@gArray,geometry::STGeomFromText('GEOMETRYCOLLECTION(LINESTRING(1 1,2 2),POINT(3 3))',0),1,0);
exec dbo.STCollectionPrint @gArray;

1,POINT (0 0)
2,POINT (0.5 0.5)
3,LINESTRING (1 1, 2 2)
4,POINT (3 3)

-- Add new geometry to beginning of array (could use 1 as position)
SET @gArray = dbo.STCollectionInsertN(@gArray,geometry::STGeomFromText('POLYGON((1 1,2 1,1.5 2,1 1))',0),0);
exec dbo.STCollectionPrint @gArray;

1,POLYGON ((1 1, 2 1, 1.5 2, 1 1))
2,POINT (0 0)
3,POINT (0.5 0.5)
4,LINESTRING (1 1, 2 2)
5,POINT (3 3)

-- Add new element to end of array (cf Append) - Could use index via @gArray/STNumGeometries()
SET @gArray = dbo.STCollectionInsertN(@gArray,geometry::STGeomFromText('LINESTRING(10 10,11 11)',0),-1);
exec dbo.STCollectionPrint @gArray;

1,POLYGON ((1 1, 2 1, 1.5 2, 1 1))
2,POINT (0 0)
3,POINT (0.5 0.5)
4,LINESTRING (1 1, 2 2)
5,POINT (3 3)
6,LINESTRING (10 10, 11 11)

-- Add single element in middle of collection
SET @gArray = dbo.STCollectionInsertN(@gArray,geometry::STGeomFromText('POINT(-1 -1)',0),@gArray.STNumGeometries()/2);
exec dbo.STCollectionPrint @gArray;

1,POLYGON ((1 1, 2 1, 1.5 2, 1 1))
2,POINT (0 0)
3,POINT (-1 -1)
4,POINT (0.5 0.5)
5,LINESTRING (1 1, 2 2)
6,POINT (3 3)
7,LINESTRING (10 10, 11 11)

-- Update added geometry in middle
SET @gArray = dbo.STCollectionUpdateN(@gArray,geometry::STGeomFromText('POINT(-1.1 -1.1)',0),2);
exec dbo.STCollectionPrint @gArray;

1,POLYGON ((1 1, 2 1, 1.5 2, 1 1))
2,POINT (-1.1 -1.1)
3,POINT (-1 -1)
4,POINT (0.5 0.5)
5,LINESTRING (1 1, 2 2)
6,POINT (3 3)
7,LINESTRING (10 10, 11 11)

-- Extract Point Geometries
DECLARE @pArray geometry;
SET @pArray = dbo.STCollectionExtract(@gArray,1);
exec dbo.STCollectionPrint @pArray;

1,POINT (-1.1 -1.1)
2,POINT (-1 -1)
3,POINT (0.5 0.5)
4,POINT (3 3)

-- Convert Points to a linestring
DECLARE @line geometry;
SET @line = dbo.STCollectionToLine(@pArray,2,2);
exec dbo.STCollectionPrint @line;

1,LINESTRING (-1.1 -1.1, -1 -1, 0.5 0.5, 3 3)

Additionally, STCollectionForce’s processing can be seen here.

ST_ForceCollection

PostGIS has a function called ST_ForceCollection which

Converts the geometry into a GEOMETRYCOLLECTION.

I have written a function called STCollectionForce which implements that which PostGIS provides, but extends it to create Multi geometries from their singular representation. So, a single LineString becomes a MultiLineString with one element.

The following examples show how STCollectionForce works.

-- 1. Convert Polygon to GeometryCollection
DECLARE @geom geometry;
SET @geom = [dbo].[STCollectionForce](
               geometry::STGeomFromText('POLYGON((0 0 1,0 5 1,5 0 1,0 0 1),(1 1 1,3 1 1,1 3 1,1 1 1))',0),
               0,
               0
            );
SELECT @geom.AsTextZM() as geom;

-- GEOMETRYCOLLECTION (POLYGON ((0 0, 0 5, 5 0, 0 0)), POLYGON ((1 1, 3 1, 1 3, 1 1)))

-- 2. Convert Polygon Rings to LineStrings
DECLARE @geom geometry;
SET @geom = [dbo].[STCollectionForce](
               geometry::STGeomFromText('POLYGON((0 0 1,0 5 1,5 0 1,0 0 1),(1 1 1,3 1 1,1 3 1,1 1 1))',0),
               1,
               0
            );
SELECT @geom.AsTextZM() as geom;

-- GEOMETRYCOLLECTION (LINESTRING (0 0, 0 5, 5 0, 0 0), LINESTRING (1 1, 3 1, 1 3, 1 1))

-- 3. Convert Polygon Rings to a MultiLineString
DECLARE @geom geometry;
SET @geom = [dbo].[STCollectionForce](
               geometry::STGeomFromText('POLYGON((0 0 1,0 5 1,5 0 1,0 0 1),(1 1 1,3 1 1,1 3 1,1 1 1))',0),
               1,
               1
            );
SELECT @geom.AsTextZM() as geom;

-- MULTILINESTRING ((0 0, 0 5, 5 0, 0 0), (1 1, 3 1, 1 3, 1 1))

-- 4. Convert LineString to GeometryCollection
DECLARE @geom geometry;
SET @geom = [dbo].[STCollectionForce](
             geometry::STGeomFromText('LINESTRING(0 0,1 1,2 2,3 3,4 4)',0),
			 /* @p_linestring */      1,
			 /* @p_multilinestring */ 0 
			);
SELECT @geom.AsTextZM() as geom;
GO

-- GEOMETRYCOLLECTION (LINESTRING (0 0, 1 1, 2 2, 3 3, 4 4))

-- 5. Convert LineString to MultiLineString
DECLARE @geom geometry;
SET @geom = [dbo].[STCollectionForce](
             geometry::STGeomFromText('LINESTRING(0 0,1 1,2 2,3 3,4 4)',0),
			 /* @p_linestring */      1,
			 /* @p_multilinestring */ 1 
			);
SELECT @geom.AsTextZM() as geom;
GO

-- GEOMETRYCOLLECTION (MULTILINESTRING ((0 0, 1 1, 2 2, 3 3, 4 4)))

Summary

Array processing is a “must have” for the algorithm implementer needs. This is shown by the fact that both Oracle and PostgreSQL provide such a datatype. In TSQL SQL/OGC GeometryCollections can be usefully leveraged to create a form of pseudo array processing.

The documentation for the functions presented and described in this article are available here.

In another article I would show how XML can be used in TSQL to implement an array and its processing.

The Spatial Database Advisor

Using a GeometryCollection as an Array for TSQL (Stored Procedure) Development.