STMulti

STMulti — Function that returns @p_geometry as a MULTI* geometry

Function Specification

Function [dbo].[STMulti] (
           @p_geometry geometry
         )
Returns geometry 

Description

In situations where a geometry column is constrained to hold only the multi version of a base geometry, storage of a single geometry eg LineString requires that geometry to be wrapped as a multi geometry eg MultiLineString.

ALTER TABLE [dbo].[Colony] WITH CHECK
ADD CONSTRAINT [Colony_Geom_Geometry_Type_Ck]
CHECK ([geom] is null OR [geom].[STGeometryType]()=’MultiPoint’);

So, @p_geometry is returned as a MULTI* geometry, e.g., POINT -> MULTIPOINT

If @p_geometry is already a MULTI*, it is returned unchanged e.g., MULTIPOINT -> MULTIPOINT

STMulti, where is wraps the single geometry returns a multi-geometry that contains only 1 geometry, so the SQL Server Spatial function STNumGeometries will return 1.

Parameters

    @p_geometry (geometry) - Supplied geometry of a supported type.

Result

This function always returns a multi-geometry type object.

Notes

A CircularString input geometry is returned as a GEOMETRYCOLLECTION as it cannot be returned as a MUTLICURVE as it is not instantiable.

Additionally, one cannot construct a MULTILINESTRING containing it MUTLILINESTRING(CIRCULARSTRING(….)).

CompoundCurves are not supported because MultiCurve is not instantiable in SQL Server Spatial

Examples

USE GISDB -- Change to your own database.
GO
    
SELECT f.mGeom.AsTextZM() as mGeom, f.mGeom.STNumGeometries() as numGeometries
  FROM (SELECT [dbo].[STMulti](geometry::STPointFromText('POINT(0 0)',0)) as mGeom
         UNION ALL
        SELECT [dbo].[STMulti](geometry::STGeomFromText ('POLYGON ((0 0,10 0,10 10,0 10,0 0))',0)) as mgeom
         UNION ALL
        SELECT [dbo].[STMulti](geometry::STGeomFromText ('LINESTRING(0 0,10 10,20 20)',0)) as mgeom
         UNION ALL
        SELECT [dbo].[STMulti](geometry::STGeomFromText('CIRCULARSTRING(9.962 -0.872,10.1 0,9.962 0.872)',0)) as mgeom
         ) as f;
GO

mGeom                                                                  numGeometries
MULTIPOINT ((0 0))                                                                 1
MULTIPOLYGON (((0 0, 10 0, 10 10, 0 10, 0 0)))                                     1
MULTILINESTRING ((0 0, 10 10, 20 20))                                              1
GEOMETRYCOLLECTION (CIRCULARSTRING (9.962 -0.872, 10.1 0, 9.962 0.872))            1

I hope this function is of use to someone.