Realiza cualquier forma en el mapa con Excel

Realiza cualquier forma en el mapa con Excel

mario.hernandez 08 April 2019

En algunas organizaciones es difícil tener instalado algún lenguaje de programación, sin embargo, siempre se cuenta con  Microsoft Excel en los equipos de cómputo (Al menos en México). Es por esto que este post está implementado en Excel, si bien es necesario saber un poco de macros de Excel, no será necesario ningún conocimiento de programación para ejecutarlo, si deseas utilizar solo el archivo de excel para generar figuras georreferenciadas descárgalo aquí Excel icon.

Las ecuaciones de los post anteriores las he pasado a Visual Basic para que puedan ser utilizadas en Excel, pero los conceptos siguen siendo exactamente los mismos, por lo que te recomiendo leer los post anteriores:

Distancia entre dos puntos geográficos.
Cálculo de las coordenadas geográficas de un punto a partir de una distancia y un ángulo.

Como ejemplo para este post realizaremos un corazón georreferenciado, esto nos ayudará a generar círculos, sectores, cuadrados, triángulos, pentágonos, etc. los cuales visualizaremos posteriormente en Google earth o Google maps.

Formato KML

El formato KML es un formato utilizado por Google para datos georreferenciados utilizando una estructura XML, para mayor información te recomiendo leer la documentación oficial de google kml

No entraremos en detalle en el formato KML, sólo lo básico para representar nuestra figura en google earth, sin embargo, es primordial entender cómo se genera un polígono en un mapa, lo cual no es exclusivo de los archivos KML, ya que aplica para otros formatos GML, GeoJSON, etc.

El archivo KML se visualiza internamente como:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2" xmlns:gx="http://www.google.com/kml/ext/2.2">
    <Document>
        <Style id="style_dfym">
            <LineStyle>
                <color>FF00FF00</color>
                <width>2</width>
            </LineStyle>

            <PolyStyle>
                <color>640000ff</color>
                <altitudeMode>relativeToGround</altitudeMode>
                <colorMode>normal</colorMode>
                <fill>1</fill>
                <outline>1</outline>
            </PolyStyle>
        </Style>
        
        <Placemark>
            <name>NOMBRE DEL POLÍGONO</name>
            <styleUrl>#style_dfym</styleUrl>
            <Polygon>
                <outerBoundaryIs>
                    <LinearRing>
                        <coordinates>
                            -126.363797064723,6.67234407574325,0.0
                            -122.521015936572,10.3956104707276,0.0
                            -118.038883581533,13.5271000270576,0.0
                            .
                            .
                            .    
                            -132.045425543994,-1.96607067020402,0.0
                            -129.535492406093,2.50713375550364,0.0
                            -126.363797064723,6.67234407574325,0.0
                        </coordinates>
                    </LinearRing>
                </outerBoundaryIs>
            </Polygon>
        </Placemark>
    </Document>
</kml>

De lo anterior, sólo nos centraremos en la parte que define al polígono:

                        <coordinates>
                            -126.363797064723,6.67234407574325,0.0
                            -122.521015936572,10.3956104707276,0.0
                            -118.038883581533,13.5271000270576,0.0
                            .
                            .
                            .    
                            -132.045425543994,-1.96607067020402,0.0
                            -129.535492406093,2.50713375550364,0.0
                            -126.363797064723,6.67234407574325,0.0
                        </coordinates>

Cada línea dentro de <coordinates>, define "longitud,latitud,altura" de nuestro polígono y cada línea representa un ángulo, la primera es 0° y la última 360° (La primera línea y la última siempre son las mismas). De tal suerte que si queremos un triángulo, tendremos 4 líneas dentro de <coordinates> (las correspondientes a 0°, 120°, 240° y 360°), para un pentágono definiremos 6 líneas (0°, 72°, 144°, 216°, 288° y 360°). La altura para este post siempre será 0.0 ya que serán figuras a nivel del suelo, sin embargo, si deseas realizar figuras con alturas (3D), puedes definir una altura. Es importante mencionar que las latitudes y longitudes estan en formato decimal.

Como dibujar un polígono en el mapa

Como se observó anteriormente las líneas dentro de <coordinates>, son en realidad puntos hacia un determinado ángulo o acimut, para definirlos necesitaremos 2 cosas:

  1. El centro de nuestro polígono (referencia), marcador azul en la Figura 1;
  2. Generar puntos alrededor de nuestra referencia (los cuales representan líneas dentro de <coordinates>). Para definir los puntos, imagina que colocas un lápiz a una determinada distancia de la referencia, empezando por el norte verdadero 0°, una vez que lo colocas no podrás despegar el lápiz y solo podrás avanzar moviéndote "X" grados en dirección a las manecillas del reloj, tendrás que ir realizando pausas, las cuales significarán un punto que definirá y formará parte de nuestro polígono, y finalmente siempre cerrar nuestro polígono en el punto inicial (razón de que la primera línea y la última dentro de <coordinates> siempre son iguales), como se observa en  la figura 1.

