Skip to content

Instantly share code, notes, and snippets.

@marklit
Last active May 19, 2024 22:43
Show Gist options
  • Save marklit/c9cad0f535a9c8300bca130e0222d520 to your computer and use it in GitHub Desktop.
Save marklit/c9cad0f535a9c8300bca130e0222d520 to your computer and use it in GitHub Desktop.
Pull H3s for Overture's Places Dataset for May 2024
COPY (
WITH a AS (
SELECT h3_cell_to_parent(h3_string_to_h3(SUBSTR(id, 0, 17)), 2) h3_2,
COUNT(*) num_recs
FROM read_parquet('s3://overturemaps-us-west-2/release/2024-05-16-beta.0/theme=places/type=place/*.parquet',
filename=true,
hive_partitioning=1)
GROUP BY 1
)
SELECT h3_cell_to_boundary_wkt(h3_2),
num_recs
FROM a
WHERE h3_cell_to_lng(h3_2) > -175
AND h3_cell_to_lng(h3_2) < 175
) TO 'places.2024-05-16.v2.csv';
@marklit
Copy link
Author

marklit commented May 16, 2024

┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││            HTTP Stats:            ││
││                                   ││
││           in: 686.7 MiB           ││
││            out: 0 bytes           ││
││              #HEAD: 5             ││
││             #GET: 379             ││
││              #PUT: 0              ││
││              #POST: 0             ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││         Total Time: 28.35s        ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
┌───────────────────────────┐
│      RESULT_COLLECTOR     │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             0             │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      EXPLAIN_ANALYZE      │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             0             │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│        COPY_TO_FILE       │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             1             │
│          (0.01s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│  h3_cell_to_boundary_wkt  │
│           (h3_2)          │
│          num_recs         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│            2720           │
│          (0.04s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│       HASH_GROUP_BY       │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             #0            │
│        count_star()       │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│            2720           │
│          (0.27s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│            h3_2           │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│          52575472         │
│          (4.95s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│      (h3_cell_to_lng      │
│(h3_cell_to_parent(h3_...  │
│(substr(id, 0, 17)), 2...  │
│      -175.0 AND 175.0)    │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│        EC: 10685223       │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│          52575472         │
│          (12.34s)         │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│       READ_PARQUET        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             id            │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│        EC: 53426115       │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│          52622149         │
│         (630.92s)         │
└───────────────────────────┘

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment