Question about current data model: building_id / geometry_id

[traveller195]

Hello everyone,

@matkoniecz @polly64
I have just a little questions concerning the current database schema of Colouring Core:

we have the following database tables (selection):

geometries

• geometry_id - [PK] integer
• source_id - character varying(100)
• geometry_geom - geometry

building_properties

• building_property_id - [PK] integer
• uprn - bigint
• parent_uprn - bigint
• building_id - integer
• toid - character varying
• uprn_geometry - geometry

buildings

• building_id - [PK] integer
• ...

also tables 'building_user_attributes' and 'building_user_likes' uses the 'building_id' for the internal reference within the database.

Why there is no 'building_id' column in 'geometries' table, but instead a 'geometry_id'?
Is between those tables a one-to-one relation, so those IDs are always identical?

Thanks in advance :-)

best, traveller195

[tomalrussell]

Hi @traveller195

The intention is that one building may have multiple geometries - in the building table we have:

colouring-core/migrations/001.core.up.sql

Line 28 in 1292876

geometry_id integer REFERENCES geometries

so we can for example model multiple footprints of the same building changing over time.

[tomalrussell]

(I realise this doesn’t quite achieve that - ‘geometry_id’ might be better named ‘current geometry ID’ - but the direction of the reference is deliberate: a building is picking from geometries, which may range over historical/previous versions)

[traveller195]

Hi @tomalrussell
thanks for this answer.
Nice. I understand.

Two different cases came in my mind:
1.) it is useful for historical bulding footprint, as you mentioned.
2.) also it could be relevant for a building having multiple building parts --> but would be better distinguished that every building part would be independent to collect all building attributes separately.

the reference between table 'geometries' and 'buildings' is currently implemented by an foreign key 'geometry_id' within the 'buildings' table (like your given example). How is the link to 'older' building footprings modelled and linked with the buildings table?

One option could be a foreign key placed in 'geometries' table for 'building_id'. Plus two further attributes 'lifecycle_start' and 'lifecycle_end' (datetime) for each buliding footprint.

Or it could also stored in an additional join table, including lifecycle dates of the geometry.

just to share some quick ideas (it is not important now to change it here, I guess)

[polly64]

Data schema draft visual that Mateusz helped me on so I could understand database more. Currently on Technical page of open manual. Might be useful in discussions. let me know if you'd like me to share slide so you can/add/edit - can't seem to drag and drop

[traveller195]

very nice.
I also was wondering if there is already existing a nice visual graphic showing the whole database schema like this:

https://wiki.openstreetmap.org/wiki/File:OSM_DB_Schema_2016-12-13.svg

But of course, it needs a lot of time to create it. I suppose.

[matkoniecz]

nice visual graphic showing the whole database schema like this

I would just generate it automatically using one of tools for that - this schema was intended to provide more human-readable view of the most important parts

[matkoniecz]

In case visual needs to be altered

For now, not ("I realise this doesn’t quite achieve that") but we now have an answer why it was designed this way :)

[traveller195]

no problem. I agree. Generating it automatically would be the better way, I guess. Yes.

alright and thanks for all answers. My question was answered.
Everything else we can discuss later :)

[matkoniecz]

Automatically generated files are in #1095 (reply in thread)

[polly64]

if this is helpful we should look to doing it together and making available in manual too

[polly64]

great- yes we need something non-technical PI's like me can clearly understand

[traveller195]

yes. Could be helpful to generate such a graphic automatically (like @matkoniecz suggested)

For the moment, I use just a word document full of screenshots from the db structure I made from pgAdmin. To have an easier overview about the database :-D as my workaround

[matkoniecz]

Navicat produced something like this (the same diagram in various formats):

https://user-images.githubusercontent.com/899988/219654117-944237ad-e1f2-45ff-87c7-d02f1ecac943.svg
https://user-images.githubusercontent.com/899988/219654125-32fe21f8-4b3b-425c-868f-c507870cbe06.png
https://github.com/colouring-cities/colouring-core/files/10766694/Diagram.1.pdf
https://user-images.githubusercontent.com/899988/219654144-a9d8b310-b99e-46ce-9501-56ba1b94e241.jpg

(note "25 more columns" for building table :) )

It could be made better by manual rearranging, but I expect that people able to read and understand this will be also able to generate it.

[traveller195]

@matkoniecz excellent. thanks a lot! it provides a nice overwiev about how the tables are related.

yes, buildings table is to big for such a graphic :)

thanks!

[matkoniecz]

Oh, and Navicat is commercial but they also have 14 days trial.

[traveller195]

additional note:
I just understand, that geometries db table is used also for adiministrative boundary (e.g. boroughs in London), as well.
My first thought was, it could be better to seperate building and administrative geometries in different tables. or add an attribute for 'type' or something in this db table?
And here also the 'name' of each borough is placed in 'source_id' column...

[matkoniecz]

My first thought was, it could be better to seperate building and administrative geometries in different tables. or add an attribute for 'type' or something in this db table?

Or move geometry data to table with borough names.

[traveller195]

@matkoniecz yes, I agree. This would be the best solution.