Analytics Toolbox for Redshift

Analytics Toolbox for Redshift

Go back

Geocoding your address data

In this example, we will geocode a table with some Starbucks address data that we have available in Redshift. The geocoding process will add a new column to your input table called “geom” (or the name that you choose) with a Point geometry based on the geographic coordinates of the location; which are derived from the location information in your table (e.g. street address, postal code, country, etc.).

Geocoding from the Redshift console

As a module within CARTO’s Analytics Toolbox, the location data services (lds) capabilities are available as SQL procedures that can be executed directly from your Redshift console or client of choice after connecting your Redshift project with your CARTO account. To check whether your Google account or Service Account has access to the LDS module, please execute this query:

1
SELECT carto_dev_data.carto.VERSION_ADVANCED()

The lds module is generally available in the Analytics Toolbox since the “July 26, 2022” version. Please check the Getting Access section if you run into any errors when running the query above.

For this example we will use a table with the Starbucks addresses that can be found in the publicly available bqcartodemos.sample_tables.starbucks_ny_geocode . The table contains a column called “full_address” that we will use as input for the geocoding process.

Geocode input table in RS console

Once you are all set getting access to the lds module, geocoding your data is as easy as opening your Redshift console or SQL client and running the GEOCODE_TABLE() procedure as detailed in the following query:

1
2
3
4
5
CALL carto.GEOCODE_TABLE(
    'carto_dev_data.demo_tables.starbucks_ny_geocode',
    'full_address','geom', 'us');
-- The table 'carto_dev_data.demo_tables.starbucks_ny_geocode' will be updated
-- adding the columns: geom , carto_geocode_metadata.

In this case, we select ‘carto_dev_data.demo_tables.starbucks_ny_geocode’ as input table and “full_address” as address column. We choose the “geom” as the column name for the geometry column, and we also specify the name of the country based on its ISO 3166-1 alpha-2 code ISO 3166-1 alpha-2 code. You can refer to the SQL reference if you need more details about this procedure and its parameters.

As a result of the query, we obtain the input table modified with a new column called “geom” with the geographic coordinates (latitude and longitude) and the “carto_geocode_metadata” column with additional information of the geocoding result in JSON format.

Geocode output table in RS console

Geocoding from CARTO Workspace

The Data Explorer offers you a graphical interface that you can use to geocode your data. Let’s use it here to reproduce the same use case that we have done from the Redshift console but from the CARTO Workspace.

You will find the option Geocode table available from the Data Explorer in tables that do not contain any geometry column. To find your table please select the corresponding connection, pick the right dataset/folder and find the table you want to geocode from the collapsible tree.

Geocode inout table in CARTO Workspace

Clicking on the “Geocode table” button will trigger a wizard that you can follow along to configure the different parameters to geocode your data.

In this case, to reproduce the geocoding example that we have done before from a SQL console, we will select geocode by address and we will choose the “full_address” column as input parameter. You can also provide extra location information choosing “United States of America” in the country selector.

Geocode proces in CARTO step 1

Click on “Continue” to proceed to the next step where you can review the summary of the operation that will be performed on your data and confirm it by clicking on “Geocode”.

Geocode proces in CARTO step 2

The geocoding process could take some minutes, remember that you may be geocoding a big amount of data and that the operation is calling an external geocoding service. You can minimize the process window and continue working with CARTO in the meantime and follow the progress of the geocoding process at any time you want.

Geocode proces in CARTO processing

Once the process finishes, you will be able to access your geocoded table, which will have a new column called “geom” including the geographic coordinates of your input data.

Geocode output table in CARTO Workspace
EU flag

This project has received funding from the European Union’s Horizon 2020 research and innovation programme under grant agreement No 960401.