[Tile-serving] [openstreetmap/osm2pgsql] Load vehicle charging stations and their associated sockets (Discussion #1933)

[Isaac Boates]

This flex recipe loads points with `amenity=charging_station` into a table called `charging_station`, and loads any associated information about their sockets into another table called `socket`. The tables can be joined via `node_id`

It came from the OSM hackathon on 26.02.2023 at Geofabrik.

Sorry, but the Lua script uses the older `add_row` commands instead of `insert()`, because my version of osm2pgsql is currently out of date.

It works best if you pre-filter the .pbf file with osmium:

```sh
osmium tags-filter /path/to/your.pbf n/amenity=charging_station /path/to/your/filtered.pbf
```

```lua
local srid = 4326

local tables = {}

tables.charging_station = osm2pgsql.define_node_table('charging_station', {
    { column = 'brand', type = 'text' },
    { column = 'operator', type = 'text' },
    { column = 'network', type = 'text' },
    { column = 'capacity', type = 'text' },
    { column = 'ref', type = 'text' },
    { column = 'voltage', type = 'text' },
    { column = 'socket', type = 'text' },
    { column = 'authentication', type = 'text' },
    { column = 'fee', type = 'text' },
    { column = 'parking_fee', type = 'text' },
    { column = 'maxstay', type = 'text' },
    { column = 'opening_hours', type = 'text' },
    { column = 'payment', type = 'text' },
    { column = 'tags', type = 'jsonb' },
    { column = 'geom', type = 'point', projection = srid }
})

tables.socket = osm2pgsql.define_node_table('socket', {
    { column = 'content', type = 'text' },
    { column = 'type', type = 'text' },
    { column = 'number', type = 'text' },
    { column = 'current', type = 'text' },
    { column = 'output', type = 'text' },
    { column = 'voltage', type = 'text' },
    { column = 'geom', type = 'point', projection = srid }
})

-- tables.socket_type = osm2pgsql.define_node_table('socket_type', {
--     { column = 'type', type = 'text' },
--     { column = 'geom', type = 'point', projection = srid }
-- })

function has_value (tab, val)
    for index, value in ipairs(tab) do
        if value == val then
            return true
        end
    end
    return false
end


function osm2pgsql.process_node(object)

    if not object.tags.amenity or object.tags.amenity ~= 'charging_station' then
        return
    end

    tables.charging_station:add_row({
        brand = object.tags.brand,
        operator = object.tags.operator,
        network = object.tags.network,
        capacity = object.tags.capacity,
        ref = object.tags.ref,
        voltage = object.tags.voltage,
        socket = object.tags.socket,
        authentication = object.tags.authentication,
        fee = object.tags.fee,
        parking_fee = object.tags.parking_fee,
        maxstay = object.tags.maxstay,
        opening_hours = object.tags.opening_hours,
        payment = object.tags.payment,
        tags = object.tags
    })

    content = nil
    type = nil
    number = nil
    current = nil
    output = nil
    voltage = nil

    socket_types = {}
    i = 0

    -- look for socket types
    for k, v in pairs(object.tags) do
        if k ~= "socket" then
            if osm2pgsql.has_prefix(k, "socket") then
                socket_type = string.match(k, "socket:(%w+)")
                if not has_value(socket_types, socket_type) then
                    -- socket:type2
                    socket_types[i] = socket_type
--                     tables.socket_type:add_row({
--                         type = socket_type
--                     })
                    i = i + 1
                end
            end
        end
    end

    content = object.tags.socket

    -- look for any socket properties
    for k, socket_type in pairs(socket_types) do

        number = object.tags["socket:"..socket_type]
        current = object.tags["socket:"..socket_type..":current"]
        output = object.tags["socket:"..socket_type..":output"]
        voltage = object.tags["socket:"..socket_type..":voltage"]
        tables.socket:add_row({
            content = content,
            type = socket_type,
            number = number,
            current = current,
            output = output,
            voltage = voltage
        })

    end

end

```

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

Message ID: <openstreetmap/osm2pgsql/repo-discussions/1933 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20230226/8e9d1037/attachment.htm>