Uso de funciones de datos GeoJSON

La especificación GeoJson define la estructura y el contenido de los objetos JSON que se supone que representan formas geográficas en la tierra (llamadas geometrías). Según la especificación GeoJson, para que un objeto JSON sea un objeto de geometría, debe tener dos campos denominados tipo y coordenadas, donde el valor del campo de tipo especifica el tipo de geometría y el valor de las coordenadas debe ser una matriz cuyos elementos definen la forma geométrica.

Todo tipo de geometrías se especifican en términos de un conjunto de posiciones. Sin embargo, para las cuerdas de línea y polígonos, la forma geométrica real está formada por las líneas que conectan sus posiciones. La especificación GeoJson define una línea entre dos puntos como la línea recta que conecta los puntos en el sistema de coordenadas cartesianas (plano) cuyos ejes horizontal y vertical son la longitud y la latitud, respectivamente.

Hay varias funciones incorporadas que interpretan los objetos JSON como geometrías y permiten buscar las filas que contienen geometrías que cumplen determinadas condiciones.

Para seguir los ejemplos, cree una tabla desde la consola de OCI mediante el modo de entrada DDL avanzada. La sentencia DDL se proporciona a continuación.

CREATE TABLE IF NOT EXISTS PointsOfInterest (
    id INTEGER, poi JSON,
PRIMARY KEY(id));

Consulte Creación de Tablas Singleton: Modo de Entrada DDL Avanzado para conocer los pasos para crear una tabla con una sentencia DDL.

Para cargar datos en la tabla creada desde la consola de OCI, haga clic en el nombre de la tabla. Se muestran los detalles de la tabla. Haga clic en Cargar datos. Haga clic en seleccionar archivo para cargar y proporcione el archivo JSON que se va a cargar. Puede descargar el archivo DDL y JSON para los datos GeoJSON aquí

geo_dentro

Determina las geometrías dentro de una geometría GeoJSON delimitadora.

boolean geo_inside(any*, any*)

• El primer parámetro any* puede ser cualquier objeto geométrico.

• El segundo parámetro any* debe ser un polígono.

La función determina si la geometría apuntada por el primer parámetro está completamente contenida dentro del polígono apuntado por el segundo parámetro.

Si alguno de los dos parámetros no devuelve un único objeto de geometría válido, y si se puede detectar en tiempo de compilación, la función genera un error.

El comportamiento en tiempo de ejecución es el siguiente:

• Devuelve false si algún parámetro devuelve 0 o más de 1 elemento.

• Devuelve NULL si algún parámetro devuelve NULL.

• Devuelve false si algún parámetro (en tiempo de ejecución) devuelve un elemento que no es un objeto de geometría válido.

• Devuelve false si el segundo parámetro devuelve un objeto de geometría que no es un polígono.

• Si ambos parámetros devuelven un único objeto de geometría cada uno y la segunda geometría es un polígono.

  • Devuelve verdadero si la primera geometría está completamente contenida dentro del segundo polígono, es decir, todos sus puntos pertenecen al interior del polígono.

  • De lo contrario, devuelve false.

Nota: el interior de un polígono son todos los puntos del área del polígono, excepto los puntos del anillo lineal que definen el límite del polígono.

Ejemplo: busca parques naturales en el norte de California.

SELECT t.poi.name AS park_name,
t.poi.address.street AS park_location
FROM PointsOfInterest t
WHERE t.poi.kind = "nature park"
AND geo_inside(t.poi.location,
              { "type" : "polygon",
                "coordinates": [[
                  [-
120.1135253906249, 36.99816565700228],
                  [-119.0972900390625, 37.391981943533544],
                  [-119.2840576171875, 37.97451499202459],
                  [-120.2069091796874, 38.035112420612975],
                  [-122.3822021484375, 37.74031329210266],
                  [-122.2283935546875, 37.15156050223665],
                  [-121.5362548828124, 36.85325222344018],
                  [-120.1135253906249, 36.99816565700228]
                ]]
             });

Explicación:

• Consulte la tabla PointsOfInterest para filtrar las filas de nature park.

• Especifique un polígono como segundo parámetro para la función geo_inside.

