osm2pgsql is taking forever to process with lua config

Question

My osm2pgsql script is taking forever to load data into my database and I don't know why.

The reference lua scripts which perform similar computations take 1/10th the same amount of time. I know my personal computer won't blaze through the data but its taking hours to process something that takes 10-15min with the normal script.

For reference, I am running a intel i7-8000 series laptop, 8gb ram, and a quite fast ssd.

The script I am running is:

osm2pgsql -c -O flex --style=lua_config/custom.lua --slim --drop -C 2000 --database=test /osm_data/berlin-latest.osm.pbf

the berlin-latest.pbf file is 74.7mb so the -C value is even higher than suggested by osm2pgsql documentation.

The Lua config file is based on the route-relations.lua example in the github page (route-relations lua file link) and is:

 -- Set Standard Data info vals
local schema_name = 'berlin'
local srid = 25832 -- european srid
local tables = {}

------------------------------------------------------------------------------------
-- Create Tables for Data

tables.nodes = osm2pgsql.define_node_table('nodes', {
    { column = 'tags', type = 'jsonb' },
    { column = 'geom', type = 'point', projection = srid, not_null = true },
}, { schema = schema_name })

tables.ways = osm2pgsql.define_way_table('ways', {
    { column = 'tags', type = 'jsonb' },
    { column = 'rel_refs', type = 'text' },
    { column = 'rel_ids',  sql_type = 'int8[]' },
    { column = 'geom', type = 'linestring', projection = srid, not_null = true },
    { column = 'dist', type = 'real'},
    { column = 'source', type = 'int' , create_only = true },
    { column = 'target', type = 'int', create_only = true },
}, { schema = schema_name })

tables.routes = osm2pgsql.define_relation_table('routes', {
    { column = 'name', type = 'text' },
    { column = 'tags', type = 'jsonb' },
    { column = 'nodes', sql_type = 'int8[]' },
}, { schema = schema_name })

---------------------------------------------------------------------------------
-- Create sorting functions

function clean_tags(tags)
    tags.odbl = nil
    tags.created_by = nil
    tags.source = nil
    tags['source:ref'] = nil

    return next(tags) == nil
end

-- connect relations to member ways
local w2r = {}

function osm2pgsql.process_node(object)
    if ((object.tags.amenity == 'bar' or object.tags.amenity == 'cafe') or object.tags.shop or object.tags.public_transport == 'stop') then
        tables.nodes:insert({
            tags = object.tags,
            geom = object:as_point()
        })
    end
end

function osm2pgsql.process_way(object)
    if not ((object.tags.railway == 'subway' or object.tags.railway == 'tram') or object.tags.highway) then
        return
    end
    if clean_tags(object.tags) then
        return
    end
    local geom = object:as_linestring()
    local row = {
        tags = object.tags,
        geom = geom,
        dist = geom:transform(srid):length(),
    }
    local d = w2r[object.id]
    if d then
        local refs = {}
        local ids = {}
        for rel_id, rel_ref in pairs(d) do
            refs[#refs + 1] = rel_ref
            ids[#ids + 1] = rel_id
        end
        table.sort(refs)
        table.sort(ids)
        row.rel_refs = table.concat(refs, ',')
        row.rel_ids = '{' .. table.concat(ids, ',') .. '}'
    end
    tables.ways:insert(row)
end

function osm2pgsql.select_relation_members(relation)
    -- Only interested in relations with type=route, route=road and a ref
    if relation.tags.type == 'route' and (relation.tags.route == 'subway' or relation.tags.route == 'tram' or relation.tags.route == 'bus') then
        return { ways = osm2pgsql.way_member_ids(relation) }
    end
end

function osm2pgsql.process_relation(object)
    local relation_type = object:grab_tag('type')
    local relation_name = object:grab_tag('name')
    if clean_tags(object.tags) then
        return
    end
    if relation_type == 'route' and (object.tags.route == 'subway' or object.tags.route == 'tram' or object.tags.route == 'bus') then
        tables.routes:insert({
            name = relation_name,
            tags = object.tags,
        })
        for _, member in ipairs(object.members) do
            if member.type == 'w' then
                if not w2r[member.ref] then
                    w2r[member.ref] = {}
                end
                w2r[member.ref][object.id] = object.tags.ref
            end
        end
    end
end

This file or the earlier version without the reprocessing of nodes to add relation info was taking a minimum of 2 hrs to load while route-relations takes 16 seconds. I know I have quite a few more nodes/ways/relations to process but the diffence in speed seems a little extra.

For example, the script took 12 hrs.

Answer 1

Turns out it was the complexity of converting all the nodes/ways to the new srid. Instead, I maintained the 3857 srid for all nodes/ways and used the Lua config file to transform the ways to srid 25832 for calculating length only.

Now instead of:

We process the input file in 37 seconds.