Utilisation des fonctions de données GeoJSON

La spécification GeoJson définit la structure et le contenu des objets JSON qui sont censés représenter des formes géographiques sur terre (appelées géométries). Selon la spécification GeoJson, pour qu'un objet JSON soit un objet de géométrie, il doit avoir deux champs appelés type et coordonnées, où la valeur du champ de type spécifie le type de géométrie et la valeur des coordonnées doit être un tableau dont les éléments définissent la forme géométrique.

Toutes sortes de géométries sont spécifiées en termes d'un ensemble de positions. Cependant, pour les cordes et les polygones, la forme géométrique réelle est formée par les lignes reliant leurs positions. La spécification GeoJson définit une ligne entre deux points comme la ligne droite qui relie les points du système de coordonnées cartésien (plat) dont les axes horizontaux et verticaux sont respectivement la longitude et la latitude.

Il existe diverses fonctions intégrées qui interprètent les objets JSON en tant que géométries et permettent de rechercher les rangées contenant des géométries qui répondent à certaines conditions.

Pour suivre les exemples, créez une table à partir de la console OCI à l'aide du mode d'entrée LDD avancée. L'instruction LDD est indiquée ci-dessous.

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

Voir Création d'une table singleton : Mode d'entrée LDD avancée pour les étapes de création d'une table avec un énoncé LDD.

Pour charger les données dans la table créée à partir de la console OCI, cliquez sur le nom de la table. Les détails de la table s'affichent. Cliquez sur Charger les données. Cliquez sur Sélectionner le fichier à charger et indiquez le fichier JSON à charger. Vous pouvez télécharger le fichier LDD et JSON pour les données GeoJSON ici

geo_intérieur

Détermine les géométries au sein d'une géométrie GeoJSON englobante.

boolean geo_inside(any*, any*)

• Le premier paramètre any* peut être n'importe quel objet géométrique.

• Le deuxième paramètre any* doit être un polygone.

La fonction détermine si la géométrie pointée par le premier paramètre est complètement contenue à l'intérieur du polygone pointé par le deuxième paramètre.

Si l'un des deux paramètres ne retourne pas un seul objet de géométrie valide, et s'il peut être détecté lors de la compilation, la fonction génère une erreur.

Le comportement d'exécution est le suivant :

• Retourne Faux si un paramètre retourne 0 ou plus de 1 élément.

• Retourne NULL si un paramètre retourne NULL.

• Renvoie false si un paramètre (au moment de l'exécution) renvoie un élément qui n'est pas un objet géométrique valide.

• Renvoie false si le second paramètre renvoie un objet géométrique qui n'est pas un polygone.

• Si les deux paramètres renvoient chacun un objet géométrique unique et que la deuxième géométrie est un polygone.

  • Il retourne vrai si la première géométrie est complètement contenue à l'intérieur du second polygone, c'est-à-dire que tous ses points appartiennent à l'intérieur du polygone.

  • Sinon, il retourne false.

Note : L'intérieur d'un polygone est tous les points de la zone du polygone, à l'exception des points de l'anneau linéaire qui définissent la limite du polygone.

Exemple : Recherchez des parcs naturels dans le nord de la Californie.

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]
                ]]
             });

Explication :

• Interrogez la table PointsOfInterest pour filtrer les rangées du parc naturel.

• Vous spécifiez un polygone comme deuxième paramètre de la fonction geo_inside.

• Les coordonnées du polygone que vous spécifiez correspondent aux coordonnées de la partie nord de l'État de Californie aux États-Unis.

• La fonction geo_inside retourne uniquement les rangées lorsque l'emplacement du parc naturel est entièrement contenu dans les points d'emplacement spécifiés.

Résultat :

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

geo_intersect;

Détermine les géométries qui se croisent avec une géométrie GeoJSON.

boolean geo_intersect(any*, any*)

Le premier et le second paramètres any* peuvent être n'importe quel objet géométrique.

La fonction détermine si deux géométries spécifiées en tant que paramètres ont des points communs. Si l'un des deux paramètres ne retourne pas un seul objet de géométrie valide, et s'il peut être détecté lors de la compilation, la fonction génère une erreur.

Le comportement d'exécution est le suivant :

• Retourne Faux si un paramètre retourne 0 ou plus de 1 élément.

• Retourne NULL si un paramètre retourne NULL.

