/*

Przestrzenne typy danych

*/

DECLARE @g1 GEOGRAPHY, @g2 GEOGRAPHY;
SELECT 
	@g1 = GEOGRAPHY::STGeomFromText('POINT (3 3)', 4326), 
	@g2 = GEOGRAPHY::STGeomFromText('POINT (3 4)', 4322);
SELECT @g1.STDistance(@g2);
GO

DECLARE @g geometry;
SET @g = geometry::STPointFromWKB(0x010100000000000000000008400000000000001040, 0);
SELECT @g.STAsText();
GO

DECLARE @g geometry;
SET @g = geometry::STPointFromWKB(0x010100000000000000000008400000000000001040, 0);
SELECT @g.STAsBinary();
GO

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POINT (3 4)', 0);
SELECT @g.ToString();
GO

DECLARE @g geometry; 
SET @g = geometry::Parse('POINT (3 4)');
SELECT @g.ToString();
GO

DECLARE @g geometry; 
SET @g = 'POINT(3 4)';
SELECT @g.ToString();
GO

DECLARE @g geometry; 
SET @g = 'Point(3 4 0 1)';
SELECT @g.ToString(), @g.STAsText(), @g.AsTextZM();
GO

DECLARE @g geometry;
SET @g = geometry::GeomFromGml('<Point xmlns="http://www.opengis.net/gml">
<pos>3 4</pos>
</Point>',0);
SELECT @g.AsGml();
GO

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'R09Przestrzenne')
  CREATE DATABASE R09Przestrzenne;
GO

USE R09Przestrzenne;
GO

CREATE TABLE dbo.Dzielnica (
  DzielnicaID INT IDENTITY(1,1) PRIMARY KEY,
  Nazwa NVARCHAR(30) NOT NULL,
  Geometria GEOMETRY NOT NULL
);
GO

CREATE TABLE dbo.Ulica (
  UlicaID INT IDENTITY(1,1) PRIMARY KEY,
  Nazwa NVARCHAR(50) NOT NULL,
  Geometria GEOMETRY NOT NULL
);
GO

INSERT INTO dbo.Dzielnica (Nazwa, Geometria)
VALUES 
(N'Centrum', 'POLYGON((50 50, 150 50, 150 150, 100 150, 50 100, 50 50))'),
(N'Park', 'POLYGON((0 100, 50 100, 100 150, 200 150, 200 200, 0 200, 0 100))'),
(N'Dzielnica Zachodnia', 'POLYGON((0 0, 50 50, 50 100, 0 100, 0 0))'),
(N'Dzielnica Południowa', 'POLYGON((0 0, 100 0, 150 50, 50 50, 0 0))'),
(N'Dzielnica Wschodnia', 'POLYGON((100 0, 200 0, 200 150, 150 150, 150 50, 100 0))');
GO

INSERT INTO dbo.Ulica (Nazwa, Geometria)
VALUES 
(N'Główna', 'LINESTRING(0 50, 50 50, 100 100, 200 100)'),
(N'Boczna', 'LINESTRING(100 100, 100 50, 150 0)');
GO

DECLARE @g GEOMETRY;
SET @g = 'POLYGON((50 50, 150 50, 150 150, 50 150, 50 50))';
SELECT Nazwa, Geometria.STDifference(@g).ToString()
FROM dbo.Dzielnica;
GO

SELECT Nazwa, Geometria.STEnvelope().ToString()
FROM dbo.Dzielnica
WHERE Nazwa = N'Centrum';
GO

DECLARE @g GEOMETRY;
SET @g = 'POLYGON((50 50, 150 50, 150 150, 50 150, 50 50))';
SELECT Nazwa, Geometria.STIntersection(@g).ToString()
FROM dbo.Dzielnica;
GO

SELECT d.Nazwa
FROM dbo.Dzielnica AS d, dbo.Ulica AS u
WHERE u.Nazwa = N'Główna' AND u.Geometria.STIntersects(d.Geometria) = 1;
GO

SELECT Nazwa, Geometria.STPointOnSurface().ToString()
FROM dbo.Dzielnica;
GO

DECLARE @g GEOMETRY
SET @g = 'POLYGON((50 50, 150 50, 150 150, 50 150, 50 50))'
SELECT d.Geometria.STUnion(@g).ToString()
FROM dbo.Dzielnica AS d
WHERE Nazwa = N'Park'
GO

CREATE SPATIAL INDEX IX_Spatial_Dzielnica_Geometria
ON dbo.Dzielnica(Geometria)
WITH (BOUNDING_BOX = (0, 0, 200, 200),
GRIDS = (LEVEL_1 = LOW, LEVEL_2 = LOW),
CELLS_PER_OBJECT = 64);
GO

