Les recuerdo que los siguientes scripts fueron creados para ser utilizados con MariaDB. En caso de que requieran usarlos con otra base de datos, podrían validar la sintaxis del motor correspondiente.
El primer paso consiste en crear los catálogos de estados y municipios, recordando que la relación es de uno a muchos, es decir, un estado puede tener varios municipios.
-- Obtener estados
INSERT INTO tc_estado(id_estado, descripcion, id_estatus)
SELECT DISTINCT estato_abre, estado, 1
FROM tc_codigo_postal_sepo;
-- Obtener municipios
INSERT INTO tc_municipio(id_estado, descripcion, id_estatus)
SELECT estato_abre, municipio, 1
FROM tc_codigo_postal_sepo
GROUP BY municipio, estado;
Ahora que tenemos el catálogo de municipios, podemos crear los códigos postales, utilizando la relación de uno a muchos, es decir, un municipio puede tener varios códigos postales.
-- se cargan codigos postales por id municipio
INSERT INTO tc_cp (cod_postal, id_municipio, id_estatus)
SELECT DISTINCT sepo.codigo_postal, mun.id_municipio, 1
FROM tc_codigo_postal_sepo sepo
INNER JOIN tc_municipio mun ON mun.descripcion LIKE sepo.municipio
WHERE estato_abre LIKE "01";
INSERT INTO tc_cp (cod_postal, id_municipio, id_estado, id_estatus)
SELECT DISTINCT sepo.codigo_postal, mun.id_municipio, sepo.estato_abre, 1
FROM tc_codigo_postal_sepo sepo
INNER JOIN tc_municipio mun ON mun.descripcion LIKE sepo.municipio
WHERE estato_abre LIKE "32";
El siguiente paso implica la creación de un catálogo de referencia de los tipos de asentamientos. Estos pueden incluir colonias, poblados y otras denominaciones para áreas habitadas de diversos tamaños y estructuras. Con esta información, podemos crear registros que contengan la siguiente información: el ID del código postal (que nos permite obtener el municipio y estado asociados), el nombre del asentamiento y el tipo de asentamiento.
----------------------------------------------------------------------------------
-- clasificar los asentamientos por tipo de ascentamiento
----------------------------------------------------------------------------------
INSERT INTO tc_tipo_asentamiento (nombre, id_estatus)
SELECT DISTINCT tipo_asentamiento, 1
FROM tc_codigo_postal_sepo;
----------------------------------------------------------------------------------
-- crear los asentamientos por cp
----------------------------------------------------------------------------------
INSERT INTO tc_asentamiento (id_cp, nombre, id_estatus, id_tipo_asen)
SELECT cp.id_cp, sepo.asentamiento, 1, tip.id_tipo_asen
FROM tc_codigo_postal_sepo sepo
INNER JOIN tc_cp cp ON sepo.codigo_postal LIKE cp.cod_postal
INNER JOIN tc_tipo_asentamiento tip ON tip.nombre LIKE sepo.tipo_asentamiento
WHERE sepo.estato_abre LIKE "01";
INSERT INTO tc_asentamiento (id_cp, nombre, id_estatus, id_tipo_asen)
SELECT cp.id_cp, sepo.asentamiento, 1, tip.id_tipo_asen
FROM tc_codigo_postal_sepo sepo
INNER JOIN tc_cp cp ON sepo.codigo_postal LIKE cp.cod_postal
INNER JOIN tc_tipo_asentamiento tip ON tip.nombre LIKE sepo.tipo_asentamiento
WHERE sepo.estato_abre LIKE "02";
El último paso consiste en cargar el catálogo de ciudades.
----------------------------------------------------------------------------------
-- crear catalogo de ciudades por municipios
----------------------------------------------------------------------------------
INSERT INTO tc_ciudad (descripcion, municipio, id_estatus)
SELECT DISTINCT sepo.ciudad, cp.id_municipio, 1
FROM tc_codigo_postal_sepo sepo
INNER JOIN tc_cp cp ON cp.cod_postal LIKE sepo.codigo_postal
WHERE sepo.estato_abre LIKE "01"
AND ciudad IS NOT NULL
AND ciudad NOT LIKE ''
GROUP BY sepo.ciudad, cp.cod_postal;
INSERT INTO tc_ciudad (descripcion, municipio, id_estatus)
SELECT DISTINCT sepo.ciudad, cp.id_municipio, 1
FROM tc_codigo_postal_sepo sepo
INNER JOIN tc_cp cp ON cp.cod_postal LIKE sepo.codigo_postal
WHERE sepo.estato_abre LIKE "02"
AND ciudad IS NOT NULL
AND ciudad NOT LIKE ''
GROUP BY sepo.ciudad, cp.cod_postal;
Con la estructura de las tablas que hemos establecido y con los datos cargados, he creado un microservicio en springboot y he expuesto un servicio que genera la siguiente información. Envio por medio de una peticion Get el codigo postal y obtendo como respuesta un documento Json con toda la información que hemos generado, es decir el municipio, estado, asentamientos y ciudades.
@local = http://localhost:8001/ms-direcciones/codigos-postal
GET {{local}}/consultar/66460
{
"codigoPostal": {
"idCodPostal": 35018,
"codPostal": "66460",
"idMunicipio": 1622,
"municipio": "San Nicolás de los Garza",
"idEstado": 19,
"estado": "Nuevo León"
},
"asentamiento": [
{
"idAsen": 175275,
"nombreAsen": "Anáhuac 11",
"tipoAsen": "Condominio"
},
{
"idAsen": 175276,
"nombreAsen": "Año de Juárez (Fomerrey 86)",
"tipoAsen": "Colonia"
},
{
"idAsen": 175277,
"nombreAsen": "Jardín de las Puentes",
"tipoAsen": "Colonia"
},
{
"idAsen": 175278,
"nombreAsen": "Las Puentes Sector 1",
"tipoAsen": "Colonia"
},
{
"idAsen": 175279,
"nombreAsen": "Las Puentes Sector 2",
"tipoAsen": "Fraccionamiento"
},
{
"idAsen": 175280,
"nombreAsen": "Las Puentes Sector 3",
"tipoAsen": "Fraccionamiento"
},
{
"idAsen": 175281,
"nombreAsen": "Las Puentes Sector 4",
"tipoAsen": "Fraccionamiento"
},
{
"idAsen": 175282,
"nombreAsen": "Las Puentes Sector 5",
"tipoAsen": "Fraccionamiento"
},
{
"idAsen": 175283,
"nombreAsen": "Las Puentes Sector 6",
"tipoAsen": "Fraccionamiento"
},
{
"idAsen": 175284,
"nombreAsen": "Las Puentes Sector 7",
"tipoAsen": "Fraccionamiento"
},
{
"idAsen": 175285,
"nombreAsen": "Las Puentes Sector 8",
"tipoAsen": "Fraccionamiento"
},
{
"idAsen": 175286,
"nombreAsen": "Las Puentes Sector 9",
"tipoAsen": "Fraccionamiento"
},
{
"idAsen": 175287,
"nombreAsen": "Las Puentes Sector 10",
"tipoAsen": "Fraccionamiento"
},
{
"idAsen": 175288,
"nombreAsen": "Las Puentes Sector 11",
"tipoAsen": "Fraccionamiento"
},
{
"idAsen": 175289,
"nombreAsen": "Las Puentes Sector 12",
"tipoAsen": "Fraccionamiento"
},
{
"idAsen": 175290,
"nombreAsen": "Las Puentes Sector 14",
"tipoAsen": "Fraccionamiento"
},
{
"idAsen": 175291,
"nombreAsen": "Las Puentes Sector 15",
"tipoAsen": "Fraccionamiento"
},
{
"idAsen": 175292,
"nombreAsen": "Paseo de las Puentes",
"tipoAsen": "Colonia"
},
{
"idAsen": 175293,
"nombreAsen": "Residencial las Puentes",
"tipoAsen": "Colonia"
},
{
"idAsen": 175294,
"nombreAsen": "Rincón de las Puentes",
"tipoAsen": "Colonia"
},
{
"idAsen": 175295,
"nombreAsen": "Rincón de los Andes",
"tipoAsen": "Colonia"
},
{
"idAsen": 175296,
"nombreAsen": "Valle de las Puentes",
"tipoAsen": "Colonia"
},
{
"idAsen": 175297,
"nombreAsen": "Villa las Puentes",
"tipoAsen": "Colonia"
}
],
"ciudad": [
{
"id_ciudad": 543,
"nombreCiudad": "San Nicolás de los Garza"
}
]
}
Para optimizar el rendimiento del servicio, es recomendable implementar un sistema de caché. En este ejemplo, observamos cómo la primera consulta se realiza directamente a la base de datos, mientras que las consultas subsecuentes se responden desde la caché. Es esencial analizar detenidamente el uso de la caché antes de su implementación. En este caso, estamos consultando catálogos que no experimentarán cambios significativos a lo largo de varios meses. Por lo tanto, es aconsejable minimizar, en la medida de lo posible, las consultas innecesarias al motor de base de datos. Esta estrategia se traduce en un mejor rendimiento, tiempos de respuesta más rápidos y, en caso de utilizar un servicio bajo demanda, una reducción en los costos de facturación.
DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-1] o.s.web.servlet.DispatcherServlet : GET "/ms-direcciones/codigos-postal/consultar/86640", parameters={} DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-1] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped to com.arq.arquetipo.infrastructure.controller.ControllerDirecciones#consultaCodigo(String) DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-1] o.mariadb.jdbc.message.server.OkPacket : Database change: is 'erm_cat_direcciones' DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-1] o.m.jdbc.client.impl.StandardClient : execute query: SET NAMES utf8mb4 DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-1] o.mariadb.jdbc.message.server.OkPacket : System variable change: character_set_client = utf8mb4 DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-1] o.mariadb.jdbc.message.server.OkPacket : System variable change: character_set_connection = utf8mb4 DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-1] o.mariadb.jdbc.message.server.OkPacket : System variable change: character_set_results = utf8mb4 DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-1] o.m.jdbc.client.impl.StandardClient : execute query: set autocommit=0 DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-1] o.mariadb.jdbc.message.server.OkPacket : System variable change: autocommit = OFF DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-1] o.m.jdbc.client.impl.StandardClient : execute query: SELECT cp.id_cp idCodPostal, cp.cod_postal codPostal,mun.id_municipio idMunicipio, mun.descripcion municipio, est.id_estado idEstado, est.descripcion estado FROM tc_cp cp INNER JOIN tc_municipio mun ON mun.id_municipio = cp.id_municipio INNER JOIN tc_estado est ON est.id_estado = mun.id_estado WHERE cp.cod_postal = ? DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-1] o.m.jdbc.client.impl.StandardClient : execute query: SELECT asen.id_asen idAsen, asen.nombre nombreAsen, tip.nombre tipoAsen FROM tc_asentamiento asen INNER JOIN tc_tipo_asentamiento tip ON tip.id_tipo_asen = asen.id_tipo_asen WHERE asen.id_cp = ? DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-1] o.m.jdbc.client.impl.StandardClient : execute query: SELECT cdd.id_ciudad, cdd.descripcion nombreCiudad FROM tc_ciudad cdd WHERE cdd.municipio = ? DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-1] o.m.jdbc.client.impl.StandardClient : execute query: set autocommit=1 DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-1] o.mariadb.jdbc.message.server.OkPacket : System variable change: autocommit = ON DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-1] o.s.w.s.m.m.a.HttpEntityMethodProcessor : Using 'application/json', given [*/*] and supported [application/json, application/*+json] DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-1] o.s.w.s.m.m.a.HttpEntityMethodProcessor : Writing [CodigoPostalResponse(codigoPostal=CodigoPostalEntity(idCodPostal=48914, codPostal=86640, idMunicipio (truncated)... DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-1] o.s.web.servlet.DispatcherServlet : Completed 200 OK DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-2] o.s.web.servlet.DispatcherServlet : GET "/ms-direcciones/codigos-postal/consultar/86640", parameters={} DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-2] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped to com.arq.arquetipo.infrastructure.controller.ControllerDirecciones#consultaCodigo(String) DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-2] o.s.w.s.m.m.a.HttpEntityMethodProcessor : Using 'application/json', given [*/*] and supported [application/json, application/*+json] DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-2] o.s.w.s.m.m.a.HttpEntityMethodProcessor : Writing [CodigoPostalResponse(codigoPostal=CodigoPostalEntity(idCodPostal=48914, codPostal=86640, idMunicipio (truncated)... DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-2] o.s.web.servlet.DispatcherServlet : Completed 200 OK DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-3] o.s.web.servlet.DispatcherServlet : GET "/ms-direcciones/codigos-postal/consultar/86640", parameters={} DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-3] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped to com.arq.arquetipo.infrastructure.controller.ControllerDirecciones#consultaCodigo(String) DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-3] o.s.w.s.m.m.a.HttpEntityMethodProcessor : Using 'application/json', given [*/*] and supported [application/json, application/*+json] DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-3] o.s.w.s.m.m.a.HttpEntityMethodProcessor : Writing [CodigoPostalResponse(codigoPostal=CodigoPostalEntity(idCodPostal=48914, codPostal=86640, idMunicipio (truncated)... DEBUG 77358 --- [ms-direcciones] [http-nio-8001-exec-3] o.s.web.servlet.DispatcherServlet : Completed 200 OK
