MySQL Spatial i PostGIS - GISLab - Uniwersytet Przyrodniczy we

MySQL Spatial i PostGIS –
implementacje standardów danych
przestrzennych
Adam Piórkowski, [email protected]
AKADEMIA GÓRNICZO-HUTNICZA im. STANISŁAWA STASZICA
Wydział Geologii, Geofizyki i Ochrony Środowiska
Katedra Geoinformatyki i Informatyki Stosowanej
Instytut Geodezji i Geoinformatyki
Uniwersytetu Przyrodniczego we
Wrocławiu i Polski Oddział OSGeo.
II Konferencja z cyklu „Wolne
oprogramowanie w geoinformatyce”
na temat WSPÓŁCZESNE TRENDY I
PERSPEKTYWY ROZWOJU
13-14 maja 2010, Uniwersytet
Przyrodniczy we Wrocławiu,
Centrum Naukowo-Dydaktyczne,
Plac Grunwaldzki 24a, aula Jana
Pawła II
Agenda
• Wolnodostępne bazy danych
• Rozszerzenia przestrzenne w s.z.b.d.
• Standardy opisu danych przestrzennych
• Standard OpenGIS
• Standard SQL/MM
• MySQL Spatial
• PostGIS
• Różnice w implementacji standardu OpenGIS
Wolnodostępne bazy danych
MySQL AB
Licencja PostgreSQL,
oparta na licencji Open Source,
podobnej do licencji BSD
Licencja GPL,
konieczna wersja komercyjna
w przypadku dystrybucji wraz
z oprogramowaniem
Rozszerzenia przestrzenne
Rozwiązania zintegrowane z konkretnymi s.z.b.d.
Oracle: Oracle Spatial
http://www.oracle.com/technology/documentation/spatial.html
MS SQL 2008 – Spatial Storage
http://technet.microsoft.com/en-us/library/bb933790.aspx
IBM DB2 - Spatial Extender l
http://www-01.ibm.com/software/data/spatial/
MySQL Spatial
http://dev.mysql.com/doc/refman/5.1/en/spatial-extensions.html
PostgreSQL – PostGIS
http://postgis.refractions.net/
Inne: ArcGIS (ESRI), SDE(ESRI)
Standard OpenGIS
OGC - The Open Geospatial Consortium
http://www.opengeospatial.org/
Standard:
SFS - Simple Features SQL
OpenGIS Implementation Specification
for Geographic information
- Simple feature access - SQL option
Opis danych (tylko) 2D
Standard obejmuje:
• definicje obiektów,
• format zapisu obiektów (WKB, WKT),
• operacje na obiektach,
• indeksowanie.
Standard OpenGIS
Geometry
Obiekt geometryczny
Point
Curve
Surface
GeometryCollection
punkt
krzywa
powierzchnia
kolekcja
LineString
Polygon
linia łamana
wielokąt
MultiSurface
MultiCurve
MultiPolygon
Line
LineRing
linia
krzywa zamknięta
MultiPoint
MultiLineString
Standard OpenGIS
FORMAT WKB i WKT
• Format WKT (ang. Well-Known Text)
Zapis danych w postaci tekstowej, np:
-
POINT(1 1)
LINESTRING(1 1,2 2,3 4)
POLYGON((1 1,5 1,5 5,1 5,1 1),(3 4, 4 3, 4 4, 3 4, 5 3))
MULTIPOINT(3 4,4 3)
MULTILINESTRING((1 1,2 2,5 1),(4 3,5 2,5 4))
• Format WKB (ang. Well-Known Binary)
Binarny format składowania danych, zawiera informacje
o kodowaniu, rodzaju figury i koordynaty (zmiennoprzecinkowe).
01 01000000 000000000000F02A
000000000000F018
rodzaj zapisu bitowego figura, tu: punkt
01 = młodszy/starszy
X
Y
Standard OpenGIS
Funkcje proste zdefiniowane w OpenGIS:
-
Dimension ( ) — rozmiar obiektu,
GeometryType ( ) — nazwa (rodzaj) obiektu,
Envelope( ) — zwraca minimalny obiekt zawierający daną figurę,
AsText( ) — zapisuje obiekt w postaci WKT,
AsBinary( ) — zapisuje obiekt w postaci WKB,
IsEmpty( ) — sprawdza, czy obiekt nie jest pusty,
IsSimple( ) — sprawdza, czy obiekt nie jest złożony,
Boundary( ) — tworzy obiekt ograniczający.
Standard OpenGIS
Relacje między obiektami zdefiniowane w OpenGIS:
-
Equals(g1:g2) – figury są równe (identyczne),
Disjoint(g1:g2) – figury są rozłączne,
Intersects(g1:g2) – figury,
Touches(g1:g2) – figury się stykają,
Crosses(g1:g2) – figury się przecinają,
Within(g1:g2) – figura g2 zawiera się w figurze g1,
Contains(g1:g2) - figura g2 zawiera figurę g1,
Overlaps(g1:g2) – g2 nakłada się na g1,
Relate(anotherGeometry:Geometry,
intersectionPatternMatrix:String)
=> badana jest zależność między figurami, do badania części
wspólnej wykorzystywany jest wzorzec badania sąsiedztwa
w postaci macierzy.
Standard OpenGIS
Funkcje analityczne zdefiniowane w OpenGIS:
- Distance(g1:g2) – podaje odległość między dwoma figurami
w metryce danej przestrzeni,
- Buffer(distance:Double) - ConvexHull( ) - otoczka wypukła dla danego obiektu,
- Intersection(g1:g2) – tworzy obiekt części wspólnej dwóch figur,
- Union(g1:g2) – tworzy sumę dwóch figur,
- Difference(g1:g2) – tworzy różnicę figur g2-g1,
- SymDifference(anotherGeometry:Geometry).
Standard SQL/MM
OGC - The Open Geospatial Consortium
http://www.opengeospatial.org/
Składowe standardu:
- 1 – Framework
- 2 – Full Text
- 3 – Spatial
- 4 – General Purpose Facilities – wycofany
- 5 – Still Image
- 6 – Data Mining
- 7 - History
Standard SQL/MM
SQL/MM Spatial
-0, 1, 2, 3 wymiary danych
... Dużo więcej możliwości
OpenGIS vs. SQL/MM
Konwencja nazewnicza – typy geometryczne
OpenGIS
SQL/MM
Point
Curve
Linestring
ST_Point
ST_Curve
ST_Linestring
ST_Circularstring
ST_CompoundCurve
ST_Surface
ST_CurvePolygon
ST_Polygon
ST_Collection
ST_Multipoint
ST_MultiCurve
ST_Multilinestring
ST_Multisurface
ST_Multipolygon
Surface
Polygon
GeomCollection
Multipoint
Multicurve
Multilinestring
Multisurface
Multipolygon
OpenGIS vs. SQL/MM
Konwencja nazewnicza – operacje
OpenGIS
SQL/MM
Equals
Disjoint
Touches
Within
Overlaps
Crosses
Intersects
Contains
Relate
ST_Equals
ST_Disjoint
ST_Touches
ST_Within
ST_Overlaps
ST_Crosses
ST_Intersects
ST_Contains
ST_Relate
MySQL Spatial
ten sam producent,
pakiet ściśle zintegrowany z s.z.b.d.,
dostępny w każdej wersji s.z.b.d.,
dostępny dla silników:
MyISAM, InnoDB, NDB, ARCHIVE,
• niepełna implementacja standardu OGC,
• funkcje,
• układ odniesienia – tylko planarny,
• dokumentacja – refman – 23 strony
•
•
•
•
PostGIS
• producent: Refractions Research
http://postgis.refractions.net/
• projekt ‘żywy’ – obecna wersja 1.5.1,
• GNU General Public License,
• pakiet do doinstalowania w s.z.b.d. –
dostępny w ‘Stack builder’,
• wersja zależna od wersji s.z.b.d.,
• różnice w funkcjonalności między wersjami,
• implementacja standardu OGC,
• implementacja standardu SQL/MM,
• definicja i implementacja standardu PostGIS,
• dokumentacja podstawowa – 311stron.
PostGIS
Standard PostGIS rozszerza o:
•
•
•
•
•
formaty EWKT, EWKB i HEXEWKB,
obsługę 3dm,3dz,4d,
wbudowane układy odniesienia (+ dane!),
obiekty interpolowane krzywymi (SQL/MM),
OBIEKTY GEOGRAPHY
• analogicznie do GEOMETRY,
• opisują lokalizacje na sferze lub sferoidzie
PostGIS
Przykładowy układ odniesienia:
PROJCS["NAD83 / UTM Zone 10N",
GEOGCS["NAD83",
DATUM["North_American_Datum_1983",
SPHEROID["GRS 1980",6378137,298.257222101]
],
PRIMEM["Greenwich",0],
UNIT["degree",0.0174532925199433]
],
PROJECTION["Transverse_Mercator"],
PARAMETER["latitude_of_origin",0],
PARAMETER["central_meridian",-123],
PARAMETER["scale_factor",0.9996],
PARAMETER["false_easting",500000],
PARAMETER["false_northing",0],
UNIT["metre",1]
]
Układy odniesienia
http://spatialreference.org/
Układy odniesienia
Implementacja OGC Konwencja nazewnicza
MySQL
- Konwencja OGC,
- wyznaczanie długości – GLength
(Length zarezerwowane dla VARCHAR)
PostgreSQL
- Konwencja SQL/MM
Implementacja OGC
Przykład
20
Gmina
STAROPOLSKA
15
Ciek
3
1
10
5
4
2
0
5
10
15
20
25
Implementacja OGC
Przykład
Implementacja OGC -
Tworzenie bazy i tabeli z danymi przestrzennymi
MySQL
mysql=> CREATE DATABASE daneprzestrzenne;
mysql=> CONNECT daneprzestrzenne;
mysql=> CREATE TABLE Gminy(id_gminy INT,
nazwa_gminy VARCHAR(100), obszar POLYGON);
mysql=> CREATE TABLE Drogi(id_drogi INT,
przebieg LINESTRING);
Implementacja OGC -
Tworzenie bazy i tabeli z danymi przestrzennymi
PostGIS/PostgreSQL
psql=# CREATE DATABASE przestrzenna1;
CREATE DATABASE
psql=# \c przestrzenna1
You are now connected to database "przestrzenna1".
psql=# CREATE TABLE Gminy(id_gminy INT, nazwa_gminy
VARCHAR(100));
CREATE TABLE
psql=# SELECT AddGeometryColumn('', 'gminy','obszar',-1,
'POLYGON',2);
ERROR: function addgeometrycolumn(unknown, unknown, unknown,
integer, unknown, integer) does not exist at character 8
HINT: No function matches the given name and argument types.
You might need to add explicit type casts.
Implementacja OGC -
Tworzenie bazy i tabeli z danymi przestrzennymi
PostGIS/PostgreSQL
psql=# CREATE DATABASE przestrzenna TEMPLATE=template_postgis;
CREATE DATABASE
psql=# \c przestrzenna
You are now connected to database "przestrzenna".
psql=# CREATE TABLE Gminy(id_gminy INT, nazwa_gminy
VARCHAR(100));
CREATE TABLE
psql=# SELECT AddGeometryColumn('', 'gminy','obszar',1,'POLYGON',2);
addgeometrycolumn
-------------------------------------------------public.gminy.obszar SRID:-1 TYPE:POLYGON DIMS:2
(1 row)
Implementacja OGC -
Tworzenie bazy i tabeli z danymi przestrzennymi
PostGIS/PostgreSQL
CREATE TABLE Gminy(id_gminy INT, nazwa_gminy VARCHAR(100));
SELECT AddGeometryColumn('', 'gminy','obszar',-1,'POLYGON',2);
CREATE TABLE Drogi(id_drogi INT);
SELECT AddGeometryColumn('', 'drogi','przebieg',-1,
'LINESTRING',2);
Implementacja OGC -
Wpisywanie danych przestrzennych
MySQL, PostGIS/PostgreSQL
INSERT INTO Gminy (id_gminy, Nazwa_gminy, obszar)
VALUES (1, 'Staropolska', GeomFromText('POLYGON((6 3, 6 19,
21 19, 21 3, 6 3))'));
INSERT INTO Drogi(id_drogi, przebieg)
VALUES (1, GeomFromText('LINESTRING(0 10, 15 10, 25 13)', -1));
INSERT INTO Drogi(id_drogi, przebieg)
VALUES (2, GeomFromText('LINESTRING(0 2, 10 0)', -1));
INSERT INTO Drogi(id_drogi, przebieg)
VALUES (3, GeomFromText('LINESTRING(10 10, 10 15)', -1));
INSERT INTO Drogi(id_drogi, przebieg)
VALUES (4, GeomFromText('LINESTRING(0 5, 10 0)', -1));
Implementacja OGC Proste zapytania
mysql> SELECT id_drogi, GLENGTH(przebieg) FROM Drogi;
+----------+-------------------+
| id_drogi | GLENGTH(przebieg) |
+----------+-------------------+
|
1 | 25.4403065089106 |
|
2 | 10.1980390271856 |
|
3 |
5 |
|
4 | 11.1803398874989 |
+----------+-------------------+
mysql> SELECT id_drogi, LENGTH(przebieg) FROM Drogi;
+----------+------------------+
| id_drogi | LENGTH(przebieg) |
+----------+------------------+
|
1 |
61 |
|
2 |
45 |
|
3 |
45 |
|
4 |
45 |
+----------+------------------+
Implementacja OGC Proste zapytania
psql=# SELECT id_drogi, LENGTH(przebieg) FROM Drogi;
id_drogi |
length
----------+-----------------1 | 25.4403065089106
2 | 10.1980390271856
3 |
5
4 | 11.1803398874989
(4 rows)
Implementacja OGC Proste zapytania
mysql> SELECT Nazwa_gminy, Area(obszar) FROM Gminy;
+-------------+--------------+
| Nazwa_gminy | Area(obszar) |
+-------------+--------------+
| Staropolska |
240 |
+-------------+--------------+
1 row in set (0.00 sec)
psql=# SELECT Nazwa_gminy, Area(obszar) FROM Gminy;
nazwa_gminy | area
-------------+-----Staropolska | 240
(1 row)
Implementacja OGC -
Relacje między obiektami (1)
mysql> SELECT id_drogi, Nazwa_gminy FROM Drogi, Gminy
WHERE Contains(obszar, przebieg);
+----------+-------------+
| id_drogi | Nazwa_gminy |
+----------+-------------+
|
3 | Staropolska |
+----------+-------------+
1 row in set (0.00 sec)
psql=# SELECT id_drogi, Nazwa_gminy FROM Drogi, Gminy
WHERE Contains(obszar, przebieg);
id_drogi | nazwa_gminy
----------+------------3 | Staropolska
(1 row)
Implementacja OGC -
Relacje między obiektami (2)
mysql> SELECT id_drogi, Nazwa_gminy FROM Drogi, Gminy
WHERE Disjoint(obszar, przebieg);
+----------+-------------+
| id_drogi | Nazwa_gminy |
+----------+-------------+
|
2 | Staropolska |
+----------+-------------+
1 row in set (0.00 sec)
psql=# SELECT id_drogi, Nazwa_gminy FROM Drogi, Gminy
WHERE Disjoint(obszar, przebieg);
id_drogi | nazwa_gminy
----------+------------2 | Staropolska
4 | Staropolska
(2 rows)
Implementacja OGC -
Relacje między obiektami (3)
mysql> SELECT id_drogi, Nazwa_gminy FROM Drogi, Gminy
WHERE Intersects(obszar, przebieg);
+----------+-------------+
| id_drogi | Nazwa_gminy |
+----------+-------------+
|
1 | Staropolska |
|
3 | Staropolska |
|
4 | Staropolska |
+----------+-------------+
3 rows in set (0.00 sec)
psql=# SELECT id_drogi, Nazwa_gminy FROM Drogi, Gminy
WHERE Intersects(obszar, przebieg);
id_drogi | nazwa_gminy
----------+------------1 | Staropolska
3 | Staropolska
(2 rows)
Implementacja OGC -
Relacje między obiektami (4)
„The OpenGIS specification defines the following functions. Currently,
MySQL does not implement them according to the specification. Those
that are implemented return the same result as the corresponding
MBR-based functions.”
Funkcje testujące zależności przestrzenne są dostępne W przyszłości (od 2004 r.) mają zostać zaimplementowane
prawidłowo, obecnie podają wyniki funkcji działających na
prostokątach MBR.
MBRIntersects(@geom, @geoin)
Intersects(@geom, @geoin)
Implementacja OGC -
Działanie funkcji analitycznych
mysql> SELECT id_drogi, AsText(ENVELOPE(przebieg)),
Dimension(ENVELOPE(przebieg)) AS "Wymiar" FROM Drogi;
+----------+------------------------------------------+--------+
| id_drogi | AsText(ENVELOPE(przebieg))
| Wymiar |
+----------+------------------------------------------+--------+
|
1 | POLYGON((0 10,25 10,25 13,0 13,0 10))
|
2 |
|
2 | POLYGON((0 0,10 0,10 2,0 2,0 0))
|
2 |
|
3 | POLYGON((10 10,10 10,10 15,10 15,10 10)) |
2 |
|
4 | POLYGON((0 0,10 0,10 5,0 5,0 0))
|
2 |
+----------+------------------------------------------+--------+
psql=# SELECT id_drogi, AsText(ENVELOPE(przebieg)),
ST_Dimension(ENVELOPE(przebieg)) AS "Wymiar" FROM drogi;
id_drogi |
astext
| Wymiar
----------+---------------------------------------+-------1 | POLYGON((0 10,0 13,25 13,25 10,0 10)) |
2
2 | POLYGON((0 0,0 2,10 2,10 0,0 0))
|
2
3 | LINESTRING(10 10,10 15)
|
1
4 | POLYGON((0 0,0 5,10 5,10 0,0 0))
|
2
Implementacja OGC Funkcje agregujące
mysql> SELECT SUM(GLength(przebieg)) FROM Drogi;
+------------------------+
| SUM(GLength(przebieg)) |
+------------------------+
|
51.8186854235951 |
+------------------------+
1 row in set (0.00 sec)
psql=# SELECT SUM(ST_Length(przebieg)) FROM Drogi;
sum
-----------------51.8186854235951
(1 row)
Implementacja OGC -
Zaawansowane konstrukcje zapytań
PostGIS
psql=# SELECT
Length(ST_Union(ARRAY(SELECT przebieg FROM drogi)));
length
-----------------51.8186854235951
(1 row)
psql=# SELECT Length(ST_Union(przebieg)) FROM drogi;
length
-----------------51.8186854235951
(1 row)
Implementacja OGC -
Funkcje operacji geometrycznych
MySQL
„OpenGIS proposes a number of other functions that can produce geometries. They
are designed to implement spatial operators. These functions are not implemented
in MySQL.” – najnowszy refman ….
•
•
•
•
•
•
Buffer(g,d)
ConvexHull(g)
Difference(g1,g2)
Intersection(g1,g2)
SymDifference(g1,g2)
Union(g1,g2)
Literatura
Piórkowski A. 2011. Mysql spatial and postgis – implementations of spatial data standards, EJPAU 14(1), #03.
http://www.ejpau.media.pl/volume14/issue1/art-03.pdf
[1] OGC - The Open Geospatial Consortium, [@:] http://www.opengeospatial.org/
[2] OpenGIS Implementation Specification for Geographic information - Simple feature access - SQL
option. [@:] http://www.opengeospatial.org/standards/sfs
[3] ISO/IEC 13249-3:1999, Information technology — Database languages — SQL Multimedia and
Application Packages — Part 3: Spatial, International Organization For Standardization, 2000.
[4] Melton J., Eisenberg A.: SQL Multimedia and Application Packages (SQL/MM). SIGMOD Record
30(4), 2001.
[5] Oracle Spatial, [@:] http://www.oracle.com/technology/documentation/spatial.html
[6] Marek Wojciechowski, Łukasz Matuszczak: 'Oracle interMedia na tle standardu SQL/MM
i prototypowych systemów multimedialnych baz danych', Materiały IX konf. PLOUG, Zakopane,
2003.
[7] Krzysztof Jankiewicz, Marek Wojciechowski: 'Standard SQL/MM: SQL Multimedia and Application
Packages', Materiały IX Seminarium PLOUG 'Przetwarzanie zaawansowanych struktur danych: Oracle
interMedia, Spatial, Text i XML DB', Warszawa, 2004.
[8] IBM DB2 - Spatial Extender l [@:] http://www-01.ibm.com/software/data/spatial/
[9] MS SQL 2008 Spatial Storage [@:] http://technet.microsoft.com/en-us/library/bb933790.aspx
[10] MySQL Spatial [@:] http://dev.mysql.com/doc/refman/5.1/en/spatial-extensions.html
[11] PostgreSQL – PostGIS [@:] http://postgis.refractions.net/
[12] Paweł Netzel: PostGIS, PostgreSQL. Materiały I Konferencji Wolne Oprogramowanie
w Geoinformatyce, Wrocław 2009.
Dziękuję za uwagę