1

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.

  • I don't have a detailed answer, but I would suggest normalizing the data in a way that creates one row for each address, resulting in numerous duplicate company_names. Then, after the geocoding process is complete merge/group the multiple points into single multi-point feature by dissolving (grouping) them on the company_name value. – Matt Goodman Mar 4 at 17:08
  • thanks, can I have different values for the attributes of the same entity ? I mean each office of a company has some particularities. Which method do you use to merge multiple points into a single multi-point feature ? – Basile Mar 5 at 9:52
2

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

input

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:

result_1

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)

result_2


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


References:

| improve this answer | |

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.