Wrong route selection in case of stations on different networks, but same names

[kaestli]

There are two stations "EF02", one after 2021 in the 2D network in Germany, one around 2012 in the CL network in Greece:
http://eida-federator.ethz.ch/fdsnws/station/1/query?station=EF02&format=text

a query asking for the time window of the first, but the coordinates of the second, will erraneously return the first (which does not match coordinate requirements)
http://eida-federator.ethz.ch/fdsnws/station/1/query?starttime=2022-06-01T00%3A00%3A00&endtime=2022-06-10T00%3A00%3A00&minlatitude=38.4137&maxlatitude=38.4138&minlongitude=21.94&maxlongitude=21.95&format=text&level=channel&nodata=404

[kaestli]

This is a bug happening when caching or storing the available route epochs in stationlite; already the content of the routing DB is wrong:

[stationlite db query triggered by request:]
SELECT DISTINCT channelepoch.code AS channelepoch_code, channelepoch.locationcode AS channelepoch_locationcode, station.code AS station_code, network.code AS network_code, epoch.starttime AS epoch_starttime, epoch.endtime AS epoch_endtime, routing.starttime AS routing_starttime, routing.endtime AS routing_endtime, endpoint.url AS endpoint_url
FROM channelepoch JOIN epoch ON channelepoch.epoch_ref = epoch.id
JOIN epochtype ON epoch.epochtype_ref = epochtype.id
JOIN routing ON routing.epoch_ref = epoch.id
JOIN network ON channelepoch.network_ref = network.id JOIN station ON channelepoch.station_ref = station.id
JOIN stationepoch ON stationepoch.station_ref = station.id JOIN endpoint ON routing.endpoint_ref = endpoint.id JOIN service ON endpoint.service_ref = service.id
WHERE network.code LIKE '%'
AND station.code LIKE '%'
AND channelepoch.code LIKE '%'
AND channelepoch.locationcode LIKE '%'
AND service.name = 'station' AND epochtype.type = 'CHANNEL' AND
stationepoch.latitude >= 38.41301 AND stationepoch.latitude <= 38.41401 AND stationepoch.longitude >= 21.94 AND stationepoch.longitude <= 21.95
AND (epoch.endtime > '2022-06-03T00:00:00'::timestamp OR epoch.endtime IS NULL)
AND epoch.starttime < '2022-06-12T00:00:00'::timestamp;

[response]
channelepoch_code | channelepoch_locationcode | station_code | network_code | epoch_starttime | epoch_endtime | routing_starttime | routing_endtime | endpoint_url
-------------------+---------------------------+--------------+--------------+---------------------+---------------------+---------------------+---------------------+-----------------------------------------------------
HHE | 00 | EF02 | 2D | 2021-10-07 00:00:00 | 2032-12-31 00:00:00 | 2021-01-01 00:00:00 | 2032-12-31 00:00:00 | http://geofon.gfz-potsdam.de/fdsnws/station/1/query
HHN | 00 | EF02 | 2D | 2021-10-07 00:00:00 | 2032-12-31 00:00:00 | 2021-01-01 00:00:00 | 2032-12-31 00:00:00 | http://geofon.gfz-potsdam.de/fdsnws/station/1/query
HHZ | 00 | EF02 | 2D | 2021-10-07 00:00:00 | 2032-12-31 00:00:00 | 2021-01-01 00:00:00 | 2032-12-31 00:00:00 | http://geofon.gfz-potsdam.de/fdsnws/station/1/query
(3 rows)

[kaestli]

Note this issue is fundamental to the data model of stationlite; stationlite assumes stationcodes to be unique.

As a result, for non-unique station codes in case of data/metadata selection based on station properties (location, epoch in case no stream epochs are defined), response may refer to the wrong station.

This requires major refactoring.

[kaestli]

The problem affects 4884 of roughly 21000 stations, of which 527 in permanent networks. Stations like "A02", "A03" show up in up to 8 (mostly temporary) networks.