# Riskrapport: så tas adressdatabasen fram

Adressdatabasen (tabellerna core.addresses och core.address_streets) är en härledd databas
(Derivative Database) som bygger på OpenStreetMap och omfattas av Open Database License (ODbL) 1.0.
Den här filen beskriver metoden enligt ODbL avsnitt 4.6 b: urval, normalisering och
uppbyggnad, med den kod vi faktiskt kör.

Källa: OpenStreetMap, Geofabriks utdrag sweden-latest.osm.pbf
(https://download.geofabrik.de/europe/sweden.html).
Adressdata © OpenStreetMap-bidragsgivare, ODbL 1.0: https://www.openstreetmap.org/copyright
Kommunkod: SCB, Kommuner (CC0), kopplad med punkt i polygon.

## 1. Urval (DuckDB spatial, ST_ReadOSM)

Noder och ways med:
map_contains(tags, 'addr:housenumber') AND (map_contains(tags, 'addr:street') OR map_contains(tags, 'addr:place'))
Relationer räknas men används inte.

## 2. Kolumner

osm_type = osm_type
osm_id = osm_id
street = CASE WHEN contains(coalesce(tags['addr:street'], tags['addr:place']), chr(65533)) THEN NULL ELSE nullif(trim(coalesce(tags['addr:street'], tags['addr:place'])), '') END
number = unnest(list_filter(list_transform(string_split_regex(tags['addr:housenumber'], '[;,]'), lambda x: upper(regexp_replace(trim(x), '\s+', '', 'g'))), lambda x: x <> ''))
postcode = CASE WHEN regexp_full_match(replace(tags['addr:postcode'], ' ', ''), '[0-9]{5}') THEN replace(tags['addr:postcode'], ' ', '') END
city = nullif(trim(tags['addr:city']), '')

## 3. Från ways (linjer och ytor) till punkter

def osm_sql(con: duckdb.DuckDBPyConnection, root: Path, src: Source) -> str:
    """osm: entities matching the layer's `where` (over `tags`) as points. ST_ReadOSM returns raw
    nodes/ways/relations without geometry, so ways are rebuilt from their node refs in a second
    pass that keeps only the referenced nodes (semi join on the small ref list: flat memory).
    Closed ways -> a point on the ring's surface, open ways -> line centroid. Relations are
    counted and skipped. `columns` are expressions over `tags`, `osm_type` ('n' | 'w'), `osm_id`.
    """
    (lyr,) = src.layers
    read = f"ST_ReadOSM({qstr(str(resolve(root, lyr.path)))})"
    where = f" AND ({lyr.where})" if lyr.where else ""
    con.execute(
        "CREATE OR REPLACE TEMP TABLE osm_ent AS "
        f"SELECT kind::VARCHAR AS kind, id, tags, refs, lon, lat FROM {read} "
        f"WHERE kind IN ('node', 'way', 'relation'){where}"
    )
    counts = dict(con.execute("SELECT kind, count(*) FROM osm_ent GROUP BY 1").fetchall())
    log(f"{src.out}: matching OSM entities {counts}, relations skipped")
    con.execute(
        "CREATE OR REPLACE TEMP TABLE osm_refs AS SELECT id AS way_id, unnest(refs) AS ref, "
        "generate_subscripts(refs, 1) AS i FROM osm_ent WHERE kind = 'way'"
    )
    con.execute(
        "CREATE OR REPLACE TEMP TABLE osm_xy AS SELECT id, lon, lat "
        f"FROM {read} WHERE kind = 'node' AND id IN (SELECT ref FROM osm_refs)"
    )
    con.execute(
        """CREATE OR REPLACE TEMP TABLE osm_way_pt AS
        SELECT w.id, CASE
                 WHEN len(w.pts) = 1 THEN w.pts[1]
                 WHEN len(w.pts) >= 4 AND len(w.pts) = len(e.refs) AND e.refs[1] = e.refs[-1]
                   THEN coalesce(TRY(ST_PointOnSurface(ST_MakeValid(
                                   ST_MakePolygon(ST_MakeLine(w.pts))))),
                                 ST_Centroid(ST_MakeLine(w.pts)))
                 ELSE ST_Centroid(ST_MakeLine(w.pts)) END AS g
          FROM (SELECT r.way_id AS id, list(ST_Point(x.lon, x.lat) ORDER BY r.i) AS pts
                  FROM osm_refs r JOIN osm_xy x ON x.id = r.ref GROUP BY r.way_id) w
          JOIN osm_ent e ON e.id = w.id AND e.kind = 'way'"""
    )
    points = (
        "SELECT 'n' AS osm_type, id AS osm_id, tags, ST_Point(lon, lat) AS g "
        "FROM osm_ent WHERE kind = 'node' "
        "UNION ALL SELECT 'w', e.id, e.tags, p.g FROM osm_ent e JOIN osm_way_pt p "
        "ON p.id = e.id WHERE e.kind = 'way'"
    )
    geom = (
        "ST_SetCRS(ST_Transform(g, 'EPSG:4326', "
        f"{qstr(STAGE_CRS)}, always_xy := true), {qstr(STAGE_CRS)})"
    )
    cols = select_list(lyr.columns) if lyr.columns else "osm_type, osm_id"
    inner = f"SELECT {cols}, {qstr(lyr.label)} AS src_layer, {geom} AS geometry FROM ({points})"
    return f"SELECT * FROM ({inner}) WHERE geometry IS NOT NULL AND NOT ST_IsEmpty(geometry)"

## 4. Uppbyggnad av core.addresses (PostGIS)

{load} = de utvalda raderna ovan, {next} = den nya tabellen.

INSERT INTO {next} (id, source, street, number, postcode, postort, postort_src, kommunkod, geom)
WITH k AS (
  SELECT a.osm_type, a.osm_id, a.street, a.number, a.postcode, a.city, a.geom,
         c.kommunkod, c.kommunnamn
    FROM {load} a
    CROSS JOIN LATERAL (SELECT kommunkod, kommunnamn FROM core.scb_kommun_2025 c
                         WHERE ST_Intersects(c.geom, a.geom) LIMIT 1) c
   WHERE a.street IS NOT NULL AND a.number IS NOT NULL
), pc AS (
  SELECT postcode, mode() WITHIN GROUP (ORDER BY city) AS city
    FROM k WHERE postcode IS NOT NULL AND city IS NOT NULL GROUP BY postcode
), filled AS (
  SELECT k.*, coalesce(k.city, pc.city, k.kommunnamn) AS postort,
         CASE WHEN k.city IS NOT NULL THEN 'addr:city'
              WHEN pc.city IS NOT NULL THEN 'postcode' ELSE 'kommun' END AS postort_src
    FROM k LEFT JOIN pc USING (postcode)
), keyed AS (
  SELECT *, core.search_norm(street) || '|' || number || '|' || core.search_norm(postort) AS dkey
    FROM filled
), clustered AS (
  SELECT *, ST_ClusterDBSCAN(geom, 250, 1) OVER (PARTITION BY dkey) AS cl FROM keyed
), ranked AS (
  SELECT *, row_number() OVER (PARTITION BY dkey, cl ORDER BY osm_type = 'n' DESC, osm_id) AS rn
    FROM clustered
)
SELECT 'osm:' || osm_type || osm_id || '/' || number, 'osm', street, number, postcode, postort,
       postort_src, kommunkod, geom
  FROM ranked WHERE rn = 1

## 5. Uppbyggnad av core.address_streets

INSERT INTO {next} (street, postort, kommunkod, n)
SELECT street, postort, kommunkod, count(*) FROM {src} GROUP BY street, postort, kommunkod

## 6. Normalisering för sökning (core.search_norm)

CREATE OR REPLACE FUNCTION core.search_norm(t text) RETURNS text
LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT
RETURN nullif(btrim(regexp_replace(lower(public.unaccent('public.unaccent'::regdictionary, t)),
                                   '[^a-z0-9]+', ' ', 'g')), '');