Basic support for Carto API v3

[AnaBelgun]

• example of Carto 3.0 vector tiles/api docs - https://api-docs.carto.com/

[nf-s]

Carto API v3 investigation

Create OAuth token

POST to OAuth token

curl --request POST \
  --url 'https://auth.carto.com/oauth/token' \
  --header 'content-type: application/x-www-form-urlencoded' \
  --data 'grant_type=xxx' \
  --data 'client_id=xxx' \
  --data 'client_secret=xxx' \
  --data 'audience=xxx' 

This token will expire and should not be used in the client.

Create Access token

• Set <auth-token> to output form previous request
• Set grants to specified connection_name, source (tables) etc
• Set allowed_apis to restrict access

curl --location --request POST 'https://gcp-australia-southeast1.api.carto.com/v3/tokens' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer <auth-token>' \
--data-raw '{
    "grants": [
        {
            "connection_name": "carto_dw",
            "source": "carto-demo-data.demo_tables.airports"
        }
    ],
    "referers": [],
    "allowed_apis": ["sql", "maps","imports"]
}'

This token can be used in the client

Estimate: 0.5

query endpoint

"Get a map layer in different formats using a SQL query"

Eg https://gcp-asia-northeast1.api.carto.com/v3/maps/:connection/query?q=select *from some-table

This endpoint return URLs to different formats

Eg response

{
    "nrows": n,
    "ndjson": {
        "url": [
           "xxx"
        ]
    },
    "geojson": {
        "url": [
            "xxx"
        ]
    },
    "json": {
        "url": [
            "xxx"
        ]
    },
    "binary": {
        "url": [
            "xxx"
        ]
    },
    "schema": [
        {
            "name": "name",
            "type": "string"
        },
        {
            "name": "id",
            "type": "number"
        },
        {
            "name": "geom",
            "type": "geometry"
        }
    ]
}

We can use geojson URL directly in TerriaJS

Proposal: a simple CartoQueryCatalogItem

With following trait parameters:

• baseUrl with default https://gcp-us-east1.api.carto.com/
• q = SQL query
• connection = connection name - default = carto_dw

Estimate: 0.5

table endpoint

Get a map layer in different formats from a table in your data warehouse.

Eg https://gcp-australia-southeast1.api.carto.com/v3/maps/carto_dw/table?name=carto-demo-data.demo_tables.airports

Similar to query - but might also contain tilejson response

Eg response

{
  "size": 70307,
  "nrows": 1000,
  "ndjson": {
    "url": [
      "https://gcp-australia-southeast1.api.carto.com/v3/maps/carto_dw/table?format=ndjson&name=carto-demo-data.demo_tables.airports&cache=1630518782697"
    ]
  },
  "geojson": {
    "url": [
      "https://gcp-australia-southeast1.api.carto.com/v3/maps/carto_dw/table?format=geojson&name=carto-demo-data.demo_tables.airports&cache=1630518782697"
    ]
  },
  "json": {
    "url": [
      "https://gcp-australia-southeast1.api.carto.com/v3/maps/carto_dw/table?format=json&name=carto-demo-data.demo_tables.airports&cache=1630518782697"
    ]
  },
  "tilejson": {
    "url": [
      "https://gcp-australia-southeast1.api.carto.com/v3/maps/carto_dw/table?format=tilejson&name=carto-demo-data.demo_tables.airports&cache=1630518782697"
    ]
  },
  "binary": {
    "url": [
      "https://gcp-australia-southeast1.api.carto.com/v3/maps/carto_dw/table?format=binary&name=carto-demo-data.demo_tables.airports&cache=1630518782697"
    ]
  },
  "schema": [
    { "name": "cartodb_id", "type": "number" },
    { "name": "gps_code", "type": "string" },
    { "name": "name", "type": "string" },
    { "name": "geom", "type": "geometry" }
  ]
}

Proposal: a simple CartoTableCatalogItem

Only supports geojson (not tilejson)

With following trait parameters:

• baseUrl with default https://gcp-us-east1.api.carto.com/
• columns = "Columns to retrieve from the layer, by default all are returned"
• tableName = Table name
• connection = connection name - default = carto_dw
• geo_column = "Column name of the geom at the table" - default = geom

Estimate: 0.5

Proposal: CartoTableCatalogItem with tilejson / mvt support

Adding mapbox vector tile support means we can visualise much larger datasets - but may not be necessary.

