[Tile-serving] [openstreetmap/osm2pgsql] Can Flex styles run arbitrary SQL against the database? (#1415)

[Ryan Lambert]

With the flex output is it possible to run arbitrary SQL queries from the Lua styles?  I have wondered this a few times already and previously looked around through the docs and did not find anything obvious. This week I started experimenting with partitioning schemes with the data and think this ability would be very helpful for this idea as well.

The planned partitioning scheme is to use the the region of the data (e.g. `north-america--us-west`) and the date the data is from. In order to use Postgres partitions (Pg10+) those values need to be in the table and in a primary key.  In a non-partitioned scheme having the date and region on every row would just waste a bunch of space for no real benefit.

Normal (no partitioning) would create the simple PK.

```sql
ALTER TABLE osm.road_line
    ADD CONSTRAINT pk_osm_road_line_osm_id
    PRIMARY KEY (osm_id)
;
```

When the partitioning switch was enabled it would run different SQL to add necessary columns, comments and the more complicated PK.  This sets the stage to load new data and only have to script basic `ATTACH PARTITION` commands.

```sql
ALTER TABLE osm.road_line ADD COLUMN osm_region TEXT NOT NULL DEFAULT { osm_region } ;
ALTER TABLE osm.road_line ADD COLUMN osm_date DATE NOT NULL DEFAULT { osm_date };

ALTER TABLE osm.road_line
    ADD CONSTRAINT pk_osm_road_line_osm_id_region_date
    PRIMARY KEY (osm_date, osm_region, osm_id)
;
```

Other ideas for running arbitrary SQL based on logic within Lua is to run appropriate post-import commands to create generated columns, comments, etc.

Thanks!

-- 
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/discussions/1415
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20210210/91ffcfb9/attachment.htm>