Created
January 30, 2020 01:32
-
-
Save renato04/52efdf66a7bee516f96bcb0e2f83bcc2 to your computer and use it in GitHub Desktop.
SQL Server Spacial Statements
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment