Criando um BD MySQL Spatial

MySQL Spatial
Airton Lastori
[email protected]
10-abr-2013
1
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
@MySQLBR
facebook.com/MySQLBR
meetup.com/MySQL-BR
2
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
MySQLBR
Alguns clientes MySQL
3
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Alguns clientes MySQL
fonte: alexa.com/topsites 30-jan-2013
4
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Alguns clientes MySQL
mysql.com/customers
5
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Alguns clientes MySQL
mysql.com/customers
6
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
MySQL: casos de sucesso
+ de 20 indústrias
mysql.com/customers
7
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Evolução da escalabilidade do MySQL
9
2008
2009
2010
2013
até 4 CPU
MySQL 5.0
até 16 CPU
MySQL 5.1
até 32 CPU
MySQL 5.5
até 48 CPU
MySQL 5.6
MySQL AB
Sun
Oracle
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Compromisso Oracle com MySQL Community
Portfólio disponível para download e uso sob GPL
 MySQL Database Community Server
 MySQL Cluster
 MySQL Workbench
 MySQL Connectors
 MySQL Proxy
 Documentação não GPL
 Forums, Lists, Bugs, Blogs…
10
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Conectores: amplo suporte a várias linguagens
Oracle
•
•
•
•
•
•
Connector/ODBC
Connector/Net (ADO.NET)
Connector/Python
Connector/J (JDBC)
Connector/C (C API)
Connector/C++ (C++ API)
Comunidade
•
•
•
•
•
•
PHP
Perl
Ruby
TCL
Eiffel
…
dev.mysql.com/doc/refman/5.5/en/connectors-apis.html
11
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
MySQL Enterprise Edition
Maior produtividade e mitigação de riscos
Oracle Premier
Lifetime Support
MySQL Enterprise
Security
Oracle Product
Certifications/Integrations
MySQL Enterprise
Audit
MySQL Enterprise
Backup
MySQL Enterprise
Scalability
MySQL Enterprise
High Availability
12
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
MySQL Enterprise
Monitor/Query Analyzer
MySQL Workbench
Insert Information Protection Policy Classification from Slide 12
Arquitetura MySQL Database
Suporte às mais
conhecidas
plataformas/linguagens
de desenvolvimento
Gerenciamento
eficiente e multithread de sessões
Parsing completo
DML+DDL,
otimizador,
baseado em custo,
caching de queries
e resultados
Várias opções de
Storage Engine
para necessidades
específicas das
aplicações
13
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Opções flexíveis de
logging e
armazenamento
físico
Insert Information Protection Policy Classification from Slide 12
Qual Storage Engine usar?
MyISAM
InnoDB
Archive
NDB Cluster
✖
✔
✖
✔
Tabela
Linha
Tabela
Linha
Assíncrona ou
Semi-síncrona
Assíncrona ou
Semi-síncrona
Assíncrona ou
Semi-síncrona
Síncrona, Multimaster
Foreign Keys
✖
✔
✖
a partir da v7.3
Full-text indexes
✔
a partir da v5.6
✖
✖
Compressão de dados
somente Leitura
✔
✔
✖
Caches
somente Índices
Dados e Índices
✖
Dados e Índices
Suporte Geospacial
Tipos de Dados e
Índices
somente Tipos de
Dados
somente Tipos de
Dados
somente Tipos de
Dados
256TB
64TB
Ilimitado
384EB
Transações
Nível de Lock
Replicação
Limite de
armazenamento
dev.mysql.com/doc/refman/5.5/en/storage-engines.html
14
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
MySQL Spatial Extensions
Suporte à geração,
armazenamento e análise de
informações geográficas.
15
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
OpenGIS
Simple Features Specification for SQL
 Modelo conceitual para dados espaciais / geográficos
 propõe extensões para SGBDs SQL
 padronizado pelo Open Geospatial Consortium
 inclui SQL with Geometry Types, estendendo a linguagem
 MySQL é aderente ao OpenGIS
 implementa um subconjunto dos SQL with Geometry Types
 colunas podem possuir os Tipos Geométricos do OpenGIS
 também estão disponíveis funções geométricas para análise
www.opengis.org/docs/99-049.pdf
16
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Terminologia
OpenGIS Simple Features Specification for SQL
 geographic feature ou geospatial feature ou geometry
 algo com uma localização no mundo
 definido e representado por um ponto ou agregado de pontos
 pode ser uma entity, space, definable location
 entity
 uma montanha, um lago, uma cidade etc
 space
 um distrito municipal, os trópicos etc
 definable location
 um cruzamento entre ruas, etc
