Obsidian/Doktoritöö/001_artiklid/OSM/shell/lua/arhiiv/ardok_highways.lua

-- NOTE: Beautification tool not available. Original content preserved.

-- Määrame skeemi keskkonnamuutujast või vaikimisi 'public'
local schema = os.getenv("PGSCHEMA") or "public"
local tables = {}

-- Define the "countries" locator and get all country geometries from the
-- database. Use the import-countries.lua file to import them first, before
-- you run this.
local countries = osm2pgsql.define_locator({name = "countries"})
countries:add_from_db("SELECT code, ST_Subdivide(geom, 200) FROM " .. schema .. ".countries")

tables.highways =
    osm2pgsql.define_way_table(
    "ardok_highways",
    {
        -- Add a SERIAL column and tell osm2pgsql not to fill it (PostgreSQL will
        -- do that for us)
        {column = "id", sql_type = "serial", create_only = true},
        -- We always need a highway type, so we can declare the column as NOT NULL
        {column = "hwtype", type = "text", not_null = true},
        {column = "service", type = "text"},
        {column = "access", type = "text"},
        {column = "country", type = "text"},
        {column = "colour", type = "text"},
        {column = "name", type = "text"},
        {column = "tags", type = "jsonb"},
        {column = "surface", type = "text"},
        -- type "direction" is special, see below
        {column = "oneway", type = "direction"},
        {column = "maxspeed", type = "int"},
        -- type "bool" is special, see below
        {column = "lit", type = "bool"},
        -- osm2pgsql doesn't know about PostgreSQL arrays, so we define the SQL
        -- type of this column and then have to convert our array data into a
        -- valid text representation for that type, see below.
        {column = "nodes", sql_type = "int8[]"},
        {column = "rel_refs", type = "int"}, -- for the refs from the relations
        {column = "rel_ids", sql_type = "int8[]"}, -- array with integers (for relation IDs),
        {column = "geom", type = "linestring", not_null = true}
    },
    {
        schema = schema,
        proj = 3301
    }
)

-- Tables don't have to have a geometry column
tables.routes =
    osm2pgsql.define_relation_table(
    "ardok_routes",
    {
        {column = "ref", type = "int"}, -- for the refs from the relations
        {column = "tags", type = "jsonb"}
    },
    {
        schema = schema,
        proj = 3301
    }
)

-- Helper function to remove some of the tags we usually are not interested in.
-- Something like this can be useful if you are writing all tags to the
-- database in a JSON(B) column and don't want that cluttered with lots of tags
-- nobody cares about. Returns true if there are no tags left.
local function clean_tags(tags)
    local keys_to_remove = {
        ""
    }

    for _, key in ipairs(keys_to_remove) do
        tags[key] = nil
    end

    -- Mittevajalike tag'ide eemaldamine
    local function hide_tags(tags)
        local keys_to_hide = {
            "odbl",
            "created_by",
            "source",
            "source:ref",
            "source:addr",
            "addr:country",
            "addr:city",
            "addr:street",
            "addr:housenumber",
            "addr:housename",
            "addr:postcode",
            "height",
            "building",
            "maaamet:ETAK",
            "building:use",
            "building:levels",
            "old_addr:street",
            "old_addr:housenumber",
            "source:addr:2023",
            "maaamet:orig_tunnus",
            "wikidata",
            "old_addr:housename",
            "old_addr:place",
            "name",
            "phone",
            "internet_access",
            "wheelchair",
            "roof:shape",
            "name:en",
            "name:ru",
            "brand",
            "fuel",
            "website",
            "alt_name",
            "capacity",
            "operator",
            "fuel:diesel",
            "self_service",
            "opening_hours",
            "brand:wikidata",
            "fuel:octane_95",
            "fuel:octane_98",
            "brand:wikipedia",
            "roof:material",
            "email",
            "name:et",
            "roof",
            "stars",
            "fee",
            "old_name",
            "name:fi",
            "description:ru",
            "url",
            "name:lt",
            "addr:place",
            "roof:levels",
            "addr:name",
            "name:pl",
            "name:lv",
            "payment:credit_cards",
            "payment:debit_cards",
            "amenity",
            "tourism",
            "shop",
            "man_made",
            "addr:old_street",
            "operator:wikidata",
            "fuel:adblue",
            "contact:email",
            "leisure",
            "contact:phone",
            "contact:website",
            "healthcare",
            "maaamet:ADS",
            "name:vro",
            "check_date",
            "building:material",
            "owner:ru",
            "int_name",
            "wikipedia",
            "power",
            "fuel:HGV_diesel",
            "branch",
            "EE:EHIS:id",
            "operator:type",
            "operator:type",
            "layer"
        }
        for _, key in ipairs(keys_to_hide) do
            tags[key] = nil
        end
        return next(tags) == nil
    end

    return next(tags) == nil
