Analysis Guides

Convert Text into City and Admin Region Geometries

In CARTO, geometries are stored using the_geom column. If no geometry coordinates are found when connecting a dataset to your account, your dataset is imported with null values in the_geom column; even if your dataset contains location information (such as cities, regions, zip codes, and so on).

See the Preparing and Formatting Data Guide for tips about prepping data before it is imported.

For example, suppose you have a dataset that has a city column with multiple rows of values. The connected map layer appears empty in CARTO Builder since the_geom column contains null values. However, Builder is intuitive enough to guide you through the process of geocoding your data. As a result, coordinates are created for the_geom column, allowing you to visualize and style data.

For this guide, let’s apply the Geocode analysis in order to visualize the administrative boundaries and cities where stores are located in Mexico. We will use the Administrative Regions and Cities options to geocode our data.

CHEATSHEET: Geocode Types

The following types of location text can be geocoded and applied to your data:

  • Latitude and Longitude: Generates location points using two parameter columns, one for latitude values and one for longitude values.
  • Cities: Generates location points using a single parameter column with city name values. For greater accuracy, you can specify a column to be used for the Administrative Region names, or alternatively, specify a column for country names. For both Administrative Regions and country names, you can enter custom values by typing in the name (if the data is within a single region or country. For example, manually type in and select `United States`).
  • Countries: Requires a single parameter column with country names to generate location border geometries as polygons.
  • Administrative Regions: Requires a single parameter column with administrative region names to generate location points. You can also specify a column to be used for country names (if all are unique), or alternatively, type in the custom country name.
  • Postal Codes: If your dataset consists of a column with postal codes, this option geocodes those values to generate location points. Your input data must be a text column. You can also specify a column to be used as a country names, similar to the Administrative Regions option. For specific details about how CARTO geocodes US zip codes, see the Postal Code Geocoder section in the Data Servies API documentation.
  • IP Addresses: Geocodes your data into point geometries for IP addresses. This is useful if you are analyzing location data based on a set of user’s IP addresses.
  • Street Addresses: Geocodes your data into a point geometry for a street address. You can specify the column to be used for the street addresses, in addition to the columns to be used for the city, state and country. You can also specify custom fields using the Advanced Mode.

The Geocode analysis is subject to quota limitations and extra fees may apply. Please request a demo if you are interested in enabling this service for your account.

Geocode by Administrative Region

Let’s create a map and visualize the administrative regions of Mexico. When geocoding by admin regions, your dataset must contain a single column with region data in order to generate location points.

  1. Import the template .carto file, packaged from “Download resources” of this guide and create the map.

Click on “Download resources” from this guide to download the zip file to your local machine. Extract the zip file to view the files used for this guide.

Builder opens with the States of Mexico as the first map layer and Store Locations as the second map layer. Both layers indicate that there is no geometry and provides a shortcut to apply the GEOCODE analysis.

  1. From the States of Mexico map layer, click GEOCODE.

    The ANALYSIS tab opens and Geocode is automatically selected.

  2. Switch to the Data View, you will notice that the_geom column contains null values, even though the connected dataset has values.

    The Data View and Map View appear as buttons on your map visualization when a map layer is selected. Click to switch between viewing your connected dataset as a table, or show the map view of your data.

    Note the columns name_0 (which indicates the country name as Mexico) and name_1 (which contain the administrative regions that we are looking for).

    Data View

  3. For best practices, let’s rename those columns directly from the Data View:

    • Double-click on the name_0 column and rename it to country.
    • Double-click on the name_1 column and rename it region.

    You will need to manually refresh your browser, or go back to the LAYERS list and select the layer again, in order to visualize any renamed columns.

  4. From the ANALYSIS tab, change the geocode TYPE to Administrative Regions and apply the following parameters:

    • For the ADMINISTRATIVE REGIONS parameter, select the region column from the connected dataset.
    • Click the checkbox next to COUNTRY and select the country column from the connected dataset. Alternatively, if you did not have country data in your dataset, you can manually type a column to add it.

    Adm regions parameters

    • Click APPLY to run the analysis.
  5. To visualize the analysis results from your Map View,

    • click Center map on layer ().

    The states of Mexico appear as administrative regions.

    Admin regions of Mexico

  6. Click the Data View to see how the specified admin regions were converted to polygons in the_geom column.

Geocode by Cities

The map includes a second layer of store locations in Mexico, which Builder cannot find geometries for. Let’s geocode this layer by city names in order to visualize these locations. When geocoding by cities, your dataset must contain a single column with city name values. Additional options allow you to specify the admin region or the country for more accurate data.

  1. Navigate back to the LAYERS list of CARTO Builder.

  2. From the Store Locations map layer, click GEOCODE.

    The ANALYSIS tab opens and Geocode is automatically selected.

  3. Switch to the Data View, you will notice that the_geom column contains null values, even though the connected dataset has city names in the name column.

  4. For best practices, rename the name column to city directly from the Data View.

  5. Fix the spelling for the “Mazatlan” values in the city column.

    If you sort the column, you will notice that there are two spellings of the city Mazatlan. Edit the cell value for one of them for consistent spelling. This ensures that accurate data is being plotted for that city value. For details about editing data, view the Adding Rows and Columns in the Data View Guide.

    Mispelled city value

  6. From the ANALYSIS tab, change the geocode TYPE to Cities and apply the following parameters:

    • For the CITY parameter, select the city column from the connected dataset.
    • Click the checkbox next to COUNTRY. Since the connected dataset does not have a country column, manually type and select Mexico to add it.

      Add custom country

    • Click APPLY to run the analysis.

    The city names were plotted into store locations in Mexico. Try adding some widgets to explore your data.

    Final city

  7. Click the Data View to see how the specified cities were converted into geometry coordinates in the the_geom column.

External Resources

If you are using the Data Services API with CARTO Engine to manage your data, view the City Geocoder and the Level-1 Administrative Regions Geocoder documentation.