• Renvoie false si un paramètre (au moment de l'exécution) renvoie un élément qui n'est pas un objet géométrique valide.

Si les deux paramètres renvoient chacun un objet géométrique unique, la fonction renvoie true si les 2 géométries ont des points en commun; sinon, false.

Exemple : Le Texas envisage de réglementer l'accès à l'approvisionnement en eau souterraine. Un aquifère est une couche souterraine de roche perméable à l'eau, de fractures rocheuses ou de matériaux non consolidés. Le gouvernement veut imposer de nouvelles réglementations pour les endroits très proches d'un aquifère.

Les coordonnées de l'aquifère ont déjà été cartographiées. Vous voulez connaître tous les comtés de l'État du Texas qui se croisent avec cet aquifère afin que vous puissiez informer le gouvernement du comté pour chaque comté affecté de participer aux pourparlers pour la nouvelle réglementation.

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]
          ]
        ]
    }
);

Explication :

• L'interrogation ci-dessus extrait les emplacements qui se croisent avec l'emplacement de l'aquifère. C'est-à-dire si les coordonnées de localisation ont des points en commun avec l'emplacement de l'aquifère.

• Vous utilisez geo_intersect pour voir si les coordonnées de l'emplacement ont des points communs avec les coordonnées de l'aquifère spécifiées.

Résultat :

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

géo_distance

Détermine la distance entre deux objets géospatiaux.

double geo_distance(any*, any*)

Le premier et le second paramètres any* peuvent être n'importe quel objet géométrique.

La fonction retourne la distance géodésique entre les deux géométries d'entrée. La distance renvoyée est le minimum parmi les distances de n'importe quelle paire de points où le premier point appartient à la première géométrie et le deuxième point à la deuxième géométrie. Entre deux de ces points, leur distance est la longueur de la ligne géodésique qui relie les points.

Aperçu de la ligne géodésique

Une ligne géodésique entre 2 points est la ligne la plus courte qui peut être tracée entre les 2 points sur la surface ellipsoïdale de la terre. Pour une définition simplifiée, mais plus illustrative, supposons un instant que la surface de la terre est une sphère. Ensuite, la ligne géodésique entre deux points sur la terre est l'arc mineur entre les deux points sur le grand cercle correspondent aux points, c'est-à-dire le cercle qui est formé par l'intersection de la sphère et le plan défini par le centre de la terre et les deux points.

La figure suivante montre la différence entre les lignes géodésiques et droites entre Los Angeles et Londres.

Description de l'illustration geodetic-vs-straight-line.jpg

Si l'un des deux paramètres ne retourne pas un seul objet de géométrie valide, et s'il peut être détecté lors de la compilation, la fonction génère une erreur.

Le comportement d'exécution est le suivant :

• Retourne -1 si un paramètre retourne zéro ou plus de 1 élément.

• Retourne NULL si un paramètre retourne NULL.

• Retourne -1 si l'un des paramètres n'est pas un objet de géométrie.

Sinon, la fonction retourne la distance géodésique en mètres entre les 2 géométries d'entrée.

