Several issues have come up related to data types that can be used in an
`define_table()` command and how data is converted from Lua to osm2pgsql. In
all these cases it is possible to use the generic `sql_type` mechanism of
osm2pgsql to reach the intended goal, but it is a bit awkward, always needs
explaining and exposes the user to possible errors which are hard to understand
(cryptic error messages from `COPY`). It would be nice, if we can say: "Yes,
osm2pgsql supports these common constructs out of the box".
## Geography
Osm2pgsql has always supported the *geometry* datatype with its subtypes like
*Point*, *Linestring*, etc. and the setting of the SRID. But it doesn't
natively support *geography* data types with their variants. It is easy enough
to work around this using the `sql_type` setting, but still a bit awkward. This
has been [discussed
here](https://github.com/osm2pgsql-dev/osm2pgsql/discussions/2152).
Proposal: Create new datatypes `geography`, `geography-point`,
`geography-linestring`, and so on. Default projection would be 4326. Generate
an error if the projection is not valid for a geography data type.
Variant A: Also add `geometry-point` as alias for `point` geometry and so on,
giving us a consistent naming scheme.
Implementation: Is trivial, just the new types need to be recognized and the
different default for the projection. There is no need to write special WKB or
so.
## Identity
Unique IDs on tables are often useful. Sometimes they need to be generated. How
to do this is [documented in the
manual](https://osm2pgsql.org/doc/manual.html#using-an-additional-id-column).
But this is a bit awkward and we always get questions on this and the use of
the `serial` type.
Proposal: Create new types `id2`, `id4`, and `id8` (with aliases `smallid`,
`id`, and `bigid`) that create integer [identity
columns](https://www.postgresql.org/docs/current/ddl-identity-columns.html) of
the specified size with `GENERATED ALWAYS AS IDENTITY`. It will also set
`create_only` to `true` and `not_null`, i.e. osm2pgsql will not try to fill
this column.
Variant A: Use `identity` instead of `id` in the type names. Not so easy to
confuse with `int`.
Variant B: Also automatically generate a unique index for all ID columns. Might
be too "magic", and not every use case needs one.
Variant C: Add a `sequence` option, which allows setting the sequence name
instead of creating a default one. For special use cases (say using the same ID
space for several tables), users can do a [`CREATE
SEQUENCE`](https://www.postgresql.org/docs/current/sql-createsequence.html)
before running osm2pgsql and then refer to that.
Implementation: Only the new types need to be recognized and a few SQL
templates extended. A bit more to do if a sequence can be set, but still easily
done.
## Arrays
PostgreSQL can store arrays of any type. We use this in osm2pgsql to store the
list of member node IDs of a way, for instance. Currently you need to define
them in Lua as `sql_type = 'int8[]'` etc. and then build the context as text
yourself: `nodes = '{' .. table.concat(object.nodes, ',') .. '}'`. [See also
the question in discussions forum on
this](https://github.com/osm2pgsql-dev/osm2pgsql/discussions/2216).
Proposal: Add new data types `int-array` and its variants for the different
integer types and add an automatic conversion from Lua tables with integers.
The user doesn't have to write the conversion any more and we can generate
better error message if the data is invalid for the type.
Variant A: Allow scalar values in conversion, will result in a single-element
array.
Variant B: Also add this for some other scalar types. The only ones that make
sense are probably `real` and `text`.
Variant C: Use the PostgreSQL syntax `int[]` and its variants instead.
Variant D: Make this a general feature that works for all data types, i.e.
adding `[]` makes any type into an array.
Implementation: Add the new data types and new conversion functions. Depends on
the variant how much work this will be.
--
Reply to this email directly or view it on GitHub:
https://github.com/osm2pgsql-dev/osm2pgsql/issues/2274
You are receiving this because you are subscribed to this thread.
Message ID: <osm2pgsql-dev/osm2pgsql/issues/[email protected]>_______________________________________________
Tile-serving mailing list
[email protected]
https://lists.openstreetmap.org/listinfo/tile-serving
