This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
The SQL `VALUES` statement (in DuckDB) | |
DuckDB version: 0.10.2 | |
Bill Wallis, 2024-05-21 | |
*/ | |
select version(); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import multiprocessing | |
from pathlib import Path | |
from queue import Queue | |
from time import sleep | |
from typing import Callable | |
import pyarrow as pa | |
import pyarrow.parquet as pq | |
from tqdm import tqdm |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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), |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/usr/bin/env python3 | |
import argparse | |
import os | |
from pmtiles.reader import MmapSource, Reader, all_tiles | |
from pmtiles.writer import Writer | |
from pmtiles.tile import Compression | |
from pmtiles.tile import zxy_to_tileid | |
from tqdm import tqdm | |
def merge_pmtiles(input_dir: str, output_file: str) -> None: |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/usr/bin/env bash | |
set -euxo pipefail | |
if [ "$EUID" -ne 0 ]; then | |
echo "Please run as root" | |
exit 1 | |
fi | |
if ! command nvcc --version >/dev/null 2>&1; then |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
Data sources for National Park boundaries: | |
- England: https://environment.data.gov.uk/DefraDataDownload/?mapService=NE/NationalParksEngland&Mode=spatial | |
- Scotland: https://spatialdata.gov.scot/geonetwork/srv/eng/catalog.search#/home | |
- Wales: https://datamap.gov.wales/layers/inspire-nrw:NRW_NATIONAL_PARK | |
*/ | |
WITH | |
park_info AS( | |
-- Select required information about each National Park from a merged dataset |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Snap the points to their closest lines, found in the subquery below | |
SELECT | |
point_id, | |
line_id, | |
ST_LINE_INTERPOLATE_POINT(line_geom, | |
ST_Line_Locate_Point(line_geom, point_geom)) AS snapped_points --Create the snapped points | |
FROM | |
--Subquery to find the closest line to each point (within a pre-defined raidus) | |
( |
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create table hex_r8 as ( | |
with envelope as ( | |
select st_envelope(wkb_geometry) geom from quartiers_sociologiques | |
), h3_id as ( | |
select h3_polyfill(geom, 8) id from envelope | |
) | |
select id, st_setsrid(h3_h3index_to_geoboundary(id), 4326) geom from h3_id | |
); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT jslibs.h3.ST_GEOGPOINTFROMH3("u2ce02j") | |
SELECT jslibs.h3.ST_H3_BOUNDARY(jslibs.h3.ST_H3(ST_GEOGPOINT(13.377534960188237, 49.747300576525554), 11)) | |
SELECT jslibs.h3.h3GetResolution("u2ce02j"); | |
# lat/lon to hexagon | |
SELECT jslibs.h3.ST_H3(ST_GEOGPOINT(statistics.coordinate.longitude, statistics.coordinate.latitude), 10) AS dwell_hex_id, | |
WITH polygon AS ( |
NewerOlder