• Las coordenadas del polígono que especifique corresponden a las coordenadas de la parte norte del estado de California en los Estados Unidos.

• La función geo_inside solo devuelve filas cuando la ubicación del parque natural está completamente contenida dentro de los puntos de ubicación especificados.

Resultado:

{"park_name":"portola redwoods state park",
"park_location":"15000 Skyline Blvd"}

geo_intersección

Determina las geometrías que se cruzan con una geometría GeoJSON.

boolean geo_intersect(any*, any*)

El primero y el segundo parámetros any* pueden ser cualquier objeto geométrico.

La función determina si dos geometrías que se especifican como parámetros tienen puntos en común. Si alguno de los dos parámetros no devuelve un único objeto de geometría válido, y si se puede detectar en tiempo de compilación, la función genera un error.

El comportamiento en tiempo de ejecución es el siguiente:

• Devuelve false si algún parámetro devuelve 0 o más de 1 elemento.

• Devuelve NULL si algún parámetro devuelve NULL.

• Devuelve false si algún parámetro (en tiempo de ejecución) devuelve un elemento que no es un objeto de geometría válido.

Si ambos parámetros devuelven un único objeto de geometría cada uno, la función devuelve true si las 2 geometrías tienen puntos en común; de lo contrario, false.

Ejemplo: Texas está considerando regular el acceso al suministro de agua subterránea. Un acuífero es una capa subterránea de roca permeable que soporta agua, fracturas de roca o materiales no consolidados. El gobierno quiere imponer nuevas regulaciones para lugares que están muy cerca de un acuífero.

Las coordenadas del acuífero ya han sido mapeadas. Desea conocer todos los condados en el estado de Texas que se cruzan con ese acuífero para que pueda notificar al gobierno del condado de cada condado afectado para participar en conversaciones para las nuevas regulaciones.

SELECT t.poi.county AS County_needs_regulation,
t.poi.contact AS Contact_phone
FROM PointsOfInterest t WHERE
geo_intersect(
    t.poi.location,
    {

     "type" : "polygon",
      "coordinates": [
          [
            [-97.668457031249, 29.34387539941801],
            [-95.207519531258, 29.19053283229458],
            [-92.900390625653, 30.37287518811801],
            [-94.636230468752, 32.21280106801518],
            [-97.778320312522, 32.45415593941475],
            [-99.799804687541, 31.18460913574325],
            [-97.668457031249, 29.34387539941801]
          ]
        ]
    }
);

Explicación:

• La consulta anterior recupera las ubicaciones que se cruzan con la ubicación del acuífero. Es decir, si las coordenadas de ubicación tienen algún punto en común con la ubicación del acuífero.

• Utilice geo_intersect para ver si las coordenadas de la ubicación tienen algún punto común con las coordenadas del acuífero que se especifican.

Resultado:

{"County_needs_regulation":"Tarrant","Contact_phone":"469 745 5687"}
{"County_needs_regulation":"Kinga","Contact_phone":"469 384 7612"}

distancia_geo

Determina la distancia entre dos objetos geoespaciales.

double geo_distance(any*, any*)

El primero y el segundo parámetros any* pueden ser cualquier objeto geométrico.

La función devuelve la distancia geodésica entre las dos geometrías de entrada. La distancia devuelta es la mínima entre las distancias de cualquier par de puntos donde el primer punto pertenece a la primera geometría y el segundo punto a la segunda geometría. Entre dos de estos puntos, su distancia es la longitud de la línea geodésica que conecta los puntos.

Visión general de la línea geodésica

Una línea geodésica entre 2 puntos es la línea más corta que se puede dibujar entre los 2 puntos en la superficie elipsoidal de la tierra. Para una definición simplificada, pero más ilustrativa, suponga por un momento que la superficie de la tierra es una esfera. Entonces, la línea geodésica entre dos puntos en la tierra es el arco menor entre los dos puntos en el gran círculo que corresponde a los puntos, es decir, el círculo que se forma por la intersección de la esfera y el plano definido por el centro de la tierra y los dos puntos.

La siguiente figura muestra la diferencia entre las líneas geodésicas y rectas entre Los Ángeles y Londres.

Descripción de la ilustración geodetic-vs-straight-line.jpg

