Ik heb de CHB data:
select * from CHB
"ref" "IFOPT" "Haltenaam" "RD_x" "RD_y" "lat" "lon"
"1" "15005330" "Leie" "232435" "557220" "52.99637664752450" "6.54057291373564"
"2" "64310190" "Kapelweg" "150006" "374156" "51.35658750670460" "5.31550748713737"
"3" "64310200" "Kapelweg" "150005" "374149" "51.35652457690170" "5.31549322931864"
"4" "20630300" "Hidaard" "169417" "565802" "53.07886532454360" "5.60234676759784"
"5" "20630310" "Hidaard" "169414" "565806" "53.07890134857550" "5.60230217872840"
Ik heb de OSM data:
"ref" "id" "lat" "lon" "user" "name" "public_transport" "refIFOPT"
"1" "123477" "51.23874840000000" "4.06093980000000" "escada" "" "stop_position" ""
"2" "160300" "51.47103800000000" "7.19213130000000" "Andy78_DE" "Heusnerstraße" "stop_position" "de:5911:5335:0:02"
"3" "361716" "50.92334920000000" "6.94350580000000" "krza" "Eifelplatz" "stop_position" ""
"4" "365842" "50.93391330000000" "7.03727190000000" "berndw" "Buchheimer Weg" "stop_position" ""
"5" "403582" "51.50973710000000" "7.21103340000000" "Andy78_DE" "Riemke Markt" "stop_position" "de:5911:5132:2:05"
"6" "442622" "50.97883400000000" "7.00295710000000" "ohligser" "Am Faulbach" "stop_position" ""
"7" "443040" "50.97588690000000" "6.85803900000000" "thoroe" "Nattermannallee" "stop_position" ""
"8" "443844" "50.93077180000000" "6.95103480000000" "hsimpson" "Rothgerberbach/Poststraße" "stop_position" ""
"9" "443853" "50.93627430000000" "6.94818240000000" "Martin2009" "Neumarkt" "stop_position" ""
Gecombineerd met afstand (even alleen die met naam Kapelweg genomen):
select o.ref osm_ref, o.id, o.name, o.lat osm_lat, o.lon osm_lon,
c.ref chb_ref, c.ifopt, c.lat chb_lat, c.lon chb_lon,
pow(pow((o.lat-c.lat)*110.574,2) + pow(111.35*(o.lon-c.lon)*COS(((o.lat+c.lat)/2)*(PI()/180)),2),0.5) as distance
from OSMhaltes o, CHB c
where o.name = c.haltenaam
"osm_ref" "id" "name" "osm_lat" "osm_lon" "chb_ref" "ifopt" "chb_lat" "chb_lon" "distance"
"14616" "431408168" "Kapelweg" "51.50909890000000" "5.38057410000000" "2" "64310190" "51.35658750670460" "5.31550748713737" "17.45822585759118"
"22884" "617835306" "Kapelweg" "52.13764550000000" "5.37297650000000" "2" "64310190" "51.35658750670460" "5.31550748713737" "86.45553499490778"
"22885" "617835307" "Kapelweg" "52.13694420000000" "5.37101940000000" "2" "64310190" "51.35658750670460" "5.31550748713737" "86.37198886975507"
"23744" "621793827" "Kapelweg" "51.35652410000000" "5.31557570000000" "2" "64310190" "51.35658750670460" "5.31550748713737" "0.008464852478539415"
"31969" "661435315" "Kapelweg" "53.01627210000000" "6.21074960000000" "2" "64310190" "51.35658750670460" "5.31550748713737" "193.42714439684156"
"31970" "661435316" "Kapelweg" "53.01620760000000" "6.21180320000000" "2" "64310190" "51.35658750670460" "5.31550748713737" "193.4431311855096"
"89448" "2147483647" "Kapelweg" "51.35678020000000" "5.31552290000000" "2" "64310190" "51.35658750670460" "5.31550748713737" "0.021333805277325164"
"123907" "2147483647" "Kapelweg" "51.50914530000000" "5.38028860000000" "2" "64310190" "51.35658750670460" "5.31550748713737" "17.458065519502803"
"134470" "2147483647" "Kapelweg" "52.13769320000000" "5.37290470000000" "2" "64310190" "51.35658750670460" "5.31550748713737" "86.46057705623625"
"134471" "2147483647" "Kapelweg" "52.13690390000000" "5.37106470000000" "2" "64310190" "51.35658750670460" "5.31550748713737" "86.36767563155604"
"134704" "2147483647" "Kapelweg" "51.50914020000000" "5.38055830000000" "2" "64310190" "51.35658750670460" "5.31550748713737" "17.46235291468986"
"134705" "2147483647" "Kapelweg" "51.50909700000000" "5.38030240000000" "2" "64310190" "51.35658750670460" "5.31550748713737" "17.45315255597968"
"137204" "2147483647" "Kapelweg" "51.35658610000000" "5.31543350000000" "2" "64310190" "51.35658750670460" "5.31550748713737" "0.0051470397973646664"
"138212" "2147483647" "Kapelweg" "51.35670800000000" "5.31553350000000" "2" "64310190" "51.35658750670460" "5.31550748713737" "0.013445647144957183"
"14616" "431408168" "Kapelweg" "51.50909890000000" "5.38057410000000" "3" "64310200" "51.35652457690170" "5.31549322931864" "17.4652042341334"
"22884" "617835306" "Kapelweg" "52.13764550000000" "5.37297650000000" "3" "64310200" "51.35652457690170" "5.31549322931864" "86.46253125826945"
"22885" "617835307" "Kapelweg" "52.13694420000000" "5.37101940000000" "3" "64310200" "51.35652457690170" "5.31549322931864" "86.37898410997609"
"23744" "621793827" "Kapelweg" "51.35652410000000" "5.31557570000000" "3" "64310200" "51.35652457690170" "5.31549322931864" "0.005734841693818557"
"31969" "661435315" "Kapelweg" "53.01627210000000" "6.21074960000000" "3" "64310200" "51.35652457690170" "5.31549322931864" "193.43406753709334"
"31970" "661435316" "Kapelweg" "53.01620760000000" "6.21180320000000" "3" "64310200" "51.35652457690170" "5.31549322931864" "193.450053891627"
"89448" "2147483647" "Kapelweg" "51.35678020000000" "5.31552290000000" "3" "64310200" "51.35652457690170" "5.31549322931864" "0.028340465238296676"
"123907" "2147483647" "Kapelweg" "51.50914530000000" "5.38028860000000" "3" "64310200" "51.35652457690170" "5.31549322931864" "17.465044873769756"
"134470" "2147483647" "Kapelweg" "52.13769320000000" "5.37290470000000" "3" "64310200" "51.35652457690170" "5.31549322931864" "86.46757327945275"
"134471" "2147483647" "Kapelweg" "52.13690390000000" "5.37106470000000" "3" "64310200" "51.35652457690170" "5.31549322931864" "86.37467089787715"
"134704" "2147483647" "Kapelweg" "51.50914020000000" "5.38055830000000" "3" "64310200" "51.35652457690170" "5.31549322931864" "17.469331398869063"
"134705" "2147483647" "Kapelweg" "51.50909700000000" "5.38030240000000" "3" "64310200" "51.35652457690170" "5.31549322931864" "17.46013180058202"
"137204" "2147483647" "Kapelweg" "51.35658610000000" "5.31543350000000" "3" "64310200" "51.35652457690170" "5.31549322931864" "0.007970479022567667"
"138212" "2147483647" "Kapelweg" "51.35670800000000" "5.31553350000000" "3" "64310200" "51.35652457690170" "5.31549322931864" "0.02047421950925622"
"14616" "431408168" "Kapelweg" "51.50909890000000" "5.38057410000000" "6286" "50423160" "52.13773696194390" "5.37302287075493" "69.5129678494289"
"22884" "617835306" "Kapelweg" "52.13764550000000" "5.37297650000000" "6286" "50423160" "52.13773696194390" "5.37302287075493" "0.010598224023196549"
"22885" "617835307" "Kapelweg" "52.13694420000000" "5.37101940000000" "6286" "50423160" "52.13773696194390" "5.37302287075493" "0.16257997157490447"
"23744" "621793827" "Kapelweg" "51.35652410000000" "5.31557570000000" "6286" "50423160" "52.13773696194390" "5.37302287075493" "86.47257241859361"
"31969" "661435315" "Kapelweg" "53.01627210000000" "6.21074960000000" "6286" "50423160" "52.13773696194390" "5.37302287075493" "112.4727758889194"
"31970" "661435316" "Kapelweg" "53.01620760000000" "6.21180320000000" "6286" "50423160" "52.13773696194390" "5.37302287075493" "112.50258792093676"
"89448" "2147483647" "Kapelweg" "51.35678020000000" "5.31552290000000" "6286" "50423160" "52.13773696194390" "5.37302287075493" "86.44445047296342"
"123907" "2147483647" "Kapelweg" "51.50914530000000" "5.38028860000000" "6286" "50423160" "52.13773696194390" "5.37302287075493" "69.50769321830757"
"134470" "2147483647" "Kapelweg" "52.13769320000000" "5.37290470000000" "6286" "50423160" "52.13773696194390" "5.37302287075493" "0.009414828883145574"
"134471" "2147483647" "Kapelweg" "52.13690390000000" "5.37106470000000" "6286" "50423160" "52.13773696194390" "5.37302287075493" "0.1624657608871719"
"134704" "2147483647" "Kapelweg" "51.50914020000000" "5.38055830000000" "6286" "50423160" "52.13773696194390" "5.37302287075493" "69.50839314710093"
"134705" "2147483647" "Kapelweg" "51.50909700000000" "5.38030240000000" "6286" "50423160" "52.13773696194390" "5.37302287075493" "69.51304064510092"
"137204" "2147483647" "Kapelweg" "51.35658610000000" "5.31543350000000" "6286" "50423160" "52.13773696194390" "5.37302287075493" "86.46617348221875"
"138212" "2147483647" "Kapelweg" "51.35670800000000" "5.31553350000000" "6286" "50423160" "52.13773696194390" "5.37302287075493" "86.45239215889096"
"14616" "431408168" "Kapelweg" "51.50909890000000" "5.38057410000000" "6287" "50423170" "52.13694576711950" "5.37102201284603" "69.42665218702581"
"22884" "617835306" "Kapelweg" "52.13764550000000" "5.37297650000000" "6287" "50423170" "52.13694576711950" "5.37102201284603" "0.1543668352893318"
"22885" "617835307" "Kapelweg" "52.13694420000000" "5.37101940000000" "6287" "50423170" "52.13694576711950" "5.37102201284603" "0.0002488271604365112"
"23744" "621793827" "Kapelweg" "51.35652410000000" "5.31557570000000" "6287" "50423170" "52.13694576711950" "5.37102201284603" "86.37896609200396"
"31969" "661435315" "Kapelweg" "53.01627210000000" "6.21074960000000" "6287" "50423170" "52.13694576711950" "5.37102201284603" "112.61685714729991"
"31970" "661435316" "Kapelweg" "53.01620760000000" "6.21180320000000" "6287" "50423170" "52.13694576711950" "5.37102201284603" "112.64671194801333"
"89448" "2147483647" "Kapelweg" "51.35678020000000" "5.31552290000000" "6287" "50423170" "52.13694576711950" "5.37102201284603" "86.35083657498667"
"123907" "2147483647" "Kapelweg" "51.50914530000000" "5.38028860000000" "6287" "50423170" "52.13694576711950" "5.37102201284603" "69.42133848188134"
"134470" "2147483647" "Kapelweg" "52.13769320000000" "5.37290470000000" "6287" "50423170" "52.13694576711950" "5.37102201284603" "0.1529256263256029"
"134471" "2147483647" "Kapelweg" "52.13690390000000" "5.37106470000000" "6287" "50423170" "52.13694576711950" "5.37102201284603" "0.005471998951791245"
"134704" "2147483647" "Kapelweg" "51.50914020000000" "5.38055830000000" "6287" "50423170" "52.13694576711950" "5.37102201284603" "69.4220753934387"
"134705" "2147483647" "Kapelweg" "51.50909700000000" "5.38030240000000" "6287" "50423170" "52.13694576711950" "5.37102201284603" "69.42668771524433"
"137204" "2147483647" "Kapelweg" "51.35658610000000" "5.31543350000000" "6287" "50423170" "52.13694576711950" "5.37102201284603" "86.37255152457949"
"138212" "2147483647" "Kapelweg" "51.35670800000000" "5.31553350000000" "6287" "50423170" "52.13694576711950" "5.37102201284603" "86.35877993891829"
"14616" "431408168" "Kapelweg" "51.50909890000000" "5.38057410000000" "9326" "64200210" "51.50915613876340" "5.38036169639699" "0.016023201883001072"
"22884" "617835306" "Kapelweg" "52.13764550000000" "5.37297650000000" "9326" "64200210" "51.50915613876340" "5.38036169639699" "69.49644136466115"
"22885" "617835307" "Kapelweg" "52.13694420000000" "5.37101940000000" "9326" "64200210" "51.50915613876340" "5.38036169639699" "69.42001483747767"
"23744" "621793827" "Kapelweg" "51.35652410000000" "5.31557570000000" "9326" "64200210" "51.50915613876340" "5.38036169639699" "17.46608617978806"
"31969" "661435315" "Kapelweg" "53.01627210000000" "6.21074960000000" "9326" "64200210" "51.50915613876340" "5.38036169639699" "175.99465066628073"
"31970" "661435316" "Kapelweg" "53.01620760000000" "6.21180320000000" "9326" "64200210" "51.50915613876340" "5.38036169639699" "176.01101343780365"
"89448" "2147483647" "Kapelweg" "51.35678020000000" "5.31552290000000" "9326" "64200210" "51.50915613876340" "5.38036169639699" "17.43966695608055"
"123907" "2147483647" "Kapelweg" "51.50914530000000" "5.38028860000000" "9326" "64200210" "51.50915613876340" "5.38036169639699" "0.005205646622122715"
"134470" "2147483647" "Kapelweg" "52.13769320000000" "5.37290470000000" "9326" "64200210" "51.50915613876340" "5.38036169639699" "69.50175191564813"
"134471" "2147483647" "Kapelweg" "52.13690390000000" "5.37106470000000" "9326" "64200210" "51.50915613876340" "5.38036169639699" "69.41553009009404"
"134704" "2147483647" "Kapelweg" "51.50914020000000" "5.38055830000000" "9326" "64200210" "51.50915613876340" "5.38036169639699" "0.013738747688491143"
"134705" "2147483647" "Kapelweg" "51.50909700000000" "5.38030240000000" "9326" "64200210" "51.50915613876340" "5.38036169639699" "0.007723253224893163"
"137204" "2147483647" "Kapelweg" "51.35658610000000" "5.31543350000000" "9326" "64200210" "51.50915613876340" "5.38036169639699" "17.462006439697152"
"138212" "2147483647" "Kapelweg" "51.35670800000000" "5.31553350000000" "9326" "64200210" "51.50915613876340" "5.38036169639699" "17.447191145436094"
"14616" "431408168" "Kapelweg" "51.50909890000000" "5.38057410000000" "9327" "64200220" "51.50908424659900" "5.38066420159143" "0.00645111905112207"
"22884" "617835306" "Kapelweg" "52.13764550000000" "5.37297650000000" "9327" "64200220" "51.50908424659900" "5.38066420159143" "69.50454592944047"
"22885" "617835307" "Kapelweg" "52.13694420000000" "5.37101940000000" "9327" "64200220" "51.50908424659900" "5.38066420159143" "69.42815984137938"
"23744" "621793827" "Kapelweg" "51.35652410000000" "5.31557570000000" "9327" "64200220" "51.50908424659900" "5.38066420159143" "17.463827282911012"
"31969" "661435315" "Kapelweg" "53.01627210000000" "6.21074960000000" "9327" "64200220" "51.50908424659900" "5.38066420159143" "175.9955648777458"
"31970" "661435316" "Kapelweg" "53.01620760000000" "6.21180320000000" "9327" "64200220" "51.50908424659900" "5.38066420159143" "176.01191885970678"
"89448" "2147483647" "Kapelweg" "51.35678020000000" "5.31552290000000" "9327" "64200220" "51.50908424659900" "5.38066420159143" "17.437421931585096"
"123907" "2147483647" "Kapelweg" "51.50914530000000" "5.38028860000000" "9327" "64200220" "51.50908424659900" "5.38066420159143" "0.02689154266717594"
"134470" "2147483647" "Kapelweg" "52.13769320000000" "5.37290470000000" "9327" "64200220" "51.50908424659900" "5.38066420159143" "69.50985794443083"
"134471" "2147483647" "Kapelweg" "52.13690390000000" "5.37106470000000" "9327" "64200220" "51.50908424659900" "5.38066420159143" "69.42367417503762"
"134704" "2147483647" "Kapelweg" "51.50914020000000" "5.38055830000000" "9327" "64200220" "51.50908424659900" "5.38066420159143" "0.009599186536415595"
"134705" "2147483647" "Kapelweg" "51.50909700000000" "5.38030240000000" "9327" "64200220" "51.50908424659900" "5.38066420159143" "0.025113624241714566"
"137204" "2147483647" "Kapelweg" "51.35658610000000" "5.31543350000000" "9327" "64200220" "51.50908424659900" "5.38066420159143" "17.45976200576114"
"138212" "2147483647" "Kapelweg" "51.35670800000000" "5.31553350000000" "9327" "64200220" "51.50908424659900" "5.38066420159143" "17.444942573810227"
"14616" "431408168" "Kapelweg" "51.50909890000000" "5.38057410000000" "12478" "24660300" "53.01621575103330" "6.21181070873574" "176.01337432423142"
"22884" "617835306" "Kapelweg" "52.13764550000000" "5.37297650000000" "12478" "24660300" "53.01621575103330" "6.21181070873574" "112.51396462478203"
"22885" "617835307" "Kapelweg" "52.13694420000000" "5.37101940000000" "12478" "24660300" "53.01621575103330" "6.21181070873574" "112.64798315575378"
"23744" "621793827" "Kapelweg" "51.35652410000000" "5.31557570000000" "12478" "24660300" "53.01621575103330" "6.21181070873574" "193.4493386336744"
"31969" "661435315" "Kapelweg" "53.01627210000000" "6.21074960000000" "12478" "24660300" "53.01621575103330" "6.21181070873574" "0.07135293486990572"
"31970" "661435316" "Kapelweg" "53.01620760000000" "6.21180320000000" "12478" "24660300" "53.01621575103330" "6.21181070873574" "0.001032145363638845"
"89448" "2147483647" "Kapelweg" "51.35678020000000" "5.31552290000000" "12478" "24660300" "53.01621575103330" "6.21181070873574" "193.42355902387314"
"123907" "2147483647" "Kapelweg" "51.50914530000000" "5.38028860000000" "12478" "24660300" "53.01621575103330" "6.21181070873574" "176.0147705111786"
"134470" "2147483647" "Kapelweg" "52.13769320000000" "5.37290470000000" "12478" "24660300" "53.01621575103330" "6.21181070873574" "112.5118462580791"
"134471" "2147483647" "Kapelweg" "52.13690390000000" "5.37106470000000" "12478" "24660300" "53.01621575103330" "6.21181070873574" "112.65029429517776"
"134704" "2147483647" "Kapelweg" "51.50914020000000" "5.38055830000000" "12478" "24660300" "53.01621575103330" "6.21181070873574" "176.00938869383555"
"134705" "2147483647" "Kapelweg" "51.50909700000000" "5.38030240000000" "12478" "24660300" "53.01621575103330" "6.21181070873574" "176.01953402826004"
"137204" "2147483647" "Kapelweg" "51.35658610000000" "5.31543350000000" "12478" "24660300" "53.01621575103330" "6.21181070873574" "193.44589217819114"
"138212" "2147483647" "Kapelweg" "51.35670800000000" "5.31553350000000" "12478" "24660300" "53.01621575103330" "6.21181070873574" "193.43091930635907"
"14616" "431408168" "Kapelweg" "51.50909890000000" "5.38057410000000" "12479" "24660310" "53.01626807040710" "6.21073878183819" "175.99534335979857"
"22884" "617835306" "Kapelweg" "52.13764550000000" "5.37297650000000" "12479" "24660310" "53.01626807040710" "6.21073878183819" "112.48236959146607"
"22885" "617835307" "Kapelweg" "52.13694420000000" "5.37101940000000" "12479" "24660310" "53.01626807040710" "6.21073878183819" "112.616343743877"
"23744" "621793827" "Kapelweg" "51.35652410000000" "5.31557570000000" "12479" "24660310" "53.01626807040710" "6.21073878183819" "193.43168365548175"
"31969" "661435315" "Kapelweg" "53.01627210000000" "6.21074960000000" "12479" "24660310" "53.01626807040710" "6.21073878183819" "0.0008506963249668266"
"31970" "661435316" "Kapelweg" "53.01620760000000" "6.21180320000000" "12479" "24660310" "53.01626807040710" "6.21073878183819" "0.0716149001372821"
"89448" "2147483647" "Kapelweg" "51.35678020000000" "5.31552290000000" "12479" "24660310" "53.01626807040710" "6.21073878183819" "193.4058996134406"
"123907" "2147483647" "Kapelweg" "51.50914530000000" "5.38028860000000" "12479" "24660310" "53.01626807040710" "6.21073878183819" "175.9967314620942"
"134470" "2147483647" "Kapelweg" "52.13769320000000" "5.37290470000000" "12479" "24660310" "53.01626807040710" "6.21073878183819" "112.48024726229346"
"134471" "2147483647" "Kapelweg" "52.13690390000000" "5.37106470000000" "12479" "24660310" "53.01626807040710" "6.21073878183819" "112.61865770398916"
"134704" "2147483647" "Kapelweg" "51.50914020000000" "5.38055830000000" "12479" "24660310" "53.01626807040710" "6.21073878183819" "175.99135674545897"
"134705" "2147483647" "Kapelweg" "51.50909700000000" "5.38030240000000" "12479" "24660310" "53.01626807040710" "6.21073878183819" "176.00149600807174"
"137204" "2147483647" "Kapelweg" "51.35658610000000" "5.31543350000000" "12479" "24660310" "53.01626807040710" "6.21073878183819" "193.42823303637994"
"138212" "2147483647" "Kapelweg" "51.35670800000000" "5.31553350000000" "12479" "24660310" "53.01626807040710" "6.21073878183819" "193.41326104322985"
Ik kan wel een query verzinnen die de kleinste afstand met daarbijbehorende IFOPT vindt:
select min(d.distance2) dist, ifopt, name
from (
select o.ref osm_ref, o.id, o.name, o.lat osm_lat, o.lon osm_lon,
c.ref chb_ref, c.ifopt, c.lat chb_lat, c.lon chb_lon,
pow(pow((o.lat-c.lat)*110.574,2) + pow(111.35*(o.lon-c.lon)*COS(((o.lat+c.lat)/2)*(PI()/180)),2),0.5) as distance2
from OSMhaltes o, CHB c
where o.name = c.haltenaam
and c.haltenaam = 'Kapelweg') d
group by ifopt, name
"dist" "ifopt" "name"
"0.001032145363638845" "24660300" "Kapelweg"
"0.0008506963249668266" "24660310" "Kapelweg"
"0.009414828883145574" "50423160" "Kapelweg"
"0.0002488271604365112" "50423170" "Kapelweg"
"0.005205646622122715" "64200210" "Kapelweg"
"0.00645111905112207" "64200220" "Kapelweg"
"0.0051470397973646664" "64310190" "Kapelweg"
"0.005734841693818557" "64310200" "Kapelweg"
Maar hoe kom ik van daaruit nu bij de dichstsbijzijnde OSM node?