Batch geocode city/state to lat/long using free or open source tool

Question

I am looking for a way to batch geocode a dataset containing cites/states to lat/longs. The lat/long for the centroid of the city is fine. My data does not contain streets or street numbers. Only city/state combinations, e.g. Brooklyn, NY.

I have tried various geocoders, such as the Census Geocoder and the Google Sheets "Awesome Table" plugin "Geocode." Both of these geocoders require a "full address" and will not geocode city/state alone without a street/street number.

Answer 1

Here's what ended up working for me. Not geocoding, but using a gazetteer:

1. Download the USGS U.S. Board on Geographic Names GNIS Data Topical Gazetteer "Populated Places" from: https://www.usgs.gov/core-science-systems/ngp/board-on-geographic-names/download-gnis-data
2. Save the gazetter text file as an XLSX file, use Text to Columns to separate into columns, and then create a new field that concatenates the city and state, e.g. "Brooklyn_NY"
3. Create a new field in my original data that concatenates city and state the same way and use VLOOKUP to join lat/long info from the gazetteer to my original dataset based on the new "city_state" fields

Answer 2

Maps for Sheets add-on for Google Sheets can geocode, reverse geocode and other mapping tricks. To get lat/longs only use formula =gsGetLatLng(CITY_STATE_CELL). For other geocoding details gsGeocode formula might be useful.

Answer 3

I designed CSV2GEO that can process any amount of address tokens combined together, or listed as separate columns. There are no restrictions what data format you can upload as long you specify how columns belong together to each address token, or how one column can have multiple address tokens. For more you check Address Tokens Composition, Normalized Address Tokenazation and Flexible Input File Format at Batch Geocoding .

Ideally I would like to see every geocoder out there adapts what CSV2GEO had done to allow flexible user input and easy manipulation during geocoding parsing.

Disclaimer: I founded CSV2GEO