Inicio rápido de H3 (Databricks SQL)

Artículo
04/18/2024

El inicio rápido para las funciones geoespaciales de H3 en esta página muestra los puntos siguientes:

Cómo cargar conjuntos de datos de geolocalización en Unity Catalog.
Cómo convertir columnas de latitud y longitud en columnas de celda de H3.
Cómo convertir columnas WKT de polígono o multipolígono de código postal en columnas de celda de H3.
Cómo consultar el análisis de recogida y entrega desde el aeropuerto de LaGuardia al distrito financiero de Manhattan.
Cómo representar recuentos agregados de H3 en un mapa.

Cuadernos y consultas de ejemplo

Preparación de los datos de Unity Catalog

En este cuaderno se hace lo siguiente:

Configurar el conjunto de datos de taxi público desde el sistema de archivos de Databricks.
Configurar el conjunto de datos Código postal NYC.

Preparación de los datos de Unity Catalog

Obtener el cuaderno

Consultas SQL de Databricks con Databricks Runtime 11.3 LTS y versiones posteriores

Consulta 1: compruebe que se han configurado los datos base. Consulte Cuaderno.

use catalog geospatial_docs;
use database nyc_taxi;
show tables;
-- Verify initial data is setup (see instructions in setup notebook)
-- select format_number(count(*),0) as count from yellow_trip;
-- select * from nyc_zipcode;

Consulta 2: código postal de NYC de H3: aplique h3_polyfillash3 en resolución 12.

use catalog geospatial_docs;
use database nyc_taxi;
-- drop table if exists nyc_zipcode_h3_12;
create table if not exists nyc_zipcode_h3_12 as (
  select
    explode(h3_polyfillash3(geom_wkt, 12)) as cell,
    zipcode,
    po_name,
    county
  from
    nyc_zipcode
);
-- optional: zorder by `cell`
optimize nyc_zipcode_h3_12 zorder by (cell);
select
  *
from
  nyc_zipcode_h3_12;

Consulta 3: trayectos de taxi de H3: aplique h3_longlatash3 en resolución 12.

use catalog geospatial_docs;
use database nyc_taxi;
-- drop table if exists yellow_trip_h3_12;
create table if not exists yellow_trip_h3_12 as (
  select
    h3_longlatash3(pickup_longitude, pickup_latitude, 12) as pickup_cell,
    h3_longlatash3(dropoff_longitude, dropoff_latitude, 12) as dropoff_cell,
    *
  except
    (
      rate_code_id,
      store_and_fwd_flag
    )
  from
    yellow_trip
);
-- optional: zorder by `pickup_cell`
-- optimize yellow_trip_h3_12 zorder by (pickup_cell);
select
  *
from
  yellow_trip_h3_12
 where pickup_cell is not null;

Consulta 4: recogidas de LGA de H3: recogidas de 25M desde LaGuardia (LGA)

use catalog geospatial_docs;
use database nyc_taxi;
create
or replace view lga_pickup_h3_12 as (
  select
    t.*
  except(cell),
    s.*
  from
    yellow_trip_h3_12 as s
    inner join nyc_zipcode_h3_12 as t on s.pickup_cell = t.cell
  where
    t.zipcode = '11371'
);
select
  format_number(count(*), 0) as count
from
  lga_pickup_h3_12;
-- select
  --   *
  -- from
  --   lga_pickup_h3_12;

Consulta 5: trayectos al distrito financiero de H3: 34M trayectos en total al distrito financiero

use catalog geospatial_docs;
use database nyc_taxi;
create
or replace view fd_dropoff_h3_12 as (
  select
    t.*
  except(cell),
    s.*
  from
    yellow_trip_h3_12 as s
    inner join nyc_zipcode_h3_12 as t on s.dropoff_cell = t.cell
  where
    t.zipcode in ('10004', '10005', '10006', '10007', '10038')
);
select
  format_number(count(*), 0) as count
from
  fd_dropoff_h3_12;
-- select * from fd_dropoff_h3_12;

Consulta 6: LGA-FD de H3: 827K trayectos al FD con recogida en LGA

use catalog geospatial_docs;
use database nyc_taxi;
create
or replace view lga_fd_dropoff_h3_12 as (
  select
    *
  from
    fd_dropoff_h3_12
  where
    pickup_cell in (
      select
        distinct pickup_cell
      from
        lga_pickup_h3_12
    )
);
select
  format_number(count(*), 0) as count
from
  lga_fd_dropoff_h3_12;
-- select * from lga_fd_dropoff_h3_12;

Consulta 7: LGA-FD por código postal: recuento de trayectos al FD por código postal + gráfico de barras

use catalog geospatial_docs;
use database nyc_taxi;
select
  zipcode,
  count(*) as count
from
  lga_fd_dropoff_h3_12
group by
  zipcode
order by
  zipcode;

Consulta 8: LGA-FD por H3: recuento de trayectos al FD por celda de H3 + visualización de marcador de mapa

use catalog geospatial_docs;
use database nyc_taxi;
select
  zipcode,
  dropoff_cell,
  h3_centerasgeojson(dropoff_cell) :coordinates [0] as dropoff_centroid_x,
  h3_centerasgeojson(dropoff_cell) :coordinates [1] as dropoff_centroid_y,
  format_number(count(*), 0) as count_disp,
  count(*) as `count`
from
  lga_fd_dropoff_h3_12
group by
  zipcode,
  dropoff_cell
order by
  zipcode,
  `count` DESC;

Recuentos de LGA-FD por H3 1