Utilizzo delle funzioni dati GeoJSON

La specifica GeoJson definisce la struttura e il contenuto degli oggetti JSON che dovrebbero rappresentare forme geografiche sulla terra (chiamate geometrie). Secondo la specifica GeoJson, affinché un oggetto JSON sia un oggetto geometrico, deve disporre di due campi denominati tipo e coordinate, in cui il valore del campo tipo specifica il tipo di geometria e il valore delle coordinate deve essere un array i cui elementi definiscono la forma geometrica.

Tutti i tipi di geometrie sono specificati in termini di un insieme di posizioni. Tuttavia, per le stringhe di linea e i poligoni, la forma geometrica effettiva è formata dalle linee che collegano le loro posizioni. La specifica GeoJson definisce una linea tra due punti come la linea retta che collega i punti nel sistema di coordinate cartesiane (piatto) i cui assi orizzontale e verticale sono rispettivamente la longitudine e la latitudine.

Esistono varie funzioni incorporate che interpretano gli oggetti JSON come geometrie e consentono di cercare le righe contenenti geometrie che soddisfano determinate condizioni.

• geo_inside
• geo_intersect
• geo_distance
• geo_within_distance
• geo_near
• geo_is_geometry

Per seguire gli esempi, creare una tabella dalla console OCI utilizzando la modalità di input DDL avanzata. Di seguito è riportata l'istruzione DDL.

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

Per la procedura di creazione di una tabella con un'istruzione DDL, vedere Creazione di una tabella Singleton: modalità di input DDL avanzata.

Per caricare i dati nella tabella creata dalla console OCI, fare clic sul nome della tabella. Vengono visualizzati i dettagli della tabella. Fare clic su Carica dati. Fare clic su Seleziona file da caricare e fornire il file JSON da caricare. È possibile scaricare il file DDL e JSON per i dati GeoJSON qui.

geo_inside

Determina le geometrie all'interno di una geometria GeoJSON di delimitazione.

boolean geo_inside(any*, any*)

• Il primo parametro any* può essere qualsiasi oggetto geometrico.
• Il secondo parametro any* deve essere un poligono.

La funzione determina se la geometria puntata dal primo parametro è completamente contenuta all'interno del poligono puntato dal secondo parametro.

Se uno qualsiasi dei due parametri non restituisce un singolo oggetto geometria valido e se può essere rilevato in fase di compilazione, la funzione genera un errore.

Di seguito viene descritto il funzionamento del runtime.

• Restituisce false se un parametro restituisce 0 o più di 1 elemento.
• Restituisce NULL se un parametro qualsiasi restituisce NULL.
• Restituisce false se un parametro (in fase di esecuzione) restituisce un elemento che non è un oggetto geometria valido.
• Restituisce false se il secondo parametro restituisce un oggetto geometria diverso da un poligono.
• Se entrambi i parametri restituiscono un singolo oggetto di geometria e la seconda geometria è un poligono.
  • Restituisce vero se la prima geometria è completamente contenuta all'interno del secondo poligono, cioè tutti i suoi punti appartengono all'interno del poligono.
  • Altrimenti restituisce falso.

Nota

L'interno di un poligono è costituito da tutti i punti dell'area del poligono, ad eccezione dei punti dell'anello lineare che definiscono il limite del poligono.

Esempio: cercare parchi naturali nella California settentrionale.

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

Spiegazione:

• Eseguire una query sulla tabella PointsOfInterest per filtrare le righe per il parco naturale.
• È possibile specificare un poligono come secondo parametro della funzione geo_inside.
• Le coordinate del poligono specificato corrispondono alle coordinate della parte settentrionale dello stato della California negli Stati Uniti.
• La funzione geo_inside restituisce righe solo quando la posizione del parco naturale è completamente contenuta all'interno dei punti di posizione specificati.

Risultato:

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

geo_intersect

Determina le geometrie che si intersecano con una geometria GeoJSON.

boolean geo_intersect(any*, any*)

Il primo e il secondo parametro any* possono essere qualsiasi oggetto geometrico.

La funzione determina se due geometrie specificate come parametri hanno punti in comune. Se uno qualsiasi dei due parametri non restituisce un singolo oggetto geometria valido e se può essere rilevato in fase di compilazione, la funzione genera un errore.

Di seguito viene descritto il funzionamento del runtime.

