UPDATE geo_places
SET region3 = region2
WHERE region3 IS NULL OR region3=""
DROP VIEW if EXISTS v_regiones;
CREATE VIEW if NOT EXISTS v_regiones AS
SELECT distinct region1 AS nombre
FROM geo_places;
DROP VIEW if EXISTS v_provincias;
CREATE VIEW if NOT EXISTS v_provincias AS
SELECT distinct region2 AS nombre
FROM geo_places;
DROP VIEW if EXISTS v_comarcas;
CREATE VIEW if NOT EXISTS v_comarcas AS
SELECT distinct region3 AS nombre
FROM geo_places;
DROP VIEW if EXISTS v_municipios;
CREATE VIEW if NOT EXISTS v_municipios AS
SELECT distinct region4 AS nombre
FROM geo_places;
DROP VIEW if EXISTS v_localidades;
CREATE VIEW if NOT EXISTS v_localidades AS
SELECT DISTINCT
g.Locality AS nombre,
g.Region4 AS municipio
FROM geo_places g;
DROP VIEW if EXISTS v_geolocalizaciones;
CREATE VIEW if NOT EXISTS v_geolocalizaciones AS
SELECT DISTINCT
g.Latitude latitud,
g.Longitude longitud
FROM geo_places g;
DROP VIEW if EXISTS v_cod_postales;
CREATE VIEW if NOT EXISTS v_cod_postales AS
SELECT DISTINCT
g.Postcode codigo
FROM geo_places g;
SET @i:=0;
CREATE TABLE if not exists t_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 t_provincias as
SELECT @i:=@i+1 AS id, v.nombre
FROM v_provincias as v;
SET @i:=0;
CREATE TABLE if not exists t_comarcas as
SELECT @i:=@i+1 AS id, v.nombre
FROM v_comarcas as v;
SET @i:=0;
CREATE TABLE if not exists t_municipios as
SELECT @i:=@i+1 AS id, v.nombre
FROM v_municipios as v;
SET @i:=0;
DROP TABLE if EXISTS t_localidades;
CREATE TABLE if not exists t_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 t_geolocalizaciones as
SELECT @i:=@i+1 AS id, v.latitud, v.longitud
FROM v_geolocalizaciones as v;
SET @i:=0;
CREATE TABLE if not exists t_cod_postales as
SELECT @i:=@i+1 AS id, v.codigo
FROM v_cod_postales as v;
ALTER TABLE t_regiones
ADD PRIMARY KEY (id);
ALTER TABLE t_provincias
ADD PRIMARY KEY (id);
ALTER TABLE t_comarcas
ADD PRIMARY KEY (id);
ALTER TABLE t_municipios
ADD PRIMARY KEY (id);
ALTER TABLE t_localidades
ADD PRIMARY KEY (id);
ALTER TABLE t_geolocalizaciones
ADD PRIMARY KEY (id);
ALTER TABLE t_cod_postales
ADD PRIMARY KEY (id);
CREATE UNIQUE INDEX indice_region
ON t_regiones (nombre);
CREATE UNIQUE INDEX indice_provincia
ON t_provincias (nombre);
CREATE UNIQUE INDEX indice_comarca
ON t_comarcas (nombre);
CREATE UNIQUE INDEX indice_municipios
ON t_municipios (nombre);
CREATE UNIQUE INDEX indice_localidades
ON t_localidades (nombre,municipio);
CREATE UNIQUE INDEX indice_geolocalizaciones
ON t_geolocalizaciones (latitud,longitud);
select "codigos postales=" AS tabla, count(*) as registros from t_cod_postales
union
select "geolocalizaciones=",count(*) from t_geolocalizaciones
union
select "localidades=",count(*) from t_localidades
union
select "municipios=",count(*) from t_municipios
union
select "comarcas=",count(*) from t_comarcas
union
select "provincias=",count(*) from t_provincias
union
select "regiones=",count(*) from t_regiones
| tablas | |
|---|---|
| codigos postales= | 11163 |
| localidades= | 36345 |
| municipios= | 8118 |
| comarcas= | 460 |
| provincias= | 52 |
| regiones= | 19 |
-- MySQL Workbench Forward Engineering
drop table if exists cod_localidades;
drop table if exists cod_postales;
drop table if exists loc_localizaciones;
drop table if exists geolocalizaciones;
drop table if exists localidades;
drop table if exists municipios;
drop table if exists prov_comarcas;
drop table if exists comarcas;
drop table if exists provincias;
drop table if exists regiones;
CREATE TABLE IF NOT EXISTS cod_postales (
id BIGINT NOT NULL,
codigo VARCHAR(15) 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),
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 DEFAULT NULL,
municipio VARCHAR(80) NULL DEFAULT NULL,
fk_municipio BIGINT NULL DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE INDEX indice_localidades (nombre ASC, municipio ASC),
INDEX fk_localidades_municipios1_idx (fk_municipio ASC),
CONSTRAINT fk_localidades_municipios1
FOREIGN KEY (fk_municipio)
REFERENCES municipios (id));
CREATE TABLE IF NOT EXISTS cod_postales (
id BIGINT NOT NULL,
codigo VARCHAR(15),
PRIMARY KEY (id),
UNIQUE INDEX mi_indice (codigo));
-- -----------------------------------------------------
-- 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));
CREATE TABLE IF NOT EXISTS geo_localizaciones (
id BIGINT NOT NULL auto_increment,
latitud DOUBLE NULL DEFAULT NULL,
longitud DOUBLE NULL DEFAULT NULL,
PRIMARY KEY (id));
CREATE TABLE IF NOT EXISTS loc_localizaciones (
fk_localidad BIGINT NOT NULL,
fk_geolocalizacion BIGINT NOT NULL,
PRIMARY KEY (fk_localidad, fk_geolocalizacion),
INDEX fk_localidades_has_geo_localizaciones_geo_localizaciones1_idx (fk_geolocalizacion),
INDEX fk_localidades_has_geo_localizaciones_localidades1_idx (fk_localidad),
CONSTRAINT fk_localidades_has_geo_localizaciones_localidades1
FOREIGN KEY (fk_localidad)
REFERENCES localidades (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_localidades_has_geo_localizaciones_geo_localizaciones1
FOREIGN KEY (fk_geolocalizacion)
REFERENCES geo_localizaciones (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
set @i:=0;
insert into regiones (id, nombre)
select @i:=@i+1 id, v.nombre
from v_regiones v;
set @i:=0;
insert into provincias (id, nombre)
select @i:=@i+1 id, v.nombre
from v_provincias v;
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);
set @i:=0;
insert into comarcas (id, nombre)
select @i:=@i+1 id, v.nombre
from v_comarcas v;
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%"
set @i:=0;
SET FOREIGN_KEY_CHECKS=0;
truncate municipios;
SET FOREIGN_KEY_CHECKS=1;
insert into municipios
(id,nombre,fk_provincia,fk_comarca)
select distinct @i:=@i+1 id, t.nombre,
t.fk_provincia, t.fk_comarca from
(select distinct region4 nombre,
p.id fk_provincia,
c.id fk_comarca
from geo_places g, provincias p, comarcas c
where g.Region2 = p.nombre and
g.Region3 = c.nombre) t
¿Cuántos municipios hay en la Marina Alta?
SELECT count(m.id) FROM municipios m, comarcas c
where m.fk_comarca = c.id and
c.nombre = "marina alta"
set @i:=0;
insert into localidades (id, nombre, municipio, fk_municipio)
select @i:=@i+1 id, v.nombre, v.municipio,
m.id fk_municipio
from v_localidades v, municipios m
where v.municipio = m.nombre
set @i:=0;
INSERT INTO cod_postales
select @i:=@i+1 id, codigo
from v_cod_postales
INSERT INTO cod_localidades
SELECT cp.id, l.id
FROM cod_postales cp, geo_places g,
localidades l
where cp.codigo = g.postcode AND
g.Locality = l.nombre AND
g.Region4 = l.municipio
insert into geo_localizaciones
(latitud,longitud)
select distinct g.Latitude, g.Longitude
from geo_places g
SELECT DISTINCT locality, region4, count(distinct concat(latitude,longitude)) AS puntitos
FROM geo_places
GROUP BY 1,2
HAVING puntitos >1
INSERT INTO loc_localizaciones
SELECT distinct l.id, gl.id
FROM localidades l, geo_places g, geo_localizaciones gl
WHERE l.nombre = g.Locality AND l.municipio = g.region4 AND gl.latitud = g.Latitude AND gl.longitud= g.Longitude
select "codigos postales=" AS tabla, count(*) as registros from cod_postales
union
select "loc_localizaciones=",count(*) from loc_localizaciones
UNION
select "cod_localidades=",count(*) from cod_localidades
union
select "localidades=",count(*) from localidades
UNION
select "geo_localizaciones=",count(*) from geo_localizaciones
union
select "municipios=",count(*) from municipios
UNION
select "prov_comarcas=",count(*) from prov_comarcas
union
select "comarcas=",count(*) from comarcas
union
select "provincias=",count(*) from provincias
union
select "regiones=",count(*) from regiones
| tabla | registros |
|---|---|
| codigos postales= | 11163 |
| loc_localizaciones= | 37246 |
| cod_localidades= | 37812 |
| localidades= | 36345 |
| geo_localizaciones= | 35066 |
| municipios= | 8118 |
| prov_comarcas= | 479 |
| comarcas= | 460 |
| provincias= | 52 |
| regiones= | 19 |
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;