Creating data with consequence for CSDP

This document contains the latest process to data consequence to the building structures and road polygons.

For buildings

Creating the file for performing a spatial join

1. Get the GML files from the USGS website.
2. Load the boundingPolygon.kml from the package that Dick had sent.
3. Export it as a GeoJSON file while choosing the extent as the boundingPolygon.kml.
4. Repeat this same process in QGIS for all the GML files which lie inside Dick's bounding box.
5. Once, all the GML files which lie inside Dick's bounding box are clipped, we then merge them.
6. To merge them use the Merge Vector Layers from the Processing Toolbox.
7. Select the clipped layers and then click on Save.
8. Once you have the merged layer, you need to click on Fix Geometries from the Processing Toolbox on them.
9. This will be the file that you will use to perform the spatial join with the pushpins.
10. Export this file as a GeoJSON file.

Performing the spatial join

1. Load the GeoJSON file in QGIS.
2. Load the consequence pushpins from Dick. Load the file as a Delimited Text Layer.
3. To perform a spatial join, in the Processing Toolbox, type Join Attributes by Location
4. Make the Merged Layer with fixed geometry as the Base Layer
5. Make the Structure Consequences as the Join Layer
6. Choose intersects as the Geometric predicate.
7. Then click on Run.
8. Save this joined layer as a GeoJSON file
9. Right click on the Joined Layer and click on export.
10. Export the file as a PostgreSQL SQL dump.
11. While exporting, set CREATE_SCHEMA as NO.
12. You now have the SQL dump ready for the next steps.

Migrate the SQL Dump to PostgreSQL

Note: These steps are taken from the How to add consequence data to 3DCity DB file

1. Open PG Admin.
2. Select Tools > Query Tool.
3. lick the open file icon, navigate to the SQL dump, and open the file.
4. Highlight the first few rows, up to and including the CREATE TABLE statement.
5. Press the run icon (or press F5) to run these highlighted rows.
6. Delete the highlighted rows.
7. With nothing highlighted, press the run icon (or press F5) to run the rest of the dump import.
8. You now have the tables ready for running the aggregate queries.

Perform aggregate queries

1. Run these queries in the following sequence.
2. blah
3. blah
4. Export the csv out. This will be the damages.csv

Updating the heights of the building structures

1. Get the LAZ files from the blah blah location
2. foo1
3. foo2
4. Generate the new_heights.csv

Adding the consequence data in the CityGML file

1. Run the Python CLI with with the following arguments.

For roads

Adding consequence to the road polygons.

Buffering road centerlines

The document can be found here.

Remaining process

The document for the remaining process can be found here.

Chopping up road polygons

Done by Vannary in FME

Chopping

Adding the id column (NSI Identifier)

Adding the centroid of the polygons

Export

1. The files are exported as a GeoJSON and csv file

Updating the consequences of road polygons

Dick occassionally sends the updated consquences for Roads and building structures.

Adding the new column

1. From the discussions during the meeting, addition of a new column was proposed.
   1. water_level_above_ground_wp and water_level_above_ground_wop
2. These columns are calculated using the following formula.

   water_level_above_ground_wp (ft) = flooding_damage_driving_param_wp (ft) + foundation_height (ft)
   water_level_above_ground_wop (ft) = flooding_damage_driving_param_wop (ft) + foundation_height (ft)
3. If the sum of the values is less than zero, the value is set as zero. Else, it is kept as is.
4. This process is done manually in Excel and then the xlsx and csv are saved.
5. We save the csv with the following suffix with-formula.csv

Join my attribute in FME

1. We take the road polygons (GeoJSON) and the csv with the new column.
2. We use the AttributeJoiner to join the two on the nsi_identifier column.
3. Then we extrude the output of the join using the Extruder.
4. The Extruder returns a 3d-tileset which is then uploaded on Cesium Ion.