• Restituisce false se un parametro restituisce 0 o più di 1 elemento.
• Restituisce NULL se un parametro qualsiasi restituisce NULL.
• Restituisce false se un parametro (in fase di esecuzione) restituisce un elemento che non è un oggetto geometria valido.

Se entrambi i parametri restituiscono un singolo oggetto geometrico, la funzione restituisce true se le geometrie 2 hanno punti in comune; altrimenti false.

Esempio: Texas sta prendendo in considerazione la possibilità di regolare l'accesso all'approvvigionamento idrico sotterraneo. Una falda acquifera è uno strato sotterraneo di roccia permeabile all'acqua, fratture rocciose o materiali non consolidati. Il governo vuole imporre nuove regole per i luoghi che sono molto vicini a una falda acquifera.

Le coordinate della falda acquifera sono già state mappate. Vuoi conoscere tutte le contee dello stato del Texas che si intersecano con quella falda acquifera in modo da poter notificare al governo della contea per ogni contea interessata di partecipare a colloqui per i nuovi regolamenti.

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

Spiegazione:

• La query precedente recupera le posizioni che si intersecano con la posizione della falda acquifera. Cioè se le coordinate della posizione hanno punti in comune con la posizione della falda acquifera.
• È possibile utilizzare geo_intersect per verificare se le coordinate della posizione hanno punti in comune con le coordinate della falda acquifera specificate.

Risultato:

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

geo_distance

Determina la distanza tra due oggetti geospaziali.

double geo_distance(any*, any*)

Il primo e il secondo parametro any* possono essere qualsiasi oggetto geometrico.

La funzione restituisce la distanza geodetica tra le due geometrie di input. La distanza restituita è il minimo tra le distanze di qualsiasi coppia di punti in cui il primo punto appartiene alla prima geometria e il secondo punto alla seconda geometria. Tra due di questi punti, la loro distanza è la lunghezza della linea geodetica che collega i punti.

Panoramica della linea geodetica

Una linea geodetica tra 2 punti è la linea più breve che può essere tracciata tra i 2 punti sulla superficie ellissoidale della terra. Per una definizione semplificata, ma più illustrativa, assumere per un momento che la superficie della terra è una sfera. Quindi, la linea geodetica tra due punti sulla terra è l'arco minore tra i due punti del grande cerchio corrispondente ai punti, cioè il cerchio che si forma dall'intersezione della sfera e del piano definito dal centro della terra e dai due punti.

La figura seguente mostra la differenza tra le linee geodetiche e rette tra Los Angeles e Londra.

Descrizione dell'immagine geodetic-vs-straight-line.jpg

Se uno qualsiasi dei due parametri non restituisce un singolo oggetto geometria valido e se può essere rilevato in fase di compilazione, la funzione genera un errore.

Di seguito viene descritto il funzionamento del runtime.

• Restituisce -1 se un parametro qualsiasi restituisce zero o più di 1 elemento.
• Restituisce NULL se un parametro qualsiasi restituisce NULL.
• Restituisce -1 se uno qualsiasi dei parametri non è un oggetto geometria.

In caso contrario, la funzione restituisce la distanza geodetica in metri tra le 2 geometrie di input.

Nota

I risultati sono ordinati in ordine crescente per distanza (visualizzando prima la distanza più breve).

Esempio: quanto dista il ristorante più vicino dalla posizione specificata?

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

Spiegazione:

• Eseguire una query sulla tabella PointsOfInterest per filtrare le righe per il ristorante.
• Fornire il punto di posizione corretto e determinare la distanza utilizzando la funzione geo_distance.

Risultato:

