[Tile-serving] [openstreetmap/osm2pgsql] Recreate osmosis --write-apidb using flex backend (#1174)

[mmd]

osmosis --write-apidb is the only way to populate an API DB. Unfortunately, it is _extremely_ slow, making it fairly impractical to use except for the smallest available extracts.

I thought let's give the Flex backend a try. It's probably not what it's been designed for. Nevertheless, it may be a good exercise to see where it still might have some gaps.

Here's my first try:


<details>
<summary>Code (click to open)</summary>

```
inspect = require('inspect')

local tables = {}

tables.current_nodes = osm2pgsql.define_table{

             name = 'current_nodes', 
             columns = {
               { column = 'id',           type = 'bigint'    , not_null = true },
               { column = 'latitude',     type = 'integer'   , not_null = true },
               { column = 'longitude',    type = 'integer'   , not_null = true },
               { column = 'changeset_id', type = 'bigint'    , not_null = true },
               { column = 'visible',      type = 'boolean'   , not_null = true },
               { column = 'timestamp',    type = 'timestamp' , not_null = false },  -- not_true = true
               { column = 'tile',         type = 'bigint'    , not_null = true },
               { column = 'version',      type = 'bigint'    , not_null = true },
             }
}

tables.current_node_tags = osm2pgsql.define_table{

             name = 'current_node_tags', 
             columns = {
               { column = 'id',           type = 'bigint'   , not_null = true },
               { column = 'k',            type = 'text'     , not_null = true },
               { column = 'v',            type = 'text'     , not_null = true },
             }
}

-- Debug output: Show definition of tables
--for name, dtable in pairs(tables) do
--    print("\ntable '" .. name .. "':")
--   print("  name='" .. dtable:name() .. "'")
--    print("  columns=" .. inspect(dtable:columns()))
--end


function osm2pgsql.process_node(object)

    tables.current_nodes:add_row({
           id = object.id,
           latitude = 1000000,   -- not available
           longitude = 1000000,  -- not available
           changeset_id = object.changeset,
           visible = true,
--           timestamp = object.timestamp,      -- conversion not clear
           tile = 0,
           version = object.version
    })

   for k, v in pairs(object.tags) do 
      tables.current_node_tags:add_row({
             id = object.id,
             k = k,
             v = v
      })
   end

end

function osm2pgsql.process_way(object)
-- TODO
end

function osm2pgsql.process_relation(object)
-- TODO

end

```
</details>


So far, I encountered a number issues which I'm unable to solve/fix.

---


1. current_node_tags uses character varying (without limit) for keys and values. I can only create text columns at the moment.

```
openstreetmap=# \d current_node_tags
                      Table "public.current_node_tags"
 Column  |       Type        | Collation | Nullable |        Default        
---------+-------------------+-----------+----------+-----------------------
 node_id | bigint            |           | not null | 
 k       | character varying |           | not null | ''::character varying
 v       | character varying |           | not null | ''::character varying

```

---

2. Node object: No access to latitude and longitude
I couldn't find a way to access lat/lng values of a node.

---

3. Timestamp is correctly created as timestamp without time zone. However, I'm unable to assign the object.timestamp value to this field. The error message I'm getting says:

```
result COPY END for current_nodes failed: ERROR:  date/time field value out of range: "1442143582"
HINT:  Perhaps you need a different "datestyle" setting.
CONTEXT:  COPY current_nodes, line 1, column timestamp: "1442143582"
```

I couldn't find an example in flex-config/* which assigned a timestamp to a timestamp field (only a hstore in unitable.lua is currently demoed).

---

4. Defining primary keys

I'd like to define primary keys for my tables. Is this supported at the moment?

---


5. The output still mentions  planet_osm* tables multiple times, although I'm not using them. Could this be suppressed somehow?


```
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels

Stopping table: planet_osm_nodes
Stopping table: planet_osm_ways

Building index on table: planet_osm_ways
Stopped table: planet_osm_nodes in 0s
Stopping table: planet_osm_rels
Building index on table: planet_osm_rels
```



-- 
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/issues/1174
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20200518/39c844d8/attachment.htm>