63 lines
2.7 KiB
Markdown
63 lines
2.7 KiB
Markdown
utils
|
|
=====
|
|
|
|
This folder contains some scripts for preparing some data for the municipality of Wasseiges.
|
|
|
|
## Init
|
|
|
|
The scripts are python scripts that should run into a virtual environment.
|
|
|
|
First, install the local dependencies:
|
|
|
|
```bash
|
|
$ pip3 install -r requirements.txt
|
|
$ source bin/activate
|
|
```
|
|
|
|
## Import of the layer Arbres remarquables
|
|
|
|
- This data is downloadable by <http://geoportail.wallonie.be/catalogue/d594f5a3-34ac-4cc2-a357-aae5d5263f35.html>
|
|
- The layer MUST be converted to a valid geojson format in WGS84 coordinates. You can use QGIS to do so.
|
|
- Then simply run the script `geojson2sql` to produce a SQL file with some INSERT instructions.
|
|
|
|
```bash
|
|
(venv) python3 geojson2sql.py
|
|
```
|
|
|
|
### Some notes about this import
|
|
|
|
The import script creates database entries by filling not all the fields of the table. Only mandatory (non-nullable) fields and informative fields are filled.
|
|
|
|
Here is the column list and the corresponding values list for the table of sites
|
|
|
|
```
|
|
column_list = ['id_site', 'uuid_sinp', 'id_program', 'name', 'geom', 'timestamp_create', 'id_type', 'obs_txt']
|
|
values_list = ['__ID__', '__UUID__', '2', 'properties.SPFR', '__POINT__', '__NOW__', '1', 'géoportail wallon' ]
|
|
```
|
|
|
|
Some values in these list MUST follow some special conventions:
|
|
|
|
- the "__ID__" value will be converted to a unique identifier for the site that will be created by adding 1000000 to the index of the geojson table.
|
|
- the "__UUID__" value will be converted to a a formula that creates uuid value
|
|
- the "__POINT__" value will be converted to a PostGIS ST_GeomFromText formula based on the coordinates of the geojson file
|
|
- the "__NOW__" value will be converted to a formula that creates a timestamp
|
|
|
|
|
|
Furthermore, the `id_program` and `id_type` MUST match the correct values! The first field will decide which program the sites will be attached to.
|
|
|
|
Lastly, if the values starts with `properties.`, it will be mapped to a property in the geojson. E.g, `properties.COMMENTAIR`.
|
|
|
|
For populating the table of visits, the same function is applied. But there is another special convention that applies for building the json details of the visit: the "__JSON__" value will be converted to a dedicated formula that will build the json object in the database.
|
|
|
|
Here is the column list and the corresponding values list for the table of visits
|
|
|
|
```
|
|
column_list = ['id_site', 'json_data', 'timestamp_create', 'obs_txt']
|
|
values_list = ['__ID__', '__JSON__', '__NOW__', 'import']
|
|
```
|
|
|
|
### Populate the database
|
|
|
|
Simply execute the sql files onto the database to import the data.
|
|
|
|
Note that you may activate the uuid extension on the database the first time you use it (for using the function `uuid_generate_v4()`). This can be done by `CREATE EXTENSION IF NOT EXISTS "uuid-ossp";` |