dibujando un polígono en el mapaFigura1. Dibujar un polígono.

Como podrás ver, cada punto es determinado con respecto a nuestra referencia, una distancia y un ángulo (que es exactamente lo que realizamos en este post). Entre más puntos definidos, mejor detallado estará nuestro polígono, aunque existen polígonos que necesitan pocos puntos para definirlos perfectamente, por ejemplo, para un triángulo necesitamos solo 3 puntos, para un cuadrado nos basta con 4 puntos, un pentágono con 5, un hexágono con 6, etc. (Cuando definimos 72 puntos ya dará la impresión de estar observando un círculo).

Con lo anterior podremos definir la "longitud,latitud,altura" que contendrá cada línea dentro de <coordinates> de nuestro archivo KML.

Para definir puntos de formas más complejas necesitamos conocer la ecuación polar que defina nuestro polígono, para un círculo, pentágono, hexágono, etc., es mucho más sencillo ya que tendrá la misma distancia hacia todos las direcciones con respecto a nuestra referencia.

Para la forma de corazón utilizaremos la siguiente expresión en forma polar:

Ecuacion polar de un corazón

donde r será nuestra distancia y angulo el ángulo.

Implementación con Excel

Puedes descargar el archivo de excel aquí Excel icon.

Los elementos en la hoja de excel las he ordenado de esta forma, a fin de que sea fácil modificar la forma del polígono a georreferenciar:

Estructura de la Hoja de Excel

Figura 2. Estructura para definir el polígono a georreferenciar.

Columnas:

  • "A" a "F": Define el o los puntos de referencia donde comenzarán a dibujarse las formas en el mapa dentro del KML.
  • "G": Es el tamaño que tendrá la figura en kilómetros.
  • "H": Nombre que tendrá nuestra forma (polígono).
  • "J": Comenzamos a generar nuestros puntos en pasos de 5 grados hasta 360 grados (Si se requiere más o menos definición en la forma del polígono se debe aumentar o disminur los pasos, según el caso).
  • "K": Agregar un offset de 90 grados, ya que tenemos que comenzar a dibujar con respecto al norte verdadero (ver el siguiente post).
  • "L": Conversión de grados a radianes.
  • "M": Forma del Polígono. Para este ejemplo, define la expresión matemática del corazón (ecuación 1):
        =IF(3 - 2*SIN(L2)+COS(2*L2) - 2*ABS(COS(L2)) < 0,0,3 - 2*SIN(L2) + COS(2*L2) - 2*ABS(COS(L2)))
  • "N": Para poder controlar el tamaño de la forma con la distancia en [km] (Columna "G"), vamos a normalizar los valores dividiéndolos por el valor máximo de los 360°.
  • Columna "I". Boton para ejecutar la Macro de Excel.

La implentación del código que utiliza la Macro de Excel, encuentrala al final del post.

Archivo de salida KML

El archivo KML "forma_fecha_hora.kml" se encontrará en la misma dirección donde se encuentra el archivo de excel "deformasymapas.xslm", el KML cual podrás visualizar con google earth o qgis.

Salida macro excel figuras georreferenciadas

Figura 3. Archivo KML después de ejecutar el botón "¡Generar KML!"

I love Uruguay

Figura 4. Visualización en Google earth (Versión Escritorio).

Otras formas de polígonos

A continuación encuentra otras ideas de formas:

Polygon Triangle

Circle polygon

Sector polygon

Sector 2 polygon

Sectors pay polygon

Hexagon polygon

Pentagon polygon

Figura 5. Ejemplos de otros polígonos georreferenciados con macro Excel.

 

Código en Micosoft Visual Basic

' Macro de excel para georeferencias figuras
' Aprede a realizarlo en:
'
' www.deformasymapas.com
'

Function dms2dec(grad As Integer, min As Integer, seg As Double) As Double
    '
    'Convierte coordenadas de grados minutos a segundos a decimal
    '
    
    Dim dms(0 To 2) As Variant
    
    dms(0) = grad
    dms(1) = min
    dms(2) = seg
    
    signo = 1
    For i = 0 To 2
        If dms(i) <> 0 Then
            signo = signo * dms(i)
            Exit For
        End If
    Next i
    
    If signo < 0 Then signo = -1
    
    If signo > 0 Then
        dms2dec = grad + Abs(min) / 60 + Abs(seg) / 3600
    Else
        dms2dec = grad - Abs(min) / 60 - Abs(seg) / 3600
    End If
    
End Function


