Spatial Data with SQL Server

Programming SQL-Spatial-1

DECLARE @line geometry = 'LINESTRING(5 15,22 10)'
select @line AS AsRaw, @line.ToString() AS AsWKT
GO

Programming SQL-Spatial-10

--Comparing STUnion, STIntersection, STDifference, and STSymDifference methods
DECLARE @S1 geometry = 'POLYGON ((60 40, 410 50, 400 270, 60 370, 60 40))'
DECLARE @S2 geometry = 'POLYGON ((300 100, 510 110, 510 330, 300 330, 300 100))'

select @S1 UNION ALL SELECT @S2
SELECT S1_UNION_S2 = @S1.STUnion(@S2)
SELECT S1_INTERSECTION_S2 = @S1.STIntersection(@S2)
SELECT S1_DIFFERENCE_S2 = @S1.STDifference(@S2)
SELECT S2_DIFFERENCE_S1 = @S2.STDifference(@S1)
SELECT S1_SYMDIFFERENCE_S2	= @S1.STSymDifference(@S2)

Programming SQL-Spatial-2

CREATE TABLE District
(DistrictId int PRIMARY KEY,
DistrictName nvarchar(20),
DistrictGeo geometry)

CREATE TABLE Street
(StreetId int PRIMARY KEY,
StreetName nvarchar(20),
StreetGeo geometry)

INSERT INTO District VALUES
(1, 'Downtown', 'POLYGON ((0 0, 150 0, 150 -150, 0 -150, 0 0))'),
(2, 'Green Park', 'POLYGON((300 0, 150 0, 150 -150, 300 -150, 300 0))'),
(3, 'Harborside', 'POLYGON((150 -150, 300 -150, 300 -300, 150 -300, 150 -150))')

SELECT * FROM District

Programming SQL-Spatial-3

INSERT INTO Street VALUES
(1, 'First Avenue', 'LINESTRING (100 -100, 20 -180, 180 -180)'),
(2, 'Beach Street', 'LINESTRING (300 -300, 300 -150, 50 -50)')

SELECT NAME = StreetName, AsRaw = StreetGeo, AsWKT = StreetGeo.ToString()
FROM Street
UNION ALL
SELECT Name = DistrictName, AsRaw = DistrictGeo, AsWKT = DistrictGeo.ToString()
FROM District

Programming SQL-Spatial-4

--STBuffer Method (used to increase the width of the streets)
SELECT 
Name = StreetName,
AsRaw = StreetGeo.STBuffer(5),
AsWKT = StreetGeo.STBuffer(5).ToString()
FROM Street
UNION ALL
SELECT
Name = DistrictName,
AsRaw = DistrictGeo,
AsWKT = DistrictGeo.ToString()
FROM District

Programming SQL-Spatial-5

--STCentroid Method (used to locate center of shape)
SELECT
DistrictGeo,
DistrictGeo.ToString()
FROM District
UNION ALL
SELECT
DistrictGeo.STCentroid().STBuffer(5),
DistrictGeo.STCentroid().ToString()
FROM District 

Programming SQL-Spatial-6

--STEnvelope Method (used to place a bounding box around each street)
SELECT
StreetName,
StreetGeo.STBuffer(5),
StreetGeo.ToString()
FROM Street
UNION ALL
SELECT
StreetName + 'Bounds',
StreetGeo.STEnvelope(),
StreetGeo.STEnvelope().ToString()
FROM Street

Programming SQL-Spatial-7

--STIntersects and STIntersection Methods (finds all district and street intersections)
SELECT
S.StreetName,
D.DistrictName
FROM District AS D CROSS JOIN Street AS S
WHERE S.StreetGeo.STIntersects(D.DistrictGeo) = 1
ORDER BY S.StreetName

Programming SQL-Spatial-8

--STIntersection Method (generates road fragments for each district)
SELECT
S.StreetName,
D.DistrictName,
S.StreetGeo.STIntersection(D.DistrictGeo).STBuffer(5) AS Intersection,
S.StreetGeo.STIntersection(D.DistrictGeo).ToString() AS IntersectionWKT
FROM District AS D CROSS JOIN Street AS S
WHERE S.StreetGeo.STIntersects(D.DistrictGeo) = 1
ORDER BY S.StreetName

Programming SQL-Spatial-9

--STDimension Method (shows the number of dimensions for each shape)
SELECT 
StreetName AS Shape,
StreetGeo.ToString() AS ShapeWKT,
StreetGeo.STDimension() AS Dimensions
FROM Street
UNION ALL 
SELECT
DistrictName AS Shape,
DistrictGeo.ToString() AS ShapeWKT,
DistrictGeo.STDimension() AS Dimensions
FROM District