SQL JOIN naar de dichtstbijzijnde waarde (in R)

[breew]

Voor een eigen project (ik speel graag met data) ben ik in Rstudio bezig om een database met locatiegegevens te koppelen aan een database met verkeersongevallen. Doel is om de boel mooi op een kaartje weer te kunnen geven.

De tabellen zien er nu als volgt uit:

code:

tabel ongevallen (met ongevallen + locatie)
ID   INCIDENTID   WEGNUMMER   RIJBAAN   HECTOMETERAANDUIDING   

tabel hm (met hectometrering)
ID  WVK_ID   WEGNUMMER   RIJBAAN   HECTOMETERAANDUIDING   LATITUDE   LONGITUDE

Middels een LEFT JOIN heb ik deze samengevoegd (ik wil namelijk bij elk ongeval een de Latitude en Longitude hebben).

code:

strSQL<-paste("SELECT *", 
+                 "FROM ongevallen",
+                 "LEFT JOIN hm",
+                 "ON (ongevallen.WEGNUMMER =hm.WEGNUMMER AND",
+                 "ongevallen.RIJBAAN = hm.RIJBAAN AND",
+                 "ongevallen.HECTOMETERAANDUIDING = hm.HECTOMETERAANDUIDING)"
+   )

Nu krijg ik precies wat ik wil....

ECHTER...

Bij sommige ongevallen is door een operator een hectometrering ingevuld die in het nwb niet (exact) bestaat. Zo is er bijvoorbeeld een ongeval gemeld op een afrit bij 166.6 (in tabel ongevallen), terwijl in het nwb (in tabel hm) die rijbaan pas begint bij locatie 166.7. Dat ongeval kan nu dus niet worden gematcht aan een locatie.

Mijn vraag
Hoe kan ik beide tabellen (ongevallen en hm) joinen, waarbij ik hectometers van ongevallen zo precies mogelijk een locatie mee wil geven. Mocht de locatie uit [ongevasllen] niet exact bestaan in [hm], join dan op de meest dichtstbijzijnde locatie in [hm].

Ik ben geen expert (begeef nu al 16 uur in R ;-) ), dus met wat ik zelf vind met Google word ik niet echt wijs uit voor mijn situatie. Er zijn wel voorbeelden, maar ik zou bij god niet weten hoe die op mijn situatie te betrekken ;-)

Ik hoop dat iemand mij een beetje Jip-en-Janneke uitleg kan geven (kan wat ik wil überhaupt?), of mij kan doorverwijzen naar (voor mij leesbaar) leesvoer over dit onderwerp.

[Morrar]

Ik zou even die join als tweede stap in je hoofd houden. Eigenlijk gaat het hier om ontbrekende / ongeldige waarden. Je zou een lijst kunnen maken met alle geldige waarden en dan een functie schrijven die alle waarden checkt tegen die lijst. Als de waarde op de lijst voorkomt retourneert de functie dezelfde waarde. Als de waarde niet voorkomt, retourneert de functie de waarde die het dichtstbijzijnde licht (= minimaliseert absolute verschil).

Even snel wat uitgewerkt in R:

S:

# Locations vector
locations = c(167.6, 156.5, 45.4, 53.2, 70.2)

# Valid locations
valid = c(167.7, 156.0, 45.5, 53.2, 70.2)

# Get nearest valid location
get_valid <- function(loc) {
    # Location is valid
    if(loc %in% valid) {
        return(loc)
    }
    
    # Location not valid, find nearest
    else{
        # Find index of nearest element
        nearest = which.min(abs(valid - loc))
        return(valid[nearest])
    }
}

# Find valid locations
valid_locs <-sapply(locations, get_valid)

P.S. Ik ben geen ster in R, dus wellicht kan het nog efficienter... Geef mij maar Python

[ Voor 43% gewijzigd door Morrar op 16-04-2016 19:14 ]

[ajakkes]

Of de melding is niet valide of het punt had wel kunnen bestaan.

In dit geval zou je de punten ook handmatig kunnen toevoegen, of de data verbeteren.

[breew]

Morrar schreef op zaterdag 16 april 2016 @ 18:45:
Ik zou even die join als tweede stap in je hoofd houden. Eigenlijk gaat het hier om ontbrekende / ongeldige waarden. Je zou een lijst kunnen maken met alle geldige waarden en dan een functie schrijven die alle waarden checkt tegen die lijst. Als de waarde op de lijst voorkomt retourneert de functie dezelfde waarde. Als de waarde niet voorkomt, retourneert de functie de waarde die het dichtstbijzijnde licht (= minimaliseert absolute verschil).

Nice, dat is een mooie invalshoek!!!
Ik ga er vannacht/morgen eens voor zitten.

[breew]

ajakkes schreef op zaterdag 16 april 2016 @ 18:49:
Of de melding is niet valide of het punt had wel kunnen bestaan.
In dit geval zou je de punten ook handmatig kunnen toevoegen, of de data verbeteren.

Ook een mogelijkheid, maar wel veel werk.. en zie zegt dat ik het goede "bewerk"?

Ik ga voor de oplossing van Morrar... waarbij ik ga kijken of ik ongevallen die binnen 500m van bestaande hectometrering zijn gelogd, gewoon op die hm mee kan nemen.

[The Eagle]

Het kigt er een beetje aan wat je doel is. Als je met statistiek aan de gang wilt gebruik je andere manieren om ontbrekende data te filteren dan wanneer je echt exacte matches nodig hebt.