Si alguno de los dos parámetros no devuelve un único objeto de geometría válido, y si se puede detectar en tiempo de compilación, la función genera un error.

El comportamiento en tiempo de ejecución es el siguiente:

• Devuelve -1 si algún parámetro devuelve cero o más de 1 elemento.

• Devuelve NULL si algún parámetro devuelve NULL.

• Devuelve -1 si alguno de los parámetros no es un objeto de geometría.

De lo contrario, la función devuelve la distancia geodésica en metros entre las 2 geometrías de entrada.

Nota: Los resultados se ordenan de forma ascendente por distancia (mostrando primero la distancia más corta).

Ejemplo: ¿a qué distancia se encuentra el restaurante más cercano de la ubicación especificada?

SELECT
t.poi.name AS restaurant_name,
t.poi.address.street AS street_name,
geo_distance(
    t.poi.location,
    {
       "type" : "point",
       "coordinates": [-
121.94034576416016,37.2812239247177]
    }
) AS distance_in_meters
FROM PointsOfInterest t
WHERE t.poi.kind = "restaurant" ;

Explicación:

• Puede consultar la tabla PointsOfInterest para filtrar las filas de restaurant.

• Proporcione el punto de ubicación correcto y determine la distancia mediante la función geo_distance.

Resultado:

{"restaurant_name":"Coach Sports Bar & Grill","street_name":"80 Edward St","distance_in_meters":799.2645323337218}
{"restaurant_name":"Ricos Taco","street_name":"80 East Boulevard St","distance_in_meters":976.5361117138553}
{"restaurant_name":"Effie's Restaurant and Bar","street_name":"80 Woodeard St","distance_in_meters":2891.0508307646282}

La distancia entre la ubicación actual y el restaurante más cercano es de 799 metros.

geo_within_distance

Determina los objetos geoespaciales cercanos a un punto.

boolean geo_within_distance(any*, any*,double)

El primero y el segundo parámetros any* pueden ser cualquier objeto geométrico.

La función determina si la primera geometría está a una distancia de N metros de la segunda geometría.

Si alguno de los dos parámetros no devuelve un único objeto de geometría válido, y si se puede detectar en tiempo de compilación, la función genera un error.

El comportamiento en tiempo de ejecución es el siguiente:

• Devuelve false si algún parámetro devuelve 0 o más de 1 elemento.

• Devuelve NULL si cualquiera de los dos primeros parámetros devuelve NULL.

• Devuelve false si alguno de los dos primeros parámetros devuelve un elemento que no es un objeto de geometría válido.

Finalmente, si ambos parámetros devuelven un solo objeto de geometría cada uno, devuelve true si la primera geometría se encuentra a una distancia de N metros de la segunda geometría, donde N es el número devuelto por el tercer parámetro; de lo contrario, false. La distancia entre 2 geometrías se define como el mínimo entre las distancias de cualquier par de puntos donde el primer punto pertenece a la primera geometría y el segundo punto a la segunda geometría. Si N es un número negativo, se establece en 0.

Ejemplo:¿Hay un ayuntamiento en los próximos 5 km? ¿Hasta dónde está?

SELECT t.poi.address.street AS city_hall_address,
geo_distance(
    t.poi.location,
    {
        "type" : "point",
        "coordinates" : [-120.653828125,38.85682013474361]
    }

) AS distance_in_meters
FROM PointsOfInterest t
WHERE t.poi.kind = "city hall" AND
geo_within_distance(
    t.poi.location,
    {
        "type" : "point",
        "coordinates" : [-120.653828125,38.85682013474361]
    },
    5000
);

Explicación:

• Puede consultar la tabla PointsOfInterest para filtrar las filas de city hall.

• Utilice la función geo_within_distance para filtrar el ayuntamiento a menos de 5 km (5000 m) de la ubicación indicada.

• También recupera la distancia real entre su ubicación y el ayuntamiento mediante la función geo_distance.

Resultado:

{"city_hall_address":"70 North 1st street","distance_in_meters":1736.0144040331768}

El ayuntamiento se encuentra a 1736 m (1,73 km) de la ubicación actual.

geo_cerca

Determina los objetos geoespaciales cercanos a un punto.

boolean geo_near(any*, any*, double)

