Go to content Go to navigation and search

Home

Current SQL Server Blog Articles


Search

Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Alternate Centroid Functions for SQL Server Spatial

Saturday December 31 2011 at 01:04

Keywordsgeometry STCentroid centroid multi-centroid multipoint multilinestring multipolygon
Summary

This article presents a collection of centroid functions for use with Sql Server Spatial (Denali edition) geometry object type.

The top download from my website is for my package of centroid algorithms for Oracle sdo_geometry.

There is nothing wrong with the STCentroid function implemented in SQL Server Spatial (for one thing it handles geography objects natively), but it seems that having a choice of centroid algorithms is a big thing for users of spatial data within databases.

The original algorithm for getting the centroid of a polygon involves use of LAG/LEAD SQL99 analytic functions that have not been a part of SQL Server until Denali.

For the polygon centroid function (STCentroid_P) one can ask the function to generate a centroid:

  • In the middle of the MBR of the geometry, or
  • In a weighted position based on coordinate density, or
  • With a defined or fixed (supplied) “seed” X ordinate position.

Here are the functions.

Centroid for MultiPoint Geometries

SQL Server Spatial returns null when executing STCentroid against multipoint geometries, so let’s build our own.

  1. USE [GISDB] -- Change to your database
  2. GO
  3. -- MultiPoint Centroid Creator
  4. --
  5. DROP   FUNCTION [dbo].[STCENTROID_P] ;
  6. CREATE FUNCTION [dbo].[STCENTROID_P] (
  7.     @p_geometry  GEOMETRY,
  8.     @p_round_x   INT = 3,
  9.     @p_round_y   INT = 3,
  10.     @p_round_z   INT = 3 )
  11. RETURNS GEOMETRY
  12. AS
  13. BEGIN
  14.   DECLARE
  15.      @v_x         FLOAT = 0.0,
  16.      @v_y         FLOAT = 0.0,
  17.      @v_z         FLOAT,
  18.      @v_geomn     INT,
  19.      @v_part_geom geometry,
  20.      @v_geometry  geometry,
  21.      @v_gtype     nvarchar(MAX);
  22.   BEGIN
  23.       IF ( @p_geometry IS NULL )
  24.        RETURN @p_geometry;
  25.       SET @v_gtype = @p_geometry.STGeometryType();
  26.       IF ( @v_gtype = 'MultiPoint' )
  27.       BEGIN
  28.         -- Get parts of multi-point geometry
  29.         --
  30.         SET @v_geomn  = 1;
  31.         WHILE ( @v_geomn <= @p_geometry.STNumGeometries() )
  32.         BEGIN
  33.           SET @v_part_geom = @p_geometry.STGeometryN(@v_geomn);
  34.           SET @v_x = @v_x + @v_part_geom.STX
  35.           SET @v_y = @v_y + @v_part_geom.STY
  36.           SET @v_geomn = @v_geomn + 1;
  37.         END;
  38.         SET @v_geometry = geometry::Point(@v_x/@p_geometry.STNumGeometries(),
  39.                                           @v_y/@p_geometry.STNumGeometries(),
  40.                                            @p_geometry.STSrid);
  41.       END
  42.       ELSE
  43.          SET @v_geometry = @p_geometry;
  44.       RETURN @v_geometry;
  45.     END;
  46. END
  47. GO

Now let’s test noting that SQL Server’s STCentroid always returns NULL for MultiPoint geometries

  1. WITH multiPoint AS (
  2.    SELECT geometry::STGeomFromText('MULTIPOINT((0 0),(100 0),(100 100),(0 100),(150 110),(150 150),(110 150),(110 110))',0) AS geom
  3. )
  4. SELECT 'O' AS label,  geom.STBuffer(2) AS geom FROM multiPoint
  5. UNION ALL
  6. SELECT 'M' AS label, dbo.STCentroid_P(geom,3,3,3).STBuffer(5) AS geom FROM multiPoint
  7. UNION ALL
  8. SELECT 'S' AS label, geom.STCentroid().STBuffer(5) AS geom FROM multiPoint;

Which looks like this:

