Go to content Go to navigation Go to search

Featured Articles:

    Applying And Extending Oracle Spatial - Book Released
    Analyzing Spatial Query Performance Improvements in Oracle Spatial and Graph 12c Through Cross-Vendor Comparison

Newest Article:

SQL Server Spatial: STFlipVectors

Wednesday August 01 2018 at 15:51

In the past I have found use for the ability to “flip” vectors in a polygon in order to provide an independent check on the topological integrity of planar enforced polygons.

What I mean by topological integrity is that there are no gaps or overlaps between adjacent polygons.

A simple method for doing this relies on the fact that if a linestring is part of a shared boundary there will be two versions of it: one in one polygon and one in the other.
If they are exactly the same then there cannot be a gap or an overlap.
With normal ring ordering (anticlockwise/clockwise vertex order for their outer/inner rings), the segments that define a specific part of a the polygon boundary will have different direction.

This can be seen in the following diagram.

Two segments with different direction will not be metrically equal because their start and end points are different due to their different directions.
To make them the same requires a process that will normalize the direction.

I call this Flipping.

This flipping is different from simply reversing the coordinate order of a linestring segment.
After all, if we flip each of the two segments with different direction, we will still end up with two segments with different direction!
Hence we need a process that provides that all segments are flipped to the same direction.

This is what STFlipVectors() does, whose call signature is:

  1. CREATE FUNCTION [dbo].[STFlipVectors]
  2. (
  3.   @p_line_collection geometry
  4. )
  5. RETURNS geometry
  6. AS
  7. BEGIN
  8.  ...
  9. END

Its documentation can be seen see here.

Here are some examples of how to use it:

  1. -- Process geometry extracting segments with no flipping
  2. WITH gc AS (
  3. SELECT geometry::STGeomFromText(
  5. POLYGON((10 0,20 0,20 20,10 20,10 0)),
  6. POLYGON((20 0,30 0,30 20,20 20,20 0)),
  7. POINT(0 0))',0) AS geom
  8. )
  9. SELECT v.sx,v.sy,v.ex,v.ey,COUNT(*) AS shareCount
  10.   FROM gc AS a
  11.        CROSS apply
  12.      [dbo].[STVectorize](a.geom) AS v
  13. GROUP BY v.sx,v.sy,v.ex,v.ey
  14. GO

Note: 8 Rows Produced

sx sy ex ey shareCount
10 0 20 0 1
10 20 10 0 1
20 0 20 20 1
20 0 30 0 1
20 20 10 20 1
20 20 20 0 1
30 0 30 20 1
30 20 20 20 1
  1. -- Process geometry extracting segments with flipping
  2. --
  3. WITH gc AS (
  4. SELECT geometry::STGeomFromText(
  6. POLYGON((10 0,20 0,20 20,10 20,10 0)),
  7. POLYGON((20 0,30 0,30 20,20 20,20 0)),
  8. POINT(0 0))',0) AS geom
  9. )
  10. SELECT v.sx,v.sy,v.ex,v.ey,COUNT(*) AS shareCount
  11.   FROM gc AS a
  12.        CROSS apply
  13.             [dbo].[STVectorize] (
  14.                 [dbo].[STFlipVectors] ( a.geom )
  15.               ) AS v
  16. GROUP BY v.sx,v.sy,v.ex,v.ey
  17. GO

Note: 7 Rows Produced

sx sy ex ey shareCount
10 0 10 20 1
10 0 20 0 1
10 20 20 20 1
20 0 20 20 2
20 0 30 0 1
20 20 30 20 1
30 0 30 20 1

Note that one segment, the shared boundary, is recorded as existing twice.

What can we do with such information?

There are many things one can do, for example provide an independent check that data editing has not created gaps or overlaps at particular tolerances.

But we will concentrate here on a simple implementation of a boundary “dissolve” operation.
(Yes this can be done via STUnion() but here we are simple demonstrating a use for flipped vector processing.)

  1. WITH gc AS (
  2. SELECT geometry::STGeomFromText(
  4. POLYGON((10 0,20 0,20 20,10 20,10 0)),
  5. POLYGON((20 0,30 0,30 20,20 20,20 0)),
  6. POINT(0 0))',0) AS geom
  7. )
  8. SELECT geometry::STGeomFromText(
  9.          REPLACE(
  10.             geometry::UnionAggregate(vector).STAsText(),
  12.                'POLYGON (('   COLLATE DATABASE_DEFAULT
  13.          )
  14.          +  
  15.          ')' COLLATE DATABASE_DEFAULT,
  16.          0).STAsText() AS polygon
  17.   FROM (SELECT [dbo].[STMakeLine] (
  18.                   geometry::Point(v.sx,v.sy,0),
  19.                   geometry::Point(v.ex,v.ey,0),
  20.                   3,2
  21.                ) AS vector
  22.          FROM gc AS a
  23.               CROSS apply
  24.               [dbo].[STVectorize] (
  25.                 [dbo].[STFlipVectors] ( a.geom )
  26.               ) AS v
  27.         GROUP BY v.sx,v.sy,v.ex,v.ey
  28.        HAVING COUNT(*) = 1  /* Get rid of duplicate lines */
  29.      ) AS f
  30. GO


