SQL Server Spacial Statements

script.sql

CREATE TABLE Districts 
( DistrictId int IDENTITY (1,1),
DistrictName nvarchar(20),
DistrictGeo geometry);
GO

CREATE TABLE Streets 
( StreetId int IDENTITY (1,1),
StreetName nvarchar(20),
StreetGeo geometry);
GO


INSERT INTO Districts (DistrictName, DistrictGeo)
VALUES ('Downtown', geometry::STGeomFromText
('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0));

INSERT INTO Districts (DistrictName, DistrictGeo)
VALUES ('Green Park', geometry::STGeomFromText
('POLYGON ((300 0, 150 0, 150 150, 300 150, 300 0))', 0));

INSERT INTO Districts (DistrictName, DistrictGeo)
VALUES ('Harborside',geometry::STGeomFromText
('POLYGON ((150 0, 300 0, 300 300, 150 300, 150 0))', 0));

INSERT INTO Streets (StreetName, StreetGeo)
VALUES ('First Avenue',geometry::STGeomFromText
('LINESTRING (100 100, 20 180, 180 180)', 0))

INSERT INTO Streets (StreetName, StreetGeo)
VALUES ('Mercator Street', geometry::STGeomFromText
('LINESTRING (300 300, 300 150, 50 51)', 0))

CREATE TABLE PropertiesForSale (
  ID int,
  Address varchar(255),
  Location geography,
  Price money,
  Description varchar(max),
  Listdate datetime
)
GO


INSERT INTO PropertiesForSale VALUES
(1,
'Pilgrims Way, Chew Stoke, Somerset',
geography::Point(51.354940,-2.635765,4326),
750000,
'Grade II Listed former Rectory, with magnificent architectural features and stunning gardens.',
'2008-08-01 17:00:00'),
(2,
'Moulsford, Wallingford, Oxfordshire',
geography::Point(51.549963,-1.149013,4326),
1650000,
'Situated on the River Thames, this period house features landscaped gardens extending up to 240ft, and private mooring.',
'2008-07-07 14:30:00'),
(3,
'Pantings Lane, Highclere, Newbury',
geography::Point(51.347206,-1.375828,4326),
965000,
'A newly developed 5-bedroom house on the edge of Highclere, with very high build specifications used throughout.',
'2008-07-07 12:00:00')
GO

select * , Location.Lat as 'Latitude',
Location.Long as 'Logitude'
from PropertiesForSale 

select *, DistrictGeo.STArea() 
from Districts

select *, DistrictGeo.STIsClosed() from Districts

select *, StreetGeo.STIsClosed() from Streets

select s.StreetName, d.DistrictName from Streets s
inner join Districts d
on s.StreetGeo.STIntersects(d.DistrictGeo) = 1