{"id":2320,"date":"2017-11-06T12:58:10","date_gmt":"2017-11-06T20:58:10","guid":{"rendered":"http:\/\/miriamposner.com\/classes\/dh101f17\/?page_id=2320"},"modified":"2017-11-08T14:08:54","modified_gmt":"2017-11-08T22:08:54","slug":"geocoding-your-data","status":"publish","type":"page","link":"https:\/\/miriamposner.com\/classes\/dh101f17\/tutorials-guides\/mapping\/geocoding-your-data\/","title":{"rendered":"Geocoding your data"},"content":{"rendered":"<p>A lot of mapping programs want your data to be geocoded &#8212; that is, expressed as latitudes and longitudes. But a lot of time your data just has place names, not precise geocoordinates. To get these coordinates, you have several options:<\/p>\n<p><a href=\"http:\/\/gis.ucla.edu\/geocoder\">UCLA Geography Geocoder<\/a><\/p>\n<p><a href=\"http:\/\/www.findlatitudeandlongitude.com\/batch-geocode\/#.Vjp0Aa6rTMV\">Batch geocoder<\/a>\u00a0(throttled so that it slows after 300 entries)<\/p>\n<p>Google Fusion Tables can automatically geocode your data, but I\u2019ve never found a way to export those latitudes and longitudes\u00a0back out.<\/p>\n<p><a href=\"https:\/\/chrome.google.com\/webstore\/detail\/geocode-by-awesome-table\/cnhboknahecjdnlkjnlodacdjelippfg?hl=en\">This add-on<\/a>\u00a0for Google sheets geocodes quickly.<\/p>\n<p><a href=\"http:\/\/docs.cartodb.com\/tutorials\/how_to_georeference.html\">Carto can do it automaticall<\/a>y, but it does charge above a certain number of addresses.<\/p>\n<p>To make things easier, you may want to\u00a0<a href=\"https:\/\/support.office.com\/en-us\/article\/Filter-for-unique-values-or-remove-duplicate-values-d6549cf0-357a-4acf-9df5-ca507915b704\">filter\u00a0all your addresses to the unique values,<\/a>\u00a0so that you\u2019re only geocoding one instance of every address. Then geocoding goes much faster.<\/p>\n<p>However, once you\u2019ve done that, you\u2019re left with two spreadsheets: one with geocoded addresses and one with just the addresses. How do you merge the two? You\u2019ll\u00a0<a href=\"http:\/\/blog.ouseful.info\/2011\/05\/06\/merging-datesets-with-common-columns-in-google-refine\/\">use OpenRefine\u2019s join function<\/a>.<\/p>\n<p>Open both of your spreadsheets in Open Refine. Let\u2019s assume the original spreadsheet is called \u201cLong Spreadsheet\u201d and the spreadsheet with unique values in it is called \u201cShort Spreadsheet\u201d.<\/p>\n<p>With Long Spreadsheet open in Open Refine, select the column that contains the address information. Then, using the tiny triangle drop-down menu at the top of the column, select Edit Column and then Add Column Based on This Column. In the window that pops up, replace the word \u201cvalue\u201d in the text box with<\/p>\n<p><code>cell.cross(\u201cShort\u00a0Spreadsheet\u201d, \u201cCity\u201d).cells[\u201cLongitude\u201d].value[0]<\/code><\/p>\n<p>This tells Open Refine to look in Short Spreadsheet, find the column called City and use that to find the corresponding longitude (stored in a column called Longitude). Then Open Refine will add a new column that contains that data.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A lot of mapping programs want your data to be geocoded &#8212; that is, expressed as latitudes and longitudes. But<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":141,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_eb_attr":"","footnotes":""},"class_list":["post-2320","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/miriamposner.com\/classes\/dh101f17\/wp-json\/wp\/v2\/pages\/2320","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/miriamposner.com\/classes\/dh101f17\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/miriamposner.com\/classes\/dh101f17\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/miriamposner.com\/classes\/dh101f17\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/miriamposner.com\/classes\/dh101f17\/wp-json\/wp\/v2\/comments?post=2320"}],"version-history":[{"count":0,"href":"https:\/\/miriamposner.com\/classes\/dh101f17\/wp-json\/wp\/v2\/pages\/2320\/revisions"}],"up":[{"embeddable":true,"href":"https:\/\/miriamposner.com\/classes\/dh101f17\/wp-json\/wp\/v2\/pages\/141"}],"wp:attachment":[{"href":"https:\/\/miriamposner.com\/classes\/dh101f17\/wp-json\/wp\/v2\/media?parent=2320"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}