[BW] Use data from Geoportal

[k-nut]

The ministry of education of Baden-Württemberg seems to provide an INSPIRE WFS service for school locations. The output is a bit more convoluted than we are used to from other states but I think it would still be worth it to switch (especially since we would get reliable geo-locations): Start URL could be https://gis.kultus-bw.de/geoserver/us-govserv/ows?service=WFS&request=GetFeature&typeNames=us-govserv%3AGovernmentalService&outputFormat=application%2Fjson

GetCapabilities for the endpoint is: https://gis.kultus-bw.de/geoserver/us-govserv/ows?service=WFS&request=GetCapabilities

[vizsim]

According to the frontpage there are ~26,000 schools in BW:

However, the INSPIRE WFS endpoint suggests an expected number of schools: ~5,700 schools

There is a clear discrepancy between the two sources. It is currently unclear whether this is caused by the legacy scraper or by incorrect handling of the WFS data.

[k-nut]

This is definitely a problem with us not deleting data. No way BW has so many more schools than all other states.

With our current scraper, we unfortunately cannot extract stable ids for all schools since not all of them have a Dienststellennummer and we fall back to the uuid from the WFS for those schools that don't this leads to us having 20 duplicates of Staatliches Schulamt Göppingen right now for example. It seems to me like those uuids do change across runs unfortunately.

I think it would make sense to not rely on the uuid but to instead try to fallback to another identifier that should be unique (maybe phone number or in the worst case the name itself).

[tordans]

It seems to me like those uuids do change across runs unfortunately.

I think it would make sense to not rely on the uuid but to instead try to fallback to another identifier that should be unique (maybe phone number or in the worst case the name itself).

I thought about creating a hash from some properties that are more stable than others, like a cleaned name, school type, and lat/lng (rounded to ~1km; if present). This should bring us close to a stable, relevant ID.

However, this still leaves the case that we want to remove all data that we don't have anymore.
Could we just clear all data on each run and only have the fresh data?
Or could we introduce a "last seen" DateTime property that allows to filter schools that are likely closed because we did not see them for a year in the source data…?

---

Ref osmberlin/osm-schulabgleich#2

[k-nut]

We already haven update_timestamp field in the schools which is exposed in the API and the CSV files. This should let you filter out those that are too old. (I vaguely remember that this time stamp might only be set on updates, not on create which would be a bug. That might have been fixed in the meantime though...)
I guess this is something that you could work with already?

I have also manually deleted some old data a while ago but we don't have a great process around this yet. I might do that again for BW since it certainly is wrong that we have so many duplicates right now...

[tordans]

(I vaguely remember that this time stamp might only be set on updates, not on create which would be a bug. That might have been fixed in the meantime though...)

According to my LLM code inspection the field is set on create and update. But there are are many entries without this value in the data which should be the once that where created and never deleted before that was introduced. The migrations allows the field to be empty / did not backfill.

---

For https://github.com/osmberlin/osm-schul-abgleich/ I will now remove all without a timestamp and all with a timestamp older than one year.

FYI the results of this change are:

Metric	Value	Meaning
removedTooOld	310	update_timestamp parseable but older than 12 months (vs run time).
removedMissingTimestamp	22 075	No usable update_timestamp (empty / null in CSV).
removedUnparseable	0	Non-empty strings that don’t parse as dates.
kept	32 750	Rows that passed the filter and feed the rest of the pipeline.

BW/SH are the two where most data is cleaned up.

[k-nut]

I have deleted all schools with the BW-UUID prefix from the database now and created a new export. This should give you much fewer duplicates. Let me know if you see something that still needs fixing.