search

Laixer / FunderMaps

Manage and visualize foundation data
Apache License 2.0
3 stars 1 forks source link

Clean up data.views and data.materialized_views #174

Closed tabeckers closed 4 years ago

tabeckers commented 4 years ago

The views and materialized views in the data namespace have to be updated. They still refer to old building ids and old tables. Comments and descriptions have to be added for each view and materialized views. Some views should also be materialized.

tabeckers commented 4 years ago

When this process is finished, the data.premise table can be dropped.

tabeckers commented 4 years ago

All materialized views were created. They will be populated over the weekend, since this operation will take a very long time and locks the data at its first run.

See #194

yorickdewid commented 4 years ago

I'll review

tabeckers commented 4 years ago

The CAT column (category) was set to 1 for every subquery in both the foundation_indicative and foundation_risk materialized views. This would mean that every foundation would have the same type and the same risk. This error was due to copy-paste behaviour without proper double checking.

All categories have been updated to their correct values.

tabeckers commented 4 years ago

Also, the formatter of DBeaver or PgAdmin has removed ALL comments and has reformatted the entire query. It seems that as of now there isn't that much we can do about this.

tabeckers commented 4 years ago

The CAT column should be mapped to an enum. For the foundation type (foundation_indicative) this was done using a temporary enum: data.foundation_type_temp. This can be mapped from the integer value in the foundation_indicative view using the function data.map_foundation_temp().

The CAT column of the view foundation_risk will be converted to an enum, since we already know the amount of foundation risk levels (A, B, C, D, E).

tabeckers commented 4 years ago

The geocoder has been expanded with a new table for country, municipality, district and neighborhood. The geocoder.building table will reference the geocoder.district table. The process of doing so is contained in issue #209.

These new tables have to be references from our views and materialized views in the data schema.

tabeckers commented 4 years ago

All relevant tables have bene processed, all references have been corrected and the entire data schema has been cleaned up. This is ready for review.

tabeckers commented 4 years ago

All queries should be stored in text format in the shared drive. These versions of the materialized views should contain all comments, formatting and explanations. The same format as the old documented views should be used (also present in the drive).