PostgreSQL Functions and PostGIS Expressions
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.
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
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
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
- 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
- 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.
Alternatively, you can also apply the
column_name::type syntax, which is especially useful when casting date columns, as shown in the following example.
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:
Create two new columns in your table, of type string, with names “latitude” and “longitude”.
Apply the following SQL queries to split your unique coordinates column into two separate columns:
Note: The SQL function defines the value of the coordinates column to be lat, long. If you use a different separator, or your coordinates are in a different order, apply this example format to your specific syntax. To learn more about SQL, see The Map Academy course about SQL and PostGIS in CARTO.
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.
- Create a new number type column for latitude, and one for longitude
- With the CARTO Editor, Custom SQL query option, enter:
- Replace your datasets name for
- Replace your new blank longitude column for
- Replace your new blank latitude column for
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: