Posts tagged spatials

Kolega zapytał mnie dzisiaj, jak przenieść między bazami danych w SQL Server 2008 / 2008 R2 dane geograficzne za pomocą Import/Export Wizarda. Przyznam, że nie znałem odpowiedzi na pytanie, ale z kontekstu pytania domyśliłem się, w czym problem. Otóż wizard ten nie oferuje w ogóle obsługi typów danych zaimplementowanych z użyciem CLR (wyjątkiem jest tu XML, który ma dość oczywistą postać tekstową). Jak sobie zatem poradzić z problemem, gdy chcemy przenieść takie dane między dwoma serwerami (nie między bazami na jednym serwerze – to jest do zrobienia za pomocą prostych poleceń SELECT…INTO lub INSERT INTO…SELECT) ? Rozwiązań jest kilka. Ja pokażę trzy.

Metoda 1. – Generowanie skryptu w Management Studio

W Management Studio (SSMS) w oknie Object Explorer klikamy prawym przyciskiem myszy na bazie danych i wybieramy Tasks – Generate Scripts…

W wizardzie do generowania skryptów wybieramy tabelę z danymi geograficznymi i klikamy Next, aż zobaczymy ekran z przyciskiem Advanced…

Oczywiście, klikamy na tym przycisku (swoją drogą łatwym do przeoczenia!), by ujrzeć zaawansowaną (?) konfigurację skryptowania. Szukamy pozycji Types of data for script i wybieramy w niej Schema and data. To zapewni nam zeskryptowanie zarówno definicji tabeli, jak i jej danych.

W przypadku, gdy zdecydujesz, że wygenerowany kod T-SQL ma być od razu wrzucony do okna nowego skryptu, możesz otrzymać komunikat informujący o dłuuuuugich liniach w tekście. Rozchodzi się o to, że dane binarne (geograficzne) są skryptowane “as is” tj. w postaci heksadecymaliów. Nie trzeba dużej wyobraźni, by domyśleć się, że kształt granic Polski zapisanych za pomocą tysięcy punktów to całkiem duuuże binarium wymagające spoooorej liczby bajtów :-)

WAŻNE: Jeżeli odpowiesz “Tak” (a czy masz jakiś wybór? ;-)) na pytanie z okienka powyżej, spodziewaj się, że SSMS zacznie działać zauważalnie wolniej (a czasem po prostu zawiśnie lub zwróci OutOfMemoryException)…

Skrypt zapisujemy (albo i nie), otwieramy połączenie z drugą instancją SQL Server i jesteśmy gotowi do przerzucenia danych.

Metoda 2. – Import/Export Wizard

Druga metoda bazuje na użyciu Import/Export Wizarda i pewnego chwytu, który może wydać się dość rozpaczliwy, ale przy braku obsługi danych geograficznych w owym wizardzie… :-)

A zatem: Start – Uruchom – dtswizard.exe. Otwieramy “czarodzieja”, wybieramy serwer źródłowy i bazę źródłową, serwer docelowy i bazę docelową, a następnie… Skoro wiemy, że wizard nie daje sobie rady z typem geography, trzeba ów typ zmienić. Jak? Po prostu rzutując dane geograficzne na typ binarny varbinary(max).

Dalej jest już prosto. Dla tak spreparowanej binarnej kolumny wybieramy jako kolumnę docelową kolumnę typu image lub varbinary(max). A potem? Gdy już dane uda się zaimportować, logujemy się do docelowego serwera i na docelowej tabeli wykonujemy dwie operacje:

1. Dodajemy kolumnę typu geography.
2. Kopiujemy dane z kolumny binarnej, w której wylądowały dane geograficzne w procesie przenoszenia danych wizardem, do kolumny utworzonej chwilę wcześniej.

Bez dwóch zdań, wstyd dla Microsoftu, że wizard nie daje sobie rady z typami przestrzennymi (zresztą, to samo jest z typem hierarchyid).

Metoda 3. – Pass-through query

Trzecia metoda oparta jest na mechanizmie serwerów dołączonych (linked servers). Tworzymy na instancji źródłowej, z której chcemy eksportować dane geograficzne, linked server wskazujący na instancję docelową. Na docelowej instancji w odpowiedniej bazie danych zakładamy tabelę, do której dane geograficzne chcemy wrzucić, i konstruujemy tzw. pass-through query, czyli coś w stylu:

INSERT INTO OPENQUERY(
  [ASUS\SQL2008],
  'SELECT CountryName, CountryNameLocal, geom FROM Spatials.dbo.Country_Copy'
)
SELECT CountryName, CountryNameLocal, geom FROM Spatials.dbo.Country;

W powyższym kodzie [ASUS\SQL2008] to nazwa linked servera. Drugim parametrem w powyższym użyciu funkcji OPENQUERY jest zapytanie, którego zadaniem jest wskazanie tabeli i kolumn, do których chcemy wstawić dane. Wygląda niecodziennie, ale najważniejsze, że działa.

A za to nie działają dwa, wydawałoby się bardziej pospolite polecenia:

SELECT * INTO [ASUS\SQL2008].Spatials.dbo.Country_Copy FROM Spatials.dbo.Country;

-- Wynik:
-- Msg 117, Level 15, State 1, Line 1
-- The object name 'ASUS\SQL2008.Spatials.dbo.Country_Copy' 
-- contains more than the maximum number of prefixes. The maximum is 2.

INSERT INTO [ASUS\SQL2008].Spatials.dbo.Country_Copy (CountryName, CountryNameLocal, geom)
SELECT CountryName, CountryNameLocal, geom FROM Spatials.dbo.Country;

-- Wynik:
-- Msg 7325, Level 16, State 1, Line 1
-- Objects exposing columns with CLR types are not allowed 
-- in distributed queries. Please use a pass-through query 
-- to access remote object '"Spatials"."dbo"."Country_Copy"'.

Podsumowanie

Jak widać, przemieszczenie danych geograficznych (lub jakichkolwiek danych zapisanych w kolumnach typów opartych o CLR) nie jest trywialne. Pewnie, można wziąć backup bazy i odtworzyć na docelowym serwerze. Ale jeżeli baza jest duża, a ilość danych do przemieszczenia niewielka, ta metoda już nie wydaje się najlepsza. Poza tym co, jeśli chcemy przenieść dane między SQL Server 2008 R2 a SQL Server 2008? (swoisty downgrade) Wtedy już metoda oparta o backup (czy – równoważnie – o detach/attach) nie wchodzi w grę, bo po prostu nie da się przenieść bazy z wersji wyższej SQL Servera do wersji niższej. O ile wiadomo, że dtswizard.exe to prosta aplikacja, której zadaniem jest dokonywanie najprostszych transferów danych, o tyle brak obsługi danych przestrzennych w SSIS już nieco dziwi. Ale cóż, widać rozwój jednych “ficzerów” SQL Servera jest zbyt szybki i niektóre teamy w Redmond zwyczajnie za nim nie nadążają. Normalna sytuacja w rozbudowanym procesie twórczym skomplikowanego systemu, jakim jest SQL Server :-)