Geocoding several addresses to Multipoint geometry

Question

I have a csv-file of different companies, see sketch below. Each company has at least one address. Some can have more than 50 addresses. There is one address per cell. In total my csv-file has around 3000 rows.

+--------------+------------------+------------------+------------------+
| company_name |    address_1     |    address_2     |    address_3     |
+--------------+------------------+------------------+------------------+
| name_1       | some_address_1   | some_address_2   |                  |
| name_2       | 2_some_address_1 |                  |                  |
| name_3       | 3_some_address_1 | 3_some_address_2 | 3_some_address_3 |
+--------------+------------------+------------------+------------------+

My goal is to geocode these addresses to points and convert them to a Multipoint geometry. And then represent each company by a single entity with its different locations.

I have tried with MMQGIS and Google Earth but I don't know how to convert other addresses.

My main trouble is about geocoding several columns in a same row and merge the points to a multipoint geometry.

And I would like to add a new column containing a multipoint geometry with the geocoding of the cells which are not empty.

Is there a solution with geopandas or PostGIS?

The thing is that once the process is completed, I intend to store the result in a PostGIS database and display it on QGIS.

Answer 1

Let's assume there is a table with companies and their addresses stored in companies.csv, see image below

I. Open Source Solution: GeoPy Nominatim

Using the code below it is possible to achieve the desired output, in particular geocoding several columns in the same row and merging the points to a multipoint geometry.

import pandas as pd
import shapely.geometry as geometry
from geopy.geocoders import Nominatim

geolocator = Nominatim(user_agent='my-application', timeout=10)

def geocoding(adds):
    adds_geocoded = list(geolocator.geocode(add) for add in adds.dropna())
    lon_lat = list((round(loc.latitude,6), round(loc.longitude,6)) for loc in adds_geocoded if loc is not None)
    return geometry.MultiPoint(lon_lat)

df = pd.read_csv('companies.csv', delimiter=';')

address_columns = ['Address_{}'.format(i) for i in range(1, df.shape[1])]

df['multipoint'] = df[address_columns].apply(geocoding, axis=1)

out = 'RESULT.csv'
df.to_csv(out, sep=';', encoding='utf-8', index=False)

The result:

P.S. "Mind the Gap" between input and output data, Nominatim is a good geocoder but some addresses can be missing.

II. Proprietary Solution: ArcGIS API for Python

import pandas as pd
import shapely.geometry as geometry
from arcgis.geocoding import geocode
from arcgis.gis import GIS

gis = GIS("http://www.arcgis.com", "***", "***")

def geocoding(adds):
    adds_geocoded = list(geocode(add) for add in adds.dropna())
    lat_lon = list((round(loc[0]['location']['y'], 6), round(loc[0]['location']['x'], 6))  for loc in adds_geocoded if loc is not None)
    return geometry.MultiPoint(lat_lon)

df = pd.read_csv('companies.csv', delimiter=';')

address_columns = ['Address_{}'.format(i) for i in range(1, df.shape[1])]

df['multipoint'] = df[address_columns].apply(geocoding, axis=1)

out = 'RESULT_2.csv'
df.to_csv(out, sep=';', encoding='utf-8', index=False)

---

The last column "multipoint" concatenates geocoded addresses into a Multipoint structure, created by means of Shapely Multipoint class.

---

References:

• Well-known text representation of geometry
• What is a multipoint shapefile?
• ArcPy Classes | Geometry | Multipoint
• Chapter 4. Using PostGIS: Data Management and Queries | 4.1.1. OpenGIS WKB and WKT