POLYGON ((10 0, 20 0, 30 0, 30 20, 20 20, 10 20, 10 0))

I hope this function is of interest to someone.

My Services

I offer the following services to customers:

  1. Integration of GeoSpatial within IT
    Advice and guidance on how to fully integrate geospatial data and processing needs within existing, pure, IT environments (using existing GIS software or without purchasing proprietary GIS software);
  2. Independent Solutions Advice and Sanity Checking
    Solutions not Software ;
    How to efficiently and effectively solve a business requirement via existing technology;
    Does what you have been offered in a tender really stack up? Or do you want independent quality assurance of a tender document before submitting it to a customer?
    When it is best to use Open Source as against Commercial off the shelf (COTS) and vice versa?
    Is buzzword-compliant (SOA, BPEL, J2EE, .NET, WebServices…) technology really needed to solve a business problem? If so, what is the best way to achieve this?
  3. Analyzing ROI of your current GIS software technology
    Matching business problems to existing software investment. Are you getting value from your software investment? How to improve that ROI;
  4. Systems Review
    Does the solution you have been provided actually match your requirements?;
  5. Database Benchmarking:
    How well are you using your Oracle Spatial/SQL Server Spatial/PostGIS database?
    How good is your databases documentation/metadata?
    Is the integrity and quality of the data stored in your databases as good as you think?
    Is your use of Oracle (Spatial), SQL Server Spatial consistent with industry best-practice?
    Get advice on some simple “rules” to follow to improve your database!
  6. Database Consulting through design, test, build and configuration services
    Ensuring your databases reflect your needs via:
    1. Data Quality Rules Documentation, Conformance Testing, Correction and Certification;
    2. Data Conversion, Migration, Loading;
    3. Database Design;
    4. Coding of appropriate in-Database processing:
      Via design and coding services in:
      1. PL/SQL – Oracle,
      2. PL/pgSQL – PostgreSQL,
      3. T-SQLSQL Server,
      4. Java,
      5. C#.
    5. Database Performance Tuning;
    6. Spatially enabling Data Warehouses and Processes;
    7. ArcSDE Configuration and Tuning.
  7. SpatialDB Training Courses and Staff Mentoring
    Spatial SQL Training on your datasets;
    After Training support
  8. Free Software
  9. Commercial Support
    Free PL/SQL packages
    (bug fixes, extensions and new functionality).

I have over 25 years experience with database technologies from mainframes through to personal computers. I have 25 years Oracle use and am recognised by the Oracle Spatial development team as one of the early adopters of Oracle’s Sdo_Geometry data type. In 2011 that experience was recognised when Oracle awarded me the “2011 Oracle Spatial Excellence Award for Education and Research”.

I was on the original beta program for SQL Server 2008 Spatial (aka “Katmai”). While most of my solutions work centres on Oracle, I now have quite a few years experience designing, programming, tuning and working with SQL Server. I make available over 200 TSQL functions to SQL Server Spatial users across multiple versions.

I also have the necessary PostgreSQL/PostGIS and MySQL experience to help you implement the best spatial database solution possible.

I have (next to the original designer and programmer) the longest experience of using ArcSDE in the industry and can help you get the best out of this piece of ESRI software and show you how to make it work with other spatial software (esp open source). Also, with the new ESRI Spatial Type for Oracle, PostgreSQL and SQL Server, I am in a unique position to help you plan your migrations from SDEBINARY to either the ESRI Spatial Type, SQL Server spatial or Oracle’s Sdo_Geometry.

My experience is not limited just to databases. I have managed “GIS” infrastructure management teams, designed and built systems integrating spatial processing within business systems in the areas of mapping, editing, analysis, GPS field data capture etc.

Finally, as a geospatial professional with IT and Oracle (Certified Professional SQL 10gR2; Certified Associate DBA 10gR2) qualifications and experience, I can help guide the uptake of geospatial technologies into business’s IT infrastructures.

Let me help you cost-effectively spatially-enable your enterprise.