How to specify CSV column names for charts

[amruthkiran94]

Hey!
So I've been tinkering around with v8 a bit where I'm trying to display a CSV file as charts based on each coordinate clicked. I have the coordinates displayed but I'm not able to figure out how to specify the column names from the CSV that has to be charted. Sharing the code below:
{ "catalog": [ { "name": "NAES", "type": "csv", "url": "build/TerriaJS/test/csv/naesall.csv", "featureInfoTemplate" : { "name": "{{site}} | Viral Load:{{viral_load}}", "template": "<chart src='build/TerriaJS/test/csv/naesall.csv' column-names='Time,Viral Load' column-units='Date,VL'></chart>" } } ] }

I've tried using x-column={{date}} x-column={{viral_load}} and x-column='date' x-column= 'viral_load' . In both instances I get a No chartable data found error. Any help would be highly appreciated.

Thanks!

[nf-s]

Hi @amruthkiran94

Is this CSV time-series? If so the chart should automatically be shown for the selected feature.

For example http://ci.terria.io/main/#share=s-iC6I6sm2MzhLNsJYm4X0vbrjnWk

Which is using http://ci.terria.io/main/build/TerriaJS/test/csv/lat_lon_enum_date_id.csv

---

There is info about featureInfoTemplate and charts here https://github.com/TerriaJS/terriajs/blob/main/doc/connecting-to-data/customizing-data-appearance/feature-info-template.md#time-series-charts

As for setting x-column to a specific column name - you should use x-column='some_col_name'

There is also info about chart attributes here

terriajs/lib/ReactViews/Custom/ChartCustomComponent.ts

Lines 29 to 92 in fc33525

	export interface ChartCustomComponentAttributes {
	/** The title of the chart. If not supplied, defaults to the name of the context-supplied feature, if available, or else simply "Chart". */
	title?: string;
	/** An id for the chart; give different charts from the same feature different ids. The actual catalogItem.id used for the expanded chart will also incorporate the chart title and the catalog item name it came from. */
	identifier?: string;
	/** 'true' to hide the Expand and Download buttons on the chart. By default and for any other value, the buttons are shown when applicable.
	* Overrides can-download. */
	hideButtons?: boolean;
	/** Comma-separated URLs for data at each available time range. The first in the list is shown in the feature info panel preview.
	* Eg. `sources="http://example.com/series?offset=1d,http://example.com/series?offset=5d,http://example.com/series?all"`*/
	sources?: string[];
	/** Comma-separated display names for each available time range, used in the expand-chart dropdown button.
	* Eg. `source-names="1d,5d,30d"`. */
	sourceNames?: string[];
	/** 'false' to hide the Download button on the chart. By default true and for any other value, the download button is shown. */
	canDownload?: boolean;
	/** Same as sources, but for download only. Defaults to the same as sources.
	* Eg. `sources="http://example.com/series?offset=1d,http://example.com/series?offset=5d,http://example.com/series?all"` */
	downloads?: string[];
	/** Same as source-names, but for download only. Defaults to the same as source-names.
	* Eg. `source-names="1d,5d,30d,max"`. */
	downloadNames?: string[];
	/** Defaults to 'feature-info'. Can also be 'histogram'. TODO: improve. */
	styling?: string;
	/** Maps column names to titles. Eg. column-titles="time:Time,height:Height,speed:Speed" OR column-titles="Time,Height,Speed" */
	columnTitles?: ({ name: string; title: string } | string)[];
	/** Maps column names to units. Eg. column-units="height:m,speed:km/h" OR column-units="m,km/h" */
	columnUnits?: ({ name: string; units: string } | string)[];
	/** The x column name or number to show in the preview, if not the first appropriate column. NOT FULLY IMPLEMENTED YET. */
	xColumn?: string;
	/** The y column name or number to show in the preview, if not the first scalar column. */
	yColumn?: string;
	/** Comma-separated list of y column names or numbers to show in the preview. Overrides "y-column" if provided. */
	yColumns?: string[];
	/** The preview chart x-axis label. Defaults to empty string. Eg. long-names="Last 24 hours,Last 5 days,Time". */
	previewXLabel?: string;
	/** An x-coordinate to highlight. */
	highlightX?: string;
	/** The URL of the data to show in the chart panel, once "expand" is clicked. Eg. `src="http://example.com/full_time_series.csv"`. */
	src?: string;
	/** The URL of the data to show in the feature info panel. Defaults to src. Eg. `src-preview="http://example.com/preview_time_series.csv"`. */
	srcPreview?: string;
	/** csv-formatted data, with \n for newlines. Eg. data="time,a,b\n2016-01-01,2,3\n2016-01-02,5,6".
	* or json-formatted string data, with \quot; for quotes, eg. `data="[[\quot;a\quot;,\quot;b\quot;],[2,3],[5,6]]"`. */
	data?: string;
	}

Attribute names are in kebab-case - eg previewXLabel becomes preview-x-label

If you are able to provide a subset of your dataset I can have a closer look

Cheers,
Nick

[amruthkiran94]

Thank you so much for the response @nf-s. I was also exploring the National Map's "Wind" dataset linked here. Basically I'm trying to build a real-time and dynamic charting behavior using a PostgreSQL API (PostgREST) that is linked to a catalogue file. I've already had a look at a similar question posted here as the basis of my work.

The file, based on point selection:

  {
     "id":"1b",
     "name":"CSV as API (Local)",
     "url":"data/latlong_data.csv",
     "type":"csv",
     "cacheDuration":"5m",
     "featureInfoTemplate":{
        "template":"<chart title='{{name}}' sources='http://localhost:3000/sample?name=eq.{{name}}' column-names='Time,Value' column-units='Date,Value'></chart>"
     }
}

The sources parameter requests the table to chart but its response type is JSON