DROP INDEX IX_Spatial_Dzielnica_Geometria ON dbo.Dzielnica
GO

DECLARE @Polska GEOGRAPHY;
SET @Polska = GEOGRAPHY::STGeomFromText('POLYGON((
14.260253906249996 53.94315470224929 , 14.436035156249991 53.08082737207479,
14.150390625000014 52.84259457223951, 14.633789062500003 52.54963607438229,
14.721679687499998 51.53608560178474, 15.073242187499993 51.17934297928927,
14.831542968750014 50.87531114220077, 15.073242187499993 50.819818262156545,
15.292968749999987 50.944584434950116, 16.369628906250007 50.61113171332364,
16.23779296875001 50.429517947122875, 16.699218750000014 50.106487727673326 ,
17.072753906249986 50.1909677655856, 16.918945312500007 50.429517947122875,
17.77587890625 50.23315183247224, 17.687988281250003 50.14874640066278,
17.885742187500014 49.93707975697529, 18.039550781249993 50.02185841773444,
18.083496093749993 49.99361546254112, 18.61083984375001 49.82380908513249,
18.962402343750003 49.39667507519397, 19.467773437500003 49.553725513475776,
19.84130859375001 49.16733860629107, 20.104980468750003 49.196064000723794,
20.456542968749996 49.35375571830991, 20.7421875 49.35375571830991,
20.939941406250014 49.25346477497737, 21.37939453125 49.41097319969584,
22.016601562500003 49.31079887964632, 22.93945312500001 48.980216985374994,
22.697753906250003 49.496674527470425, 23.730468749999986 50.37349614430351,
24.016113281249996 50.387507803003146, 24.147949218749993 50.61113171332364,
23.620605468750007 51.56341232867588, 23.730468749999986 52.052490476001,
23.22509765624999 52.25470880113082, 23.48876953125001 52.52290594027806,
23.92822265625 52.68304276227742, 23.92822265625 53.1072166918934,
23.510742187499993 54.13669645687003, 22.917480468749996 54.36775852406839,
19.665527343750017 54.44449176335762, 18.69873046875001 54.3549556895541,
18.435058593750017 54.76267040025494, 18.786621093750007 54.699233528481386,
18.325195312500003 54.82600799909496, 17.226562499999996 54.76267040025494,
14.304199218749994 53.91728101547621, 14.260253906249996 53.94315470224929))',
4326);

SELECT @Polska.STArea()/(1000 * 1000);

SELECT @Polska.STLength()/1000;

SELECT @Polska.STNumPoints();
GO

/*

FILESTREAM

*/

/*

Dane hierarchiczne

*/

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'R09HierarchyId')
  CREATE DATABASE R09HierarchyId;
GO

USE R09HierarchyId;
GO


CREATE TABLE dbo.Organizacja (
  PracownikID HIERARCHYID PRIMARY KEY,
  Imie NVARCHAR(20) NOT NULL,
  Nazwisko NVARCHAR(50) NOT NULL,
  DataZatrudnienia SMALLDATETIME NOT NULL,
);
GO

INSERT INTO dbo.Organizacja (PracownikID, Imie, Nazwisko, DataZatrudnienia)
VALUES 
('/', N'Jan', N'Kowalski', '20050101'),
('/1/', N'Jan', N'Nowak', '20050101'),
('/2/', N'Anna', N'Kwiatkowska', '20060101'),
('/1/1/', N'Maria', N'Tomaszewska', '20060101'),
('/1/2/', N'Tomasz', N'Adamski', '20060101'),
('/2/1/', N'Alicja', N'Kowalska', '20070101'),
('/2/1/1/', N'Adam', N'Nowak', '20080101');
GO

SELECT Imie, Nazwisko
FROM dbo.Organizacja
WHERE PracownikID = (
  SELECT PracownikID.GetAncestor(2)
  FROM dbo.Organizacja
  WHERE Imie = N'Adam' AND Nazwisko = N'Nowak'
);
GO

DECLARE @Manager HIERARCHYID = '/2/';
DECLARE @LastChild HIERARCHYID = '/2/2/';
INSERT INTO dbo.Organizacja (PracownikID, Imie, Nazwisko, DataZatrudnienia)
VALUES
(@Manager.GetDescendant(@LastChild, NULL), N'Paweł', N'Potasiński', GETDATE());
GO

SELECT Imie, Nazwisko, PracownikID.GetLevel() AS Poziom
FROM dbo.Organizacja
ORDER BY Poziom;
GO

SELECT Imie, Nazwisko
FROM dbo.Organizacja
WHERE PracownikID = HIERARCHYID::GetRoot();
GO