El primero y el segundo parámetros any* pueden ser cualquier objeto geométrico.

La función determina si la primera geometría está a una distancia de N metros de la segunda geometría.

Si alguno de los dos parámetros no devuelve un único objeto de geometría válido, y si se puede detectar en tiempo de compilación, la función genera un error.

El comportamiento en tiempo de ejecución es el siguiente:

• Devuelve false si algún parámetro devuelve 0 o más de 1 elemento.

• Devuelve NULL si cualquiera de los dos primeros parámetros devuelve NULL.

• Devuelve false si alguno de los dos primeros parámetros devuelve un elemento que no es un objeto de geometría válido.

Finalmente, si ambos de los dos primeros parámetros devuelven un solo objeto de geometría cada uno, devuelve true si la primera geometría se encuentra a una distancia de N metros de la segunda geometría, donde N es el número devuelto por el tercer parámetro; de lo contrario, false.

Nota: geo_near se convierte internamente en geo_within_distance más un orden (implícito) por la distancia entre las dos geometrías. Sin embargo, si la consulta ya tiene un orden por (explícito), no se realiza ningún orden por distancia. La función geo_near sólo puede aparecer en la cláusula WHERE, donde debe ser un predicado de nivel superior, es decir, no anidado en un operador OR o NOT.

Ejemplo 1:¿Hay un hospital a menos de 3 km de la ubicación indicada?

SELECT
t.poi.name AS hospital_name,
t.poi.address.street AS hospital_address
FROM PointsOfInterest t
WHERE t.poi.kind = "hospital"
AND
geo_near(
    t.poi.location,
    {"type" : "point",
     "coordinates" : [-122.03493933105469,37.32949164059004]
    },
    3000
);

Explicación:

• Puede consultar la tabla PointsOfInterest para filtrar las filas de hospital.

• La función geo_near se utiliza para filtrar hospitales a menos de 3000 metros de la ubicación indicada.

Resultado:

{"hospital_name":"St. Marthas hospital","hospital_address":"18000 West Blvd"}
{"hospital_name":"Memorial hospital","hospital_address":"10500 South St"}

Ejemplo 2: ¿A qué distancia se encuentra una gasolinera a la siguiente milla de la ubicación especificada?

SELECT
t.poi.address.street AS gas_station_address,
geo_distance(
    t.poi.location,
    {
        "type" : "point",
        "coordinates" : [-121.90768646240233,37.292081740702365]
    }
) AS distance_in_meters
FROM PointsOfInterest t
WHERE t.poi.kind = "gas station" AND
geo_near(
    t.poi.location,
    {
        "type" : "point",
        "coordinates" : [-121.90768646240233,37.292081740702365]
    },
    1600
);

Explicación:

• Puede consultar la tabla PointsOfInterest para filtrar las filas de la estación de gas.

• Utilice la función geo_near para filtrar estaciones de servicio a una milla (1600 m) de la ubicación indicada.

• También recupera la distancia real entre su ubicación y la gasolinera mediante la función geo_distance.

Resultado:

{"gas_station_address":"33 North Avenue","distance_in_meters":886.7004173859665}

La distancia real a la estación de servicio más cercana dentro de la siguiente milla es de 886 m.

geo_es_geometría

Valida un objeto geoespacial.

boolean geo_is_geometry(any*)

El parámetro any* puede ser cualquier objeto geométrico.

La función determina si la entrada dada es un objeto de geometría válido.

• Devuelve false si el parámetro devuelve cero o más de 1 elemento.

• Devuelve NULL si el parámetro devuelve NULL.

• Devuelve true si la entrada es un único objeto de geometría válido. De lo contrario, false.

Ejemplo: determine si la ubicación que apunta a city hall es un objeto geométrico válido.

SELECT geo_is_geometry(t.poi.location) AS city_hall
FROM PointsOfInterest t
WHERE t.poi.kind = "city hall"

Explicación: la función geo_is_geometry se utiliza para determinar si una ubicación determinada es un objeto geométrico válido o no.

Resultado:

{ "city_hall" : true}

Temas relacionados

• Creación de Tabla Singleton: Modo de Entrada DDL Avanzado
• Carga de filas de tabla