aboutsummaryrefslogtreecommitdiffstats
path: root/schema.sql
Commit message (Collapse)AuthorAgeFiles
* Update Mineralrättigheter import logic.Guilhem Moulin2025-04-281
| | | | | | | | | | | | | | | | | | | | Thanks to EU Directive 2019/1024 of the European Parliament and of the Council of 20 June 2019 on open data and the re-use of public sector information, and the Commission Implementing Regulation (EU) 2023/138 of 21 December 2022, the mineral register is now available under the terms of the CC0 1.0 Universal licence, see https://www.sgu.se/produkter-och-tjanster/geologiska-data/malmer-och-mineral--geologiska-data/mineralrattigheter-och-prospektering/ Given we no longer need to parse SVG images from the webmap, we drop webmap-download-mrr.py and add layers for expired and forbidden permits (ut_metaller_industrimineral_forfallna, ut_olja_gas_diamant_forfallna, bearbetningskoncessioner_forfallna, ut_metaller_industrimineral_forbud, ut_diamant_forbud) as well as markanvisningar_bk_ansokta. Unfortunately the GeoPackage file doesn't include peat concessions, so we drop them from config.yml for now. Should they be of interest we can always restore webmap-download-mrr.py and/or order the register from Bergsstaten, cf. https://resource.sgu.se/bergsstaten/exporter-ur-mrr-info.pdf .
* Change layer cache logic to target destination layers rather than sources.Guilhem Moulin2025-04-241
| | | | | | | | | | | | | | | | | | | | In a future commit we'll fingerprint layers to detect changes. Comparing modification times is not enough since some sources (for instance Naturvårdsverket's SCI_Rikstackande) are updated on the server even though no objects are being added; the source layer remains unchanged but the file differs because of OBJECTID changes we are not interested in. Rather than using another cache layer/table for fingerprints, we cache destination layernames rather than triplets (source_path, archive_member, layername), along with the time at which the import was started rather than source_path's mtime. There is indeed no value in having exact source_path's mtime in the cache. What we need is simply a way to detect whether source paths have been updated in a subsequent run. Thanks to the shared locks the ctime of any updated source path will be at least the time when the locks are released, thereby exceeding the last_updated value.
* Skogsstyrelsen: Relax some constraints on the schema to reflect current GPKG ↵Guilhem Moulin2025-04-231
| | | | files.
* webmap-import: Add a cache layer and store the source file's last ↵Guilhem Moulin2025-04-231
| | | | | | | | | | | | | | | | | | | | | | | | modification time. That way we can avoid the expensive unpack+import when the source file(s) have not been updated since the last run. The check can be bypassed with a new flag `--force`. We use a sequence for the FID:s (primary key) and a UNIQUE constraint on triplets (source_path, archive_member, layername) as GDAL doesn't support multicolumns primary keys. To avoid races between the stat(2) calls, gdal.OpenEx() and updates via `webmap-download` runs we place a shared lock on the downloaded files. One could resort to some tricks to eliminate the race between the first two, but there is also some value in having consistency during the entire execution of the script (a single source file can be used by multiple layers for instance, and it makes sense to use the very same file for all layers in that case). We also intersperse dso.FlushCache() calls between _importSource() calls in order to force the PG driver to call EndCopy() to detect errors and trigger a rollback when _importSource() fails.
* Relax some UNIQUE constraint on the MRR layer's DiaryNR column.Guilhem Moulin2024-10-291
| | | | | | | Apparently there are duplicates for dnr BS 22-28-2000 (bearbetningskoncessioner_beviljade) and BS 23-126-2007, BS 23-116-2005, BS 23-226-1930, BS 23-149-1960, BS 23-7-2000, and BS 23-105-2010 (markanvisningar_bk_beviljade).
* PostgreSQL: Use `character varying (n)` not `character (n)`.Guilhem Moulin2024-10-271
| | | | | | Forgot that PostgreSQL space-pads the latter, and that the former doesn't in fact yield extra storage costs, cf. https://www.postgresql.org/docs/13/datatype-character.html
* nva:Skogsstyrelsen: Remove NOT NULL constraint on the DatAvtal column.Guilhem Moulin2024-10-271
| | | | | | As of today SK 117-2024 has not contract date set, so the constraint fails during import. https://www.skogsstyrelsen.se/skogens-parlor/NVAvtal/?objektid=4020527
* PostgreSQL: Add NOT NULL constraints on the geometry columns.Guilhem Moulin2024-10-271
| | | | | Among other things this allows CLUSTERing on the GIST indices, cf. https://postgis.net/docs/manual-3.3/performance_tips.html#database_clustering
* Add layer with noteworthy dams.Guilhem Moulin2024-10-191
| | | | | | | | Unfortunately the dam register is way too noisy Unfortunately it looks like there is no way to download a pre-curated dam registry with only noteworthy dams used for production (SvK's dammar_pf.shp comes close but still misses some), so we manually remove the ones which are more than 2km from a production site or a power station.
* Add layer with planed gigafactories.Guilhem Moulin2024-10-191
| | | | Own work.
* Add layers from Svenska Kraftnät (SvK).Guilhem Moulin2024-10-191
|
* typofixGuilhem Moulin2024-10-191
|
* Add layers for Naturvårdsverkets and Länsstyrelserns Riksintressen.Guilhem Moulin2024-10-181
| | | | | | | Cf. https://skyddadnatur.naturvardsverket.se/ . We exclude Nationalstadspark (MB 4 kap 7§) since the only entry is in Stockholm (Kungliga nationalstadsparken, Ulriksdal-Haga-Brunnsviken-Djurgården) outside our extent.
* Add layers for Naturvårdsregistret and Naturvårdsavtal.Guilhem Moulin2024-10-171
|
* typofixGuilhem Moulin2024-09-261
|
* Ignore field ‘OBJECTID’ on the layers from Sametinget.Guilhem Moulin2024-09-261
| | | | Looks like a FID sequence, so not really useful for us…
* Add layer data for Rennäringen.Guilhem Moulin2024-09-261
| | | | From Sametinget via Länsstyrelsen.
* Skogsstyrelsen: Fetch Shapefiles rather than GeoPackage files.Guilhem Moulin2024-09-191
| | | | | | | | | | | | | | | | | The former have been deprecated per following note on https://www.skogsstyrelsen.se/sjalvservice/karttjanster/geodatatjanster/nerladdning-av-geodata/ Påminnelse om vektordata ------------------------ Från och med 1 mars finns vektordata i GeoPackage-format. Shape-filerna tas bort 16 september. För de flesta datamängder kommer vi att ta bort de länsvisa lagren och bara ha rikstäckande lager. These GeoPackage layers have geometry type POLYGON but appear to contain MULTIPOLYGONs as well, so fortunately there is no data loss due to geometry splitting.
* Add layer definitions for Vindbrukskollen layers.Guilhem Moulin2024-06-221
| | | | | | | | | | | | | | | | | Only the land-based ones for now. Source: https://www.energimyndigheten.se/energisystem-och-analys/elproduktion/vindkraft/vindbrukskollen/ Layers: - https://ext-geodatakatalog.lansstyrelsen.se/GeodataKatalogen/srv/api/records/GetMetaDataById?id=ed5814b2-08bf-493a-a164-7819e1b590d6 LST Vindbrukskollen landbaserade vindkraftverk - https://ext-geodatakatalog.lansstyrelsen.se/GeodataKatalogen/srv/api/records/GetMetaDataById?id=c816bd1e-bc6c-487f-a962-770f05f677b6 LST Vindbrukskollen landbaserade projekteringsområden - https://ext-geodatakatalog.lansstyrelsen.se/GeodataKatalogen/srv/api/records/GetMetaDataById?id=c290bc31-1af8-497e-a9a5-87fcec55d0ce LST Vindbrukskollen havsbaserad vindkraft Webmaps: - https://vbk.lansstyrelsen.se/ - https://ext-geodatakatalog.lansstyrelsen.se/GeodataKatalogen/srv/swe/catalog.search#/map
* Add logic for field regex substitution.Guilhem Moulin2024-06-191
| | | | | | | | | This is useful to replace a YYYYMMDD formatted date with YYYY-MM-DD. The target field can then be set to not-nullable and its type set to Date, as the OGR_F_SetField*() with take care of the conversion. We could also do that via an SQL query, but in our case the sources are not proper RDBMS so SQL is emulated anyway.
* sks:UtfordAvverk: Add NOT NULL constraint on AnmaldHa column.Guilhem Moulin2024-06-191
| | | | | There are a couple (15) of NULL AnmaldHa's, all from Sep 1, 2005 in Borås, so we don't care about these.
* Add logic to replace field value literals.Guilhem Moulin2024-06-191
| | | | And set them to NULL.
* Add schema for MRR layers.Guilhem Moulin2024-06-121
|
* sks:UtfordAvverk: Add UNIQUE constraint on OBJECTID.Guilhem Moulin2024-06-121
| | | | | | Not sure why I thought there was duplicates but I must have done something wrong as there are none in the 667034+223463+214082+ 72656 = 1177234 features found after removing the spatial filter.
* Add schema.sql.Guilhem Moulin2024-06-111
This is useful to bootstrap the database without having to run GRANT CREATE ON SCHEMA postgis TO webmap_import; after DROPping the database or re-creating the cluster.
generated by cgit v1.2.3 (git 2.25.1) at 2025-06-07 03:50:59 +0000