Centroid for Area Geometries

  1. DROP FUNCTION [dbo].[STCENTROID_A];
  2. CREATE FUNCTION [dbo].[STCENTROID_A] (
  3.   @p_geometry  geometry,
  4.   @p_start     INT = 0,  /* 0 = use average of all Area's vertices for starting X centroid calculation
  5.                             1 = centre X of MBR
  6.                             2 = User supplied starting seed X */
  7.   @p_seed_x    FLOAT = NULL,
  8.   @p_round_x   INT = 3,
  9.   @p_round_y   INT = 3,
  10.   @p_round_z   INT = 3
  11. )
  12. RETURNS geometry
  13. AS
  14. BEGIN
  15.   DECLARE
  16.      @v_cx    FLOAT,
  17.      @v_cy    FLOAT,
  18.      @v_seed  geometry,
  19.      @v_wkt   nvarchar(MAX);
  20.   BEGIN
  21.     IF ( @p_geometry IS NULL )
  22.        RETURN @p_geometry;
  23.     IF ( @p_start NOT IN (0,1,2) )
  24.        RETURN CAST('Starting position must be 0, 1 or 2.' AS VARCHAR(MAX)); -- geometry);
  25.     IF ( @p_start = 2 AND @p_seed_x IS NULL )
  26.        RETURN CAST('Seed X value not provided.' AS VARCHAR(MAX)); -- geometry);
  27.     -- Create starting seed point geometry
  28.     --
  29.   SET @v_seed = CASE @p_start
  30.                      WHEN 0 THEN @p_geometry
  31.                WHEN 1 THEN geometry::Point(@p_geometry.STEnvelope().STPointN(1).STX,
  32.                                                    @p_geometry.STEnvelope().STPointN(1).STY,
  33.                                                    @p_geometry.STSrid).STUnion(
  34.                                    geometry::Point(@p_geometry.STEnvelope().STPointN(3).STX,
  35.                                                    @p_geometry.STEnvelope().STPointN(3).STY,
  36.                                                    @p_geometry.STSrid))
  37.                    WHEN 2 THEN geometry::Point(@p_seed_x,0,@p_geometry.STSrid)
  38.                ELSE @p_geometry
  39.           END;
  40.     -- Check user seed X between MBR of object
  41.   --
  42.   IF ( @p_start = 2 AND
  43.      ( @p_seed_x <= @p_geometry.STEnvelope().STPointN(1).STX OR @p_seed_x >= @p_geometry.STEnvelope().STPointN(3).STX ) )
  44.        RETURN CAST('Seed X value not between provided geometry''s MBR.' AS VARCHAR(MAX)); -- geometry);
  45.     SELECT TOP 1
  46.            @v_cx = cx,
  47.            @v_cy = cy
  48.       FROM (SELECT z.x                 AS cx,
  49.                    z.y + ( ydiff / 2 ) AS cy,
  50.                    ydiff
  51.               FROM (SELECT w.id,
  52.                            w.x,
  53.                            w.y,
  54.                            CASE WHEN w.ydiff IS NULL THEN 0 ELSE w.ydiff END AS ydiff,
  55.                            CASE WHEN w.id = 1
  56.                                 THEN CASE WHEN w.INOUT = 1
  57.                                           THEN 'INSIDE'
  58.                                           ELSE 'OUTSIDE'
  59.                                       END
  60.                                 WHEN SUM(w.INOUT) OVER (ORDER BY w.id) % 2 = 1
  61.                                 /* Need to look at previous result as inside/outside is a binary switch */
  62.                                 THEN 'INSIDE'
  63.                                 ELSE 'OUTSIDE'
  64.                             END AS INOUT
  65.                       FROM (SELECT ROW_NUMBER() OVER (ORDER BY u.y ASC) AS id,
  66.                                    u.x,
  67.                                    u.y,
  68.                                    CASE WHEN u.touchCross IN (0)         /* Cross */ THEN 1
  69.                                         WHEN u.touchCross IN (-1,-2,1,2) /* Touch */ THEN 0
  70.                                         ELSE 0
  71.                                     END AS INOUT,
  72.                                    ABS(LEAD(u.y,1) OVER(ORDER BY u.y) - u.y) AS YDiff
  73.                               FROM (SELECT s.x,
  74.                                            s.y,
  75.                                            /* In cases where polygons have boundaries/holes that touch at a point we need to count them more than once */
  76.                                            CASE WHEN COUNT(*) > 2 THEN 1 ELSE SUM(s.touchcross) END AS touchcross
  77.                                       FROM (SELECT t.x,
  78.                                                    t.y,
  79.                                                    t.touchcross
  80.                                               FROM (SELECT r.x,
  81.                                                            CASE WHEN (r.endx = r.startx)
  82.                                                                 THEN (r.starty + r.endy ) / 2
  83.                                                                 ELSE round(r.starty + ( (r.endy-r.starty)/(r.endx-r.startx) ) * (r.x-r.startx),@p_round_y)
  84.                                                             END AS y,
  85.                                                            CASE WHEN ( r.x = r.startx AND r.x = r.endx )
  86.                                                                 THEN 99  /* Line is Vertical */
  87.                                                                 WHEN ( ( r.x = r.startx AND r.x > r.endx )
  88.                                                                     OR ( r.x = r.endX   AND r.x > r.startX ) )
  89.                                                                 THEN -1 /* Left Touch */
  90.                                                                 WHEN ( ( r.x = r.endX   AND r.x < r.startX  )
  91.                                                                     OR ( r.x = r.startX AND r.x < r.endX ) )
  92.                                                                 THEN 1 /* Right Touch */
  93.                                                                 ELSE 0 /* cross */
  94.                                                             END AS TouchCross
  95.                                                        FROM (SELECT c.x,
  96.                                                                     round(v.sx,@p_round_x) AS startX,
  97.                                                                     round(v.sy,@p_round_y) AS startY,
  98.                                                                     round(v.ex,@p_round_x) AS endX,
  99.                                                                     round(v.ey,@p_round_y) AS endY
  100.                                                                FROM (SELECT round(avg(p.x),@p_round_x) AS x
  101.                                                                        FROM [dbo].[STDUMPPOINTS](@v_seed) p
  102.                                                                     ) c
  103.                                                                     CROSS apply
  104.                                                                     [dbo].[STVECTORIZE](@p_geometry) v
  105.                                                             ) r
  106.                                                       WHERE r.x BETWEEN r.StartX AND r.endx
  107.                                                          OR r.x BETWEEN r.endx   AND r.startx
  108.                                                    ) t
  109.                                            ) s
  110.                                      GROUP BY s.x,s.y
  111.                                    ) u
  112.                            ) w
  113.                    ) z
  114.              WHERE z.INOUT = 'INSIDE'
  115.            ) f
  116.         ORDER BY f.ydiff DESC;
  117.      RETURN geometry::Point(@v_cx, @v_cy, @p_geometry.STSrid);
  118.   END;
  119. END
  120. GO

