Offering a few tips for teams that want to put their data on a map:
1. Bring your data to a flat excel table. Avoid having any merged cells in the header. Keep the headers in the first row only; from row 2 onwards your data should start. Example: If you had a super-header of "Expenses", and sub-columns of 2011 & 2012, then turn that into two distinct columns titles like "Expenses_2011", "Expenses_2012".
1. Bring your data to a flat excel table. Avoid having any merged cells in the header. Keep the headers in the first row only; from row 2 onwards your data should start. Example: If you had a super-header of "Expenses", and sub-columns of 2011 & 2012, then turn that into two distinct columns titles like "Expenses_2011", "Expenses_2012".
2. Even for the data rows, please clean this up. Un-merge the cells, repeat common information or delete that column (like, first column in your file is useless - there's only one state right?).
3. Make a new latitude column and a longitude column in your table. You have to assign lat-long to each row.
4.For bulk finding lat-long, this tool might help:
http://sahbhag.in/files/googlemaps-latlong-from-address.html
Copy-paste the locations column in the textbox, and it searches with google maps and takes the lat-long of the top match. Disclaimer: zero guarantees for accuracy! Note: wait for it as it cycles through each location. Once it's done, you can copy-paste the output CSV textbox and paste>import wizard in excel, or save as a .csv file and open it in excel / calc, etc.
4.For bulk finding lat-long, this tool might help:
http://sahbhag.in/files/googlemaps-latlong-from-address.html
Copy-paste the locations column in the textbox, and it searches with google maps and takes the lat-long of the top match. Disclaimer: zero guarantees for accuracy! Note: wait for it as it cycles through each location. Once it's done, you can copy-paste the output CSV textbox and paste>import wizard in excel, or save as a .csv file and open it in excel / calc, etc.
5. To manually find lat-long of a location by clicking on a map, here's a good tool: http://itouchmap.com/latlong.html
6. Once your data is simplified and ready to be machine-readable, you can now upload the excel/spreadsheet to a mapping service. I'll recommend two: CartoDB : http://cartodb.com, and Google MyMaps: https://www.google.com/mymaps/
Example: Recently for Pune we got a little data about accidents, and some entries had lat-longs. So I was able to use my free account on cartodb.com to make this: https://nikhilvj.cartodb.com/viz/25be79a2-ed62-11e5-9bf6-0e8c56e2ffdb/map
Its nowhere near publishing levels because the data we've gotten isn't proper. But this could give you an idea.
No comments:
Post a Comment