[{"duid":"2","name":"Point_B","value":200,"lat":12.99694736,"lon":77.59832382}]

TerriaMap doesn't seem to be accepting this response type. Is there any way to accept a CSV via the sources parameter itself or do I have to do something else?

[nf-s]

Currently, there is no way to set http Accept headers for sources.

We do have a new ApiTableCatalogItem which can transform JSON into tabular data.
This can be used by replacing chart with api-chart.

api-chart has a api-table-catalog-item-json attribute which can be used to set trait values - see https://docs.terria.io/guide/connecting-to-data/catalog-type-details/api-table/ for trait docs

I don't have an example of api-chart ready - but it would be something like

"template":"<api-chart title='{{name}}' api-table-catalog-item-json='{\"apis\":[{\"url\":\"http://localhost:3000/sample?name=eq.{{name}}\"}]}' column-names='Time,Value' column-units='Date,Value'></api-chart>"

where api-table-catalog-item-json is the following JSON blob stringified

{
  "apis": [
    {
      "url": "http://localhost:3000/sample?name=eq.{{name}}"
    }
  ]
}

All of this is quite new and experimental, so it might be unpredictable

[amruthkiran94]

Thanks @nf-s I've been trying out your suggestion, here's what I found

{
  "id":"1c",
  "name":"CSV as API-Chart (Local)",
  "url":"data/latlong_data.csv",
  "type":"api-table",
  "cacheDuration":"5m",
  "featureInfoTemplate":{
    "template":"<api-chart title='{{name}}' api-table-catalog-item-json='{\"apis\":[{\"url\":\"http://localhost:3000/sample\"}]}' column-names='Time,Value' column-units='Date,Value'></api-chart>"  
  }
}

Based on what I could understand, I've set type as api-table and placed the template as you suggested but it looks like featureInfoTemplate isn't a trait for api-table. How would I create a catalouge item that can also support the featureInfoTemplate for api-table?

Edit:
I've also found this #5373 (comment) where an example was provided by @KeyboardSounds

{
      "name": "Pedestrian Traffic",
      "type": "api-table",
      "refreshInterval": 60,
      "removeDuplicateRows": true,
      "idKey": "sensor_id",
      "dateTimeKey": "date_time",
      "defaultStyle": {
        "time": {
          "idColumns": ["sensor_id"]
        }
      },
      "apis": [
        {
          "apiUrl": "https://data.melbourne.vic.gov.au/resource/d6mv-s43h.json",
          "keyToColumnMapping": [
            { 
              "columnName": "Total in both directions",
              "keyInApiResponse": "total_of_directions"
            },
            { 
              "columnName": "Time",
              "keyInApiResponse": "date_time"
            },
            { 
              "columnName": "id",
              "keyInApiResponse": "sensor_id"
            }
          ]
        },
        {
          "apiUrl": "https://data.melbourne.vic.gov.au/resource/h57g-5234.json",
          "kind": "PER_ID",
          "keyToColumnMapping": [
            { 
              "columnName": "latitude",
              "keyInApiResponse": "latitude"
            },
            { 
              "columnName": "longitude",
              "keyInApiResponse": "longitude"
            }
          ]
        }
      ]
    }

I'm not sure how to implement this for my use-case, as some of the attributes like keyToColumnMapping has been changed (atleast as per the latest V8 docs) . Any help would be amazing!

Thanks!

[nf-s]

Hi @amruthkiran94

Sorry I didn't realise that ApiTableCatalogItem didn't have featureInfoTemplate yet!

We have two PRs to add that functionality - so hopefully we will get them merged soon

• Implement featureInfoUrlTemplate for Geojson data #5660
• Add featureInfoTemplate to all Mappable CatalogItems #6415

Instead of using keyToColumnMapping - you can set column titles using TableColumnTraits.

This is an updated version of the "Pedestrian Traffic" catalog item

{
  "type": "api-table",
  "name": "City of Melbourne Pedestrian Traffic Counter",
  "description": "The City of Melbourne Pedestrian Counting System data has been obtained from the City of Melbourne Open data platform: www.data.melbourne.vic.gov.au to demonstrate the directional movement of pedestrians across the city.\n\nThe counters generate a per minute reading on each of the pedestrian traffic sensors which are then displayed as near-live data.\n\nTo find out more about the City of Melbourne's Pedestrian Counting System or to seek access to their data, please click:\n\nhttp://www.pedestrian.melbourne.vic.gov.au/#date=27-09-2021&time=1",
  "apis": [
    {
      "url": "https://data.melbourne.vic.gov.au/resource/d6mv-s43h.json?$limit=50000"
    },
    {
      "kind": "PER_ID",
      "url": "https://data.melbourne.vic.gov.au/resource/h57g-5234.json"
    }
  ],
  "idKey": "sensor_id",
  "columns": [
    {
      "name": "total_of_directions",
      "title": "Total in both directions"
    },
    {
      "name": "date_time",
      "title": "Time"
    },
    {
      "name": "latitude",
      "title": "latitude"
    },
    {
      "name": "longitude",
      "title": "longitude"
    },
    {
      "name": "sensor_id",
      "title": "Sensor ID"
    },
    {
      "name": "installation_date",
      "title": "Installation Date"
    },
    {
      "name": "sensor_description",
      "title": "Sensor Description",
      "type": "hidden"
    }
  ],
  "activeStyle": "total_of_directions",
  "defaultStyle": {
    "time": {
      "spreadStartTime": true,
      "timeColumn": "date_time"
    }
  },
  "refreshInterval": 60,
  "removeDuplicateRows": true
}

[amruthkiran94]

Thank you so much for this @nf-s! Is there a complete catalogue item I could possibly access? Would love to see the chart and featureInfoTemplate details as well.