Geocoding

[Maysam Ayoub]

Dears,

I need to generate the geographical coordinates of around 5000 companies so that I can calculate travel times. For most companies, there is data for street address, PO box, city, and the country.
Here's a subset of the date (just two companies):

* Example generated by -dataex-. For more info, type help dataex
clear
input str22 EntityName str34 HeadquarterAddressLine1 str35 HeadquarterAddressLine2 str6 HeadquarterCity str11 HeadquarterCountry float HeadquarterPostalCode
" Pierre & Vacances"     "L’Artois, Espace Pont de Flandre" ".11, rue de Cambrai, Paris Cedex 19" "Paris"  "France"      75947
"Edisun Power Europe AG" "Universitatstrasse 51"              ""                                    "Zurich" "Switzerland"  8006
end

------------------ copy up to and including the previous line ------------------

Your help is appreciated

Regards,

[Jared Greathouse]

Impossible (as far as I know). But, this sounds like a good Python/Stata problem.

Anyways, you don't give enough details on how you might start doing this, even theoretically. Where does this dataset come from? Why aren't the coordinates provided already? Who collected the data? And also, why is calculating travel time so necessary?

[Mike Lacy]

-search geocode- reveals 20+ relevant user-written Stata packages that include both geocoding and/pr travel time capacities. I have not recently used any of them, and my recollection is that there have been difficulties over the years with changes in e.g. Google Maps breaking such programs, but I'd definitely recommend trying some of what shows up there. Discussions of geocoding have appeared now and then on StataList, so feeding something like /site:statalist.org geocode/ to your favorite search engine should reveal past discussions.

[Jared Greathouse]

My fear isn't the travel time, my fear is that there's nothing that would allow for explicit geocoding of addresses (but I could be totally wrong on this front). Mike Lacy

I know Geonear is great (for Euclidean distance at least), but I've never seen a user written command allowing for coding of addresses, but again, I could be completely wrong.

[Mike Lacy]

To my recollection -- 5 years old or so old--- the geocoding packages used to work pretty decently, with occasional glitches. I believe they all function by issuing calls to some online service, such as Google Maps or MapQuest. From a quick experiment I just did, it appears that -geocode- (available at SSC) is broken now, but maybe one of the other package works or perhaps I misused -geocode-. I don't have much expertise here, but perhaps someone else can work this out or already knows which package to use.

[Iuri Gavronski]

I used a Google Script for that purpose. You have the addresses in a Google Sheets column and the output goes to two columns you set aside for the lat/long data.
See Geocoding With Google Sheets (willgeary.github.io)

[Jared Greathouse]

Iuri Gavronski This is fascinating. Is this Python or Java working under the hood?

[Iuri Gavronski]

Hi Jared Greathouse ,
As far as I know, it is a scripting language for Google Sheets. Looks like a JavaScript dialect.
You upload the dataset to Sheets, run the script, then download it as xlxs or csv and import it back to Stata.
Worked like a charm for me.

[Maysam Ayoub]

Iuri Gavronski thank you so much! Amazing

also, thanks everyone for your input

[eric_a_booth]

I've got a github repo to do this here: https://github.com/ericabooth/googlesheets-stata-public

You'll have to set up your oauth first - I've got instructions in my github page (it takes about 2 minutes). My version of the command also allows you to insert interactive Google Charts objects and pull latest survey results/responses from Google Forms.

 net install googlesheets, from("https://raw.githubusercontent.com/ericabooth/googlesheets-stata-public/master/") replace
discard
which googlesheets
help googlesheets

Also might be of interest:
Google Chart interactive visualiations, tables, and entire sites, see: https://github.com/ericabooth/googlechart-stata-public

Live demo / example here: https://ericabooth.github.io/GoogleChart_Example_Site/

 net install googlechart, from("https://raw.githubusercontent.com/ericabooth/googlechart-stata-public/master/") replace
discard
which googlechart
help googlechart

[Sergiy Radyakin]

Is there any way of doing gecoding offline? (with any of the tools mentioned above)

[eric_a_booth]

Sergiy Radyakin I did this a while back when needed -geocode- from ssc and it wasnt available (or maybe it's that it was limiting certain # of hits per hour that was too slow). My notes from this install are below. I always intended to go back and package this into a stata adofile to handle passing elements between Python and Stata, but it's in my 'to do someday' list of programs, so your mileage may vary with this (if this issue comes up for me again I may revive this work and if so I'll post here or to my github).

You have to first download the database and run it locally (e.g., via docker):
To run a local Nominatim server for offline street-level geocoding, you will use Docker to download and configure the system.
Be aware that Nominatim requires large resources and can run slowly. A single US state or small country needs at least 4GB of RAM and 10-30GB of local (read: dont stream over cloud) SSD.
------------------------------
First you need this installed via python in your system path/terminal environment or via passthru from Stata:

pip install pgeocode pandas (or pip3 install with --break-system-packages)

Then (this might require PostgreSQL installed and running too - I can't recall if Docker handles this or if I already had it running from a prior project?)
##1: Install Docker
Ensure Docker and the Docker Compose plugin are installed on your machine.
 See:  [https://www.bluehost.com](https://www.bluehost.com/blog/how-do-i-run-n8n-on-docker/)  [https://github.com](https://github.com/Freika/dawarich/issues/614)
* Windows/Mac: Download and run [Docker Desktop](https://www.docker.com/products/docker-desktop/).
* Linux: Install via your package manager (sudo apt install docker-compose-v2).

## Step 2: Download the Region Map Data (.pbf)
Download the OpenStreetMap data for your specific region from [Geofabrik](https://download.geofabrik.de/).

   1. Go  to your continent and country (e.g.,   North America -> United States -> Texas).
   2. Download the file ending in .osm.pbf (e.g., texas-latest.osm.pbf).
   3. Save this file into a new folder on your computer named nominatim-data.

## S 3: Create the Docker Compose File
Inside that same nominatim-data folder, create a file named docker-compose.yml and paste the following configuration:

services:
  nominatim:
    image: mediagis/nominatim:4.4
    container_name: nominatim
    volumes:
      # Mounts the folder containing your downloaded .pbf file
      - .:/data
    environment:
      # MUST match the exact filename you downloaded in Step 2
      - NOMINATIM_PBF_FILE=/data/texas-latest.osm.pbf
      # Adjust threads based on your CPU cores to speed up import
      - NOMINATIM_IMPORT_STYLE=full
    ports:
      - "8080:8080"
    shm_size: 2gb # Helps PostgreSQL handle heavy data processing
    restart: always

## Step 4: Initialize and Run the Container
Open your terminal or command prompt, go to your nominatim-data folder, and run this command:

docker compose up -d

#then,
for the first Run (Import Phase): docker will download the Nominatim image and begin extracting, indexing, and loading the map data into an internal database. This can take anywhere from 30 minutes to several hours depending on your computer's speed and the size of the region. You can monitor the import process by running:

docker logs -f nominatim

* Ready State: after the logs stop scrolling and indicate the server is listening, the offline geocoder is active. test it : opening http://localhost:8080 in web browser.

#If you havent already:
pip install pgeocode pandas (or pip3 install with --break-system-packages)

#python3 will need to run:
from geopy.geocoders import Nominatim

# Point to your local server instead of the public API
geolocator = Nominatim(
    user_agent="offline_app",
    domain='http://localhost:8080/nominatim' # Replace with your local server IP/port
)

address = "110 Congress Ave, Austin, TX"
location = geolocator.geocode(address)

if location:
    print(f"Latitude: {location.latitude}, Longitude: {location.longitude}")