DECLARE @Manager HIERARCHYID = '/1/';
SELECT Imie, Nazwisko, PracownikID.IsDescendantOf(@Manager)
FROM dbo.Organizacja;
GO

SELECT HIERARCHYID::Parse('/1/2/');
GO

SELECT CAST('/1/2/' AS HIERARCHYID);
GO

UPDATE dbo.Organizacja
SET PracownikID = PracownikID.GetReparentedValue('/2/1/','/1/1/')
WHERE PracownikID = '/2/1/1/';
GO

SELECT Imie, Nazwisko, PracownikID.ToString()
FROM dbo.Organizacja;
GO

SELECT Imie, Nazwisko, CAST(PracownikID AS NVARCHAR(4000))
FROM dbo.Organizacja;
GO

/*

Dokumenty XML

*/

DECLARE @doc XML = N'<slowa>
<slowo nr="1">Ala</slowo>
<slowo nr="2">ma</slowo>
<slowo nr="3">kota</slowo>
</slowa>';
SELECT @doc.query('//slowo');
SELECT @doc.query('//slowo[2]/text()');
SELECT @doc.query('//slowo[@nr > 1]');
GO

DECLARE @doc XML = N'<Root>abc</Root>';
SELECT @doc = @doc.query('<NewRoot>{ /Root }</NewRoot>');
SELECT @doc;
GO

DECLARE @i INT = 2;
DECLARE @doc XML = N'<slowa>
<slowo nr="1">Ala</slowo>
<slowo nr="2">ma</slowo>
<slowo nr="3">kota</slowo></slowa>';
SELECT @doc.query('//slowo[@nr=sql:variable("@i")]');
GO

USE AdventureWorks2008;
GO

SELECT P.ProductID, P.Name, S.Name AS Subcategory
FROM Production.Product AS P
INNER JOIN Production.ProductSubcategory AS S
ON P.ProductSubcategoryID = S.ProductSubcategoryID
WHERE P.ProductID IN (680, 706, 707)
FOR XML RAW;
GO

SELECT P.ProductID, P.Name, S.Name AS Subcategory
FROM Production.Product AS P
INNER JOIN Production.ProductSubcategory AS S
ON P.ProductSubcategoryID = S.ProductSubcategoryID
WHERE P.ProductID IN (680, 706, 707)
FOR XML RAW('Product'), ROOT('Products');
GO

SELECT P.ProductID, P.Name, S.Name AS Subcategory
FROM Production.Product AS P
INNER JOIN Production.ProductSubcategory AS S
ON P.ProductSubcategoryID = S.ProductSubcategoryID
WHERE P.ProductID IN (680, 706, 707)
FOR XML RAW('Product'), ROOT('Products'), ELEMENTS;
GO

SELECT P.ProductID, P.Name, S.Name AS Subcategory
FROM Production.Product AS P
INNER JOIN Production.ProductSubcategory AS S
ON P.ProductSubcategoryID = S.ProductSubcategoryID
WHERE P.ProductID IN (680, 706, 707)
FOR XML RAW('Product'), ROOT('Products'), ELEMENTS, XMLSCHEMA;
GO

SELECT Product.ProductID, Product.Name, Subcategory.Name AS SubcategoryName
FROM Production.Product AS Product
INNER JOIN Production.ProductSubcategory AS Subcategory
ON Product.ProductSubcategoryID = Subcategory.ProductSubcategoryID
WHERE Product.ProductID IN (680, 706, 707)
FOR XML AUTO, ROOT('Products'), ELEMENTS;
GO

SELECT 
  Product.ProductID '@ID', 
  Product.Name, 
  Subcategory.Name AS 'Subcategory/Name'
FROM Production.ProductSubcategory AS Subcategory
INNER JOIN Production.Product AS Product
ON Product.ProductSubcategoryID = Subcategory.ProductSubcategoryID
WHERE Product.ProductID IN (680, 706, 707)
FOR XML PATH('Product'), ROOT('Products');
GO

DECLARE @doc1 XML = '<Person>Jan Kowalski</Person>';
DECLARE @doc2 XML = '<Person>Jan Nowak</Person>';
SELECT @doc1, @doc2
FOR XML PATH('');
GO

SELECT 
  1 AS Tag,
  NULL AS Parent,
  ProductID AS [Product!1!ProductID],
  Name AS [Product!1!Name!ELEMENT],
  NULL AS [Subcategory!2]
FROM Production.Product
WHERE ProductID IN (680, 706, 707)
UNION ALL
SELECT 
  2 AS Tag,
  1 AS Parent,
  P.ProductID,
  NULL,
  S.Name