Note : Les résultats sont triés en ordre croissant de distance (en affichant d'abord la distance la plus courte).

Exemple : À quelle distance se trouve le restaurant le plus proche de l'emplacement indiqué?

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" ;

Explication :

• Interrogez la table PointsOfInterest pour filtrer les rangées du restaurant.

• Vous fournissez le bon point d'emplacement et déterminez la distance à l'aide de la fonction geo_distance.

Résultat :

{"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 distance entre l'emplacement actuel et le restaurant le plus proche est de 799 mètres.

geo_within_distance

Détermine les objets géospatiaux à proximité d'un point.

boolean geo_within_distance(any*, any*,double)

Le premier et le second paramètres any* peuvent être n'importe quel objet géométrique.

La fonction détermine si la première géométrie est à une distance de N mètres de la seconde géométrie.

Si l'un des deux paramètres ne retourne pas un seul objet de géométrie valide, et s'il peut être détecté lors de la compilation, la fonction génère une erreur.

Le comportement d'exécution est le suivant :

• Retourne Faux si un paramètre retourne 0 ou plus de 1 élément.

• Renvoie NULL si l'un des deux premiers paramètres renvoie NULL.

• Renvoie false si l'un des deux premiers paramètres renvoie un élément qui n'est pas un objet géométrique valide.

Enfin, si les deux paramètres renvoient chacun un seul objet géométrique, il renvoie true si la première géométrie est à une distance de N mètres de la deuxième géométrie, où N est le nombre renvoyé par le troisième paramètre; sinon false. La distance entre 2 géométries est définie comme le minimum parmi les distances de n'importe quelle paire de points où le premier point appartient à la première géométrie et le deuxième point à la deuxième géométrie. Si N est un nombre négatif, il est réglé à 0.

Exemple : Y a-t-il un hôtel de ville dans les 5 km suivants? Jusqu'où cela va-t-il?

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
);

Explication :

• Interrogez la table PointsOfInterest pour filtrer les rangées de l'hôtel de ville.

• Vous utilisez la fonction geo_within_distance pour filtrer l'hôtel de ville à moins de 5 km (5000 m) de l'emplacement indiqué.

• Vous pouvez également extraire la distance réelle entre votre emplacement et l'hôtel de ville à l'aide de la fonction geo_distance.

Résultat :

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

L'hôtel de ville est à 1736 m (1,73 km) de l'emplacement actuel.

geo_near;

Détermine les objets géospatiaux à proximité d'un point.

boolean geo_near(any*, any*, double)

Le premier et le second paramètres any* peuvent être n'importe quel objet géométrique.

La fonction détermine si la première géométrie est à une distance de N mètres de la seconde géométrie.

Si l'un des deux paramètres ne retourne pas un seul objet de géométrie valide, et s'il peut être détecté lors de la compilation, la fonction génère une erreur.

Le comportement d'exécution est le suivant :

• Retourne Faux si un paramètre retourne 0 ou plus de 1 élément.

• Renvoie NULL si l'un des deux premiers paramètres renvoie NULL.

• Renvoie false si l'un des deux premiers paramètres renvoie un élément qui n'est pas un objet géométrique valide.

Enfin, si les deux premiers paramètres renvoient chacun un seul objet géométrique, il retourne vrai si la première géométrie est à une distance de N mètres de la deuxième géométrie, où N est le nombre renvoyé par le troisième paramètre; sinon faux.

Note : geo_near est converti en interne en geo_within_distance plus un ordre (implicite) par la distance entre les deux géométries. Toutefois, si l'interrogation comporte déjà une commande (explicite), aucun tri par distance n'est effectué. La fonction geo_near ne peut apparaître que dans la clause WHERE, où il doit s'agir d'un prédicat de niveau supérieur, c'est-à-dire non imbriqué sous un opérateur OR ou NOT.

Exemple 1 : Y a-t-il un hôpital à moins de 3 km de l'emplacement indiqué?

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
);

Explication :

• Interrogez la table PointsOfInterest pour filtrer les rangées de l'hôpital.

• Vous utilisez la fonction geo_near pour filtrer les hôpitaux à moins de 3000 mètres de l'emplacement indiqué.

Résultat :

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

Exemple 2 : À quelle distance se trouve une station-service dans le prochain kilomètre de l'emplacement indiqué?

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
);

Explication :

• Interrogez la table PointsOfInterest pour filtrer les rangées de la station-service.

• Vous utilisez la fonction geo_near pour filtrer les stations-service à moins d'un kilomètre (1600 m) de l'emplacement indiqué.

• Vous pouvez également extraire la distance réelle entre votre emplacement et la station-service à l'aide de la fonction geo_distance.

Résultat :

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

La distance réelle à la station-service la plus proche dans le mile suivant est de 886m.

géométrie_is_géométrie

Valide un objet géospatial.

boolean geo_is_geometry(any*)

Le paramètre any* peut être n'importe quel objet géométrique.

La fonction détermine si l'entrée indiquée est un objet de géométrie valide.

• Retourne Faux si le paramètre retourne zéro ou plus de 1 élément.

• Retourne NULL si le paramètre retourne NULL.

• Retourne Vrai si l'entrée est un seul objet de géométrie valide. Sinon, faux.

Exemple : Déterminez si l'emplacement pointant vers l'hôtel de ville est un objet géométrique valide.

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

Explication : Vous utilisez la fonction geo_is_geometry pour déterminer si un emplacement donné est un objet géométrique valide ou non.

Résultat :

{ "city_hall" : true}

Rubriques connexes

• Créer une table Singleton : Mode d'entrée LDD avancé
• Chargement des rangées de table