Performance improvement: Buildings geometries in db table 'geometries'

[traveller195]

Describe the problem

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... which could be better in an extra 'name' column.

What is the reason, to import those boroughs into the db table and not loading it just from an .geojson file into the map?

see https://github.com/colouring-cities/colouring-core/blob/master/migrations/036.borough_borders_and_labels.up.sql

Expected behavior Having only building geometries in 'geometries' table. Or maybe an attribute for 'type' etc. Perhaps, a 'name' column should be added for borough names?

Screenshots screenshots from pgAdmin after running all .up.sql migrations

More information for now, I am interested to find the best solution for Colouring Dresden. Is it the best way to just apply https://github.com/colouring-cities/colouring-core/blob/master/migrations/036.borough_borders_and_labels.down.sql ? Is it possible without creating other problems?

For the initial import oft building geometries, I prefer to start with empty tables for 'geometries' and 'buildings'. To not have a shift of e.g. +33 between the building_id and geometry_id for all buildings from the beginning in the database.

possible workaround: I guess, the assignment which geometry a borough is, is made in the external_data_borough_boundary db table. So, I could perhaps move those id´s manually via SQL to higher numbers (that I can insert the building geometries first)

[matkoniecz]

https://github.com/colouring-cities/colouring-core/blob/master/migrations/036.borough_borders_and_labels.down.sql

That has London-specific ones so you will not see them on your map anyway. Maybe that should be edited away, as is London specific, and moved to London repository?

[matkoniecz]

What is the reason, to import those boroughs into the db table and not loading it just from an .geojson file into the map?

Are you aware of any method to render in react-leaflet nice labels straight from .geojson file (like flood area geometries are rendered straight from .geojson file)?

BTW, maybe https://github.com/colouring-cities/colouring-core/tree/master/app/public/geometries also should be provided somehow with settings or via environmental variables - as these are also London-specific.

Or somehow moved out of colouring-core.

[matkoniecz]

BTW: if you use "blame" or "history" on https://github.com/colouring-cities/colouring-core/blob/master/migrations/036.borough_borders_and_labels.up.sql then you can identify authors and ping them in the issue (I would respond question about code I written even long after I was active, currently I have notifications enabled about all issues but that may change in future)

[traveller195]

@matkoniecz @fallipalaiologou

thanks @matkoniecz for your hints.

That has London-specific ones so you will not see them on your map anyway. Maybe that should be edited away, as is London specific, and moved to London repository?

This would be a general question, yes. Is the planning controls section in general London-specific, or is it the plan to include it to colouring-core (like it is currently)? Do we try to adapt all features from Planning control section also for e.g. Dresden, Germany?

Concerning the city borough geometries, it would be nice to replace the London-related just with new ones for Dresden (this is now my plan, to update 'geometries' and 'external_data_borough_boundary' with new geometries)?

[matkoniecz]

Is the planning controls section in general London-specific, or is it the plan to include it to colouring-core (like it is currently)?

I think that ideally common code would stay while things which are London-specific would be migrated to London repository. Or turned into more modular blocks which can be used by different CCRP deployments.

But I would wait with it until another area deployed planning data support as it will allow to judge better what is London-specific

Do we try to adapt all features from Planning control section also for e.g. Dresden, Germany?

If you have relevant data - the yes! Though some data collected there is quite UK-specific and it may be useful to make more parts there configurable or move to CL somehow.

Obviously one of options especially for initial deployment is to hide/ignore it.

[polly64]

@traveller195 @matkoniecz we want to create a planning section that works for all cities. If a CCRP partner is working on a city where the city authority has an API accessible then this is likely to be able to be used to extract similar basic data- e.g. description, address, status (approved, rejected etc) ...that's great. However even if it doesn't, this feature his relevant and can also be used to encourage city authorities or others to develop one, and also see how this is being done by other countries (e.g. look at other governments' APIs) . We will however have to create a manual option for planning info upload as well.

We absolutely need to work with IOER, and other CCRP partners to begin to work out ,what works for everyone, and then for specific stuff we need that's extra we just add it as required - but keep this also open for others to use if relevant in the future. This process in itself is very important as it also helps us begin to understand and record universal rules relating to stock composition, operation/performance and dynamics.

However we imagine that most categories in planning will relevant to most countries though subcategory names might need to be further refined - all major cities are now and in future likely to have some sort of planning system, have some kind of planning zones, have some buildings designated as protected historic assets, have communities and developers and local authorities who need to know where development is occurring and might occur, and what status applications currently have, have land parcels owned by similar types of land owner across countries (e.g. state, commercial, non-profit-), and will find it useful to capture data on completion dates (for lifespan analysis as well) and errors in accuracy of geolocation of planning data and geolocation of applications- all of which the planning section does. So we very look forward to discussion on this with IOER and other colleagues and can book a session in any time on what needs to be adjusted, removed and added from core colouring code and what should exist only in city/country specific repositories (but be made available to all)