Now let’s test this function weighting for area (vertex density), MBR (will be the same as the geometry is balanced around its centroid spine of X=2300 ) and via a supplied seed. Comparison to STCentroid is provided.

  1. WITH poly AS (
  2.    SELECT geometry::STGeomFromText('POLYGON((2300 -700, 2800 -300, 2300 700, 2800 1100, 2300 1100, 1800 1100, 2300 400, 2300 200, 2100 100, 2500 100, 2300 -200, 1800 -300, 2300 -500, 2200 -400, 2400 -400, 2300 -700),
  3.                                                     (2300 1000, 2400  900, 2200 900, 2300 1000))',0) AS geom
  4. )
  5. SELECT 'O' AS Label, geom FROM poly
  6. UNION ALL
  7. SELECT 'A' AS Label, [GISDB].[dbo].[STCentroid_A](geom,0,NULL,3,3,3).STBuffer(15) AS geom FROM poly
  8. UNION ALL
  9. SELECT 'M' AS Label, [GISDB].[dbo].[STCentroid_A](geom,1,NULL,3,3,3).STBuffer(15) AS geom FROM poly
  10. UNION ALL
  11. SELECT 'U' AS Label, [GISDB].[dbo].[STCentroid_A](geom,2,2050,3,3,3).STBuffer(15) AS geom FROM poly
  12. UNION ALL
  13. SELECT 'S' AS Label, geom.STCentroid().STBuffer(15) AS geom  FROM poly;

This looks like:

