[Tile-serving] [openstreetmap/osm2pgsql] Enhance with a datamodel option, using JSONB (#533)

[Peter]

Hi @pnorman let's  start some design and drafts!... Do you have a **UML class diagram** of tables generated by  `osm2pgsql`?  

My suggestion is a option that produce another table structure... General ideia:

**WITHOUT the option**

```sql
CREATE TABLE sometable1 (
  id serial NOT NULL PRIMARY KEY,
  mainfield11 int,
  mainfield12 geometry(LINESTRING,4326),
  otherfield11 int,
  otherfield12 text,
  otherfield13 int,
  UNIQUE(mainfield11)
);

CREATE TABLE sometable2 (
  id serial NOT NULL PRIMARY KEY,
  fk bigint REFERENCES sometable1(id),
  mainfield21 int,
  mainfield22 text,
  otherfield21 int,
  otherfield22 text,
  otherfield23 int,
  UNIQUE(mainfield21,otherfield21)
);
```
**WITH the JSONb option**

More compact and stable  structure (not changes basic structure with future field changes),
 
```sql
CREATE TABLE sometable1 (
  id serial NOT NULL PRIMARY KEY,
  mainfield11 int,
  mainfield12 geometry(LINESTRING,4326),
  info JSONb
  UNIQUE(mainfield1)
);

CREATE TABLE sometable2 (
  id serial NOT NULL PRIMARY KEY,
  fk bigint REFERENCES sometable1(id),
  mainfield21 int,
  otherfield21 int,
  info JSONb,
  UNIQUE(mainfield21,otherfield21)
);
```

The only rule, to not move a field to JSONb, is that is "the field is in structural use": non-trivial datatypes (as *geometry* and *geography*), PRIMARY KEY, REFERENCES (FOREIGN KEY), UNIQUE, CHECK, etc. Some (subjective election) fields are "main" so we can preserve as SQL datatype. 

We can also offer SQL VIEWs to obtain the old tables again,

```sql
CREATE VIEW old_sometable1 AS
   SELECT *, 
     info->>'otherfield11'::int as otherfield11, info->>'otherfield12' as otherfield12, info->>'otherfield13'::int as otherfield13 
  FROM sometable1;

CREATE VIEW old_sometable2 AS
   SELECT *,   info->>'mainfield22' as mainfield22,
     info->>'otherfield22'::int as otherfield22, info->>'otherfield23' as otherfield23 
  FROM sometable2;
``` 
The internal representation of `otherfield11`, `otherfield22`, etc.   is in fact *integer*, but is "JSONb number", so it need a CAST to "SQL integer" in PostgreSQL 9.6 to express non-*text* fields.
 

----

> JSONB is actually possible right now, declare a column as it, and use a lua transform which produces a string which casts cleanly to jsonb.

Perfect!  Can you show an little draft with this lua script? In principle the transform can be also after usual tables build, a `psql` script... But a previous transform save some memory and CPU time.




-- 
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/533#issuecomment-271532787
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20170110/5bfc2d4d/attachment.html>