Convert Text into City and Admin Region Geometries

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 georeferencing 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 Georeference analysis in order to visualize the administrative boundaries and cities where stores are located in Mexico. We will use the Admin Regions and Cities options to geocode our data.

Geocode by Administrative Region

Let's create a map and visualize the administrative regions of Mexico. When georeferencing 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. Builder opens with the States of Mexico as the first map layer and Store Locations as the second map layer.

    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.
  2. Click on the States of Mexico map layer.

    The STYLE tab indicates that there is no geometry data and provides a shortcut to georeference your data.

  3. 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 icons 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

  4. 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.
  5. From the STYLE tab, click GEOREFERENCE.

    The ANALYSIS tab opens and Georeference is automatically selected.

  6. Change the georeference TYPE to Admin Regions and apply the following parameters:

    • For the ADMIN. REGION 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.
  7. To visualize the analysis results from your Map View:

    • Refresh Builder.
    • Click on the States of Mexico layer.
    • Click Center map on layer.

    The states of Mexico appear as administrative regions.

    Admin regions of Mexico

  8. 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 georeference this layer by city names in order to visualize these locations. When georeferencing 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. Click on the Store Locations map layer.

    The STYLE tab indicates that there is no geometry data and provides a shortcut to georeference your data.

  2. 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.

  3. For best practices, rename this column to city directly from the Data View.

  4. 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

  5. From the STYLE tab, click GEOREFERENCE.

    The ANALYSIS tab opens and Georeference is automatically selected.

  6. Change the georeference 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.
    • Click APPLY to run the analysis.

    The city names were plotted into store locations in Mexico.

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

Watch the following animated GIF to see the entire geoereferencing by city workflow.

City georeference analysis

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.