Testing a different geometry shows all four positions more clearly.

  1. WITH weightedPoly AS (
  2.    SELECT geometry::STGeomFromText('POLYGON((258.72254365233152 770.97400259630615, 268.79365642517564 739.08214548229967, 278.86476919801976 707.1902883682933, 332.57737065318844 693.76213800450114, 366.14774656266889 676.97695004976094, 426.57442319973364 697.11917559544918, 520.57147574627891 737.40362668682576, 631.35371624756431 744.11770186872184, 829.41893411349884 797.83030332389046, 1547.8249785763801 791.11622814199438, 1205.4071442996797 895.18439346138371, 832.77597170444687 1039.5370098721496, 490.3581374277465 1086.5355361454222, 416.50331042688953 1076.464423372578, 381.25441572193506 1059.6792354178378, 346.00552101698065 1042.8940474630976, 320.82773908487036 1019.3947843264614, 295.64995715276001 995.89552118982499, 287.25736317538986 964.00366407581862, 278.86476919801976 932.11180696181225, 282.2218067889678 891.82735587043567, 277.18625040254574 858.25697996095528, 272.15069401612368 824.68660405147489, 258.72254365233152 770.97400259630615))',0) AS geom
  3. )
  4. SELECT 'O' AS Label,  geom FROM weightedPoly
  5. UNION ALL
  6. SELECT 'A' AS Label, [GISDB].[dbo].[STCentroid_A](geom,0,NULL,2,2,2).STBuffer(10) AS geom FROM weightedPoly
  7. UNION ALL
  8. SELECT 'M' AS Label, [GISDB].[dbo].[STCentroid_A](geom,1,NULL,2,2,2).STBuffer(10) AS geom FROM weightedPoly
  9. UNION ALL
  10. SELECT 'U' AS Label, [GISDB].[dbo].[STCentroid_A](geom,2,1200,2,2,2).STBuffer(10) AS geom FROM weightedPoly
  11. UNION ALL
  12. SELECT 'S' AS Label, geom.STCentroid().STBuffer(10) AS geom FROM weightedPoly;

Looks like this:

Centroid for Linear geometries

  1. DROP FUNCTION [dbo].[STCentroid_L];
  2. CREATE FUNCTION [dbo].[STCENTROID_L] (
  3.   @p_geometry          geometry,
  4.   @p_position_as_ratio FLOAT = 0.5,
  5.   @p_round_x           INT = 3,
  6.   @p_round_y           INT = 3,
  7.   @p_round_z           INT = 3
  8. )
  9. RETURNS geometry
  10. AS
  11. BEGIN
  12.   DECLARE
  13.     @v_geometry geometry;
  14.   BEGIN
  15.     IF ( @p_geometry IS NULL )
  16.        RETURN @p_geometry;
  17.     SELECT TOP 1
  18.            @v_geometry = geometry::STGeomFromText('POINT(' +
  19.                      LTRIM(STR(round(i3.x2-((i3.x2-i3.x1)*i3.vectorPositionRatio),@p_round_x),24,15)) + ' ' +
  20.                      LTRIM(STR(round(i3.y2-((i3.y2-i3.y1)*i3.vectorPositionRatio),@p_round_y),24,15)) +
  21.                      CASE WHEN i3.z2 IS NOT NULL
  22.                           THEN ' ' + LTRIM(STR(round(i3.z2-((i3.z2-i3.z1)*vectorPositionRatio),@p_round_z),24,15))
  23.                           ELSE ''
  24.                       END + ')',@p_geometry.STSrid)
  25.             FROM (SELECT /* select vector/segment "containing" centroid or mid-point of linestring */
  26.                          i2.SRID,
  27.                          i2.X1,i2.Y1,i2.Z1,
  28.                          i2.X2,i2.Y2,i2.Z2,
  29.                          (i2.cumLength-i2.pointDistance)/i2.vectorLength AS vectorPositionRatio,
  30.                          CASE WHEN pointDistance BETWEEN CASE WHEN lag(cumLength,1) OVER (ORDER BY VID) IS NULL
  31.                                                               THEN 0
  32.                                                               ELSE lag(cumLength,1) OVER (ORDER BY VID)
  33.                                                            END
  34.                                                      AND vectorLength +
  35.                                                          CASE WHEN lag(cumLength,1) OVER (ORDER BY VID) IS NULL
  36.                                                               THEN 0
  37.                                                               ELSE lag(cumLength,1) OVER (ORDER BY VID)
  38.                                                           END
  39.                               THEN 1
  40.                               ELSE 0
  41.                           END AS RightSegment
  42.                     FROM (SELECT i1.VID,
  43.                                  i1.SRID,
  44.                                  i1.X1,i1.Y1,i1.Z1,
  45.                                  i1.X2,i1.Y2,i1.Z2,
  46.                                  i1.vectorLength,
  47.                                  i1.pointDistance,
  48.                                  /* generate cumulative length */
  49.                                 SUM(vectorLength) OVER (ORDER BY vid ROWS UNBOUNDED PRECEDING) AS cumLength
  50.                             FROM (SELECT v.id AS vid,
  51.                                          @p_geometry.STSrid AS srid,
  52.                                          (@p_geometry.STLength() * @p_position_as_ratio) AS pointDistance,
  53.                                          v.sx AS X1, v.sy AS Y1, v.sz AS Z1,
  54.                                          v.ex AS X2, v.ey AS Y2, v.ez AS Z2,
  55.                                          geometry::Point(v.sX,v.sy/*,v.sz*/,@p_geometry.STSrid).STDistance(geometry::Point(v.eX,v.ey,/*,v.sz*/@p_geometry.STSrid)) AS vectorLength
  56.                                     FROM [dbo].[STVECTORIZE](@p_geometry) AS v
  57.                                  ) i1
  58.                        ) i2
  59.                       )  i3
  60.                 WHERE i3.rightSegment = 1;
  61.      RETURN @v_geometry;
  62.   END;
  63. END
  64. GO

