Top 5 Recent Articles
ARTICLES CATEGORIES
- Algorithms (19)
- All (400)
- Biography (1)
- Blog (45)
- Business Requirements (1)
- Commentary (1)
- Customers (2)
- Data Models (1)
- Education (2)
- GeoRaptor (13)
- GPS (1)
- Image Processing (2)
- Import Export (8)
- Licensing (2)
- LiDAR (1)
- Linear Referencing (4)
- Manifold GIS (3)
- Mapping (1)
- MySQL Spatial (7)
- Networking and Routing (including Optimization) (4)
- Open Source (18)
- Oracle Spatial and Locator (193)
- Partitioning (1)
- PostGIS (34)
- Published Articles (1)
- qGIS (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (35)
- Space Curves (9)
- Spatial Database Functions (107)
- Spatial DB comparison (1)
- Spatial XML Processing (11)
- SQL Server Spatial (91)
- Standards (3)
- Stored Procedure (15)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)
Vectorising geometry objects in SQL Server 2008
I demonstrated in this article the usefulness of having a vectorising function in one’s spatial database kitbag (there are GetVector functions in my CENTROID and GEOM packages – in particular the GetVector function is vital to the implementation of the algorithm in my CENTROID.SDO_CENTROID function).
So, I have also added one to my SQL Server 2008 kitbag.
This function, as against the PostGIS and Oracle implementation, does not use any object types due to limitations in the SQL Server 2008 TSQL system (my implementation deliberately does not use the CLR system)
USE [GISDB] -- Change this to your database GO CREATE FUNCTION [dbo].[GetVector]( @p_geom geometry ) RETURNS @Vectors TABLE (sx FLOAT, sy FLOAT, sz FLOAT, sm FLOAT, ex FLOAT, ey FLOAT, ez FLOAT, em FLOAT) AS BEGIN DECLARE @v_GeometryType VARCHAR(1000), @ringn INT, @geomn INT, @FIRST INT, @SECOND INT, @geom geometry, @start_geom geometry, @end_geom geometry; BEGIN IF ( @p_geom IS NULL ) RETURN; SET @v_GeometryType = @p_geom.STGeometryType(); IF ( @v_GeometryType IN ('Point','MultiPoint') ) RETURN; IF ( @v_GeometryType = 'LineString' ) BEGIN SET @FIRST = 1; SET @SECOND = 2; WHILE ( @SECOND <= @p_geom.STNumPoints() ) BEGIN SET @start_geom = @p_geom.STPointN(@FIRST); SET @FIRST = @FIRST + 1; SET @end_geom = @p_geom.STPointN(@SECOND); SET @SECOND = @SECOND + 1; INSERT INTO @Vectors ( [sx],[sy],[sz],[sm],[ex],[ey],[ez],[em] ) VALUES ( @start_geom.STX, @start_geom.STY, @start_geom.Z, @start_geom.M, @end_geom.STX, @end_geom.STY, @end_geom.Z, @end_geom.M ); END; RETURN; END; IF ( @v_GeometryType = 'MultiLineString' ) BEGIN SET @geomn = 1; WHILE ( @geomn <= @p_geom.STNumGeometries() ) BEGIN SET @geom = @p_geom.STGeometryN(@geomn); SET @FIRST = 1; SET @SECOND = 2; WHILE ( @SECOND <= @geom.STNumPoints() ) BEGIN SET @start_geom = @geom.STPointN(@FIRST); SET @FIRST = @FIRST + 1; SET @end_geom = @geom.STPointN(@SECOND); SET @SECOND = @SECOND + 1; INSERT INTO @Vectors ( [sx],[sy],[sz],[sm],[ex],[ey],[ez],[em] ) VALUES ( @start_geom.STX, @start_geom.STY, @start_geom.Z, @start_geom.M, @end_geom.STX, @end_geom.STY, @end_geom.Z, @end_geom.M ); END; SET @geomn = @geomn + 1; END; RETURN; END; IF ( @v_GeometryType = 'Polygon' ) BEGIN SET @ringn = 0; WHILE ( @ringn < ( 1 + @p_geom.STNumInteriorRing() ) ) BEGIN IF ( @ringn = 0 ) SET @geom = @p_geom.STExteriorRing() ELSE SET @geom = @p_geom.STInteriorRingN(@ringn); SET @FIRST = 1; SET @SECOND = 2; WHILE ( @SECOND <= @geom.STNumPoints() ) BEGIN SET @start_geom = @geom.STPointN(@FIRST); SET @FIRST = @FIRST + 1; SET @end_geom = @geom.STPointN(@SECOND); SET @SECOND = @SECOND + 1; INSERT INTO @Vectors ( [sx],[sy],[sz],[sm],[ex],[ey],[ez],[em] ) VALUES ( @start_geom.STX, @start_geom.STY, @start_geom.Z, @start_geom.M, @end_geom.STX, @end_geom.STY, @end_geom.Z, @end_geom.M ); END; SET @ringn = @ringn + 1; END; RETURN; END; IF ( @v_GeometryType = 'MultiPolygon' ) BEGIN SET @geomn = 1; WHILE ( @geomn <= @p_geom.STNumGeometries() ) BEGIN SET @ringn = 0; WHILE ( @ringn < ( 1 + @p_geom.STGeometryN(@geomn).STNumInteriorRing() ) ) BEGIN IF ( @ringn = 0 ) SET @geom = @p_geom.STGeometryN(@geomn).STExteriorRing() ELSE SET @geom = @p_geom.STGeometryN(@geomn).STInteriorRingN(@ringn); SET @FIRST = 1; SET @SECOND = 2; WHILE ( @SECOND <= @geom.STNumPoints() ) BEGIN SET @start_geom = @geom.STPointN(@FIRST); SET @FIRST = @FIRST + 1; SET @end_geom = @geom.STPointN(@SECOND); SET @SECOND = @SECOND + 1; INSERT INTO @Vectors ( [sx],[sy],[sz],[sm],[ex],[ey],[ez],[em] ) VALUES ( @start_geom.STX, @start_geom.STY, @start_geom.Z, @start_geom.M, @end_geom.STX, @end_geom.STY, @end_geom.Z, @end_geom.M ); END; SET @ringn = @ringn + 1; END; SET @geomn = @geomn + 1; END; RETURN; END; IF ( @v_GeometryType = 'GeometryCollection' ) BEGIN SET @geomn = 1; WHILE ( @geomn <= @p_geom.STNumGeometries() ) BEGIN IF NOT ( @p_geom.STGeometryN(@geomn).STGeometryType() IN ('Point','MultiPoint') ) BEGIN INSERT INTO @Vectors ( [sx],[sy],[sz],[sm],[ex],[ey],[ez],[em] ) SELECT [sx],[sy],[sz],[sm],[ex],[ey],[ez],[em] FROM dbo.GetVector(@p_geom.STGeometryN(@geomn)); END; SET @geomn = @geomn + 1; END; RETURN; END; END; RETURN; END GO
Testing
Here’s a bunch of tests.
SELECT v.* FROM dbo.GetVector(geometry::STGeomFromText( 'LINESTRING(0 0 10 10, 1 1 10 20, 2 2 10 30, 3 3 10 40)',0)) AS v
Result Is:
sx | sy | sz | sm | ex | ey | ez | em |
---|---|---|---|---|---|---|---|
0 | 0 | 10 | 10 | 1 | 1 | 10 | 20 |
1 | 1 | 10 | 20 | 2 | 2 | 10 | 30 |
2 | 2 | 10 | 30 | 3 | 3 | 10 | 40 |
SELECT v.* FROM dbo.GetVector(geometry::STGeomFromText( 'MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))',0)) AS v;
Result Is:
sx | sy | sz | sm | ex | ey | ez | em |
---|---|---|---|---|---|---|---|
0 | 0 | NULL | NULL | 1 | 1 | NULL | NULL |
1 | 1 | NULL | NULL | 1 | 2 | NULL | NULL |
2 | 3 | NULL | NULL | 3 | 2 | NULL | NULL |
3 | 2 | NULL | NULL | 5 | 4 | NULL | NULL |
-- Ordinary polygon -- SELECT v.* FROM dbo.GetVector(geometry::STGeomFromText( 'POLYGON((326000.0 5455000.0,327000.0 5455000.0,326500.0 5456000.0,326000.0 5455000.0))',0)) AS v;
Result Is:
sx | sy | sz | sm | ex | ey | ez | em |
---|---|---|---|---|---|---|---|
326000 | 5455000 | NULL | NULL | 327000 | 5455000 | NULL | NULL |
327000 | 5455000 | NULL | NULL | 326500 | 5456000 | NULL | NULL |
326500 | 5456000 | NULL | NULL | 326000 | 5455000 | NULL | NULL |
-- Polygon with a hole -- SELECT v.* FROM dbo.GetVector(geometry::STGeomFromText( 'POLYGON((326000.0 5455000.0,327000.0 5455000.0,326500.0 5456000.0,326000.0 5455000.0), (326500.0 5455500.0,326550.0 5455200.0,326450.0 5455200.0,326500.0 5455500.0))',0)) AS v;
Result is:
sx | sy | sz | sm | ex | ey | ez | em |
---|---|---|---|---|---|---|---|
326000 | 5455000 | NULL | NULL | 327000 | 5455000 | NULL | NULL |
327000 | 5455000 | NULL | NULL | 326500 | 5456000 | NULL | NULL |
326500 | 5456000 | NULL | NULL | 326000 | 5455000 | NULL | NULL |
326500 | 5455500 | NULL | NULL | 326550 | 5455200 | NULL | NULL |
326550 | 5455200 | NULL | NULL | 326450 | 5455200 | NULL | NULL |
326450 | 5455200 | NULL | NULL | 326500 | 5455500 | NULL | NULL |
-- MultiPolygon With a hole -- SELECT v.* FROM dbo.GetVector(geometry::STGeomFromText( 'MULTIPOLYGON(((326000.0 5455000.0,327000.0 5455000.0,326500.0 5456000.0,326000.0 5455000.0), (326500.0 5455500.0,326550.0 5455200.0,326450.0 5455200.0,326500.0 5455500.0)), ((321000.0 5450000.0,322000.0 5450000.0,321500.0 5451000.0,321000.0 5450000.0)))',0)) AS v;
Result is:
sx | sy | sz | sm | ex | ey | ez | em |
---|---|---|---|---|---|---|---|
326000 | 5455000 | NULL | NULL | 327000 | 5455000 | NULL | NULL |
327000 | 5455000 | NULL | NULL | 326500 | 5456000 | NULL | NULL |
326500 | 5456000 | NULL | NULL | 326000 | 5455000 | NULL | NULL |
326500 | 5455500 | NULL | NULL | 326550 | 5455200 | NULL | NULL |
326550 | 5455200 | NULL | NULL | 326450 | 5455200 | NULL | NULL |
326450 | 5455200 | NULL | NULL | 326500 | 5455500 | NULL | NULL |
321000 | 5450000 | NULL | NULL | 322000 | 5450000 | NULL | NULL |
322000 | 5450000 | NULL | NULL | 321500 | 5451000 | NULL | NULL |
321500 | 5451000 | NULL | NULL | 321000 | 5450000 | NULL | NULL |
But what if you want the start and end of the vector to be geometry points?
-- Get as points -- SELECT geometry::Point(v.sx,v.sy,0).STAsText() AS startPoint, geometry::Point(v.ex,v.ey,0).STAsText() AS endPoint FROM dbo.GetVector(geometry::STGeomFromText( 'LINESTRING(0 0 10 10, 1 1 10 20, 2 2 10 30, 3 3 10 40)',0)) AS v;
Result is:
startPoint | endPoint |
---|---|
POINT (0 0) | POINT (1 1) |
POINT (1 1) | POINT (2 2) |
POINT (2 2) | POINT (3 3) |
Finally, what if you want each vector to be represented as a single linestring and not a individual ordinates (sx,sy, …. ,ez,em)?
-- Get as linestring -- SELECT geometry::STGeomFromText( 'LINESTRING(' + CAST(v.sx AS VARCHAR(10)) + ' ' + CAST(v.sy AS VARCHAR(10)) + ',' + CAST(v.ex AS VARCHAR(10)) +' ' + CAST(v.ey AS VARCHAR(10)) + ')',0).STAsText() AS vLine FROM dbo.GetVector(geometry::STGeomFromText( 'LINESTRING(0 0 10 10, 1 1 10 20, 2 2 10 30, 3 3 10 40)',0)) AS v;
Result is:
vLine |
---|
LINESTRING (0 0, 1 1) |
LINESTRING (1 1, 2 2) |
LINESTRING (2 2, 3 3) |
I hope this is useful to someone.
The function is available in the package of SQL Server functions downloadable from my website’s download page’s SQL Server Spatial TSQL Functions link
Documentation
- MySQL Spatial General Function Documentation
- Oracle LRS Object Documentation
- Oracle Spatial Exporter Package Documentation
- Oracle Spatial Object Function Documentation
- Oracle Spatial Object Function Documentation (Multi Page Version)
- PostGIS pl/pgSQL Function Documentation
- SC4O Oracle Java Topology Suite (Stored Procedures) Package Documentation
- SQL Server Spatial General TSQL Function Documentation
- SQL Server Spatial LRS TSQL Function Documentation