La Fórmula Estándar con la que las entidades
aseguradoras estiman su Solvencia define como la mayor concentración de riesgo
de incendio de una empresa de seguros o reaseguros al conjunto de edificios que
represente la mayor suma asegurada y que cumpla las condiciones siguientes:
- que la empresa de seguros o reaseguros tenga, en relación con cada edificio, obligaciones de seguro o reaseguro en las líneas de negocio 7 y 19 contempladas en el anexo I, que cubran los daños ocasionados por un incendio o explosión, incluso a raíz de ataques terroristas;
- que todos los edificios estén total o parcialmente ubicados en un radio de 200 metros.
Para este ejemplo supondremos que la cartera está
situada de forma aleatoria a lo largo de la Comunidad de Madrid con un capital asegurado variable
neto de cualquier reaseguro que la compañía disponga en ese momento.
Fijado el objetivo enumeramos las herramientas y
enlaces a web que utilizaremos para esta tarea. Hay que añadir que los datos utilizados junto con el software son gratuitos (algo que nos gusta mucho a los españoles).
- Centro de descargas del cnig (datos oficiales del ministerio de Fomento). Datos públicos georreferenciados relativos a calles, domicilios, portales, parcelas, edificios emblemáticos, etc. en diferentes formatos.
- QGIS Desktop 3.8.0 (gran herramienta para proyectar mapas, geolocalizar, etc.)
- Cartera FICTICIA transformada (csv - UTF8) de 50 mil pólizas. La BBDD Cartera debe contar con los campos:
o
Calle
|
o
Localidad
|
o
Número
|
o
Suma Asegurada Neta
|
o
Código Postal
|
PARTE 1 – Obtención de los datos y representación espacial
Por si acaso te lías a la hora de descargar los datos relativos
a Madrid (a mí me pasó) o de cualquier otra región te indico cómo proceder. La idea es descargar datos relativos a direcciones
físicas con coordenadas espaciales de toda la comunidad. El ministerio de
Fomento pone a disposición del público esta información en la base de datos
CartoCiudad con el único requisito de responder una encuesta en el paso final.
En esta web puedes descargar datos
espaciales como por ejemplo calles, líneas de costa, hidrografía, modelos
digitales de elevación de terreno, límites marítimos, etc. Las posibilidades
son infinitas. Para el caso que nos ocupa con la BBDD de CartoCiudad tendremos más
que suficiente. Como siguiente paso debes seleccionar que los datos serán
descargados por División administrativa para la Comunidad de Madrid (o la región
que decidas).
Una
vez completada la encuesta e instalada la última actualización de java, imprescindible
para realizar la descarga, debemos descomprimir el zip en la ubicación que nos
resulte más cómodo. Después de estos sencillos pasos nos encontraremos con los siguientes ficheros:
A primera vista, los ficheros descargados nos aportan nula información. Sin embargo
todo cambia cuando arrastramos los ficheros PORTAL_PK.shp y MANZANA.shp al cuadro
de capas de la parte inferior izquierda de QGIS.
Esta herramienta es capaz de traducir toda la información contenida en los ficheros shp y representarlos en el mapa. Para alguien que vivía ajeno a este mundo resultó asombroso 😁.
Superponiendo la capa de PORTALES y mostrando como
etiqueta sencilla el nombre de la vía vemos que la capa PORTAL es de puntos (con los datos que nos interesan) y
la capa MANZANAS es de polígonos (le he cambiado el color para que tenga un atractivo
visual más potente).
Esta herramienta es capaz de traducir toda la información contenida en los ficheros shp y representarlos en el mapa. Para alguien que vivía ajeno a este mundo resultó asombroso 😁.
Estos últimos no figuran en su
lista de atributos pero están incrustados en la capa y por eso QGIS es
capaz de pintarlos en el mapa.
Aunque no es el objetivo de este post, por si os sale en una nueva versión del trivial hay que saber que existen al menos otros dos tipos de capas cuyo uso
es bastante frecuente: capa de líneas (principalmente definen calles,
carreteras, caminos, etc.) y capa de multilíneas (capa que lista polilíneas).
¿Cómo hacemos para sacar de esta base de datos la información espacial y que podamos verla como atributo?
¿Cómo hacemos para sacar de esta base de datos la información espacial y que podamos verla como atributo?
QGIS trae por defecto una serie de herramientas que
permiten hacer maravillas con la información cargada. Entre ellas existe una que
se llama “Agregar atributos de geometría” (se agradece que todo sea tan intuitivo).
Una vez ejecutado este algoritmo nos debería aparecer
una capa temporal nombrada como “Información de geometría añadida” o similar. En
esta capa es donde QGIS nos ha añadido dos nuevos campos para cada punto, con los
valores de latitud y longitud nombrados por defecto como xcoord e ycoord.
Llegados a este punto y debido a que mi cartera ficticia
es relativamente pequeña opté por el camino más fácil en cuanto a conocimientos
técnicos de SQL (aunque por otro lado se trata de la opción más tediosa y te
cuento porqué).
El problema que os podéis encontrar es que la
dirección asignada por la compañía a cada póliza no coincida exactamente con la
dirección que podamos obtener de los datos del ministerio. No os queda otra que
pelearos con los datos y tratar de automatizar su homogeneización.
Tras una dura pelea doy por supuesto que habéis conseguido
domar la base de datos y que en vuestro csv original ya aparecen dos nuevos
campos que son xcoord e ycoord. El siguiente paso es importar a
QGIS toda esta información.
Para ello, crearemos una nueva capa vectorial desde
fichero csv delimitado por comas y cargaremos nuestra cartera georreferenciada.
De forma automática QGIS os debería detectar qué
campos se corresponden con datos geoespaciales. Sino fuera así podréis asignarlos
vosotros mismos en coordenadas del punto del siguiente en cuadro de diálogo que os muestro.
Es importante asignar a todo el proyecto (y especialmente
a esta capa) un SRC (sistema de referencia de coordenadas) acorde a los
datos que estéis cargando. En mi caso concreto el SCR asignado es EPSG:4903 –
Madrid 1870 (Madrid) también válido para todo el territorio nacional.
Este aspecto es muy importante ya que si los SRC asignados no son homogéneos, QGIS no os permitirá visualizar la capa ni tener
el metro como sistema de medida de distancias, entre otros.
Sin entrar excesivamente en este aspecto añadir que QGIS permite medir distancias en grados de una esfera utilizando un sistema de referencia elipsoidal. No olvidemos que a nosotros nos interesa obtener la mayor suma asegurada en un radio de 200 metros.
Sin entrar excesivamente en este aspecto añadir que QGIS permite medir distancias en grados de una esfera utilizando un sistema de referencia elipsoidal. No olvidemos que a nosotros nos interesa obtener la mayor suma asegurada en un radio de 200 metros.
La otra alternativa es cruzar las dos
capas a través de SQL. Es una forma mucho más rápida y fina pero el
conocimiento de un servidor en cuanto al tratamiento de BBDD bajo SQL se limita
a lanzar consultas simples e introducir operaciones aritméticas y condicionales
sencillos.
Finalizada la carga de la cartera georreferenciada
y teniéndola seleccionada junto con la capa de PORTAL_TK ya deberías poder
visualizar aquellos inmuebles de la comunidad de Madrid en los que vuestra compañía
de seguros está presente.
Este paso ya arroja un poco de luz sobre
la potencia de esta aplicación y de las innumerables aplicaciones que se nos ofrecen
(medir la penetración en el mercado local, asignar campañas de marketing
focalizadas en aquellas áreas donde no tengamos presencia tipo trae a tu vecino y el seguro le sale gratis, observar aquellas
zonas donde la siniestralidad supone un lastre para nuestra cuenta de PyG con
las modificaciones en política de suscripción y tarificación, etc.). No obstante, no
debemos desviarnos de nuestro objetivo (SCR cat por incendios).
En rojo tenemos las pólizas de la compañía y en verde todos
los portales incluidos en la BBDD del Ministerio de Fomento. Si un bloque tiene
varias plantas estos tienen la misma referencia geoespacial y por tanto se
incluyen en el mismo punto, es decir, los puntos rojos nos señalan donde está
presente la compañía de seguros lo cual no implica que haya conseguido todas
las pólizas del bloque.
Visualmente no se si hay alguna forma de distinguir
entre dos pólizas que pertenecen a la mismo coordenada espacial. No obstante, estas dos pólizas
sí que entrarán en los cálculos de la mayor suma asegurada.
Parte 2 - Obtención de la mayor suma asegurada en un radio de 200 metros
Nuevamente alabar al equipo que ha desarrollado el
software QGIS. Nos leyeron la mente en cuanto a nuestras necesidades actuariales e implementaron una herramienta llamada buffer
que nos pinta circunferencias concéntricas a los puntos de la capa que
deseemos con nuestro radio favorito. A estas alturas del post ya deberíais
haber adivinado que nuestro radio favorito serán 200 metros.
Pues bien, ejecutando la herramienta buffer automáticamente
os aparecerán estas circunferencias de radio 200 m concéntricas a cada uno de
los puntos que representan nuestra cartera.
Esta capa buffer tiene los mismo atributos que la cartera incluida la suma asegurada neta de reaseguro que es la que nos interesa agrupar por proximidad.
Esta capa buffer tiene los mismo atributos que la cartera incluida la suma asegurada neta de reaseguro que es la que nos interesa agrupar por proximidad.
Ojo si estamos trabajando con una BBDD muy grande. El resultado
visual puede ser bastante desagradable. No os desaniméis. Estamos casi llegando
al final de la meta. QGIS se nota que ha sido desarrollado bajo software libre
y estéticamente permite modificar prácticamente casi todo.
Con estas dos capas (cartera y buffer) estaríamos en
condiciones de obtener una nueva capa que asigne a cada punto su suma asegurada
y le agregue todas aquellas sumas aseguradas que estén dentro de los límites
que marca la circunferencia de la capa buffer.
Dicho así se lee bastante lioso pero no olvidar que
los desarrolladores de QGIS se anticipan siempre a nuestras necesidades y han
implementado herramientas para todo, incluido “unir atributos por localización
(resumen)”.
Con el cuadro de dialogo que se nos abre al ejecutar
esta herramienta podremos ver las múltiples opciones que nos ofrece.
Efectivamente tendremos que seleccionar como capa de entrada
nuestra capa buffer y como capa de unión la capa de Cartera. El resto es bastante
intuitivo. Debemos seleccionar:
· En campo a resumir seleccionamos el Capital Neto de
reaseguro,
· En predicado geométrico todos aquellos puntos que solapan,
están contenidos o cruzan a la circunferencia.
· En resúmenes a calcular: SUM. Aquí podéis ver que
existen incluso estadísticos como la media o la desviación típica lo que nos
puede dar lugar, por ejemplo, a algún tipo de ajuste de distribución.
Ejecutado el algoritmo se nos genera una nueva capa buffer que a priori parece idéntica a la que ya teníamos.
Ejecutado el algoritmo se nos genera una nueva capa buffer que a priori parece idéntica a la que ya teníamos.
Nada más lejos de la realidad. El algoritmo “unir
atributos por localización” ha añadido en esta capa buffer un nuevo
atributo al que nombra igual que el original pero añadiendo el sufijo _SUM donde
ha sumado todos capitales netos de reaseguro que están dentro de cada circunferencia.
Adjunto captura para que podáis verificar el
funcionamiento del algoritmo. Por un lado tenemos el capital Neto para cada póliza
y en la imagen de la derecha como QGIS ha sumado los capitales netos de las dos
pólizas de la derecha ya que sus circunferencias contienen respectivamente la
una a la otra.
Como último paso, bastaría con filtrar por el atributo
CapitalNeto_SUM y hacer que QGIS nos muestre únicamente el máximo global. En nuestro caso se encuentra en Madrid aunque también
podríais filtrar, por ejemplo, máximos por localidad. Las opciones que ofrece
este software son infinitas.
 |
| Máximo Global (situado en Madrid Ciudad ) |
 |
| Máximo local de Getafe Seguro que encontráis la forma que más os encaje a la hora de presentar el resultado. |
