technologiestiftung/giessdenkiez-de-tree-data

Script to harvest tree data from a Web Feature Service from Berlins Geodata Portal and integrate it to our GießDenKiez database.

giessdenkiez-de-tree-data's Issues

What does this do?

@Lisa-Stubert can you add a paragraph in the readme what this is for?

Bug: These scripts seem to miss some trees

These are the sql statements I used to find and update the missing trees after the scripts where run

select (SELECT count(1) from temp_trees) - (SELECT count(1) from trees) as missing;

--| missing |
--|---------|
--|   25383 |

SELECT gml_id from temp_trees
EXCEPT
SELECT gml_id from trees;

SELECT gml_id, count(1) from trees GROUP by gml_id;


CREATE TEMPORARY TABLE missing_trees AS
SELECT gml_id FROM temp_trees
EXCEPT
SELECT gml_id FROM trees;


SELECT * from missing_trees;

INSERT INTO trees (gml_id, standortnr, kennzeich, art_dtsch, art_bot, gattung_deutsch, gattung, strname, hausnr, pflanzjahr, standalter, stammumfg, baumhoehe, bezirk, eigentuemer, zusatz, kronedurch, "type", geom)
SELECT
	gml_id,
	standortnr,
	kennzeich,
	art_dtsch,
	art_bot,
	gattung_deutsch,
	gattung,
	strname,
	hausnr,
	pflanzjahr,
	standalter,
	stammumfg,
	baumhoehe,
	bezirk,
	eigentuemer,
	zusatz,
	kronedurch,
	"type",
	geom
FROM
	temp_trees
WHERE
	gml_id IN(
		SELECT
			gml_id FROM missing_trees);

Add codeowners and renovate.json

Can you add a codeowners file and a renovate.json so we can keep this repo up to date

Dependency Dashboard

This issue lists Renovate updates and detected dependencies. Read the Dependency Dashboard docs to learn more.

Awaiting Schedule

These updates are awaiting their schedule. Click on a checkbox to get an update now.

• chore(deps): update dependency node to v20.15.0
• chore(deps): update dependency pandas to v2.2.2
• chore(deps): update dependency shapely to v1.8.5.post1
• chore(deps): update devdependencies-non-major (@types/node, globals, prettier, semantic-release, tsx, typescript)
• chore(deps): update python docker tag to v3.12
• chore(deps): update actions/checkout action to v4
• chore(deps): update dependency geopandas to v1
• chore(deps): update dependency pytest to v8
• chore(deps): update dependency pytest-cov to v5
• chore(deps): update dependency semantic-release to v24
• chore(deps): update dependency shapely to v2
• chore(deps): update dependency sqlalchemy to v2
• chore(deps): update docker/build-push-action action to v6
• chore(deps): update docker/login-action action to v3
• chore(deps): update docker/metadata-action action to v5

Edited/Blocked

These updates have been manually edited so Renovate will no longer make changes. To discard all commits and start over, click on a checkbox.

• chore(deps): update dependency pytest to v6.2.5

Open

These updates have all been created already. Click a checkbox below to force a retry/rebase of any.

• chore(deps): update dependency geoalchemy2 to v0.15.1
• chore(deps): update dependency geopandas to v0.14.4
• chore(deps): update dependency owslib to v0.31.0
• chore(deps): update dependency psycopg2 to v2.9.9
• chore(deps): update dependency pytest-cov to v2.12.1
• chore(deps): update dependency requests to v2.32.3
• Click on this checkbox to rebase all open PRs at once

Detected dependencies

dockerfile

Dockerfile

• python 3.9

github-actions

.github/workflows/release.yml

• actions/checkout v4
• actions/setup-node v4
• actions/checkout v4
• docker/metadata-action v4
• docker/login-action v2
• docker/build-push-action v3
• actions/checkout v3
• docker/login-action v2
• docker/metadata-action v4
• docker/build-push-action v3

npm

package.json

• ora 8.0.1
• @technologiestiftung/eslint-config 0.1.4
• @technologiestiftung/prettier-config 1.0.0
• @technologiestiftung/semantic-release-config 1.2.4
• @types/geojson 7946.0.14
• @types/node 20.12.7
• globals 15.0.0
• postgres 3.4.4
• prettier 3.2.5
• semantic-release 23.0.6
• tsx 4.7.2
• typescript 5.4.5

nvm

.nvmrc

• node v20.12.2

pip_requirements

requirements.txt

• owslib ==0.23.0
• requests ==2.25.1
• pandas ==2.0.3
• geopandas ==0.13.2
• Shapely ==1.7.1
• pytest ==6.2.2
• pytest-cov ==2.11.1
• psycopg2 ==2.8.4
• SQLAlchemy ==1.4.52
• geoalchemy2 ==0.8.4

---

• Check this box to trigger a request for Renovate to run again on this repository

Action Required: Fix Renovate Configuration

There is an error with this repository's Renovate configuration that needs to be fixed. As a precaution, Renovate will stop PRs until it is resolved.

Error type: Preset package is missing a renovate-config entry (@inpyjamas)

Import script fails at writing `pflanzjahr` to database

The Import script (main.py) expects the pflanzjahr column to be of type text, but it is actually of type integer.

psycopg2.errors.DatatypeMismatch: column "pflanzjahr" is of type integer but expression is of type text
LINE 1: ... gattung = tree_updates_tmp.gattung, pflanzjahr = tree_updat...
                                                             ^
HINT:  You will need to rewrite or cast the expression.

Probably related to this change in the API: technologiestiftung/giessdenkiez-de-postgres-api#186