FROM Production.Product AS P
INNER JOIN Production.ProductSubcategory AS S
ON P.ProductSubcategoryID = S.ProductSubcategoryID
WHERE P.ProductID IN (680, 706, 707)
ORDER BY [Product!1!ProductID];
GO

SELECT 
  1 AS Tag,
  NULL AS Parent,
  ProductID AS [Product!1!ProductID],
  Name AS [Product!1!Name!ELEMENT],
  NULL AS [Subcategory!2]
FROM Production.Product
WHERE ProductID IN (680, 706, 707)
UNION ALL
SELECT 
  2 AS Tag,
  1 AS Parent,
  P.ProductID,
  NULL,
  S.Name
FROM Production.Product AS P
INNER JOIN Production.ProductSubcategory AS S
ON P.ProductSubcategoryID = S.ProductSubcategoryID
WHERE P.ProductID IN (680, 706, 707)
ORDER BY [Product!1!ProductID]
FOR XML EXPLICIT, ROOT('Products');
GO

DECLARE @products XML = N'<Products>
  <Product ProductID="680">
    <Name>HL Road Frame - Black, 58</Name>
    <Subcategory>Road Frames</Subcategory>
  </Product>
  <Product ProductID="706">
    <Name>HL Road Frame - Red, 58</Name>
    <Subcategory>Road Frames</Subcategory>
  </Product>
  <Product ProductID="707">
    <Name>Sport-100 Helmet, Red</Name>
    <Subcategory>Helmets</Subcategory>
  </Product>
</Products>';
SELECT @products.query('//Product[2]/Name/text()');
SELECT @products.query('
for $product in (//Product/Name/text())
order by $product descending
  return <Product>{$product}</Product>');
SELECT @products.query('//Product[Subcategory="Helmets"]/Name');

DECLARE @id INT;
SELECT @id = @products.value('(//Product/@ProductID)[1]','INT');
SELECT @id;

SELECT @products.exist('//Product[Subcategory[1] eq "Helmets"]');

SELECT 
  T.c.value('@ProductID','INT') AS ProductID,
  T.c.value('Name[1]','NVARCHAR(30)') AS Name,
  T.c.value('Subcategory[1]','NVARCHAR(20)') AS Subcategory
FROM @products.nodes('//Product') AS T(c);

--SELECT 
--  T.c.value('@ProductID','INT') AS ProductID,
--  T.c.value('Name[1]','NVARCHAR(30)') AS Name,
--  T.c.value('Subcategory[1]','NVARCHAR(20)') AS Subcategory
--FROM dbo.Companies
--CROSS APPLY Products.nodes('//Product') AS T(c);

SET @products.modify('insert <Product ProductID="600" /> as first into /Products[1]');
SELECT @products;

SET @products.modify('replace value of (//Product/@ProductID)[1] with 500');
SELECT @products;

SET @products.modify('delete (//Product[Subcategory[1] eq "Road Frames"])');
SELECT @products;

--UPDATE dbo.Companies
--SET Products.modify('delete (//Product[Subcategory[1] eq "Road Frames"])')
--WHERE Products.exist('//Product[Subcategory[1] eq "Road Frames"]') = 1;

--CREATE PRIMARY XML INDEX IX_XML_Osoby_Resume_PRIMARY 
--ON dbo.Osoba (Resume);

--SELECT Resume.query('/Resume/Wyksztalcenie') AS Wyksztalcenie
--FROM dbo.Osoba
--WHERE Resume.exist('/Resume/Wyksztalcenie') = 1;

--CREATE XML INDEX IX_XML_Osoba_Resume_PATH ON dbo.Osoba(Resume)
--USING XML INDEX IX_XML_Osoby_Resume_PRIMARY
--FOR PATH;

--SELECT OsobaID
--FROM dbo.Osoba
--WHERE Resume.exist('//Uczelnia[.="Politechnika Warszawska"]') = 1;

--CREATE XML INDEX IX_XML_Osoba_Resume_VALUE ON dbo.Osoba(Resume)
--USING XML INDEX IX_XML_Osoby_Resume_PRIMARY
--FOR VALUE;

--SELECT 
--Resume.value('/Resume/RokUrodzenia[1]','INT') AS [Rok urodzenia],
--Resume.value('/Resume/Wyksztalcenie/Uczelnia[1]','NVARCHAR(100)') AS Uczelnia
--FROM dbo.Osoba
--WHERE OsobaID = 10;

--CREATE XML INDEX IX_XML_Osoba_Resume_PROPERTY ON dbo.Osoba(Resume)
--USING XML INDEX IX_XML_Osoby_Resume_PRIMARY
--FOR PROPERTY; 
