-- sql/load.sql
-- GENERATED - DO NOT MODIFY. See README.lit
INSTALL spatial;
LOAD spatial;
SET geometry_always_xy = true;
INSTALL h3 FROM community;
LOAD h3;

CREATE TABLE data AS
WITH clean AS (
    SELECT
        id,
        parent_id,
        name,
        placetype,
        country,
        lon,
        lat,
        h3_latlng_to_cell(lat, lon, 4) as cell_id,
        modified,
        len(population) as pop_mag,
        ST_Point(lon, lat) AS point,
        substring(lower(name), 1, 3) as prefix,
        ROW_NUMBER() OVER (PARTITION BY name, placetype, country, cell_id ORDER BY pop_mag DESC, modified DESC) as rn
    FROM '%s'
    WHERE placetype NOT IN ['country', 'continent']
)
SELECT *
FROM clean
WHERE rn = 1;