17
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
OpenGIS Geometry Model
Tipos de Dados propostos no SQL with Geometry Types
*apenas tipos em negrito podem ser instanciados
18
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
OpenGIS Geometry Model
Exemplos de utilização dos Tipos de Dados instanciáveis
19
Point
Em um mapa mundi, cada Point poderia representar uma cidade.
Em um mapa municipal, Points representariam paradas de ônibus.
Linestring
Em um mapa mundi, poderia representar rios.
Em um mapa municipal, poderia representar ruas.
Polygon
Em um mapa nacional, poderia representar florestas, estados etc.
MultiPoint
Em um mapa mundi, poderia representar uma cadeia de pequenas ilhas
que formam um arquipélogo.
Em um mapa municipal, poderia representar pontos de paradas de uma
linha de ônibus.
MultiLineString
Em um mapa nacional, poderia representar rodovias federais ou rios
conectados.
MultiPolygon
Em um mapa nacional, poderia representar lagos pertencentes ao mesmo
sistema hídrico.
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
OpenGIS Geometry Model
Hierarquia das Propriedades – Geometry e Point
Geometry
type
SRID
coordinates
interior
boundary
exterior
MBR //envelope
simple
closed
empty
dimension
20
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Point
x-coordinate
y-coordinate
dimension = 0
boundary = EMPTY
OpenGIS Geometry Model
Formatos de Armazenamento
 WKT (Well-Known Text Format)
 projetado para transportar dados em ASCII, fácil leitura, por exemplo:
 POINT(15 20)
 LINESTRING(0 0, 10 10, 20 25, 50 60)
 POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))
 WKB (Well-Known Binary Format) OpenGIS e ISO SQL/MM Part 3 Spatial
 BLOBs contendo dados binários de informações geométricas WKB
 POINT(1 1) = 0101000000000000000000F03F000000000000F03F
 1 byte (unsigned) para endianess
 4 bytes (unsigned) para Tipo de Dado de 1 a 7
 8 bytes (double-precision IEEE 754) para coordenadas X e Y
21
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Criando um BD MySQL Spatial
Tipos de Dados disponíveis
22




GEOMETRY pode armazenar geometrias de qualquer tipo
POINT
LINESTRING
POLYGON




GEOMETRYCOLLECTION pode armazenar coleções de todos tipos
MULTIPOINT
MULTILINESTRING
MULTIPOLYGON
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Criando um BD MySQL Spatial
Exemplo: criando uma tabela
mapamundi
cidade
CREATE TABLE mapamundi (
cidade POINT‫‏‬
) ENGINE=MyISAM;
23
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Criando um BD MySQL Spatial
Exemplo: alterando uma tabela, adicionar coluna
mapamundi
cidade
ALTER TABLE mapamundi
ADD rio LINESTRING;
24
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
rio
Criando um BD MySQL Spatial
Exemplo: alterando uma tabela, remover colunas
mapamundi
geometria
ALTER TABLE mapamundi DROP rio;
ALTER TABLE mapamundi
ADD geometria GEOMETRY;
ALTER TABLE mapamundi DROP cidade;
25
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Criando um BD MySQL Spatial
 Criando objetos com funções WKT
 GeomFromText() ou
 funções específicas para cada Tipo, ex: PointFromText()
 Criando objetos com funções WKB
 GeomFromWKB() ou
 funções específicas para cada Tipo, ex: PointFromWKB()
 Criando objetos com funções específicas do MySQL
 Point(), LineString(), Polygon()
 GeometryCollection(), MultiPoint(), MultiLineString(), MultiPolygon()
