Your search returned no results

Try another search term or view Stack Exchange, where we have active conversations with Developers about GIS.

    http://gis.stackexchange.com/
    Support channel in Stack Exchange

    Thousands of developers around the world have helped make CartoDB a success, and we have active conversations with them about GIS in Stack Exchange.

    Go to Stack Exchange

    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.

    Tip: See Geospatial Analysis and Advanced Analysis for tips and tricks about working with PostGIS and PostgreSQL data within CARTO. Additionally, see the SQL and PostGIS in CARTO Map Academy course.

    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)

    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.

    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)