municipios_ine.sql
municipios_hab.sql
geo_2026_exa.sql
bbdd-ip.zip
cities.zip
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE cities;
INSERT INTO cities
SELECT DISTINCT NULL, city, population, c.ID
FROM worldcities w left JOIN country c
ON w.iso2 = c.codigo WHERE population !="";
SET FOREIGN_KEY_CHECKS = 1;
SET @i:=(select MAX(id) FROM cities);
UPDATE ips
SET fk_city = 1+FLOOR(RAND()*@i);
SELECT MAX(id) INTO @ip FROM ips;
SELECT MAX(id) INTO @wg FROM webs_geolocalizadas;
SELECT MAX(id) INTO @ws FROM webs_sectoriales;
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE visitas;
SET FOREIGN_KEY_CHECKS = 1;
INSERT INTO visitas
SELECT
NULL,
FLOOR(1 + RAND() * @ip) AS fk_ip,
if(RAND()>0.13,FLOOR(1 + RAND() * @wg),NULL) AS fk_geolocalizada,
null AS fk_sector,
NOW() - INTERVAL FLOOR(RAND() * 94608000) SECOND AS fechahora
FROM n10 t1 JOIN n10 t2 JOIN n10 t3 JOIN n10 t4 JOIN n10 t5 JOIN n10 t6
JOIN n10 t7 LIMIT 3000000;
UPDATE visitas
SET fk_sector = FLOOR(1 + RAND() * @ws)
WHERE fk_geolocalizada IS NULL OR fk_geolocalizada ="";
SELECT MAX(id) INTO @anu FROM anuncios;
SELECT MAX(id) INTO @ima FROM imagenes;
INSERT INTO alquiler
SELECT
FLOOR(1 + RAND() * @anu) AS fk_anuncio,
FLOOR(1 + RAND() * @ima) AS fk_imagen,
fecha_inicio,
DATE_ADD(fecha_inicio, INTERVAL duracion DAY) AS hasta,
ROUND(50 + RAND() * 950, 2) AS importe
FROM (
SELECT
DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND() * 365) DAY) AS fecha_inicio,
FLOOR(1 + RAND() * 360) AS duracion
FROM
n10 t1 JOIN n10 t2 JOIN n10 t3 JOIN n10 t4 JOIN n10 t5 JOIN n10 t6
LIMIT 789654
) fechas
INSERT INTO webs_geolocalizadas
SELECT NULL, r.nombre, 1, 0 FROM regiones r
UNION
SELECT NULL, p.nombre, 2, 0 FROM provincias p
UNION
SELECT NULL, c.nombre, 3, 0 FROM comarcas c
UNION
SELECT NULL, m.nombre, 4, 0 FROM municipios m
UNION
SELECT NULL, cp.codigo, 6, 0 FROM cod_postales cp
SET FOREIGN_KEY_CHECKS=0;
TRUNCATE cnae;
INSERT INTO cnae
SELECT NULL, s.COD_INTEGRADO, s.TITULO_CNAE2009
FROM sector_cnae2009 s;
SET FOREIGN_KEY_CHECKS=1;
SET @c:=0;
SELECT COUNT(*) INTO @i FROM cnae;
INSERT INTO webs_sectoriales
SELECT @c:=@c+1 id,
CONCAT_WS(" ","Sector",@c) nombre,
1+FLOOR(RAND()*@i) fk
FROM
n10 t1 JOIN n10 t2 join n10 t3 JOIN n10 t4
LIMIT 3000;
INSERT INTO anuncios
SELECT NULL, wg.id, NULL, n10.n,
FLOOR(RAND()*5000) imp, round(RAND(),2) pvp
FROM webs_geolocalizadas wg JOIN n10
union
SELECT NULL, NULL, ws.id, n10.n,
FLOOR(RAND()*5000) imp, round(RAND(),2) pvp
FROM webs_sectoriales ws JOIN n10
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE cities;
INSERT INTO cities (nombre,poblacion,fk_country)
SELECT DISTINCT city, if(w.population="",0,w.population), (SELECT id FROM country WHERE nombre=w.country)
FROM worldcities w;
SET FOREIGN_KEY_CHECKS = 1;
SET @i:=0;
SELECT @i:=@i+1 id,
CONCAT_WS(".",
FLOOR(RAND()*254)+1,
FLOOR(RAND()*254)+1,
FLOOR(RAND()*254)+1,
FLOOR(RAND()*254)+1
)ipv4,
CONCAT_WS(":",
lpad(hex(floor(RAND()*65536)),4,'0'),
lpad(hex(floor(RAND()*65536)),4,'0'),
lpad(hex(floor(RAND()*65536)),4,'0'),
lpad(hex(floor(RAND()*65536)),4,'0'),
lpad(hex(floor(RAND()*65536)),4,'0'),
lpad(hex(floor(RAND()*65536)),4,'0'),
lpad(hex(floor(RAND()*65536)),4,'0'),
lpad(hex(floor(RAND()*65536)),4,'0')
) ipv6
FROM n10 t1 JOIN n10 t2 JOIN n10 t3 JOIN n10 t4 JOIN n10 t5
JOIN n10 t6 JOIN n10 t7 ;
CREATE DEFINER=`root`@`localhost` FUNCTION `LEVENSHTEIN`(
`s1` VARCHAR(250),
`s2` VARCHAR(250)
)
RETURNS int
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE s1_len, s2_len, i, j, cost INT;
DECLARE c CHAR;
DECLARE c_temp INT;
DECLARE s1_char CHAR;
DECLARE cv0, cv1 VARBINARY(256);
SET s1_len = CHAR_LENGTH(s1);
SET s2_len = CHAR_LENGTH(s2);
SET cv1 = 0x00;
SET j = 1;
WHILE j <= s2_len DO
SET cv1 = CONCAT(cv1, CHAR(j));
SET j = j + 1;
END WHILE;
SET i = 1;
WHILE i <= s1_len DO
SET s1_char = SUBSTRING(s1, i, 1);
SET c = CHAR(i);
SET cv0 = CHAR(i);
SET j = 1;
WHILE j <= s2_len DO
SET cost = IF(s1_char = SUBSTRING(s2, j, 1), 0, 1);
SET c_temp = ORD(SUBSTRING(cv1, j, 1)) + cost;
SET c_temp = LEAST(
c_temp,
ORD(SUBSTRING(cv1, j + 1, 1)) + 1,
ORD(SUBSTRING(cv0, j, 1)) + 1
);
SET cv0 = CONCAT(cv0, CHAR(c_temp));
SET j = j + 1;
END WHILE;
SET cv1 = cv0;
SET i = i + 1;
END WHILE;
RETURN ORD(SUBSTRING(cv1, s2_len + 1, 1));
END
CREATE DEFINER=`root`@`localhost` FUNCTION `limpia_articulos`(
`texto` VARCHAR(250)
)
RETURNS varchar(250) CHARSET utf8mb3
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
SET texto = LOWER(texto);
SET texto = REPLACE(texto, ' del ', ' ');
SET texto = REPLACE(texto, ' de ', ' ');
SET texto = REPLACE(texto, ' la ', ' ');
SET texto = REPLACE(texto, ' el ', ' ');
SET texto = REPLACE(texto, 'la ', '');
SET texto = REPLACE(texto, 'el ', '');
SET texto = REPLACE(texto, 'A ', '');
SET texto = REPLACE(texto, 'as ', '');
SET texto = REPLACE(texto, 'las ', '');
SET texto = REPLACE(texto, 'os ', '');
RETURN TRIM(texto);
END
DROP TABLE municipios_hab;
CREATE TABLE municipios_hab AS
SELECT SUBSTRING(m.Municipios,1,5) cp, SUBSTRING(m.Municipios,7) nombre, m.Sexo, replace(m.Periodo,",","") periodo,
CAST(IF(m.Total = '', 0, replace(m.Total,".","") )AS UNSIGNED) total FROM municipios_ine m;
DROP TABLE municipios_ine_sin;
CREATE TABLE municipios_ine_sin as
SELECT DISTINCT m.nombre, SUBSTRING_INDEX(m.nombre, ',', 1) nombre2,
substring(m.cp,1,2) cp FROM municipios_hab m left JOIN municipios mm
ON m.nombre = mm.nombre
WHERE mm.nombre IS NULL;
DROP TABLE municipios_sin;
CREATE TABLE municipios_sin as
SELECT DISTINCT mm.nombre, substring(cp.codigo,1,2) cp FROM municipios_hab m right JOIN municipios mm
ON m.nombre = mm.nombre JOIN localidades l
ON mm.id = l.fk_municipio JOIN cod_localidades cl
ON l.id = cl.fk_localidad JOIN cod_postales cp
ON cl.fk_cod_postal = cp.id
WHERE m.nombre IS NULL;
CREATE TABLE municipios_result5 AS
SELECT m.cp, m.nombre, m.nombre2 ine, mm.nombre muni FROM municipios_ine_sin m left JOIN municipios_sin mm
ON m.cp = mm.cp and
limpia_articulos(m.nombre2) LIKE CONCAT('%', limpia_articulos(mm.nombre), '%')
WHERE mm.nombre IS NOT NULL
UNION
SELECT m.cp, m.nombre, m.nombre2 ine, mm.nombre muni FROM municipios_ine_sin m left JOIN municipios_sin mm
ON m.cp = mm.cp and
SOUNDEX(limpia_articulos(m.nombre2)) LIKE SOUNDEX(limpia_articulos(mm.nombre))
WHERE mm.nombre IS NOT NULL
UNION
SELECT m.cp, m.nombre, m.nombre2 ine, mm.nombre muni FROM municipios_ine_sin m left JOIN municipios_sin mm
ON m.cp = mm.cp AND
levenshtein(limpia_articulos(m.nombre2), limpia_articulos(mm.nombre)) <= 3
WHERE mm.nombre IS NOT NULL;