26
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Criando um BD MySQL Spatial
Exemplo: inserindo um Point via WKT
mapamundi
geometria
1
POINT(1 1)
INSERT INTO mapamundi VALUES (
GeomFromText('POINT(1 1)')
);
ou também…
...ou ainda…
SET @g = 'POINT(1 1)';
INSERT INTO mapamundi VALUES (
GeomFromText(@g)
);
27
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
INSERT INTO mapamundi VALUES(
Point(1,1)
);
Insert Information Protection Policy Classification from Slide 12
Criando um BD MySQL Spatial
Exemplo: removendo um Point
mapamundi
geometria
DELETE FROM mapamundi
WHERE geometria = GeomFromText('POINT(1 1)');
28
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Criando um BD MySQL Spatial
Exemplo: inserindo um Point via WKB
mapamundi
geometria
1
POINT(1 1)
INSERT INTO mapamundi VALUES (
GeomFromWKB(0x0101000000000000000000F03F000000000000F03F)
);
29
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Criando um BD MySQL Spatial
Exemplo: inserindo um LineString
mapamundi
geometria
1
POINT(1 1)
2
LINESTRING(0 0,1 1,2 2)
SET @g = 'LINESTRING(0 0,1 1,2 2)';
INSERT INTO mapamundi VALUES (
GeomFromText(@g)
);
30
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
ou também…
SET @g = ‘LINESTRING(0 0,1 1,2 2)';
INSERT INTO mapamundi VALUES (
LineStringFromText(@g)
);
Insert Information Protection Policy Classification from Slide 12
Criando um BD MySQL Spatial
Exemplo: inserindo um Polygon
mapamundi
geometria
1
POINT(1 1)
2
LINESTRING(0 0,1 1,2 2)
3
POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))
SET @g = 'POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))';
INSERT INTO mapamundi VALUES (GeomFromText(@g));
31
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Criando um BD MySQL Spatial
Exemplo: consultando registros
mapamundi
geometria
1
POINT(1 1)
2
LINESTRING(0 0,1 1,2 2)
3
POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))
SELECT AsText(geometria) FROM mapamundi;
SELECT AsBinary(geometria) FROM mapamundi;
32
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Análise de dados com MySQL Spatial
Funções Disponíveis 1/2
 Conversão
 AsBinary(), AsText(), GeomFromText(), GeomFromWKB()
 Propriedades
 Dimension(), Envelope(), GeometryType(), IsSimple(), SRID()
 X(), Y()
 EndPoint(), GLenght(), NumPoints(), PointN(), StartPoint()
 IsClosed()
 Area(), ExteriorRing(), InteriorRingN(), NumInteriorRings()
 Centroid()
 GeometryN(), NumGeometries()
33
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Análise de dados com MySQL Spatial
Funções Disponíveis 2/2
 Relações
 MBRContains(), MBRDisjoint(), MBREqual(), MBRIntersects(),
MBROverlaps(), MBRTouches(), MBRWithin()
 Contains(), Crosses(), Disjoint(), Equals(), Intersects(), Overlaps(),
Touches(), Within()
 ST_Contains(), ST_Crosses(), ST_Disjoint(), ST_Equals(),
ST_Intersects(), ST_Overlaps(), ST_Touches, ST_Within()
 Clonagem
 Buffer()
34
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Otimizações nos dados do MySQL Spatial
Índices SPATIAL para Range Scans
 Criando Índices Spatial
ALTER TABLE mapamundi CHANGE geometria geometria GEOMETRY NOT NULL;
CREATE SPATIAL INDEX sp_index ON mapamundi(geometria);
 Usando Índices Spatial em Range Scans
SET @g = 'POLYGON((0 0,9 0,9 9,0 9,0 0),(5 5,7 5,7 7,5 7, 5 5))';
SELECT AsText(geometria) FROM mapamundi
WHERE MBRContains(GeomFromText(@g),geometria);
…apenas deixe o trabalho de selecionar o índice para o Optimizer!
Use o comando EXPLAIN para confirmar se o índice é utilizado.
35
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Nível de Conformidade do MySQL Spatial com
OpenGIS
 Funcionalidades não-padronizadas
 Lenght() implementada com o nome GLenght()
 Funcionalidades adicionais
 próximos slides
 Funcionalidades não-implementadas
 próximos slides
36
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Funcionalidades Adicionais do MySQL Spatial não
definidas pelo OpenGIS
 GeometryCollection(g1,g2,...)
 LineString(pt1,pt2,...)
 MultiLineString(ls1,ls2,...)
 MultiPoint(pt1,pt2,...)
 MultiPolygon(poly1,poly2,...)
 Point(x,y)
 Polygon(ls1,ls2,...)
37
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Funcionalidades definidas pelo OpenGIS e não
implementadas pelo MySQL
 BdPolyFromText() e BdMPolyFromText()
 BdPolyFromWKB() e BdMPolyFromWKB()
 Boundary()
 Buffer() implementada a partir do MySQL 5.6
 ConvexHull()
 Difference()
 Intersection()
 IsEmpty() e IsRing()
 IsSimple() implementada a partir do MySQL 5.6
 PointOnSurface()
 SymDifference()
 Union
 Metadata Views como GEOMETRY_COLUMNS