{"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 distanza tra la posizione attuale e il ristorante più vicino è di 799 metri.

geo_within_distance

Determina gli oggetti geospaziali in prossimità di un punto.

boolean geo_within_distance(any*, any*,double)

Il primo e il secondo parametro any* possono essere qualsiasi oggetto geometrico.

La funzione determina se la prima geometria si trova a una distanza di N metri dalla seconda geometria.

Se uno qualsiasi dei due parametri non restituisce un singolo oggetto geometria valido e se può essere rilevato in fase di compilazione, la funzione genera un errore.

Di seguito viene descritto il funzionamento del runtime.

• Restituisce false se un parametro restituisce 0 o più di 1 elemento.
• Restituisce NULL se uno dei primi due parametri restituisce NULL.
• Restituisce false se uno dei primi due parametri restituisce un elemento che non è un oggetto di geometria valido.

Infine, se entrambi i parametri restituiscono un singolo oggetto geometrico ciascuno, restituisce true se la prima geometria si trova a una distanza di N metri dalla seconda geometria, dove N è il numero restituito dal terzo parametro; altrimenti false. La distanza tra 2 geometrie è definita come la distanza minima tra le distanze di qualsiasi coppia di punti in cui il primo punto appartiene alla prima geometria e il secondo punto alla seconda geometria. Se N è un numero negativo, viene impostato su 0.

Esempio: il municipio si trova nei prossimi 5 km? Quanto dista?

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

Spiegazione:

• Eseguire una query sulla tabella PointsOfInterest per filtrare le righe per il city hall.
• La funzione geo_within_distance consente di filtrare il municipio entro 5 km (5000m) dalla posizione specificata.
• È inoltre possibile recuperare la distanza effettiva tra la posizione e il municipio utilizzando la funzione geo_distance.

Risultato:

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

Il municipio si trova a 1736 m (1,73 km) dalla posizione attuale.

geo_near

Determina gli oggetti geospaziali in prossimità di un punto.

boolean geo_near(any*, any*, double)

Il primo e il secondo parametro any* possono essere qualsiasi oggetto geometrico.

La funzione determina se la prima geometria si trova a una distanza di N metri dalla seconda geometria.

Se uno qualsiasi dei due parametri non restituisce un singolo oggetto geometria valido e se può essere rilevato in fase di compilazione, la funzione genera un errore.

Di seguito viene descritto il funzionamento del runtime.

• Restituisce false se un parametro restituisce 0 o più di 1 elemento.
• Restituisce NULL se uno dei primi due parametri restituisce NULL.
• Restituisce false se uno dei primi due parametri restituisce un elemento che non è un oggetto di geometria valido.

Infine, se entrambi i primi due parametri restituiscono un singolo oggetto geometrico ciascuno, restituisce true se la prima geometria si trova a una distanza di N metri dalla seconda geometria, dove N è il numero restituito dal terzo parametro; altrimenti false.

Nota

geo_near viene convertito internamente in geo_within_distance più un ordine (implicito) in base alla distanza tra le due geometrie. Tuttavia, se la query dispone già di un ordine esplicito, non viene eseguito alcun ordine in base alla distanza. La funzione geo_near può essere visualizzata solo nella clausola WHERE, dove deve essere un predicato di livello superiore, ovvero non nidificato sotto un operatore OR o NOT.

Esempio 1: un ospedale si trova a 3 km dalla posizione specificata?

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

Spiegazione:

• Eseguire una query sulla tabella PointsOfInterest per filtrare le righe per hospital.
• Utilizzare la funzione geo_near per filtrare gli ospedali all'interno di 3000m della posizione specificata.

Risultato:

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

Esempio 2: quanto dista una stazione di servizio entro il successivo miglio dalla posizione specificata?

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

Spiegazione:

• Eseguire una query sulla tabella PointsOfInterest per filtrare le righe per la stazione di servizio.
• La funzione geo_near consente di filtrare le stazioni di servizio entro un miglio (1600m) dalla posizione specificata.
• È inoltre possibile recuperare la distanza effettiva tra la posizione e la stazione di servizio utilizzando la funzione geo_distance.

Risultato:

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

La distanza effettiva dalla stazione di servizio più vicina entro il miglio successivo è 886m.

geo_is_geometry

Convalida un oggetto geospaziale.

boolean geo_is_geometry(any*)

Il parametro any* può essere qualsiasi oggetto geometrico.

La funzione determina se l'input specificato è un oggetto geometria valido.

• Restituisce false se il parametro restituisce zero o più di 1 elemento.
• Restituisce NULL se il parametro restituisce NULL.
• Restituisce true se l'input è un singolo oggetto geometria valido. Altrimenti, è falso.

Esempio: determinare se la posizione che punta al city hall è un oggetto geometrico valido.

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

Spiegazione: utilizzare la funzione geo_is_geometry per determinare se una determinata posizione è un oggetto geometrico valido o meno.

Risultato:

{ "city_hall" : true}

---

Titolo e informazioni sul copyright

Copyright ©2024,2024,

Oracle e/o relative consociate.