Questions  /  Working with Data  /  Intro to Data

FAQs — PostgreSQL and PostGIS

Frequently asked questions about PostgreSQL expressions and PostGIS functions with CARTO.

Behind the scenes, the CARTO geospatial database is built on the PostgreSQL platform and supports advanced PostGIS capabilities, enabling you to use basic PostgreSQL expressions and PostGIS functions when creating maps with CARTO.

Tip: See Geospatial Analysis and Advanced Analysis for tips and tricks about working with PostGIS and PostgreSQL data within CARTO.

What are the most common PostgreSQL expressions?

See the links below for the actual PostgreSQL description and reference page. Note the the version is subject to change.

What are the most common PostGIS functions?

View the Handling geospatial data section, located in the CARTO SQL API documentation, for common PostGIS functions that can be used with CARTO. See the links below for the actual PostGIS description and reference page.

I have a synced dataset, how can I modify the column types?

When working with a synced dataset, your data is not editable while your dataset is connected to the source. If you need your columns to have a specific data type, you can apply the following query. (It selects only the columns you need from your original dataset by changing the column type with the CAST (column_name as type) syntax).

Note: Different cast column types are text, int, date, or boolean.

SELECT cartodb_id, the_geom_webmercator, the_geom, CAST (_number_column_name_ AS _text__), CAST (_text_column_ as _int_) FROM tablename

Alternatively, you can also apply the column_name::type syntax, which is especially useful when casting date columns, as shown in the following example.

SELECT cartodb_id, the_geom_webmercator, the_geom, my_date_column::timestamp FROM tablename

Why isn’t my single lat/long column appearing as geocoded data?

Ensure that your longitude and latitude coordinates appear in separate columns. If both your lat/long coordinates are contained in the same column, your data will not be geocoded. For details about how to geocode data with the CARTO Editor, see Georeference Data.

You can split lat/long coordinates into separate columns by applying an SQL query:

  1. Create two new columns in your table, of type string, with names “latitude” and “longitude”.

  2. Apply the following SQL queries to split your unique coordinates column into two separate columns:

UPDATE _tablename_ SET _latitude_ = split_part(_coordinates_, ', ', 1)

UPDATE _tablename_ SET _longitude_ = split_part(_coordinates_, ', ', 2)

How can I export latitude and longitude values?

CARTO point datasets contain the_geom column with latitude and longitude coordinates. Exporting the_geom renders a column in WellKnownBinary format, or WKB. For example, a field containing “-73.9696, 40.6754” appears in a CSV as “0101000020E6100000000000800D7E52C0E128A68274564440”. You can export the_geom as latitude and longitude instead by modifying your dataset with an SQL query.

  1. Create a new number type column for latitude, and one for longitude
  2. With the CARTO Editor, Custom SQL query option, enter:
UPDATE my_dataset
SET lon_column = ST_X(the_geom), lat_column = ST_Y(the_geom)
  • Replace your datasets name for my_dataset
  • Replace your new blank longitude column for lon_column
  • Replace your new blank latitude column for lat_column

Click Apply query , the_geom coordinates are split into the appropriate latitude and longitude columns. If you export your dataset as a CSV, the values appear as numbers.

I have a column with a GeoJSON, how can I set the_geom value to this?

Modify the following query with your values:

UPDATE your_table SET the_geom = st_setsrid(ST_GeomFromGeoJSON(your_GeoJSON_column), 4326)