Testing a range of percentage values (5/10, 0.5 or 50% is centroid)

  1. WITH line AS (
  2.   SELECT geometry::STGeomFromText('LINESTRING(258.72254365233152 770.97400259630615, 268.79365642517564 739.08214548229967, 278.86476919801976 707.1902883682933, 332.57737065318844 693.76213800450114, 366.14774656266889 676.97695004976094, 426.57442319973364 697.11917559544918, 520.57147574627891 737.40362668682576, 631.35371624756431 744.11770186872184, 829.41893411349884 797.83030332389046, 1547.8249785763801 791.11622814199438, 1205.4071442996797 895.18439346138371, 832.77597170444687 1039.5370098721496, 490.3581374277465 1086.5355361454222, 416.50331042688953 1076.464423372578, 381.25441572193506 1059.6792354178378, 346.00552101698065 1042.8940474630976, 320.82773908487036 1019.3947843264614, 295.64995715276001 995.89552118982499, 287.25736317538986 964.00366407581862, 278.86476919801976 932.11180696181225, 282.2218067889678 891.82735587043567, 277.18625040254574 858.25697996095528, 272.15069401612368 824.68660405147489, 258.72254365233152 770.97400259630615)',0) AS geom
  3. )
  4. SELECT 'O' AS label, geom FROM line
  5. UNION ALL
  6. SELECT CAST(a.intValue*10 AS VARCHAR) + '%' AS label, [dbo].[STCentroid_L](geom,CAST(a.IntValue AS FLOAT)/10.0,3,3,2).STBuffer(15) AS geom
  7.   FROM line
  8.        CROSS apply
  9.      dbo.generate_series(0,9,1) a;

This looks like:

Generic Centroid function

