duckdb queries to ask questions of the Overture admins dataset

overture_admins_then_and_now.sql

/* In the initial implementation of the admins schema the "locality" feature type was 
allowed to have Point, Polygon, or MultiPolygon geometry. There was no "locality area." So back
in July, this is how you would get country borders. See Simon Willison's post: 
https://til.simonwillison.net/overture-maps/overture-maps-parquet. Note the geometry type checking.*/
   
COPY (
    SELECT
           type,
           subType,
           localityType,
           adminLevel,
           isoCountryCodeAlpha2,
           JSON(names) AS names,
           JSON(sources) AS sources,
           ST_GeomFromWkb(geometry) AS geometry
      FROM read_parquet('s3://overturemaps-us-west-2/release/2023-07-26-alpha.0/theme=admins/type=*/*', filename=true, hive_partitioning=1)
     WHERE adminLevel = 2
       AND ST_GeometryType(ST_GeomFromWkb(geometry)) IN ('POLYGON','MULTIPOLYGON')
) TO '/tmp/countries.geojson'
WITH (FORMAT GDAL, DRIVER 'GeoJSON');


/* In the current (Feb 2024) implementation of the admins schema we have "locality area" with polygon
and multipolygon geometries. "locality" is just the Point geometry, so you have to self-join to find
the borders. Countries are now assigned adminLevel = 1 */

CREATE VIEW admins_view AS (
    SELECT
        *
    FROM
        read_parquet('s3://overturemaps-us-west-2/release/2024-02-15-alpha.0/theme=admins/type=*/*', filename=true, hive_partitioning=1)
);
COPY (
    SELECT
            admins.id,
            admins.subType,
            admins.isoCountryCodeAlpha2,
            names.primary AS primary_name,
            sources[1].dataset AS primary_source,
            areas.areaId,
            ST_GeomFromWKB(areas.areaGeometry) as geometry
    FROM admins_view AS admins
    INNER JOIN (
        SELECT
            id as areaId,
            localityId,
            geometry AS areaGeometry
        FROM admins_view
    ) AS areas ON areas.localityId == admins.id
    WHERE admins.adminLevel = 1
    LIMIT 10
) TO 'admins_sample.geojson'
WITH (FORMAT GDAL, DRIVER 'GeoJSON');


/* In April 2024, Overture will begin the transition from admins to divisions. The new divisions schema will
greatly simplify queries. See https://github.com/OvertureMaps/schema/discussions/117 for more information and 
discussion about this change. */

SELECT <whatever>
  FROM <wherever>
 WHERE theme = 'divisions'              
   AND type = 'division_area'
   AND subtype = 'country'