Error in reprocessing of ways in relation, if osmc_symbols-tag of the relation contains the word 'backslash'

[MathiasGroebe]

Output

2024-02-01 10:11:59 osm2pgsql version 1.10.0 (1.10.0-26-g229d832b)
2024-02-01 10:12:00 Database version: 13.5 (Debian 13.5-1.pgdg110+1)
2024-02-01 10:12:00 PostGIS version: 3.1
2024-02-01 10:12:00 Storing properties to table '"public"."osm2pgsql_properties"'.
osm2pgsql version: 1.10.0
2024-02-01 10:12:08 Reading input files done in 8s.
2024-02-01 10:12:08 Processed 21789924 nodes in 0s - 21790k/s
2024-02-01 10:12:08 Processed 3460747 ways in 7s - 494k/s
2024-02-01 10:12:08 Processed 49537 relations in 1s - 50k/s
2024-02-01 10:12:08 Reprocess marked ways (stage 2)...
2024-02-01 10:12:08 Creating id index on table 'traffic'...
2024-02-01 10:12:09 Creating id indexes took 0s
2024-02-01 10:12:09 There are 77950 ways to reprocess...
2024-02-01 10:12:10 ERROR: DB copy thread failed: Ending COPY mode for 'traffic' failed: ERROR: malformed array literal: "{green:white:green:}"
DETAIL: Unexpected end of input.
CONTEXT: COPY traffic, line 25130, column osmc_symbols: "{green:white:green:}"

What version of osm2pgsql are you using?

osm2pgsql version: 1.10.0

What operating system and PostgreSQL/PostGIS version are you using?

Ubuntu 2022.04

Tell us something about your system

16 RAM, 16 CPUs, laptop

What did you do exactly?

Importing Hiking relation using the following configuration (minimal example)

print('osm2pgsql version: ' .. osm2pgsql.version)

-- Variables
local tables = {}
local import_schema = 'osm' -- Defines the import schema
local epsg_code = 32633 -- Defines the projection
local w2r = {}

-- Table defenitions

tables.traffic = osm2pgsql.define_table({
    name = 'traffic',
    schema = import_schema,
    ids = {
        type = 'way',
        id_column = 'way_id'
    },
    columns = {{
        column = 'fid',
        sql_type = 'serial',
        create_only = true
    }, {
        column = 'highway',
        type = 'text'
    }, {
        column = 'name',
        type = 'text'
    }, {
        column = 'osmc_symbols', 
        sql_type = 'text[]' 
    },{ 
        column = 'rel_ids', 
        sql_type = 'int8[]' 
    }, {
        column = 'geom',
        type = 'linestring',
        projection = epsg_code
    }},
    indexes = {{
        column = 'geom',
        method = 'gist'
    }}
})


function osm2pgsql.select_relation_members(relation)
    -- Only interested in relations with type=route
    if relation.tags.type == 'route' and relation.tags.route == 'hiking' then
        return { ways = osm2pgsql.way_member_ids(relation) }
    end
end


-- Function which fill the tables

function osm2pgsql.process_node(object)

end

function osm2pgsql.process_way(object)

    if object.tags.highway or object.tags.railway then
        row = {
            name = object.tags.name,
            highway = object.tags.highway,
            geom = object:as_multilinestring()
        }

        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.osmc_symbols = '{'.. table.concat(refs, ',') .. '}'
            row.rel_ids = '{' .. table.concat(ids, ',') .. '}'
        end

        tables.traffic:insert(row)
    end

end

function osm2pgsql.process_relation(object)

    local type = object:grab_tag('type')

    if type == 'route' and object.tags.route == 'hiking' then
        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['osmc:symbol']
            end
        end
    end

end

What did you do to try analyzing the problem?

Importing an OSM-PBF-files fails, OSM-XML is a workaround. Error always happens, if an osmc_symbols: "green:white:green:backslash" is present in the OSM-Data.

[pnorman]