Selects the right function out of those above.

  1. DROP FUNCTION [dbo].[STCentroid];
  2. CREATE FUNCTION [dbo].[STCentroid] (
  3.   @p_geometry     geometry,
  4.   @p_start        INT = 0,  /* 0 = use average of all Area's vertices for starting X centroid calculation otherwise centre X of MBR */
  5.   @p_largest      INT = 1,
  6.   @p_round_x      INT = 3,
  7.   @p_round_y      INT = 3,
  8.   @p_round_z      INT = 3
  9. )
  10. RETURNS geometry
  11. AS
  12. BEGIN
  13.   DECLARE
  14.      @v_geomn    INT,
  15.      @v_geom     geometry,
  16.      @v_val      FLOAT,
  17.      @v_test_val FLOAT,
  18.      @v_geometry geometry,
  19.      @v_gtype    nvarchar(MAX);
  20.   BEGIN
  21.       IF ( @p_geometry IS NULL )
  22.        RETURN @p_geometry;
  23.       SET @v_gtype = @p_geometry.STGeometryType();
  24.       SET @v_geometry = @p_geometry;
  25.       IF ( @v_gtype IN ('MultiPolygon','MultiLineString') )
  26.       BEGIN
  27.         SET @v_test_val = -9999999999.9999999;
  28.         IF ( @p_largest = 0 )
  29.            SET @v_test_val = 9999999999.9999999;
  30.         -- Get parts of multi-part geometry
  31.         --
  32.         SET @v_geomn  = 1;
  33.         WHILE ( @v_geomn <= @p_geometry.STNumGeometries() )
  34.         BEGIN
  35.           SET @v_geometry = @p_geometry.STGeometryN(@v_geomn);
  36.           IF ( @v_geometry.STGeometryType() = 'LineString' )
  37.              SET @v_val = @v_geometry.STLength()
  38.           ELSE
  39.              SET @v_val = @v_geometry.STArea();
  40.           IF ( @p_largest = 1 )
  41.           BEGIN
  42.              IF ( @v_val >= @v_test_val )
  43.              BEGIN
  44.                 SET @v_test_val  = @v_val;
  45.                 SET @v_geom = @v_geometry;
  46.              END;
  47.           END;
  48.           ELSE
  49.           BEGIN
  50.              IF ( @v_val <= @v_test_val )
  51.              BEGIN
  52.                 SET @v_test_val  = @v_val;
  53.                 SET @v_geom = @v_geometry;
  54.              END;
  55.           END;
  56.           SET @v_geomn = @v_geomn + 1;
  57.         END;
  58.         SET @v_geometry = @v_geom;
  59.       END;
  60.       -- Now get centroid
  61.       --
  62.       IF ( @v_gtype = 'Point' )                            SET @v_geometry = @p_geometry;
  63.       IF ( @v_gtype = 'MultiPoint' )                       SET @v_geometry = dbo.STCentroid_P(@p_geometry,@p_round_x,@p_round_y,@p_round_z)
  64.       IF ( @v_gtype IN ('LineString','MultiLineString' ) ) SET @v_geometry = dbo.STCentroid_L(@v_geometry,0.5,@p_round_x,@p_round_y,@p_round_z)
  65.       IF ( @v_gtype IN ('Polygon','MultiPolygon') )        SET @v_geometry = dbo.STCentroid_A(@v_geometry,@p_start,NULL,@p_round_x,@p_round_y,@p_round_z)
  66.     RETURN @v_geometry;
  67.   END;
  68. END
  69. GO

Testing this we get.

  1. WITH mPoly AS (
  2.    SELECT geometry::STGeomFromText('MULTIPOLYGON (((0 0, 100 0, 100 100, 0 100, 0 0)), ((110 110, 150 110, 150 150, 110 150, 110 110)))',0) AS geom
  3. )
  4. SELECT geom FROM mPoly
  5. UNION ALL
  6. SELECT dbo.STCentroid(geom,1 /* Weight By MBR not Vertices */,1 /* Use Largest part in any multipart */,3,3,3).STBuffer(5) AS geom
  7.   FROM mPoly;

This looks like:

MultiCentroid function for multi part geometries

  1. DROP   FUNCTION [dbo].[STMULTICENTROID];
  2. CREATE FUNCTION [dbo].[STMULTICENTROID] (
  3.     @p_geometry  GEOMETRY,
  4.     @p_start     INT = 0,  /* 0 = use average of all Area's vertices for starting X centroid calculation otherwise centre X of MBR */
  5.     @p_round_x   INT = 3,
  6.     @p_round_y   INT = 3,
  7.     @p_round_z   INT = 3 )
  8. RETURNS GEOMETRY
  9. AS
  10. BEGIN
  11.   DECLARE
  12.      @v_geomn     INT,
  13.      @v_centroid  geometry,
  14.      @v_part_geom geometry,
  15.      @v_geometry  geometry,
  16.      @v_gtype     nvarchar(MAX);
  17.   BEGIN
  18.       IF ( @p_geometry IS NULL )
  19.        RETURN @p_geometry;
  20.       SET @v_gtype = @p_geometry.STGeometryType();
  21.       IF ( @v_gtype = 'Point' )      SET @v_geometry = @p_geometry;
  22.       IF ( @v_gtype = 'LineString')  SET @v_geometry = [dbo].[STCENTROID_L](@p_geometry,0.5,@p_round_x,@p_round_y,@p_round_z)
  23.       IF ( @v_gtype = 'Polygon' )    SET @v_geometry = [dbo].[STCENTROID_A](@p_geometry,@p_start,NULL,@p_round_x,@p_round_y,@p_round_z)
  24.       IF ( @v_gtype = 'MultiPoint' ) SET @v_geometry = @p_geometry;
  25.       IF ( @v_gtype IN ('MultiPolygon','MultiLineString') )
  26.       BEGIN
  27.         -- Get parts of multi-part geometry
  28.         --
  29.         SET @v_geomn  = 1;
  30.         WHILE ( @v_geomn <= @p_geometry.STNumGeometries() )
  31.         BEGIN
  32.           SET @v_part_geom = @p_geometry.STGeometryN(@v_geomn);
  33.           IF ( @v_part_geom.STGeometryType() = 'LineString' )
  34.              SET @v_centroid = [dbo].[STCENTROID_L](@v_part_geom,0.5,@p_round_x,@p_round_y,@p_round_z)
  35.           ELSE
  36.              SET @v_centroid = [dbo].[STCENTROID_A](@v_part_geom,@p_start,NULL,@p_round_x,@p_round_y,@p_round_z);
  37.           SET @v_geometry = CASE WHEN @v_geometry IS NULL THEN @v_centroid ELSE @v_geometry.STUnion(@v_centroid) END;
  38.           SET @v_geomn = @v_geomn + 1;
  39.         END;
  40.       END;
  41.       RETURN @v_geometry;
  42.     END;
  43. END
  44. GO

