Colouring Athens

Collecting open data about buildings in Athens.

Forked from https://github.com/colouring-london/colouring-london/

This fork of Colouring London for Colouring Athens differs in certain aspects regarding the content and functionality. A few changes have been made to the database to better reflect the current situation in Greece regarding building management. Another difference is the introduction of multilingual functionality. Language selection is a feature that is not included in the Colouring London platform. In the Colouring Athens platform, it was decided to introduce this function to expand and facilitate the users of the platform.

The feature requires the modification of a significant part of the code as many components of the application are involved, such as the database, the Application Programming Interface (API), and the user interface (Frontend).

In the Colouring London database and by extension the Colouring Athens database there are several fields whose values are given by predefined value tables (Lookup tables). In Colouring London, these are stored as enumerate lists, a common practice in relational databases. However, in this way it is not possible to choose different displays of the values (e.g. in case of language change). For this reason, in Colouring Athens, the fields that have predefined values from lists were converted to values from reference tables. In this way, the value of the field is stored in the database with a unique identifier of the value from the reference table, and in the reference table, two or more display languages of the values can be stored in fields. The queries for creating the reference tables as well as the connection to the building tables are given in SQL files: https://github.com/colouring-cities/colouring-athens/tree/Team-developer/migrations

For example:

colouring-athens/migrations/015.bulk_data_sources.up.sql

Lines 11 to 15 in 7ddd381

	CREATE TABLE IF NOT EXISTS reference_tables.type_classifications (
	typec_id serial PRIMARY KEY,
	description_en VARCHAR(128) NOT NULL,
	description_gr VARCHAR(128) NOT NULL
	);

colouring-athens/migrations/015.bulk_data_sources.up.sql

Lines 118 to 126 in 7ddd381

	INSERT INTO reference_tables.type_classifications
	(description_en, description_gr)
	VALUES
	('Single-family home', 'Μονοκατοικία'),
	('Duplex', 'Διπλοκατοικία'),
	('Apartment building','Πολυκατοικία'),
	('Office building','Κτίριο γραφείων'),
	('Commercial building','Εμπορικό ακίνητο'),
	('Other','Άλλο');

Next comes the API modification. A new controller and a new service have been created for the language-switching functionality and bound to the interface controls, which support multiple languages.

The new controller can accept two additional variables, one of which is the display language of the interface. The second is the unique identifier of the value of the user-viewed field, with which the value is looked up from the reference tables.

https://github.com/colouring-cities/colouring-athens/blob/Team-developer/app/src/api/controllers/multiLingualController.ts

The new service can query the database through a new method that looks up the value of the unique identifier, and the result is the corresponding value in the language of the user's choice.

for example:

colouring-athens/app/src/api/services/multilingual_fields.ts

Lines 110 to 130 in 7ddd381

	function getTypeOptions(language: string = 'gr') {
	switch (language) {
	case 'gr':
	return db.manyOrNone(
	`SELECT
	typec_id AS value,
	description_gr AS label
	FROM reference_tables.type_classifications`
	);
	break;
	default:
	return db.manyOrNone(
	`SELECT
	typec_id AS value,
	description_en AS label
	FROM reference_tables.type_classifications`
	);
	break;
	}
	}

In this way, the user can perform the processes of creating, updating, and deleting the values with the unique identifiers in the information table of the buildings while the user is always shown the text of this value in the language of the user's choice from the reference tables.

In the user interface, new widgets have been created for the predefined value fields to make it possible to incorporate the extra variables from the user's options, namely the selected language and the unique identifier of the selected value in the field.

For example: https://github.com/colouring-cities/colouring-athens/blob/Team-developer/app/src/frontend/building/data-components/select-data-entry-multilingual.tsx

Also, it was necessary to modify the system by which the user verification function is performed.

The platform stores the verifications in a separate table in the database, which accepts the user who did the verification, the field, and its verified value. Because in multi-language fields the value is the unique identifier, a new widget has been created that converts the value's unique identifier to the corresponding text value.

The value in the database is stored as the unique identifier, but in the UI the control looks up the text values from the database reference tables and returns it to the UI based on the selected language.

https://github.com/colouring-cities/colouring-athens/blob/Team-developer/app/src/frontend/building/data-components/verification-multilingual.tsx

In addition, the value entry history system controls have been modified. The platform stores a history of the user's input values in a separate table, which holds the user, date, field, previous value, and current value. As in the verification system, the database table now stores the unique identifier of the value, and the new widget is responsible for looking up the text values from the reference tables.

See: https://github.com/colouring-cities/colouring-athens/blob/Team-developer/app/src/frontend/building/edit-history/category-edit-summary.tsx

Finally, the map styles have been modified according to the unique identifier instead of the actual value.

For example:

colouring-athens/app/map_styles/polygon.xml

Lines 242 to 259 in 7ddd381

	<Filter>[year_built] = 5</Filter>
	<PolygonSymbolizer fill="rgb(255, 249, 184)" />
	</Rule>
	<Rule>
	<Filter>[year_built] = 4</Filter>
	<PolygonSymbolizer fill="rgb(251, 175, 39)" />
	</Rule>
	<Rule>
	<Filter>[year_built] = 3</Filter>
	<PolygonSymbolizer fill="rgb(230, 113, 29)" />
	</Rule>
	<Rule>
	<Filter>[year_built] = 2</Filter>
	<PolygonSymbolizer fill="rgb(143, 3, 3)" />
	</Rule>
	<Rule>
	<Filter>[year_built] = 1</Filter>
	<PolygonSymbolizer fill="rgb(143, 83, 133)" />

The choice of language by the user brought significant modifications as mentioned at the level of storage and retrieval of information from the database. However, the application contains extensive text and information, which had to follow the user's language selection.

To achieve this, an external library for React has been used, the react-i18next (https://react.i18next.com/). React.i18next offers several tools for integrating multiple languages into applications developed with the React library, and builds on the widely used i18next library. In Colouring Athens, two of them have been used, the most common being the conversion of the text into variables, whose values are stored in two or more (depending on the language options) separate JSON files.

For example:

colouring-athens/app/src/frontend/building/header-buttons/view-edit-control.tsx

Lines 14 to 33 in 7ddd381

	const { t } = useTranslation();
	return props.mode === "edit" ? (
	<NavLink
	className="icon-button view"
	title="View data"
	to={`/view/${props.cat}/${props.building.building_id}`}
	>
	{t("view")}
	<ViewIcon />
	</NavLink>
	) : (
	<NavLink
	className="icon-button edit"
	title="Edit data"
	to={`/edit/${props.cat}/${props.building.building_id}`}
	>
	{t("edit")}
	<EditIcon />
	</NavLink>
	);

With this method, the text in the code is given as variables and the application selects the appropriate value from the external JSON files based on the user's language selection. The second method used in some cases is to switch the code with the text in the selected language through if statements.

The JSON files can be found here:

https://github.com/colouring-cities/colouring-athens/tree/Team-developer/app/src/locales