end

local highway_types = {
    "motorway",
    "motorway_link",
    "trunk",
    "trunk_link",
    "primary",
    "primary_link",
    "secondary",
    "secondary_link",
    "tertiary",
    "tertiary_link",
    "unclassified",
    "residential",
    "track",
    "service"
}

-- Prepare table "types" for quick checking of highway types
local types = {}
for _, k in ipairs(highway_types) do
    types[k] = 1
end

-- Parse a maxspeed value like "30" or "55 mph" and return a number in km/h
local function parse_speed(input)
    if not input then
        return nil
    end

    local maxspeed = tonumber(input)
    -- If maxspeed is just a number, it is in km/h, so just return it
    if maxspeed then
        return maxspeed
    end

    -- If there is an 'mph' at the end, convert to km/h and return
    if input:sub(-3) == "mph" then
        local num = tonumber(input:sub(1, -4))
        if num then
            return math.floor(num * 1.60934)
        end
    end

    return nil
end

-- Each country uses their own colour for motorways. Here is the beginning
-- of a list of some countries in Europe. Source:
-- https://en.wikipedia.org/wiki/Comparison_of_European_road_signs
local cc2colour = {
    EE = "#2d00e5",
    RU = "#128044",
    LT = "#174688",
    LV = "#333b97"
}

-- This will be used to store information about relations queryable by member
-- way id. It is a table of tables. The outer table is indexed by the way id,
-- the inner table indexed by the relation id. This way even if the information
-- about a relation is added twice, it will be in there only once. It is
-- always good to write your osm2pgsql Lua code in an idempotent way, i.e.
-- it can be called any number of times and will lead to the same result.
local w2r = {}

function osm2pgsql.process_way(object)
    -- Get the type of "highway" and remove it from the tags
    local highway = object.tags.highway
    -- We are only interested in highways of the given types
    if not types[highway] then
        return
    end
    -- Only with geometry
    local geom = object:as_linestring()
    if not geom then
        return
    end
    -- Only Estonia
    local cc = countries:first_intersecting(geom)
    if cc ~= "EE" then
        return
    end

    if highway then
        local hwtype = highway
        local service = object.tags.service
        local service = object.tags.access
        local colour = cc2colour[cc]
        -- We want to put the name in its own column
        local name = object.tags.name
        local tags = object.tags
        local surface = object.tags.surface
        -- The 'oneway' column has the special type "direction", which will
        -- store "yes", "true" and "1" as 1, "-1" as -1, and everything else
        -- as 0.
        local oneway = object.tags.oneway or 0 -- The 'maxspeed' column gets the maxspeed in km/h
        local maxspeed = parse_speed(object.tags.maxspeed)
        -- The 'lit' column has the special type "bool", which will store
        -- "yes" and "true" as true and everything else as false value.
        local lit = object.tags.lit
        -- The way node ids are put into a format that PostgreSQL understands
        -- for a column of type "int8[]".
        local nodes = "{" .. table.concat(object.nodes, ",") .. "}"

        -- Data we will store in the "highways" table always has the tags from
        -- the way
        local row = {
            hwtype = hwtype,
            service = service,
            access = access,
            country = cc,
            colour = colour,
            name = name,
            tags = tags,
            surface = surface,
            oneway = oneway,
            maxspeed = maxspeed,
            lit = lit,
            nodes = nodes,
            geom = geom
        }

        -- If there is any data from parent relations, add it in
        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
        if clean_tags(object.tags) then
            return
        end
        --hide_tags(object.tags)
        tables.highways:insert(row)
    end
end

-- This function is called for every added, modified, or deleted relation.
-- Its only job is to return the ids of all member ways of the specified
-- relation we want to see in stage 2 again. It MUST NOT store any information
-- about the relation!
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 == "road" and relation.tags.ref then
        return {ways = osm2pgsql.way_member_ids(relation)}
    end
end

-- The process_relation() function should store all information about way
-- members that might be needed in stage 2.
function osm2pgsql.process_relation(object)
    if object.tags.type == "route" and object.tags.route == "road" and object.tags.ref then
        tables.routes:insert(
            {
                ref = object.tags.ref,
                tags = object.tags
            }
        )

        -- Go through all the members and store relation ids and refs so they
        -- can be found by the way id.
        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