Spatial SQL -> Exersize MS-SQL spatial methods

We have some data in Lat-Long (WGS-84) that represents a bunch of geographic venues in Milan city (Italy). Here i will demonstrate use of several build-in SQL methods that can be applied to Spatial data objects. I will take one geometry object from polygon's table,- shape that signifies footprint of "Giardini pubblici" and test a number of methods that can describe, manipulate and compare this item to others.

Milano city center Giardini pubblici


If you have no spatial data at all, you can use following script to create tiny spatial table and insert into it one polygon.

CREATE TABLE [dbo].[milano_reg](
  [id] [int] NULL,
  [name] [varchar](80) NULL,
  [type] [varchar](40) NULL,
  [geog] [geography] NULL
) ON [PRIMARY]
GO

INSERT INTO milano_reg (id, name, [type], geog)
SELECT 11, 'Giardini pubblici', 'polygon',
GEOGRAPHY::STPolyFromText(
'POLYGON (
 (9.197916 45.47682, 9.195513 45.472823,
   9.196395 45.471905, 9.197969 45.470452,
   9.200557 45.470953, 9.201649 45.472091,
   9.202453 45.471989, 9.20444 45.474246,
   9.198739 45.476917, 9.197916 45.47682),
 (9.197502 45.474189, 9.197736 45.474633,
   9.198535 45.474562, 9.198602 45.474142,
   9.198102 45.473862, 9.197502 45.474189)
   )'
,
4326);


--
-- What is it ?
-- [Spatial Reference ID], Type, Number of parts
--

SELECT [id], [name],
   geog.STSrid AS [Spatial Reference ID],
   geog.STGeometryType() AS geog_type,
   geog.STNumGeometries() AS number_of_parts
FROM [dbo].[milano_reg]
WHERE [id]=11
GO

--
-- Geometry object converted to text
--

SELECT [id], [name], geog.STAsText()
FROM [dbo].[milano_reg]
WHERE [id]=11
GO

-- Understand Structure:
-- Exterior Boundary (geog.RingN(1))
-- Interior Boundaries: geog.RingN(2), geog.RingN(3), ...

SELECT [id], [name], geog.RingN(1).STLength() AS LineLen, geog.RingN(1)
FROM [dbo].[milano_reg]
WHERE [id]=11
   UNION ALL
SELECT [id], [name], geog.RingN(2).STLength() AS LineLen, geog.RingN(2)
FROM [dbo].[milano_reg]
WHERE [id]=11
GO

--
-- Show External Border Vertices
--

SELECT [id], [name], geog.RingN(1).STLength() AS LineLen, geog.RingN(1)
FROM [dbo].[milano_reg]
WHERE [id]=11
   UNION ALL
SELECT a.id, a.[name], b.n,
  a.geog.RingN(1).STPointN(b.n).STBuffer(10) AS g
FROM [dbo].[milano_reg] AS a, num_seq AS b
WHERE a.[id]=11 AND b.n <= a.geog.RingN(1).STNumPoints()
GO


-- Show Vertix coordinates (Lat/Long)
SELECT a.id, a.[name], b.n,
   a.geog.RingN(1).STPointN(b.n).Lat AS Latitude,
   a.geog.RingN(1).STPointN(b.n).Long AS Longitude
FROM [dbo].[milano_reg] AS a, num_seq AS b
WHERE a.[id]=11 AND b.n <= a.geog.RingN(1).STNumPoints()
GO

--
-- Reduce Vertices in External Border Line
--

SELECT [id], [name], geog.RingN(1).STBuffer(1) AS g
FROM [dbo].[milano_reg]
WHERE [id]=11
   UNION ALL
SELECT a.id, a.[name], a.geog.RingN(1).STPointN(b.n).STBuffer(6) AS g
FROM [dbo].[milano_reg] AS a, num_seq AS b
WHERE a.[id]=11 AND b.n <= a.geog.RingN(1).STNumPoints()
   UNION ALL
SELECT [id], [name], geog.RingN(1).Reduce(50).STBuffer(2.5) AS g
FROM [dbo].[milano_reg]
WHERE [id]=11
GO

--
-- Measure Relationships: find something that is within 300 m distance
--

SELECT [id], [name], 0 AS Dist, geog
FROM [dbo].[milano_reg]
WHERE [id]=11
   UNION ALL
SELECT b.id, b.name, b.g.STDistance(a.geog) AS Dist, b.g.STBuffer(17) AS g
FROM [dbo].[milano_reg] AS a, [dbo].[milano_pnt] AS b
WHERE a.[id]=11 AND b.g.STDistance(a.geog) <= 300
GO

--
-- Cut one geometry using other geometry
--

-- 1. By buffered inner island
SELECT [id], [name], geog.STDifference(geog.RingN(2).STBuffer(70)) AS g
FROM [dbo].[milano_reg]
WHERE [id]=11
GO

-- 2. By reduced External boundary (as Line)
SELECT [id], [name],
geog.STDifference(geog.RingN(1).Reduce(50).STBuffer(3)) AS g,
geog.STDifference(geog.RingN(1).Reduce(50).STBuffer(3)).STGeometryType() AS geog_type,
geog.STDifference(geog.RingN(1).Reduce(50).STBuffer(3)).STNumGeometries() AS number_of_parts
FROM [dbo].[milano_reg]
WHERE [id]=11
GO

-- 3. By reduced External boundary converted to Polygon
SELECT [id], [name],
geog.STDifference(geog.RingN(1).Reduce(50).STConvexHull()) AS g,
geog.STDifference(geog.RingN(1).Reduce(50).STConvexHull()).STGeometryType() AS geog_type,
geog.STDifference(geog.RingN(1).Reduce(50).STConvexHull()).STNumGeometries() AS number_of_parts
FROM [dbo].[milano_reg]
WHERE [id]=11
GO





sqlexamples.info