[Tile-serving] [openstreetmap/osm2pgsql] How can you get country boundaries with flex output? (Discussion #1934)

[Isaac Boates]

Thanks. I was able to get part of the way there, but I can't quite figure out how I can iterate over the members of the relation to check if they are ways, and then insert  them (or potentially chain them together so I can polygonize them later).

In the end I just inserted them as geometry collections and then wrote an SQL query to process the result into polygons. It's clunky but it works.

I was wondering if you have any tips on how I can better traverse the members of the relations. Because this was as far as I could make it on my own:

(PBF pre-filtered with "n/amenity=charging_station" and "boundary=administrative" in osmium)

flex script:

```lua
local srid = 4326

local tables = {}

tables.country_pre = osm2pgsql.define_way_table('country_pre', {
    { column = 'name', type = 'text' },
    { column = 'tags', type = 'jsonb' },
    { column = 'geom', type = 'geometrycollection', projection = srid }
})

function osm2pgsql.process_relation(object)

    if object:grab_tag('admin_level') == "2" then
    tables.country_pre:insert({
        name = object.tags["name:en"],
        tags = object.tags,
        geom = object:as_geometrycollection()
    })
    end

end
```

Postprocessing query:

```
drop table if exists country;
create table country as (

    with a as (
        select
            st_asewkt((st_dump(geom)).geom) as geom,
            st_geometrytype((st_dump(geom)).geom) as geom_type,
            name
        from
            country_pre
    ),

    b as (
        select
            ST_Node(st_collect(geom)) as geom,
            name
        from
            a
        where
            geom_type = 'ST_LineString'
        group by
            name
    )

    select
        ST_SimplifyPreserveTopology((ST_Dump(st_polygonize(geom))).geom, 0.00005) as geom,
        name
    from
        b
    group by
        name
);

drop table country_pre;

create index on country (geom) using gist;
```

-- 
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/discussions/1934#discussioncomment-5198474
You are receiving this because you are subscribed to this thread.

Message ID: <openstreetmap/osm2pgsql/repo-discussions/1934/comments/5198474 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20230303/9e4f1ed5/attachment.htm>