Spatial SQL -> MS-SQL. Analyzing spatial data problems


Here are 4 simple steps that can help you to check status of spatial data object and understand the possible causes of it's malfunctioning. Methods used:
.IsValidDetailed()
.STIsEmpty()
.STGeometryType()
.STNumGeometries()
We will start with the worst assumption - that the geo object is Invalid, then check if it is empty (or NULL), and then proceed to collect additional information that can explain some kinds of unexpected behaviour.

/* [lakes] table has geography column [geog]. geo object in it supposed to be Polygon */

-- [1] find Invalid

SELECT id, geog.IsValidDetailed() AS err_detailes
FROM dbo.lakes
WHERE geog.IsValidDetailed() <> '24400: Valid';

-- [2] check Empty object

SELECT id, geog
FROM dbo.lakes
WHERE geog.STIsEmpty()=1 OR geog IS NULL;

-- [3] group by GeometryType to see what kind of shapes we have

SELECT geog.STGeometryType(), count(*) AS count_no
FROM dbo.lakes
GROUP BY geog.STGeometryType();

-- [4] how many parts and the size of each part

SELECT id, geog.STNumGeometries() AS parts_no,
  geog.STGeometryN(1).STArea() AS area1,
  geog.STGeometryN(2).STArea() AS area2
FROM dbo.lakes
WHERE geog.STNumGeometries() > 1;


sqlexamples.info