The task is to prepare script (maybe or procedure/function) to divide vector data on country and add to intersected data country code. I've database with imported OSM vector data for whole world and one table with countries with ISO country code. I used for import data osm2psql with default style. Then I prepared about 80 tables with data important to me and in convenient structure. The data comes from planet_osm_point, planet_osm_line and planet_osm_polygon (standard table for default style). Amount of data is quite huge - tables are from 25 MB to 140 GB. In tables I have all 3 geometry types (in one table can be 3 geometry types in the same record).
The task is to prepare some solution (script/function/procedure) to iteratively process all tables (with exceptions) in schema containing about 80 tables in reasonable time (like 24h). It means every feature from each of 80 tables has to be intersect with countries geometry and add to them field containing country code. Attributes/fields on intersected data should be the same like in a source data + country code. Attached sample in a graphical way.
The database is on Debian 10.7. For implementation I can share my desktop by anydesk or teamviewer (if necessary). Additional operation on database is allowed - like partitioning.
<<<<<<<<<<< GIS Expert >>>>>>>>>
Greetings!
I have handle billions of records to calculate same functionality, I can do it and can show the live demo. Please send me message to get further details. I have 10 + Years work experience
I have 5+ year experience in postgre sql in one of large scale database.
will try this once understand of existing large scale data then provide you proper solution.