jueves, enero 10, 2008

MySQL. Ejemplo de creación de tablas con atributos geográficos

.
Suponga que se tiene el siguiente mapa y se desea almacenar sus objetos geográficos que representan provincias, ciudades y rutas en una base de datos MySQl.

Primer paso registro de ciudades, para lo cual se crea la siguiente tabla:

CREATE TABLE `ciudades` (
`cp` int NOT NULL,
`localidad` varchar(30),
`provincia` varchar(30),
`ubicacion` point not null,
SPATIAL INDEX(ubicacion),
PRIMARY KEY (`cp`)
) TYPE=MyISAM;

Como se ve en la definición de la tabla se ha incorporado un atributo basado en una geometría “ubicación” y es de tipo punto. El formato de archivos MyISAM es el que soporta los datos geométricos en MySQL. A continuación se insertan una serie de registros que podrían definir ubicaciones de distintas localidades de distintas provincias.

INSERT INTO ciudades (`cp`, `localidad`, `provincia`, `ubicacion`)
VALUES (101, 'A1','A', GeomFromText('POINT(1 1)'));

INSERT INTO ciudades (`cp`, `localidad`, `provincia`, `ubicacion`)
VALUES (110, 'A2','A', GeomFromText('POINT(2 4)'));

INSERT INTO ciudades (`cp`, `localidad`, `provincia`, `ubicacion`)
VALUES (111, 'B1','B', GeomFromText('POINT(4 2)'));

INSERT INTO ciudades (`cp`, `localidad`, `provincia`, `ubicacion`)
VALUES (112, 'B2','B', GeomFromText('POINT(5 1)'));

INSERT INTO ciudades (`cp`, `localidad`, `provincia`, `ubicacion`)
VALUES (116, 'B3','B', GeomFromText('POINT(8 2)'));

INSERT INTO ciudades (`cp`, `localidad`, `provincia`, `ubicacion`)
VALUES (121, 'C1','C', GeomFromText('POINT(6 6)'));

INSERT INTO ciudades (`cp`, `localidad`, `provincia`, `ubicacion`)
VALUES (122, 'D1','D', GeomFromText('POINT(8 4)'));

INSERT INTO ciudades (`cp`, `localidad`, `provincia`, `ubicacion`)
VALUES (124, 'D2','D', GeomFromText('POINT(9 4)'));

INSERT INTO ciudades (`cp`, `localidad`, `provincia`, `ubicacion`)
VALUES (134, 'E1','E', GeomFromText('POINT(3 11)'));

INSERT INTO ciudades (`cp`, `localidad`, `provincia`, `ubicacion`)
VALUES (135, 'E2','E', GeomFromText('POINT(9 9)'));

La siguiente tabla se utilizará para almacenar las provincias por medio de una geometría tipo polígono. En este caso se decidió almacenar estos datos en una tabla aparte, pero si por si alguna razón se quisiera, tales datos espaciales se podrían almacenar en la tabla anterior en el atributo “ubicacion”.


CREATE TABLE `provincias` (
`id_provincia` int NOT NULL,
`provincia` varchar(30),
`area` polygon not null,
SPATIAL INDEX(`area`),
PRIMARY KEY (`id_provincia`)
) TYPE=MyISAM;

INSERT INTO provincias (`id_provincia`, `provincia`, `area`)
VALUES (22, 'A', GeomFromText('POLYGON((0 0, 0 8, 3 8, 3 0, 0 0))'));

INSERT INTO provincias (`id_provincia`, `provincia`, `area`)
VALUES (24, 'B', GeomFromText('POLYGON((4 0, 4 4, 10 4, 10 0, 4 0))'));

INSERT INTO provincias (`id_provincia`, `provincia`, `area`)
VALUES (25, 'C', GeomFromText('POLYGON((4 4, 4 8, 7 8, 8 4, 4 4))'));

INSERT INTO provincias (`id_provincia`, `provincia`, `area`)
VALUES (71, 'D', GeomFromText('POLYGON((7 4, 7 8, 8 10, 10 4, 7 4))'));

INSERT INTO provincias (`id_provincia`, `provincia`, `area`)
VALUES (85, 'E', GeomFromText('POLYGON((0 8, 0 12, 10 12, 10 8, 0 8))'));

El paso siguiente es definir una nueva tabla donde se almacenarán las rutas como objetos tipo LineString.

CREATE TABLE `rutas` (
`id_ruta` int NOT NULL,
`ruta` varchar(30),
`trayecto` linestring not null,
SPATIAL INDEX(`trayecto`),
PRIMARY KEY (`id_ruta`)
) TYPE=MyISAM;

INSERT INTO rutas (`id_ruta`, `ruta`, `trayecto`)
VALUES (2, `R1`, GeomFromText('LINESTRING(1 9, 7 9)'));

INSERT INTO rutas (`id_ruta`, `ruta`, `trayecto`)
VALUES (4, `R2`, GeomFromText('LINESTRING(2 11, 2 6, 4 6)'));

INSERT INTO rutas (`id_ruta`, `ruta`, `trayecto`)
VALUES (5, `R3`, GeomFromText('LINESTRING(8 1, 8 11)'));

INSERT INTO rutas (`id_ruta`, `ruta`, `trayecto`)
VALUES (6, `R4`, GeomFromText('LINESTRING(2 1, 4 1)'));

INSERT INTO rutas (`id_ruta`, `ruta`, `trayecto`)
VALUES (8, `R5`, GeomFromText('LINESTRING(1 3, 9 6)'));

INSERT INTO rutas (`id_ruta`, `ruta`, `trayecto`)
VALUES (9, `R6`, GeomFromText('LINESTRING(5 12, 8 12)'));

Tip: Utilizar ahora el visualizador Kosmo (presentando en un post anterior) conectarse a la base de datos y ver graficamente la geografía almacenada.



2 comentarios:

Anónimo dijo...

Hola Fernando.
Me están siendo de gran utilidad tus apuntes sobre Mysql, pero tengo alguna duda al respecto, al insertar los datos en las tablas bueno parce que los graba, por que Kosmos me los representa "aproximadamente" pero cuando quiero hacer una consulta desde Query Browser el campo digamos geométrico no tiene representación como tal, pasando un select * desde la linea de comandos aparecen unos codigos muy simpaticos etc.., ¿sabes a que se debe?
Muchas gracias de antemano.
Un saludo desde Madrid.
Mario

Anónimo dijo...

Buscando ejemplo de datos, acabo de leer el post y "pillarme" los dtuyos. Muchas gracias compañero