Will need to add tilejson + add TableStyle support to MapboxVectorTilesCatalogItem

Estimate: 5

tileset endpoint

Get a TileJSON for an MVT layer from a tileset in your data warehouse.

Same as "CartoTableCatalogItem with tilejson / mvt support"

Additional info

Deck.gl Carto layer reference https://docs.carto.com/deck-gl/reference/

[nf-s]

Additional info from client

I think the query endpoint is the one I want to use the most I think responses may go over 150MB If the catalogGroup is also supported, that would be wonderful.

[nf-s]

Basic geoJSON support for table and query endpoints

Note these examples are using an accessToken for a trial organisation - which will last 7 days

table example

Test share link

Shows entire carto-demo-data.demo_tables.airports table (as GeoJSON)

  {
      "type": "carto-v3",
      "accessToken": "eyJhbGciOiJIUzI1NiJ9.eyJhIjoiYWNfaGR6Zmw4cWsiLCJqdGkiOiI0MjAwOGU2OCJ9.6zr_cAS51KLaovOHrhgRHev-WzgYUU1MIhEXzfnJnSU",
      "cartoTableName": "carto-demo-data.demo_tables.airports",
      "baseUrl": "https://gcp-australia-southeast1.api.carto.com/",
      "name": "carto table test"
    }

query example

Test share link

Shows results from SQL query select * from carto-demo-data.demo_tables.airports where name like \"A%\" (as GeoJSON)

   {
      "type": "carto-v3",
      "accessToken": "eyJhbGciOiJIUzI1NiJ9.eyJhIjoiYWNfaGR6Zmw4cWsiLCJqdGkiOiI0MjAwOGU2OCJ9.6zr_cAS51KLaovOHrhgRHev-WzgYUU1MIhEXzfnJnSU",
      "cartoQuery": "select * from carto-demo-data.demo_tables.airports where name like \"A%\"",
      "baseUrl": "https://gcp-australia-southeast1.api.carto.com/",
      "name": "carto query test (airport start with A)"
    }

How accessToken was generated

Generate <OAuth Token> by following https://github.com/terriajs/terriajs/issues/6323#issuecomment-1154747680

And then run the following to create a token which grants access to

• carto-demo-data.demo_tables.airports table
• select * from carto-demo-data.demo_tables.airports where name like \"A%\" SQL query

curl --location --request POST 'https://gcp-australia-southeast1.api.carto.com/v3/tokens' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer <OAuth Token>' \
--data-raw '{
    "grants": [
      {
        "connection_name": "carto_dw",
        "source": "carto-demo-data.demo_tables.airports"
      },
      {
        "connection_name": "carto_dw",
        "source": "select * from carto-demo-data.demo_tables.airports where name like \"A%\""
      }
    ],
    "referers": [],
    "allowed_apis": [ "maps", "sql"]
}'

CartoMapV3CatalogItemTraits

These properties can be used in catalog/init JSON file.

Extends GeojsonTraits

• accessToken: string - The access token to pass to the Carto Maps API
• connectionName: string - The connection name that will be used to access the data (default = "carto_dw")
• baseUrl: string - Base URL for Carto API (default = "https://gcp-us-east1.api.carto.com/")
• cartoGeoColumn: string - Column name of the geom at the table (used for Table and Query API) (default = "geom")
• cartoQuery: string - Carto SQL Query (used for Query API). If this is defined, then the Query API will be used instead of Table API.
• cartoTableName: string - Table fully qualified name - eg "carto-demo-data.demo_tables.airports". (used for Table API). Note if cartoQuery defined, then the Query API will be used instead of Table API.
• cartoColumns: string[] - Columns to retrieve from the layer, by default all are returned. (used for Table API)

[imakihi]

HI Nick. This is awesome and thank you so much for working on this!!! I tried the CARTO3 connection with our CARTO parnter account. I am getting a connection error. I tried to use the "connection" trait in my request but the error message said "The property connection is not valid for type carto-v3".

{ "type": "carto-v3", "accessToken": "my token", "cartoTableName": "marunouchi-street-park-pss.msp_2021_summer.zone_pss", "baseUrl": "https://gcp-asia-northeast1.api.carto.com/", "connection":"msp-data-on-gcp", "name": "pss carto table test" },

I tested my request with Postman and CARTO responded to my request. Do you think I missed something?

[nf-s]

Sorry you need to use "connectionName" instead of "connection"

[imakihi]

Awesome! It is working now. Thank you!!