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?

• formatting functions to convert various data types (such as date/time, integers, numeric values, and so on) to formatted strings
• date/time functions and operators for date/time value processing
• INSERT to create new rows in a table
• UPDATE to update rows of a table
• like / not like for pattern matching expressions
• where condition comparison operators
• split_part returns a split string on a given field
• related data types for default casts between datatypes

What are the most common PostGIS functions?

• 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 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
• ST_Simplify to simplify and filter out unneeded geometries for query optimization
• ST_Transform returns a new geometry with transformed coordinates
• ST_X distance operator for returning a X coordinate point
• ST_Y distance operator for returning the Y coordinate point
• 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:

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:

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.

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:

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. 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. Enter the following SQL query:

UPDATE my_dataset
SET lon_column = ST_X(the_geom), lat_column = ST_Y(the_geom)

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)