Nowe typy danych w SQL Server 2008
Autor: Kelly Wilson
Opublikowano: 8 września 2008
Zawartość strony
Data i godzina | |
Datetimeoffset oraz Datetime2 | |
Typ danych Hierarchyid | |
Przestrzenne typy danych | |
Różnica pomiędzy geografią a geometrią |
Prowadzenie biznesu w świecie globalnej ekonomii coraz częściej wymaga, aby firmy wykorzystywały nowe typy danych, aplikacji i złożonych obliczeń. Siedem nowych typów danych wbudowanych w SQL Server 2008 zapewnia metody pracy z bardziej skompilowanymi danymi i upraszcza zarządzanie nimi.
Data i godzina
Stary typ danych daty i godziny nie dawał użytkownikom SQL Server® możliwości pracy z informacjami o dacie i godzinie z osobna. Cztery nowe typy danych: date, time, datetime2 oraz datetimeoffset zmieniły tę sytuację, upraszczając pracę z danymi daty i godziny i oferując szerszy zakres danych, precyzję mierzoną w ułamkowych częściach sekundy oraz wsparcie dla stref czasowych. Nowe aplikacje bazodanowe powinny stosować te nowe typy danych zamiast dawnego typu datetime. Przyjrzyjmy się dokładniej nowym wersjom.
Typ danych date przechowuje datę bez składnika godziny. Obejmuje zakres od 1 stycznia 1000 roku do 31 grudnia 9999 roku (od 0001-01-01 do 9999-12-31). Każda zmienna typu date zajmuje 3 bajty i posiada precyzję dziesięciu cyfr. Dokładność typu date jest ograniczona do pojedynczego dnia.
Spójrzmy na Rysunek 1, który prezentuje sposób tworzenia i inicjalizowania zmiennych typu date w skryptach T-SQL. Zmienna @myDate1 jest inicjalizowana przez ciąg sformatowany jako 'MM/DD/RRRR.' Zmienna @myDate2 nie została zainicjalizowana, a zatem będzie posiadać wartość NULL. Zmienna @myDate3 została zainicjalizowana przy użyciu daty pobranej z systemu lokalnego komputera. Wartości zmiennych mogą zostać w dowolnej chwili zmodyfikowane przy pomocy instrukcji SELECT lub SET, co zademonstrowano w poniższym przykładzie, zmieniając wartość zmiennej @myDate2. Kolumny typu date można również tworzyć w tabelach. Rysunek 2 ilustruje, w jaki sposób można stworzyć tabelę z trzema kolumnami typu date.
USE TempDB
GO
CREATE TABLE myTable
(
myDate1 date,myDate2 date,myDate3 date
)
GO
INSERT INTO myTable
VALUES('01/22/2005',
'2007-05-08 12:35:29.1234567 +12:15',
GetDate())
SELECT * FROM myTable
--Wynik
--myDate1 myDate2 myDate3
------------ ---------- ----------
--2005-01-22 2007-05-08 2007-11-20
Rysunek 1: Tworzenie i inicjalizowanie zmiennych typu date w skryptach T-SQL.
DECLARE @myDate1 date = '01/22/2005'
DECLARE @myDate2 date
DECLARE @myDate3 date = GetDate()
SELECT @myDate2 = '2007-05-08 12:35:29.1234567 +12:15'
SELECT @myDate1 AS '@myDate1',
@myDate2 AS '@myDate2',
@myDate3 AS '@myDate3'
--Wynik
--@myDate1 @myDate2 @myDate3
------------ ---------- ----------
--2005-01-22 2007-05-08 2007-11-20
Rysunek 2: Tworzenie tabeli z trzema kolumnami typu date.
Typ danych time służy do przechowywania godziny bez składnika daty. Bazuje na 24 godzinnym zegarze, a zatem wspierany zakres wynosi od 00:00:00.0000000 do 23:59:59.9999999 (godziny, minuty, sekundy oraz ułamkowe części sekund). Precyzję ułamkowych części sekundy możemy określić w czasie tworzenia typu danych. Domyślna precyzja to 7 cyfr, dokładność 100ns. Precyzja wpływa na rozmiar wymaganego miejsca do magazynowania, który może wahać się od 3 bajtów dla maksimum 2 cyfr, 4 bajtów dla 3 lub 4 cyfr oraz 5 bajtów dla od 5 do 7 cyfr.
Skrypt T-SQL zaprezentowany na Rysunku 3 ilustruje, w jaki sposób jawne konwersje ciągu inicjalizującego wartość wpływają na jej precyzję. Na początku skryptu T-SQL tworzonych i inicjalizowanych jest osiem osobnych zmiennych typu time o identycznych wartościach. Precyzja każdej z tych zmiennych jest wyrażona za pomocą jej nazwy. Na przykład, @myTime3 posiada precyzję trzech miejsc po przecinku. Wynik ilustruje, że precyzja poszczególnych typów danych time jest równa wyrażonej w liczbie miejsc po przecinku precyzji, która podana została podczas deklaracji typu. Cyfry, które wychodzą poza zakres, zostają przycięte.
DECLARE @myTime time = '01:01:01.1234567 +01:01'
DECLARE @myTime1 time(1) = '01:01:01.1234567 +01:01'
DECLARE @myTime2 time(2) = '01:01:01.1234567 +01:01'
DECLARE @myTime3 time(3) = '01:01:01.1234567 +01:01'
DECLARE @myTime4 time(4) = '01:01:01.1234567 +01:01'
DECLARE @myTime5 time(5) = '01:01:01.1234567 +01:01'
DECLARE @myTime6 time(6) = '01:01:01.1234567 +01:01'
DECLARE @myTime7 time(7) = '01:01:01.1234567 +01:01'
SELECT @myTime AS '@myTime',
@myTime1 AS '@myTime1',
@myTime2 AS '@myTime2',
@myTime3 AS '@myTime3',
@myTime4 AS '@myTime4',
@myTime5 AS '@myTime5',
@myTime6 AS '@myTime6',
@myTime7 AS '@myTime7'
--Wynik
--@myTime @myTime1 @myTime2 @myTime3 @myTime4
------------------ ---------- ----------- ------------ -------------
--01:01:01.1234567 01:01:01.1 01:01:01.12 01:01:01.123 01:01:01.1235
--
--@myTime5 @myTime6 @myTime7
---------------- --------------- ----------------
--01:01:01.12346 01:01:01.123457 01:01:01.1234567
DROP TABLE myTable
Rysunek 3: Wyświetlanie precyzji zmiennej typu time.
Typ danych time może zostać zastosowany podczas tworzenia kolumny w tabeli. Skrypt T-SQL DROP TABLE myTable zaprezentowany na Rysunku 4 tworzy tabelę o nazwie myTable1 i dodaje do niej trzy kolumny typu time. Następnie do tabeli dodawany jest rekord i zawartość tabeli zostaje wyświetlona przy pomocy instrukcji SELECT.
USE TempDB
GO
CREATE TABLE myTable1
(
myTime1 time(1),
myTime2 time(2),
myTime3 time(3)
)
GO
INSERT INTO myTable1
VALUES('01:30:01.1234567',
'02:34:01.1234567',
'03:01:59.1234567')
SELECT * from myTable1
--Wynik
--myTime1 myTime2 myTime3
------------ ----------- ------------
--01:30:01.1000000 02:34:15.1200000 03:01:59.1230000
DROP TABLE myTable1
Rysunek 4: Tworzenie myTable1.
Do początku strony
Datetimeoffset oraz Datetime2
Typ danych datetimeoffset zapewnia obsługę stref czasowych. Typ danych time nie obejmuje strefy czasowej, a zatem operuje jedynie na czasie lokalnym. Jednak w ekonomii globalnej często niezbędna jest świadomość, w jaki sposób czas w jednej części świata powiązany jest z czasem w innej części świata. Przesunięcie między strefami czasowymi wyraża się jako + lub - gg:mm.
Następujący kod tworzy zmienną typu datetimeoffset i inicjalizuje ją wartością 8:52 rano standardowego czasu pacyficznego:
DECLARE @date DATETIMEOFFSET = '2007-11-26T08:52:00.1234567-08:00'
PRINT @date
--Wynik
--2007-11-26 08:52:00.1234567 -08:00
Ciąg, który inicjalizuje zmienną datetimeoffset (@date w skrypcie) został specjalnie sformatowany, począwszy od najbardziej znaczącego elementu, kończąc na najmniej istotnym. Pojedyncza, wielka litera T oddziela elementy daty i godziny. Znak minus oddziela elementy godziny od strefy czasowej. Między znakiem minus a elementami godziny lub strefy czasowej nie znajdują się żadne spacje. Ten format stanowi jeden z dwóch formatów ISO 8601 wspieranych przez typ danych datetimeoffset (ISO 8601 to międzynarodowy standard reprezentacji wartości daty i godziny).
Precyzja komponentu godziny jest określana w taki sam sposób, jak w przypadku typu danych time i jeśli nie zostanie wprost podana, jej domyślną wartością będzie również siedem cyfr. Wspierany jest jednakowy zakres.
Typ danych datetime2 stanowi rozszerzenie oryginalnego typu datetime. Wspiera szerszy zakres dat oraz większą precyzję części ułamkowych sekundy i także umożliwia określanie precyzji. Zakres dat typu datetime2 obejmuje daty od 1 stycznia 0001 roku do 31 grudnia 9999 roku, dla porównania zakres oryginalnego typu obejmuje daty od 1 stycznia 1753 roku do 31 grudnia 9999 roku. Podobnie jak w przypadku typu danych time, oferowana jest precyzja do siedmiu miejsc po przecinku. Oryginalny typ datetime dostarcza trzycyfrową precyzję i zakres godzin od 00:00:00 do 23:59:59.999. Oto w jaki sposób tworzymy zmienną datetime2 i inicjalizujemy ją przy użyciu daty i godziny na lokalnym serwerze:
DECLARE @datetime2 DATETIME2 = GetDate();
PRINT @datetime2
--Wynik
--2007-11-26 09:39:04.1370000
Za chwilę przyjrzymy się nowemu typowi hierarchyid. Ten typ danych działa w powiązaniu z wieloma elementami danych w tabeli, zamiast z określonymi danymi daty i godziny.
Do początku strony
Typ danych Hierarchyid
Typ danych hierarchyid umożliwia nam konstruowanie relacji między elementami danych w tabeli, w szczególności w celu reprezentowania pozycji w hierarchii. Poznawanie tego typu danych warto rozpocząć od stworzenia bazy danych MyCompany i wypełnienia jej danymi pracowników przy użyciu skryptu z Rysunku 5.
USE MASTER
GO
CREATE DATABASE MyCompany
GO
USE MyCompany
GO
--Tworzenie tabeli o nazwie employee, w której składowane będą
--dane pracowników MyCompany.
CREATE TABLE employee
(
EmployeeID int NOT NULL,
EmpName varchar(20) NOT NULL,
Title varchar(20) NULL,
Salary decimal(18, 2) NOT NULL,
hireDate datetimeoffset(0) NOT NULL,
)
GO
--Te instrukcje wstawią dane pracowników MyCompany.
INSERT INTO employee
VALUES(6, 'David', 'CEO', 35900.00, '2000-05-23T08:30:00-08:00')
INSERT INTO employee
VALUES(46, 'Sariya', 'Specialist', 14000.00, '2002-05-23T09:00:00-08:00')
INSERT INTO employee
VALUES(271, 'John', 'Specialist', 14000.00, '2002-05-23T09:00:00-08:00')
INSERT INTO employee
VALUES(119, 'Jill', 'Specialist', 14000.00, '2007-05-23T09:00:00-08:00')
INSERT INTO employee
VALUES(269, 'Wanida', 'Assistant', 8000.00, '2003-05-23T09:00:00-08:00')
INSERT INTO employee
VALUES(272, 'Mary', 'Assistant', 8000.00, '2004-05-23T09:00:00-08:00')
GO
--Wynik
--EmployeeID EmpName Title Salary hireDate
------------- ------- ---------- -------- --------------------------
--6 David CEO 35900.00 2000-05-23 08:30:00 -08:00
--46 Sariya Specialist 14000.00 2002-05-23 09:00:00 -08:00
--271 John Specialist 14000.00 2002-05-23 09:00:00 -08:00
--119 Jill Specialist 14000.00 2007-05-23 09:00:00 -08:00
--269 Wanida Assistant 8000.00 2003-05-23 09:00:00 -08:00
--272 Mary Assistant 8000.00 2004-05-23 09:00:00 -08:00
Rysunek 5: Tworzenie i wypełnianie bazy danych MyCompany.
Rysunek 6 prezentuje prostą wynikową bazę danych składającą się z pojedynczej tabeli pracowników o nazwie employee. Tabela employee w bazie danych MyCompany nie posiada żadnej narzuconej struktury. To normalne dla relacyjnej bazy danych, ponieważ struktura jest narzucana dynamicznie przez aplikację za pośrednictwem jej kwerend i kodu przetwarzania.
Rysunek 6: Tabela employee w bazie danych MyCompany.
Jednak dane biznesowe posiadają zazwyczaj pewien rodzaj nieodłącznej struktury. Na przykład każda firma posiada strukturę organizacyjną, taką jak struktura firmy MyCompany pokazana na Rysunku 7. Wszyscy pracownicy MyCompany są podwładnymi Davida – dyrektora generalnego. Niektórzy pracownicy są jego bezpośrednimi podwładnymi np. Jill. Inni, jak np. Mary, podlegają mu w sposób pośredni. W terminologii programowania struktura organizacyjna firmy MyCompany jest nazywana drzewem ze względu na podobieństwo kształtów. David (na samej górze) nie podlega nikomu i jest rodzicem lub przodkiem. Pracownicy, którzy podlegają Davidowi, znajdują się pod nim. Takie węzły nazywane są dziećmi lub potomkami. David może mieć tylu potomków, ilu wymaga reprezentacja jego bezpośrednich podwładnych.
Rysunek 7: Struktura organizacyjna MyCompany.
Skrypt z Rysunku 8 przebudowuje bazę danych MyCompany przy użyciu typu danych hierarchyid, konstruując relację, która odpowiada strukturze organizacyjnej firmy MyCompany. Na początku instrukcja ALTER TABLE jest wykorzystywana do dodania kolumny typu hierarchyid. Węzeł Davida jest następnie wstawiany przy użyciu metody GetRoot typu hierarchyid. Później do drzewa dodawani są bezpośredni podwładni Davida przy użyciu metody GetDescendant.
DELETE employee
GO
ALTER TABLE employee ADD OrgNode hierarchyid NOT NULL
GO
DECLARE @child hierarchyid,
@Manager hierarchyid = hierarchyid::GetRoot()
--Pierwszym krokiem jest dodanie węzła na górze drzewa.
--Ponieważ David pełni funkcję CEO, jego węzeł stanie się
--węzłem korzenia.
INSERT INTO employee
VALUES(6, 'David', 'CEO', 35900.00,
'2000-05-23T08:30:00-08:00', @Manager)
--Kolejnym krokiem jest wstawienie rekordów
--pracowników, którzy są bezpośrednimi podwładnymi Davida.
SELECT @child = @Manager.GetDescendant(NULL, NULL)
INSERT INTO employee
VALUES(46, 'Sariya', 'Specialist', 14000.00,
'2002-05-23T09:00:00-08:00', @child)
SELECT @child = @Manager.GetDescendant(@child, NULL)
INSERT INTO employee
VALUES(271, ‚John', ‚Specialist', 14000.00,
'2002-05-23T09:00:00-08:00', @child)
SELECT @child = @Manager.GetDescendant(@child, NULL)
INSERT INTO employee
VALUES(119, ‚Jill', ‚Specialist', 14000.00,
‚2007-05-23T09:00:00-08:00', @child)
--Teraz możemy wstawić pracownika, którego szefem jest
--Sariya.
SELECT @manager = OrgNode.GetDescendant(NULL, NULL)
FROM employee WHERE EmployeeID = 46
INSERT INTO employee
VALUES(269, ‚Wanida', ‚Assistant', 8000.00,
‚2003-05-23T09:00:00-08:00', @manager)
--Następnie wstawiamy pracownika podlegającego Johnowi.
SELECT @manager = OrgNode.GetDescendant(NULL, NULL)
FROM employee WHERE EmployeeID = 271
INSERT INTO employee
VALUES(272, ‚Mary', ‚Assistant', 8000.00,
‚2004-05-23T09:00:00-08:00', @manager)
GO
Rysunek 8: Przebudowywanie bazy danych przy użyciu hierarchyid.
Po dodaniu rekordów bazy danych oraz skonstruowaniu hierarchii, zawartość tabeli employee może zostać wyświetlona przy pomocy następującej kwerendy:
SELECT EmpName, Title, Salary, OrgNode.ToString() AS OrgNode
FROM employee ORDER BY OrgNode
GO
--Wynik
--EmpName Title Salary OrgNode
---------- ---------- --------- -------
--David CEO 35900.00 /
--Sariya Specialist 14000.00 /1/
--Wanida Assistant 8000.00 /1/1/
--John Specialist 14000.00 /2/
--Mary Assistant 8000.00 /2/1/
--Jill Specialist 14000.00 /3/
OrgNode to kolumna hierarchyid. Każdy znak ukośnika / w zbiorze wynikowym wskazuje węzeł w drzewie hierarchii. David znajduje się w korzeniu, o czym świadczy pojedynczy ukośnik. Sariya, John oraz Jill stanowią podwładnych Davida i posiadają dwa znaki ukośnika wskazujące, że stanowią one drugi rząd węzłów w hierarchii. Liczby 1, 2 bądź 3 wskazują kolejność odpowiednich węzłów podrzędnych. System ten jest bardzo elastyczny. Węzły dzieci mogą być w zależności od potrzeb usuwane, wstawiane lub dodawane. Jeśli dodamy na przykład pracownika między Johnem a Jill, pracownik ten będzie wyświetlany w zbiorze wynikowym jako /2.1/.
Aby odpowiedzieć na przykładowe pytanie: "Czyim przełożonym jest Sariya?", możemy stworzyć kwerendę zaprezentowaną w następującym kodzie T-SQL:
DECLARE @Sariya hierarchyid
SELECT @Sariya = OrgNode
FROM employee WHERE EmployeeID = 46
SELECT EmpName, Title, Salary, OrgNode.ToString() AS 'OrgNode'
FROM employee
WHERE OrgNode.GetAncestor(1) = @Sariya
GO
--Wynik
--EmpName Title Salary OrgNode
--------- --------- ------- -------
--Wanida Assistant 8000.00 /1/1/
Kwerenda wykorzystuje metodę GetAncestor typu hierarchyid, która zwraca rodzica aktualnego węzła hierarchyid. W zaprezentowanym kodzie zmiennej @Sariya przypisany został węzeł hierarchii dla pracownika Sariya. Wynika to z tego, że Sariya stanowi bezpośredniego przodka wszystkich pracowników, którzy są jej podwładnymi. A zatem rozwijanie kwerendy, która zwraca pracowników bezpośrednio podlegających Sariyi, składa się z pobrania z drzewa węzła Sariya, a następnie wybrania wszystkich pracowników, dla których węzłem przodka jest węzeł Sariya.
Kolumny hierarchyid zajmują z reguły bardzo mało miejsca, jednak liczba bitów niezbędnych do reprezentowania węzła w drzewie zależy od średniej liczby potomków w węźle (nazywanej również obciążeniem węzła). Na przykład nowy węzeł w hierarchii organizacyjnej 100 000 pracowników ze średnim obciążeniem sześciu poziomów wymagałby około 5 bajtów miejsca.
Typ danych hierarchyid oferuje szereg metod, które ułatwiają pracę z danymi hierarchicznymi. Ich zestawienie zostało zaprezentowane na Rysunku 9. Szczegółowe informacje na temat wszystkich metod znaleźć można w dokumentacji SQL Server Books Online (msdn2.microsoft.com/ms130214).
Metoda | Opis |
GetAncestor | Zwraca typ hierarchyid, który reprezentuje n-tego przodka tego węzła hierarchyid. |
GetDescendant | Zwraca węzeł podrzędny tego węzła hierarchyid. |
GetLevel | Zwraca liczbę, która reprezentuje głębokość tego węzła hierarchyid w ogólnej hierarchii. |
GetRoot | Zwraca węzeł korzenia hierarchyid tego drzewa hierarchii.Statyczna. |
IsDescendant | Zwraca true, jeśli przekazany węzeł podrzędny jest potomkiem tego węzła hierarchyid. |
Parse | Konwertuje reprezentację typu string hierarchii na wartość hierarchyid.Statyczna. |
Reparent | Przenosi węzeł hierarchii do nowej lokalizacji w obrębie hierarchii. |
ToString | Zwraca ciąg znaków, który zawiera logiczną reprezentację tego węzła hierarchyid. |
Rysunek 9: Metody dostarczane przez typ danych hierarchyid.
Do początku strony
Przestrzenne typy danych
Dane przestrzenne to dane, które identyfikują geograficzne lokalizacje i kształty, w szczególności na kuli ziemskiej. Mogą to być punkty orientacyjne, drogi, a nawet lokalizacje firm. SQL Server 2008 oferuje typy danych geography oraz geometry, które służą do pracy z tego typu danymi.
Typ danych geography współpracuje z informacjami zlokalizowanymi wokół kuli ziemskiej. Model kuli ziemskiej uwzględnia w obliczeniach zakrzywioną powierzchnię Ziemi. Informacje o pozycji są podawane przy użyciu długości i szerokości geograficznej. Model ten dobrze sprawdza się w zastosowaniach takich jak transport transoceaniczny, planowanie wojskowe, a także w zastosowaniach o krótszym zasięgu nawiązujących do powierzchni Ziemi. Ten model należy wykorzystać w przypadku danych wyrażanych przy pomocy długości i szerokości geograficznej.
Typ danych geometry współpracuje z modelem płaskim. W tym modelu Ziemia jest traktowana jako płaska projekcja rozpoczynająca się od określonego punktu. Płaski model nie bierze pod uwagę krzywizny kuli ziemskiej, a zatem jest wykorzystywany głównie do opisywania krótkich odległości, na przykład w aplikacji bazodanowej, która zawiera plany wnętrz budynków.
Typy geography oraz geometry są konstruowane na podstawie obiektów wektorowych, określonych w formacie Well-Known Text (WKT) lub Well-Known Binary (WKB). Są to formaty transportowe dla danych przestrzennych opisane w specyfikacji Open Geospatial Consortium (OGC) Simple Features for SQL Specification. Rysunek 10 prezentuje listę siedmiu typów obiektów wektorowych wspieranych przez SQL Server 2008.
Obiekt | Opis |
Point | Lokalizacja. |
MultiPoint | Seria punktów. |
LineString | Seria obejmująca zero lub więcej punktów połączonych liniami. |
MultiLineString | Zestaw obiektów LineString. |
Polygon | Spójny region opisany przez zbiór zamkniętych obiektów LineString. |
MultiPolygon | Zestaw wielokątów. |
GeometryCollection | Kolekcja typów geometrycznych. |
Rysunek 10: Obiekty wektorowe wspierane przez SQL Server 2008.
Aby skonstruować typ geography przy użyciu jednego lub więcej obiektów wektorowych, deklarujemy najpierw typ geography w skrypcie T-SQL, jak pokazano na Rysunku 11. Następnie wywołujemy jedną z metod wymienionych na Rysunku 12 i przekazujemy do niej ciąg znaków dla obiektu wektorowego oraz Spatial Reference Identifier (SRID). SRID stanowi system identyfikacji współrzędnych geograficznych, zdefiniowany przez organizację European Petroleum Survey Group. Jest on częścią zestawu standardów stworzonych z myślą o magazynowaniu danych kartograficznych, pomiarowych oraz geodezyjnych. Każdy SRID identyfikuje określony typ elipsoidy, który ma zostać zastosowany w obliczeniach geograficznych. Jest to niezbędne, ponieważ ziemia nie stanowi idealnej sfery. SQL Server 2008 może jedynie realizować obliczenia dla identycznych identyfikatorów SRID.
DECLARE @geo1 geometry
SELECT @geo1 = geometry::STGeomFromText('POINT (3 4)', 0)
PRINT @geo1.ToString()
DECLARE @geo2 geometry
SELECT @geo2 = geometry::Parse('POINT(3 4 7 2.5)')
PRINT @geo2.STX;
PRINT @geo2.STY;
PRINT @geo2.Z;
PRINT @geo2.M;
DECLARE @geo3 geography;
SELECT @geo3 = geography::STGeomFromText(
'LINESTRING(47.656 -122.360, 47.656 -122.343)', 4326);
SELECT @geo3.ToString();
--Wynik
--POINT (3 4)
--3
--4
--7
--2.5
DECLARE @gx geometry;
SET @gx = geometry::STPolyFromText(
'POLYGON ((5 5, 10 5, 10 10, 5 5))', 0);
PRINT @gx.ToString();
--Wynik
--POLYGON ((5 5, 10 5, 10 10, 5 5))
Rysunek 11: Tworzenie punktów, linii i geometrii wielokątów.
Metoda | Opis |
STGeomFromText | Konstruuje instancję dowolnego typu geograficznego na podstawie tekstu wejściowego. |
STPointFromText | Konstruuje geograficzną instancję Point na podstawie tekstu wejściowego. |
STMPointFromText | Konstruuje geograficzną instancję MultiPoint na podstawie tekstu wejściowego. |
STLineFromText | Konstruuje geograficzną instancję LineString na podstawie tekstu wejściowego. |
STMLineFromText | Konstruuje geograficzną instancję MultiLineString na podstawie tekstu wejściowego. |
STPolyFromText | Konstruuje geograficzną instancję Polygon na podstawie tekstu wejściowego. |
STMPolyFromText | Konstruuje geograficzną instancję MultiPolygon na podstawie tekstu wejściowego. |
STGeomCollFromText | Konstruuje geograficzną instancję GeometryCollection na podstawie tekstu wejściowego. |
Rysunek 12: Konstruowanie obiektów geograficznych i geometrycznych.
Do początku strony
Różnica pomiędzy geografią a geometrią
Typy danych geography oraz geometry służą do pracy z różnymi typami danych, a zatem należy zdawać sobie sprawę z pewnych kluczowych różnic. W przypadku typu geometry odległości oraz powierzchnie są podawane w tych samych jednostkach miary co współrzędne instancji. Na przykład, odległość między punktami (0,0) oraz (6,8) będzie zawsze wynosić 10 jednostek. Natomiast inaczej jest w przypadku typu geography, który współpracuje z współrzędnymi elipsoidalnymi wyrażanymi w stopniach długości i szerokości geograficznej.
Typ danych GEOMETRY zwraca „niewłaściwy” wynik, gdy współrzędne są wyrażane za pomocą par długości i szerokości geograficznej. Następujący kod T-SQL wylicza odległość między punktami (90 0) oraz (90 180). Oba te punkty odwołują się do bieguna północnego, a zatem odległość między nimi powinna wynosić 0. Jednak w przypadku typu GEOMETRY wyliczona odległość wynosi 180.
DECLARE @g1 GEOMETRY, @g2 GEOMETRY, @g3 GEOGRAPHY, @g4 GEOGRAPHY
SELECT @g1 = GEOMETRY::STGeomFromText('POINT (90 0)', 0)
SELECT @g2 = GEOMETRY::STGeomFromText('POINT (90 180)', 0)
SELECT @g3 = GEOGRAPHY::STGeomFromText('POINT (90 0)', 4326)
SELECT @g4 = GEOGRAPHY::STGeomFromText('POINT (90 180)', 4326)
SELECT @g2.STDistance(@g1) AS 'GEOMETRY',
@g4.STDistance(@g3) AS 'GEOGRAPHY';
--Wynik
--GEOMETRY GEOGRAPHY
------------------------ ----------------------
--180 0
Te dwa typy danych różnią się także pod względem orientacji danych przestrzennych. W systemie płaskim wykorzystywanym w typie danych geometry, orientacja prostokąta nie stanowi istotnego czynnika. Na przykład prostokąt o współrzędnych ((0, 0), (10, 0), (0, 20), (0, 0)) jest traktowany tak samo jak prostokąt ((0, 0), (0, 20), (10, 0), (0, 0)). Natomiast w modelu danych wykorzystywanym przez typ danych geography, prostokąt jest nieokreślony bez wskazania orientacji. Weźmy pod uwagę na przykład okrąg wokół równika. Czy prostokąt opisany przez ten okrąg odwołuje się do północnej czy południowej półkuli? W przypadku pracy z danymi typu geography, orientacja i lokalizacja muszą być odpowiednio opisane.
Typ danych geography objęty jest również w SQL Server 2008 pewnymi ograniczeniami. Na przykład każda instancja typu geography musi mieścić się na pojedynczej półkuli. Większe obiekty przestrzenne nie są dozwolone i spowodują wywołanie wyjątku ArgumentException. Typy danych geography wymagające dwóch obiektów wejściowych zwracają NULL, jeśli wynik działania metod nie mieści się na pojedynczej półkuli.
SQL Server oferuje wiele metod, które pozwalają na wykonywanie operacji na typach geography oraz geometry. Rysunek 13 prezentuje pewne przykłady zastosowania metod dostarczanych przez SQL Server 2008 do pracy z danymi przestrzennymi. Ze względu na ograniczoną ilość miejsca nie można było przedstawić bardziej szczegółowych informacji na ten temat, ale pełny opis znajduje się w dokumentacji SQL Server Books Online.
DECLARE @gm geometry;
DECLARE @gg geography;
DECLARE @h geography;
SET @gm = geometry::STGeomFromText('POLYGON((0 0, 13 0, 3 3, 0 13, 0 0),(2 2, 2 1, 1 1, 1 2, 2 2))', 0);
SELECT @gm.STArea();
--Wynik
--38
SET @gg = geography::STGeomFromText('LINESTRING(0 0, 5 5)', 4326);
--Wyliczenie odległości do punktu odrobinę odsuniętego od LINESTRING.
SET @h = geography::STGeomFromText('POINT(4 4)', 4326);
SELECT @gg.STDistance(@h);
--Wynik
-- 430.182777043046
-- Wyliczenie odległości do punktu na linii LINESTRING.
SET @h = geography::STGeomFromText('POINT(5 5)', 4326);
SELECT @gg.STDistance(@h);
--Wynik
-- 0
DECLARE @temp table ([name] varchar(10), [geom] geography);
INSERT INTO @temp values ('Point', geography::STGeomFromText('POINT(
5 10)', 4326));
INSERT INTO @temp values ('LineString', geography::STGeomFromText(
'LINESTRING(13 5, 50 25)', 4326));
-- Wyliczenie odległości do punktu na linii LINESTRING.
--Wyświetlenie liczby wymiarów dla obiektu geograficznego składowanego
--w zmiennej tabeli.
INSERT INTO @temp values ('Polygon', geography::STGeomFromText(
'POLYGON((47.653 -122.358, 47.649 -122.348, 47.658 -122.348, 47.658 -122.358, 47.653 -122.358))', 4326));
SELECT [name], [geom].STDimension() as [dim]
FROM @temp;
--Wynik
--name dim
------------ -----------
--Point 0
--LineString 1
--Polygon 2
Rysunek 13: Praca z danymi przestrzennymi.
Autorka niniejszego artykułu ma nadzieję, że informacje dotyczące siedmiu nowych typów danych w SQL Server 2008 okażą się pomocne.
O autorze
Kelly Wilson zajmuje się inżynierią oprogramowania od ponad 20 lat. Do jej osiągnięć należą aplikacje z obszaru SQL Server, grafiki 3D, gier i nauki o kolorach. Kelly pracuje aktualnie na stanowisku Programming Writer w grupie SQL Server w firmie Microsoft.
Do początku strony |