What are the tags on the object?

If the same data in a PBF and XML gets different results, there's a bug.

The fact that you're getting an error is not a bug - you're supplying a string literal for a text[] value. There's nothing wrong with doing that and doing the conversion yourself - in fact you must do it because there isn't a built-in conversion but your code is responsible for getting the string literal right, including escaping.

[MathiasGroebe]

I found more out about it. But first a correction of the output of osm2pgsql which got lost during copy and past:

My first workaround was:

1. Clip and convert the PBF to OSM for my bounding box with osmium
2. Import the OSM file with osm2pgsql

What was also working:

1. Clip to my bounding box and save as PBF
2. Import with osm2pgsql

So I was converting the whole file and searched for 'green:white:green:' and found this relation https://www.openstreetmap.org/relation/1830701#map=14/50.1851/12.2991. That is far away from my boundingbox:

osm2pgsql -O flex -S demo.lua -b 13.662356,50.792525,13.888777,50.89853 sachsen-latest.osm.pbf

The error happens, if I convert the complete file sachsen-latest.osm.pbf to OSM-XML.

[joto]

As @pnorman mentioned the problem is in your Lua script. You make no attempt to escape/format the value correctly. The reason why this fails here and not somewhere else is probably the backslash in the tag (which also needs to be corrected in the OSM data, but that's beside the point here). Because you are using sql_type you have to convert the data you have somehow into the correct format for PostgreSQL to understand. Osm2pgsql can not do this for you, because it doesn't interpret the sql_type at all but just gives the data "as is" to the database and hopes that the database can interpret it according to the type you defined in sql_type.

[pnorman]

It sounds like the PBF vs XML bug was from the areas being covered being different, not anything to do with osm2pgsql. In that case, there is no bug on the osm2pgsql side and you will need to fix your code.

[joto]

I have added some warning words to the manual about the use of sql_type. For most use cases using the json or jsonb type is probably a better solution, because osm2pgsql will do the conversion automatically for you.

[MathiasGroebe]

Thanks for your help and explanations!

Here is my adjusted configuration

print('osm2pgsql version: ' .. osm2pgsql.version)

-- Variables
local tables = {}
local import_schema = 'osm' -- Defines the import schema
local epsg_code = 32633 -- Defines the projection
local w2r = {}

-- Table defenitions

tables.traffic = osm2pgsql.define_table({
    name = 'traffic',
    schema = import_schema,
    ids = {
        type = 'way',
        id_column = 'way_id'
    },
    columns = {{
        column = 'fid',
        sql_type = 'serial',
        create_only = true
    }, {
        column = 'highway',
        type = 'text'
    }, {
        column = 'name',
        type = 'text'
    }, {
        column = 'osmc_symbols', 
        type = 'jsonb' 
    },{ 
        column = 'rel_ids', 
        sql_type = 'int8[]' 
    }, {
        column = 'geom',
        type = 'linestring',
        projection = epsg_code
    }},
    indexes = {{
        column = 'geom',
        method = 'gist'
    }}
})


function osm2pgsql.select_relation_members(relation)
    -- Only interested in relations with type=route
    if relation.tags.type == 'route' and relation.tags.route == 'hiking' then
        return { ways = osm2pgsql.way_member_ids(relation) }
    end
end


-- Function which fill the tables

function osm2pgsql.process_node(object)

end

function osm2pgsql.process_way(object)

    if object.tags.highway or object.tags.railway then
        row = {
            name = object.tags.name,
            highway = object.tags.highway,
            geom = object:as_multilinestring()
        }

        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.osmc_symbols = refs
            row.rel_ids = '{' .. table.concat(ids, ',') .. '}'
        end

        tables.traffic:insert(row)
    end

end

function osm2pgsql.process_relation(object)

    local type = object:grab_tag('type')

    if type == 'route' and object.tags.route == 'hiking' then
        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['osmc:symbol']
            end
        end
    end

end