davidshumway
commented
2 years ago Assuming distances are precomputed between every building and weather station, one attempt might look like:
# For every observation from one building, returns observation along with up to 15 associated
# weather observations.
SELECT ?building1 ?buildingValue ?day1 ?minDist ?weatherProperty1 ?weatherValue WHERE {
?weatherObs a ex:weatherObservation ;
ex:hasProperty ?weatherProperty2 ;
ex:hasSimpleResult ?weatherValue ;
ex:obsDay ?day2 ;
ex:hasDistanceToBuilding [
ex:distance ?dist2 ;
ex:building ?building2 ].
FILTER(?dist2 = ?minDist) .
FILTER(?day2 = ?day1) .
FILTER(?building2 = ?building1) .
FILTER(?weatherProperty2 = ?weatherProperty1) .
# Selects minimum distance (WS to building) on given day for every
# distinct weatherProperty.
{SELECT MIN(?dist) AS ?minDist ?day1 ?building1 ?buildingValue ?weatherProperty1 WHERE {
?weatherObs a ex:weatherObservation ;
ex:obsWeatherProperty ?weatherProperty1 ;
ex:obsDay ?day2 ;
ex:hasDistanceToBuilding [
ex:distance ?dist ;
ex:building ?building2 ] .
FILTER(?building2 = ?building1) .
FILTER(?day2 = ?day1) .
# Selects every daily observation for every building.
{SELECT ?day1 ?building1 ?buildingValue WHERE {
?building1 a ex:buildingObservation ;
ex:hasSimpleResult ?buildingValue ;
ex:obsDay ?day1 .
}}
} GROUP BY ?day1 ?building1 ?buildingValue ?weatherProperty1 }
}
HughWilliams
TallTed
kidehen
Time period: 10 years. Location: State of CA.
Dataset 1: 300 NOAA weather stations (located in CA). Daily summary of up to 15 variables (e.g. avg air temperature, avg wind speed, max air temperature). Not every variable present per day per station. Roughly 6 million observations in total. Dataset 2: 600 buildings (also located in CA). Single daily observation per building. Most buildings make around 100 observations per year. Roughly 200,000 observations in total.
On average, each building has 50 weather stations within a 200KM radius.
Spatiotemporal merge: Merge nearest weather variable to a building on a given day up to a maximum distance of 200KM per variable.
Example: Building 1 has it's first daily observation on Jan. 4, 2000. The nearest weather station (1KM) has a daily summary of 10 variables. The next nearest weather station (2KM) has 3 other variables. One more weather variable is only at a weather station that is 100KM away. The final weather variable is not found at any station within 200KM.
Final result: The final result should ideally be one row per building observation with an attached column for each weather variable (or something like NaN if no weather variable is available within 200KM). (The ideal result would be outputting this final query e.g. in CSV format which could then be fed into a dask pandas dataframe for use in machine learning tasks.)
Challenges: It would be easy enough to match the nearest weather station to any given building as this is only 300 x 600 distinct distance comparisons in total (180,000). However, in the worst case, if all 15 variables were matched against the average 50 nearest weather stations for every building observation present (200k), that would be 15x50x200000 = 150,000,000 distance calculations.
Any thoughts?