loading building geometries into db

[traveller195]

loading building geometries into db from LoD2 dataset GeoSN / Saxony, Germany

[traveller195]

see https://github.com/colouring-cities/colouring-dresden/tree/main/maintenance/extract_data

[traveller195]

first using own python script: cc_sachsen_origin_data2db_schema.py to transfer LoD2 building footprint data into the colouring-core db tables geometries, buildings and buildings_properties

second using own python script: import_colouring_cities_with_ssh_03.py to import those three db tables into db first only into temp db tables! must be transferred by SQL commands manually

[traveller195]

run INDEX for db tables again after loading the building data (would make sense to do so...)?

[traveller195]

important: care about other administrative geometries in the 'geometries' db table!

see https://github.com/colouring-cities/colouring-core/issues/1102

[traveller195]

see https://github.com/colouring-cities/colouring-dresden/issues/23

fist, delete the London boroughs from geometries table. Then, insert building geometries

Afterwards, the Dresden borough geometries could be inserted

[traveller195]

change column length, to be able to insert the building data

ALTER TABLE public.geometries ALTER COLUMN source_id TYPE VARCHAR(100);

[traveller195]

now transfer the data from temp db tables into the target tables

INSERT INTO public.geometries
SELECT * FROM public.temp_geometries;

INSERT INTO public.buildings (building_id, geometry_id)
SELECT building_id, geometry_id FROM public.temp_buildings;

INSERT INTO public.building_properties (building_property_id, building_id)
SELECT building_property_id, building_id FROM public.temp_building_properties;

[traveller195]

now, create INDEX for buildings agin, now with the data

first, drop current index by

psql -f 002.index-geometries.down.sql

then, CREATE INDEX again, now with the real data psql -f 002.index-geometries.up.sql

also for buildings

psql -f 003.index-buildings.down.sql

psql -f 003.index-buildings.up.sql

[traveller195]

because , I suppose, there are some problems with performance and 2.6 million builings of whole saxony in the database (e.g. for tile renderer), I will do the last steps all again with a subset of buildings only for Dresden

[traveller195]

To Do: Update building data with

• data which is newer
• data extent city Dresden + adjacent counties?

[traveller195]

the plan is, to keep Dresden city data (no additional extent / buffer)... but it should be updated with newer data!