38
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Como começar
Instalação básica, ambiente de
desenvolvimento…
...em 15 minutos, ou menos!
39
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
40
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
No Linux ou Mac, prefira os binários
mysql.com/downloads/mysql
41
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Cliente-Servidor
Client
mysql / mysql.exe
Server
mysqld / mysqld.exe
42
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Iniciar ou Parar o servidor MySQL no Windows
…ou via MySQL Notifier
43
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Iniciar ou Parar o servidor MySQL no Linux
Iniciar:
/usr/local/mysql/bin> mysqld_safe --defaults-file=/etc/my.cnf &
Parar:
/usr/local/mysql/bin> mysqladmin shutdown
dev.mysql.com/doc/refman/5.5/en/mysqld-safe.html
44
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Primeira conexão
C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -p
Enter password: ****
Welcome to the MySQL monitor.
Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.11 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
45
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Teste via MySQL Workbench 1/2
46
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Teste via MySQL Workbench 2/2
47
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Cliente-Servidor
App + Connector
JDBC, ODBC, .Net, PHP, Python, Perl, Ruby, Native C API…
Server
mysqld / mysqld.exe
48
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Mãos à obra!
Hello World…
...em Java
49
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Adicione o driver MySQL ao CLASSPATH
caminho completo do .jar
Confira:
C:\>set CLASSPATH
CLASSPATH=.;C:\Program Files (x86)\Java\jre6\lib\ext\QTJava.zip;C:\Program
Files\MySQL\mysql-connector-java-5.1.22\mysql-connector-java-5.1.22-bin.jar
50
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
HelloMysql.java 1/2
import java.sql.*;
public class HelloMysql {
public static void main(String args[]){
System.out.println("Tentando conectar ao MySQL...");
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/mysql";
String usuario = "root";
String senha = "root";
Connection con = DriverManager.getConnection(url, usuario, senha);
System.out.println("URL: " + url);
System.out.println("Connection: " + con);
Continua…
51
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
HelloMysql.java 2/2
Statement stmt = con.createStatement();
ResultSet rs;
//objeto Statement
//objeto ResultSet
rs = stmt.executeQuery("SELECT * FROM world.country");
System.out.println("Ola mundo!");
while(rs.next()){
String nomePais = rs.getString("Name");
System.out.print(nomePais + ", ");
}//end while
con.close();
} catch( Exception e ) {
e.printStackTrace();
}//end catch
}//end main
}//end class HelloMysql
52
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
PRONTO!
java HelloMysql
C:\tutorial>javac HelloMysql.java
C:\tutorial>java HelloMysql
Tentando conectar ao MySQL...
URL: jdbc:mysql://localhost:3306/mysql
Connection: com.mysql.jdbc.JDBC4Connection@ed07f45
Ola mundo!
Aruba, Afghanistan, Angola, Anguilla, Albania, Andorra, Netherlands Antilles, Un
ited Arab Emirates, Argentina, Armenia, American Samoa, Antarctica, French South
ern territories, Antigua and Barbuda, Australia, Austria, Azerbaijan, Burundi, B
elgium, Benin, Burkina Faso, Bangladesh, Bulgaria, Bahrain, Bahamas, Bosnia and
Herzegovina, Belarus, Belize, Bermuda, Bolivia, Brazil, Barbados, Brunei, Bhutan
…
ietnam, Vanuatu, Wallis and Futuna, Samoa, Yemen, Yugoslavia, South Africa, Zamb
ia, Zimbabwe,
53
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Próximo passo: documentação
Reference Manual
1. Tutorial
2. SQL Syntax
3. Connectors & APIs, Memcached
4. MySQL Spatial Extensions
5. Functions, Views, Stored Programs
6. Optimization
dev.mysql.com/doc/refman/5.5/en/index.html
54
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Sumário
55

O MySQL é o banco de dados open source mais popular do
mundo, usado em grandes sites, aplicações mobile, embarcadas
e sistemas de informações geográficas.

Possui amplo suporte ao modelo OpenGIS, principalmente no
Storage Engine MyISAM.

Instale e comece a usar em menos de 15min. Use e abuse da
documentação!
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
@MySQLBR
facebook.com/MySQLBR
meetup.com/MySQL-BR
56
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12
Obrigado!
MySQL Spatial
Perguntas?
57
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
Insert Information Protection Policy Classification from Slide 12