Function obtiene_p2(lat1 As Variant, lon1 As Variant, d As Double, ang12 As Double) As Variant
    '
    'Coordenadas de un punto dada una distancia y acimut a partir del punto P1
    '
    
    On Error GoTo eh
    
    Dim returnVal(1) As Variant
    
    If ang12 = 360 Then ang12 = 0
    
    lat1rad = WorksheetFunction.Radians(dms2dec(Int(lat1(0)), Int(lat1(1)), CDbl(lat1(2))))
    lon1rad = WorksheetFunction.Radians(dms2dec(Int(lon1(0)), Int(lon1(1)), CDbl(lon1(2))))
    
    d_grados = d / 111.18
    d_rad = WorksheetFunction.Radians(d_grados)
    
    ang12rad = WorksheetFunction.Radians(ang12)
    
    a = Sin(lat1rad) * Cos(d_rad)
    b = Cos(lat1rad) * Sin(d_rad) * Cos(ang12rad)
    lat2 = WorksheetFunction.Asin(a + b)
    
    x = Cos(d_rad) - Sin(lat1rad) * Sin(lat2)
    y = Cos(lat1rad) * Cos(lat2)
    Z = x / y
    
    
    k = WorksheetFunction.Acos(Z)
    
    
    If ang12 >= 180 And ang12 < 360 Then k = -k

    lon2 = lon1rad + k

    returnVal(0) = WorksheetFunction.Degrees(lat2)
    returnVal(1) = WorksheetFunction.Degrees(lon2)

    GoTo fin
eh:
    k = WorksheetFunction.Acos(1#)
    
    If ang12 >= 180 And ang12 < 360 Then k = -k

    lon2 = lon1rad + k

    returnVal(0) = WorksheetFunction.Degrees(lat2)
    returnVal(1) = WorksheetFunction.Degrees(lon2)
fin:
    obtiene_p2 = returnVal
End Function


Sub obtener_kml()
    '
    'Genera archivo kml con una forma de poligono georeferenciada
    '
    kml_file = ActiveWorkbook.Path & "\forma" & Format(Now(), "_dd-mm-yyyy_hh-nn-ss") & ".kml"

    Open kml_file For Output As #1
    
    linea = "<?xml version=""1.0"" encoding=""UTF-8""?>"
    Print #1, linea
    
    linea = "<kml xmlns=""http://www.opengis.net/kml/2.2"" xmlns:gx=""http://www.google.com/kml/ext/2.2"">"
    Print #1, linea
    
    linea = "  <Document id=""feat_29"">"
    Print #1, linea
    
    linea = "    <Style id=""style_dfym"">"
    Print #1, linea
    
    Print #1, "      <LineStyle>"
    Print #1, "        <color>FF00FF00</color>"
    Print #1, "        <width>2</width>"
    Print #1, "      </LineStyle>"
    
    Print #1, "      <PolyStyle>"
    Print #1, "        <color>640000ff</color>"
    Print #1, "        <altitudeMode>relativeToGround</altitudeMode>"
    Print #1, "        <colorMode>normal</colorMode>"
    Print #1, "        <fill>1</fill>"
    Print #1, "        <outline>1</outline>"
    Print #1, "      </PolyStyle>"
    Print #1, "    </Style>"
    
    Range("A2").Select
    
    Dim lat1(2), lon1(2), coords As Variant
    Dim d, ang12 As Double
    
    renglon_puntos = 0
    Do While ActiveCell.Offset(renglon_puntos, 0).Value <> ""
        lat1(0) = ActiveCell.Offset(renglon_puntos, 0).Value
        lat1(1) = ActiveCell.Offset(renglon_puntos, 1).Value
        lat1(2) = ActiveCell.Offset(renglon_puntos, 2).Value
        
        lon1(0) = ActiveCell.Offset(renglon_puntos, 3).Value
        lon1(1) = ActiveCell.Offset(renglon_puntos, 4).Value
        lon1(2) = ActiveCell.Offset(renglon_puntos, 5).Value
        
        radio = ActiveCell.Offset(renglon_puntos, 6).Value
        
        Print #1, "    <Placemark>"
        Print #1, "      <name>" & ActiveCell.Offset(renglon_puntos, 7).Value & "</name>"
        Print #1, "      <description>"
        
        linea = "        <a href=""https://www.deformasymapas.com/"">deformasymapas</a>"
        Print #1, linea
        Print #1, "      </description>"
        Print #1, "      <styleUrl>#style_dfym</styleUrl>"
        Print #1, "      <Polygon>"
        Print #1, "        <outerBoundaryIs>"
        Print #1, "          <LinearRing>"
        Print #1, "            <coordinates>"
        
        renglon_grados = 0
        Do While ActiveCell.Offset(renglon_grados, 9).Value <> ""
            ang12 = ActiveCell.Offset(renglon_grados, 9).Value
            d = radio * ActiveCell.Offset(renglon_grados, 13).Value
            coords = obtiene_p2(lat1, lon1, CDbl(d), CDbl(ang12))
            
            Print #1, "              " & coords(1) & "," & coords(0) & ",0.0"
            
            renglon_grados = renglon_grados + 1
        Loop
        
        Print #1, "            </coordinates>"
        Print #1, "          </LinearRing>"
        Print #1, "        </outerBoundaryIs>"
        Print #1, "      </Polygon>"
        Print #1, "    </Placemark>"
        
        renglon_puntos = renglon_puntos + 1
    Loop
    
    Print #1, "  </Document>"
    Print #1, "</kml>"
    
    Close #1
    
End Sub