Node Red_如何把資料庫裏面的地址轉換成經緯度

[ph7jys]

感謝使用 Node.js Taiwan AMA，以下附上簡單提問範例供參考，請把內容改成你自己遇到的問題

目的

我們要做一個網站，然後用node red 抓資料，再把資料丟到資料庫裡，資料中有很多欄位， 其中有一欄為地址，我們想把地址那一欄的資料從資料庫抓出來，再把地址轉成經緯度後，最後再放回資料庫。

使用的工具

我在win 10 下 使用 node red

遇到的問題

資料庫中的經緯度資料表(test表單)裡面都沒東西，不知道為何如此，用debug偵測都出現function : (error) "TypeError: Cannot read property 'shelter_address' of undefined"

程式碼

下面附上我的程式碼、資料庫

程式碼 [{"id":"a1cba074.7f16f","type":"inject","z":"c89cd438.0642b8","name":"Start","topic":"","payload":"","payloadType":"date","repeat":"3600","crontab":"","once":false,"x":262.96295166015625,"y":102.22221857309341,"wires":[["7d9f9ce3.25bb24"]]},{"id":"7d9f9ce3.25bb24","type":"function","z":"c89cd438.0642b8","name":"initalize Table","func":"msg.topic = \"TRUNCATE TABLE test\"\nreturn msg;","outputs":1,"noerr":0,"x":494.9629669189453,"y":96.8888903260231,"wires":[["1ce6f38e.2e80ec"]]},{"id":"1ce6f38e.2e80ec","type":"mysql","z":"c89cd438.0642b8","mydb":"986c37e4.979ba8","name":"Clear Data","x":752.2962265014648,"y":101.11110877990723,"wires":[["9e3ba9fa.5a5508"]]},{"id":"9e3ba9fa.5a5508","type":"http request","z":"c89cd438.0642b8","name":"Get Open Data","method":"GET","ret":"obj","url":"http://data.coa.gov.tw/Service/OpenData/AnimalOpenData.aspx","tls":"","x":446.96290588378906,"y":233.55554682016373,"wires":[["11e9e520.59b15b","60fce332.2976bc"]]},{"id":"11e9e520.59b15b","type":"function","z":"c89cd438.0642b8","name":"Process JSON Data","func":"var obj = msg.payload;\n\nmsg.repetitions = obj.length;\nmsg.data = obj;\nmsg.start= 0;\n\nreturn msg;","outputs":1,"noerr":0,"x":746.9629364013672,"y":228.8888903260231,"wires":[["502b478d.387538"]]},{"id":"502b478d.387538","type":"repeat","z":"c89cd438.0642b8","name":"","repetitions":"","elseOutput":true,"outputs":2,"x":596.9627990722656,"y":409.55550104379654,"wires":[["4f3f70de.41b0c"],[]]},{"id":"b73832d1.fadaf","type":"mysql","z":"c89cd438.0642b8","mydb":"77154442.1601dc","name":"Insert Data","x":823.7408981323242,"y":588.7777462005615,"wires":[[]]},{"id":"94ecea59.6ebf38","type":"switch","z":"c89cd438.0642b8","name":"","property":"statusCode","propertyType":"msg","rules":[{"t":"eq","v":"200","vt":"str"},{"t":"neq","v":"200","vt":"str"}],"checkall":"true","outputs":2,"x":885.1853981018066,"y":513.1111431121826,"wires":[["502b478d.387538"],[]]},{"id":"4f3f70de.41b0c","type":"function","z":"c89cd438.0642b8","name":"process query","func":"var shelter_address = msg.data[msg.start].shelter_address;\nmsg.shelter_address = msg.data[msg.start].shelter_address;\n\nmsg.topic = \"INSERT INTO test (shelter_address) VALUES ('\" + shelter_address + \"')\";\n\n\nreturn msg;\n","outputs":1,"noerr":0,"x":250.5185089111328,"y":592.0000085830688,"wires":[["b73832d1.fadaf","94ecea59.6ebf38"]]},{"id":"60fce332.2976bc","type":"debug","z":"c89cd438.0642b8","name":"","active":true,"console":"false","complete":"payload","x":642.352165222168,"y":295.4445276260376,"wires":[]},{"id":"f8e5b0d3.4d272","type":"function","z":"c89cd438.0642b8","name":"Set Information","func":"\nmsg.key = \"AIzaSyDoSs5XR0hOeY7oc2OEG80Zuj8CpX-nHCo\";\nmsg.language = \"zh-TW\";\nmsg.region = \",tw\";\nreturn msg;","outputs":1,"noerr":0,"x":405.85198974609375,"y":713.5187549591064,"wires":[["ad195f7c.ab4e1"]]},{"id":"ad195f7c.ab4e1","type":"google geocoding","z":"c89cd438.0642b8","name":"Get Coordinates","geocodeBy":"address","address":"","lat":"","lon":"","googleAPI":"","bounds":"","language":"","region":"","components":"","x":730.2410755157471,"y":698.18528175354,"wires":[["a95265e9.213d78"]]},{"id":"a95265e9.213d78","type":"function","z":"c89cd438.0642b8","name":"Convert","func":"if(msg.status = \"OK\"){\n msg.payload.shelter_lat = msg.location.lat;\n msg.payload.shelter_lon = msg.location.lon;\n}\nvar shelter_lat=msg.payload.shelter_lat;\nvar shelter_lon=msg.payload.shelter_lon;\nmsg.topic = \"INSERT INTO test (shelter_lat,shelter_lon) VALUES ('\" +shelter_lat + \"','\" + shelter_lon+ \"' )\";\nreturn msg;","outputs":1,"noerr":0,"x":943.8523101806641,"y":682.5188369750977,"wires":[["fcb7e027.93423"]]},{"id":"bf64bd90.d5466","type":"function","z":"c89cd438.0642b8","name":"Is cat","func":"var animal_lon = msg.payload.lon;\nvar animal_lat = msg.payload.lat;\nmsg.animal_lon = msg.payload.lon;\nmsg.animal_lat = msg.payload.lat;\n\n\nmsg.topic = \"INSERT INTO cat (animal_lat,animal_lon) VALUES ('\" + animal_lat + \"','\" + animal_lon + \"')\";\n\nmsg.start = msg.start + 1;\nreturn msg;","outputs":1,"noerr":0,"x":1304.6299285888672,"y":702.8518724441528,"wires":[[]]},{"id":"fcb7e027.93423","type":"mysql","z":"c89cd438.0642b8","mydb":"ef3d5f2e.20b5f","name":"","x":1153.7964096069336,"y":668.5185718536377,"wires":[[]]},{"id":"4e038f25.54385","type":"function","z":"c89cd438.0642b8","name":"","func":"msg.topic = \"INSERT INTO animaldata (animal_id, animal_sex, animal_colour,\"+\n\"animal_shelter_pkid,animal_place,animal_kind,animal_bodytype,animal_age,animal_sterilization,\"+\n\"animal_bacterin,animal_remark,animal_update,animal_createtime,shelter_name,\"+\n\"album_file,cDate,shelter_address,shelter_tel) VALUES ('\" + \nanimal_id + \"', '\" + animal_sex + \"', '\" + \nanimal_colour + \"', '\" + animal_shelter_pkid + \"', '\" + animal_place + \"', '\" + animal_kind +\n\"', '\" + animal_bodytype + \"', '\" + animal_age + \"', '\" + animal_sterilization + \"', '\" +\nanimal_bacterin + \"', '\" + animal_remark + \"', '\" + animal_update + \"', '\" + animal_createtime +\n\"','\" + shelter_name + \"','\" + album_file + \"','\" + cDate + \"','\" + shelter_address + \"','\" +shelter_tel + \"')\";\n\nmsg.start = msg.start + 1;\nreturn msg;","outputs":1,"noerr":0,"x":1209.778190612793,"y":594.0372180938721,"wires":[[]]},{"id":"986c37e4.979ba8","type":"MySQLdatabase","z":"","host":"localhost","port":"3306","db":"animal","tz":""},{"id":"77154442.1601dc","type":"MySQLdatabase","z":"","host":"localhost","port":"3306","db":"animal","tz":""},{"id":"ef3d5f2e.20b5f","type":"MySQLdatabase","z":"","host":"localhost","port":"3306","db":"animal","tz":""}]

這是我們的資料庫的雲端網址，可提供各位去下載資料庫，麻煩大家了，謝謝 https://drive.google.com/file/d/0B-kOp4t2QjpgWXQ0SXllWEdTMm8/view?usp=sharing