Testing we get.

  1. WITH mPoly AS (
  2.   SELECT geometry::STGeomFromText('MULTIPOLYGON (((0 0, 100 0, 100 100, 0 100, 0 0)), ((110 110, 150 110, 150 150, 110 150, 110 110)))',0) AS geom
  3. )
  4. SELECT geom FROM mPoly
  5. UNION ALL
  6. SELECT dbo.STMultiCentroid(geom,1 /* Weight By MBR not Vertices */,3,3,3).STBuffer(5) AS geom
  7.   FROM mPoly;

Which looks like:

I hope this is of use to SQL Server Spatial users out there.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment [9]

thanks simon for providing these utterly handy scripts.
When I try to recreate function ‘Centroid_A’ (Centroid for Area Geometries) I get following error message in SQL Server:

Msg 102, Level 15, State 1, Procedure Centroid_A, Line 59
Incorrect syntax near ‘order’.
Msg 195, Level 15, State 10, Procedure Centroid_A, Line 71
LEAD’ is not a recognized built-in function name.
Msg 102, Level 15, State 1, Procedure Centroid_A, Line 110
Incorrect syntax near ‘u’.

——————————————————————————————
Any possible fixes will make my world a lot easier.

thanks,
singhai

— singhai · 9 July 2013, 22:03 · #

Singhai,

I assume you are using SQL Server 2008. This solution is only for 2012.

regards
Simon

— Simon Greener · 17 July 2013, 05:45 · #

Thank you so much for sharing. This is awesome stuff. I’m going to try extending your line centroid function to deal with 3D lines. If you’re interested I’ll post back to let you know how that went. Once again Thanks!

— Andrew Ross · 16 April 2015, 23:10 · #

I stand corrected, Z-values are already included. I guess that went even better than expected.

— Andrew Ross · 17 April 2015, 04:07 · #

Was able to implement, stuff works great. I addressed a couple of small issues, including how to make it work in 2008 R2. Noticed that every time I try to post the code back I get a 403 error page. If you want I can e-mail you the code instead

— Andrew Ross · 21 April 2015, 05:42 · #

Hi there
I was able to implement it in SQL Server 2014, but I get the following error with one of our parcels layer

Msg 6569, Level 16, State 1, Line 17
‘geometry::Point’ failed because parameter 1 is not allowed to be null.

Can you help please?

— Jorge · 28 July 2015, 02:04 · #

Jorge,

Can you narrow it down a bit more? Which function are you executing? Does your parcels layer have any null values? Which parcel fails (WKT please)?

Simon

— Simon · 28 July 2015, 10:07 · #

First, great site. I think your functions are very useful for GIS folks used to using ArcGIS who want to make Sql Server “act” a little more like the Esri tools.

On the Create statement for the Centroid_L procedure, it was failing at line 50, returning a Msg 207 (invalid column ‘id’). When I modified the SELECT to:

SELECT 1 AS vid,

It works now. I am using SQL SERVER 2012 R2. I know your post is from years ago, but I thought I’d share first, how much you’ve helped folks and second, a minor issue that was identified creating the function in SQL 11.0(.5058).

Cheers.

— Michael Miller · 13 October 2016, 02:44 · #

Michael,

The function should run correctly as it is. I checked the latest version I have and it works. It could be that the the table data returned by GetVector does not include the ID column as it should do.

I would suggest you download the latest package of SQL Server functions, and try them. You will note that in that package all the functions have an ST prefix.

I will fix this blog post when I get time.

regards
Simon

— Simon Greener · 17 October 2016, 18:35 · #