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.

• formatting functions to convert various data types (such as date/time, integers, numeric values, and so on) to formatted strings. For example, convert a string to a timestamp to_timestamp(text, text) or format a string to_char(int, text)

• date/time functions and operators for date/time value processing, includes generating the current time using current_timestamp, or extracting values such as the hour from a timestamp using extract(field from timestamp)

  Tip: See how PostgreSQL handles date/time types

• INSERT to create new rows in a table. This is useful for combining separate datasets into one dataset
• UPDATE to update rows of a table. This is useful if you need to change the value for a row or column of a dataset
• like / not like for pattern matching expressions
• where condition comparison operators

• split_part returns a split string on a given field

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

• related data types for default casts between datatypes

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

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.

• ST_Area returns the area of the surface if it is a Polygon or MultiPolygon
• ST_AsText returns a well-known text geometry without SRID (Spatial Reference System Identifier) metadata
• ST_Contains returns a boolean value based on the exterior or interior of the geometry points
• ST_Centroid returns the geometric center of a geometry
• ST_Collect returns a specified ST_Geometry value from a collection of other geometries
• ST_GeomFromText returns a geometry value from well-known text (WKT)
• ST_Intersection returns a geometry that represents the shared portion of geomA and geomB
• ST_Intersects returns the geography tolerance or boolean value for spatial intersection

• ST_setSRID sets the SRID on a geometry to a particular integer value

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

• ST_Simplify to simplify and filter out unneeded geometries for query optimization
• ST Transform returns a new geometry with transformed coordinates. To view an example with CARTO, see Handling Geospatial Data in the SQL API documentation
• ST_X distance operator for returning a X coordinate point

• ST_Y distance operator for returning the Y coordinate point

  Note: See How can I export latitude and longitude values?

• ST_Union returns a geometry that represents the point set union of the Geometries
• ST_Within returns a boolean value based on the geometry within function

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)