I have a massive file with county-level data from the 19th century. I'd like to georeference it, but I'm not sure what I need to do, as CartoDB isn't recognizing the county names through the 'Admin. Regions.' Do I need to import shapefiles and somehow connect them?

  • What fields are available in the data - just county name, or perhaps state or FIPS code? Since counties can change over that kind of time span, and multiple states can have the same county names, you'd need something to distinguish and you may not be able to match everything up. Further, countys aren't usually part of a geocoding process in the US and I'm not sure CartoDB has such reference information. You may need to pull in a county shapefile and join tables, rather than geocode (or georeference as they call it). See gis.stackexchange.com/questions/124676 for a related question. – Chris W Jul 3 '15 at 19:19
  • I have county and state names. I am, of course, a little concerned that counties have changed their names over time, but that is an issue I'll have to deal with separately. I do not have a FIPS code - will my county names align with county names in a county shapefile set? – user3128184 Jul 4 '15 at 0:27
  • Couldn't really say. In theory, yes, but spelling and capitalization or spacing errors can always throw things off - might have to do a little cleanup/checking. The bigger issue is if trying to join you'd need a new custom field or the ability to join on multiple fields (since only county + state uniquely identifies a record, not just county). That's partly why I asked about the FIPS codes, since they would be a single-field unique identifier. And old counties usually split to new counties, not just go away, so re-aggregating shouldn't be too much trouble. – Chris W Jul 4 '15 at 19:35

The CartoDB administrative regions geocoder doesn't include county data at the moment, so you'd need to upload a county dataset and merge it with your data table.

You can use the merge option in CartoDB, or you can use a SQL query in order to copy the geometries, such as:

UPDATE data_table SET the_geom = (SELECT counties_table.the_geom FROM counties_table WHERE counties_table.county_name like county)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.