Maar wat je in SQL natuurlijk zou kunnen doen is aan beide kanten een trim of een round gebruiken, die de decimaal van de hm afsloopt. Dan heb je alleen de km's, maar dat is voor jouw doel meer dan voldoende lijkt me. Ligt er vwb de syntax alleen even aan welk DBMS je er onder hebt liggen en wat voor veldttype je veld hectometeraanduiding is. Als dat een string is moet je die bij gebruik van een round wellicht naar een intiger casten en is een rtrim makkelijke.
Mssql round: MSDN: ROUND (Transact-SQL)
Oracle round: https://docs.oracle.com/c...2/b14200/functions135.htm

Mssql rtrim: ik zie net dat die alleen spaties weghaalt en das niet sql-92 compliant (en gewoon eigenwijsheid van MS want hoewel een veel voorkomend gebrukk van rtrim, zeker niet de standaard).

Dus ik zou als ik jou was indien nodig casten naar een integer en dan round toepassen

Edit: die laatste regel wordt dus iets als "round(ongevallen.hectometeraanduiding,0) = round(hm.hectometeraanduiding,0)"

[ Voor 5% gewijzigd door The Eagle op 16-04-2016 21:05 ]

[Reinier]

Dat gaat niet helemaal goed The Eagle, 101,4 en 101,5 liggen maar 100m uit elkaar maar komen niet mee in jouw join. Toch?

[The Eagle]

Ik ben meer een Oracle man, maar zie mijn mssql link example E.
Round (123.45678 ,2) levert 123.45 als resultaat, dus round(123.4567 ,0) zou dan 123 op moeten leveren

En round(integer,0) rond als het goed is af op hele getallen. Dus dan worden 101,4 en 101,5 beiden 101. Hoe het bij 101,4 en 101,8 zit weet ik ff niet, die 101,8 zou ineens 102 kunnen worden. Daarom zou ik in dit geval ook liever een trim gebruiken, die sloopt simpelweg de komma en het getal achter de komma er af. Maar dat vind MSSQL dan weer te moeilijk

Maar als TS even aangeeft welke DB ie er onder heeft hangen wil ik best effe kijken of ik de juiste syntax kan vinden

[Morrar]

In mssql is er nog een derde parameter voor nodig: ROUND (12.34, 0, 1). Die laatste parameter zorgt voor weglaten van de decimalen.

[ Voor 26% gewijzigd door Morrar op 16-04-2016 22:31 ]

[Reinier]

Dan matchen 99,9 en 100,0 niet meer.
Een join op abs(x) - abs(y) <= 1km lijkt me dan beter.

[The Eagle]

Kan ook. Je moet hoe dan ook zorgen dat je aan beide kanten de zelfde waarden er uit krijgt, anders kun je niet joinen. Hoe je dat voor mekaar krijgt is vers twee, er zijn meerdere wegen naar Rome

[breew]

The Eagle schreef op zondag 17 april 2016 @ 17:47:
Kan ook. Je moet hoe dan ook zorgen dat je aan beide kanten de zelfde waarden er uit krijgt, anders kun je niet joinen. Hoe je dat voor mekaar krijgt is vers twee, er zijn meerdere wegen naar Rome

Allemaal mooie opties. Voor mij als beginner is het "lostrekken" uit de join middels een functie (zoals morrar zegt) het meest overzichtelijk. Maar de rest van de suggesties ga ik ook zeker even op googlen

[Reinier]

The Eagle schreef op zondag 17 april 2016 @ 17:47:
Kan ook. Je moet hoe dan ook zorgen dat je aan beide kanten de zelfde waarden er uit krijgt, anders kun je niet joinen. Hoe je dat voor mekaar krijgt is vers twee, er zijn meerdere wegen naar Rome

Snap ik, maar de decimalen strippen brengt je niet in Rome

[epic007]

Morrar schreef op zaterdag 16 april 2016 @ 18:45:
... de waarde die het dichtstbijzijnde licht (= minimaliseert absolute verschil) ...

Het minimaliseren van het absolute verschil lijkt mij ook de beste oplossing, dit kan je evt. ook al in SQL doen:

SQL:

SELECT TOP 10 * 
FROM ongevallen,
LEFT JOIN hm ON (ongevallen.WEGNUMMER =hm.WEGNUMMER AND ongevallen.RIJBAAN = hm.RIJBAAN)
ORDER BY ABS(ongevallen.HECTOMETERAANDUIDING - hm.HECTOMETERAANDUIDING);

[ Voor 3% gewijzigd door epic007 op 19-04-2016 08:48 ]

[breew]

Ik kwam toevallig dit oude topic van me tegen, en wilde nog even een update (voor toekomstige zoekers) posten. Inmiddels vlieg ik dergelijke problemen aan middels een join vanuit data.table.

Bij een Left Join van data.table x met data.table y,

y[x]

kun je een aantal handige opties meegeven:

dt <- y[x, mult = "first", on = c("wegnummer", "rijbaan", "hectometer"), roll = "nearest"]

• Met mult geef je aan hoe om te gaan met meerdere exacte hits (first = gebruikt alleen de eerste hit, hiermee voorkom ik dat bij meerdere hits rijen worden toegevoegd)
• Met roll kun je, indien geen geen exacte match wordt gevonden, de laatste parameter van 'on' (in dit geval dus hectometer) aanpassen naar de dichtstbijzijnde waarde ("nearest"), en die vervolgens matchen. In het eindresultata kuj je vervolgens eenvoudig de gebruikte afwijking in hectometers berekenen, en bepalen of dit nog acceptabel is.