UPDATE geo_places
SET region3 = region2
WHERE region3 IS NULL OR region3=""
/* DROP VIEW v_regiones; */
CREATE VIEW if NOT EXISTS v_regiones AS
SELECT distinct region1 AS nombre
FROM geo_places;
CREATE VIEW if NOT EXISTS v_provincias AS
SELECT distinct region2 AS nombre
FROM geo_places;
CREATE VIEW if NOT EXISTS v_comarcas AS
SELECT distinct region3 AS nombre
FROM geo_places;
CREATE VIEW if NOT EXISTS v_municipios AS
SELECT distinct region4 AS nombre
FROM geo_places;
CREATE VIEW if NOT EXISTS v_localidades AS
SELECT DISTINCT
g.Locality AS nombre,
g.Region4 AS municipio
FROM geo_places g;
CREATE VIEW if NOT EXISTS v_cod_postales AS
SELECT DISTINCT g.Postcode codigo,
g.Latitude latitud, g.Longitude longitud
FROM geo_places g;
SET @i:=0;
CREATE TABLE if not exists regiones as
SELECT @i:=@i+1 AS id, v.nombre
FROM v_regiones as v
ORDER BY v.nombre;
SET @i:=0;
CREATE TABLE if not exists provincias as
SELECT @i:=@i+1 AS id, v.nombre
FROM v_provincias as v;
SET @i:=0;
CREATE TABLE if not exists comarcas as
SELECT @i:=@i+1 AS id, v.nombre
FROM v_comarcas as v;
SET @i:=0;
CREATE TABLE if not exists municipios as
SELECT @i:=@i+1 AS id, v.nombre
FROM v_municipios as v;
SET @i:=0;
DROP TABLE if EXISTS localidades;
CREATE TABLE if not exists localidades as
SELECT @i:=@i+1 AS id, v.nombre, v.municipio
FROM v_localidades as v;
SET @i:=0;
CREATE TABLE if not exists cod_postales as
SELECT @i:=@i+1 AS id, v.codigo,
v.latitud, v.longitud
FROM v_cod_postales as v;
ALTER TABLE regiones
ADD PRIMARY KEY (id);
ALTER TABLE provincias
ADD PRIMARY KEY (id);
ALTER TABLE comarcas
ADD PRIMARY KEY (id);
ALTER TABLE municipios
ADD PRIMARY KEY (id);
ALTER TABLE localidades
ADD PRIMARY KEY (id);
ALTER TABLE cod_postales
ADD PRIMARY KEY (id);
CREATE UNIQUE INDEX indice_region
ON regiones (nombre);
CREATE UNIQUE INDEX indice_provincia
ON provincias (nombre);
CREATE UNIQUE INDEX indice_comarca
ON comarcas (nombre);
CREATE UNIQUE INDEX indice_municipios
ON municipios (nombre);
CREATE UNIQUE INDEX indice_localidades
ON localidades (nombre,municipio);
select "codigos postales=",count(*) from cod_postales
union
select "localidades=",count(*) from localidades
union
select "municipios=",count(*) from municipios
union
select "comarcas=",count(*) from comarcas
union
select "provincias=",count(*) from provincias
union
select "regiones=",count(*) from regiones
| tablas | |
|---|---|
| codigos postales= | 35646 |
| localidades= | 36345 |
| municipios= | 8118 |
| comarcas= | 460 |
| provincias= | 52 |
| regiones= | 19 |
-- MySQL Workbench Forward Engineering
CREATE TABLE IF NOT EXISTS cod_postales (
id BIGINT NOT NULL,
codigo VARCHAR(15) NULL,
latitud DOUBLE NULL,
longitud DOUBLE NULL,
PRIMARY KEY (id));
-- -----------------------------------------------------
-- Table comarcas
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS comarcas (
id BIGINT NOT NULL,
nombre VARCHAR(80) NULL,
PRIMARY KEY (id),
UNIQUE INDEX indice_comarca (nombre));
-- -----------------------------------------------------
-- Table regiones
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS regiones (
id BIGINT NOT NULL,
nombre VARCHAR(80) NULL,
UNIQUE INDEX indice_region (nombre),
PRIMARY KEY (id));
-- -----------------------------------------------------
-- Table provincias
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS provincias (
id BIGINT NOT NULL,
nombre VARCHAR(80) NULL,
fk_region BIGINT NULL,
PRIMARY KEY (id),
UNIQUE INDEX indice_provincia (nombre),
INDEX fk_provincias_regiones_idx (fk_region),
CONSTRAINT fk_provincias_regiones
FOREIGN KEY (fk_region)
REFERENCES regiones (id));
-- -----------------------------------------------------
-- Table prov_comarcas
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS prov_comarcas (
fk_provincia BIGINT NOT NULL,
fk_comarca BIGINT NOT NULL,
PRIMARY KEY (fk_provincia, fk_comarca),
INDEX fk_provincias_has_comarcas_comarcas1_idx (fk_comarca),
INDEX fk_provincias_has_comarcas_provincias1_idx (fk_provincia),
CONSTRAINT fk_provincias_has_comarcas_provincias1
FOREIGN KEY (fk_provincia)
REFERENCES provincias (id),
CONSTRAINT fk_provincias_has_comarcas_comarcas1
FOREIGN KEY (fk_comarca)
REFERENCES comarcas (id));
-- -----------------------------------------------------
-- Table municipios
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS municipios (
id BIGINT NOT NULL,
nombre VARCHAR(80) NULL,
fk_provincia BIGINT NULL,
fk_comarca BIGINT NULL,
PRIMARY KEY (id),
UNIQUE INDEX indice_municipios (nombre),
INDEX fk_municipios_prov_comarcas1_idx (fk_provincia, fk_comarca),
CONSTRAINT fk_municipios_prov_comarcas1
FOREIGN KEY (fk_provincia, fk_comarca)
REFERENCES prov_comarcas (fk_provincia, fk_comarca));
-- -----------------------------------------------------
-- Table localidades
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS localidades (
id BIGINT NOT NULL,
nombre VARCHAR(80) NULL,
municipio VARCHAR(80) NULL,
fk_municipio BIGINT NULL,
PRIMARY KEY (id),
UNIQUE INDEX indice_localidades (nombre, municipio),
INDEX fk_localidades_municipios1_idx (fk_municipio),
CONSTRAINT fk_localidades_municipios1
FOREIGN KEY (fk_municipio)
REFERENCES municipios (id));
-- -----------------------------------------------------
-- Table cod_localidades
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS cod_localidades (
fk_cod_postal BIGINT NOT NULL,
fk_localidad BIGINT NOT NULL,
PRIMARY KEY (fk_cod_postal, fk_localidad),
INDEX fk_cod_postales_has_localidades_localidades1_idx (fk_localidad),
INDEX fk_cod_postales_has_localidades_cod_postales1_idx (fk_cod_postal),
CONSTRAINT fk_cod_postales_has_localidades_cod_postales1
FOREIGN KEY (fk_cod_postal)
REFERENCES cod_postales (id),
CONSTRAINT fk_cod_postales_has_localidades_localidades1
FOREIGN KEY (fk_localidad)
REFERENCES localidades (id));
UPDATE provincias
SET fk_region =
(SELECT DISTINCT R.ID
FROM regiones R, geo_places G
WHERE R.nombre = G.Region1 and
g.region2=provincias.nombre);
INSERT INTO prov_comarcas
SELECT DISTINCT p.id, c.id
FROM geo_places g, provincias p, comarcas c
WHERE g.Region2 = p.nombre AND
g.Region3 = c.nombre;
Consulta: provincias de Galicia y Comunidad Valenciana
/*SELECT p.nombre
FROM provincias p
WHERE fk_region in (SELECT id FROM regiones
WHERE nombre="galicia" OR
nombre LIKE "%valencia%");*/
SELECT p.nombre
FROM provincias p, regiones r
WHERE p.fk_region = r.id AND
(r.nombre = "galicia" OR
r.nombre LIKE "%valencia%")
Consulta todas las comarcas de Alicante
SELECT c.nombre
FROM comarcas c, prov_comarcas pc, provincias p
WHERE c.id =pc.fk_comarca AND
pc.fk_provincia = p.id AND
p.nombre LIKE "%alicante%"
ALTER TABLE municipios
ADD COLUMN habitantes BIGINT(20) NULL DEFAULT NULL AFTER fk_comarca;
ALTER TABLE municipios
CHANGE COLUMN habitantes habitantes BIGINT(20) NULL DEFAULT NULL
AFTER nombre;
ALTER TABLE municipios
CHANGE COLUMN habitantes poblacion BIGINT(20) NULL DEFAULT NULL;
ALTER TABLE municipios
CHANGE COLUMN poblacion poblacion BIGINT(20) NULL DEFAULT 0;
ALTER TABLE municipios
DROP COLUMN poblacion,
ADD COLUMN habitantes BIGINT(20) NULL DEFAULT NULL AFTER nombre;
ALTER TABLE cod_postales
ADD UNIQUE INDEX mi_indice (codigo);
ALTER TABLE cod_postales
ADD PRIMARY INDEX mi_indice (codigo);
ALTER TABLE cod_postales
ADD INDEX mi_indice (codigo);
SET FOREIGN_KEY_CHECKS=0;
SET UNIQUE_CHECKS=0;
....
SET FOREIGN_KEY_CHECKS=1;
SET UNIQUE_CHECKS=1;
TRUNCATE provincias;
DROP TABLE provincias;
DELETE FROM provincias;