Draw charts and maps

How to turn SQL results into Plotly charts and choropleth maps, written the same way in pages and in AI chat

Queria draws a chart by putting columns of an SQL result into a Plotly chart spec (JSON). A page’s plot block and the plot of show_sql in AI chat take the same spec.

In a page

Name an SQL block (sql id=<name>) and refer to its columns from a plot block as $<name>.<column>. A named SQL block is not shown as a table.

```sql id=pref_pop
SELECT prefecture_name, population FROM ... ORDER BY population DESC LIMIT 10
```

```plot caption="Most populous prefectures"
{
  "data": [
    { "type": "bar", "orientation": "h", "x": "$pref_pop.population", "y": "$pref_pop.prefecture_name" }
  ],
  "layout": { "height": 400, "yaxis": { "autorange": "reversed" } }
}
```

• To show the table as well, write sql id=<name> display
• caption is optional
• The spec is JSON: quote keys and strings with "

In AI chat

In AI chat, the AI passes the same spec as the plot of show_sql. There is only one result, so columns are $result.<column>. The table comes folded under the chart.

{ "data": [{ "type": "bar", "x": "$result.name", "y": "$result.value" }] }

Common charts

Charts made only of bar, scatter and pie use the lighter Plotly build and appear faster.

Maps

For a choropleth map, turn the boundary column into GeoJSON strings with ST_AsGeoJSON and pass it as geojson. You can leave out locations.

SELECT prefecture_name, ST_AsGeoJSON(geometry) AS geojson, population
FROM ...

{
  "data": [
    { "type": "choroplethmap", "geojson": "$pref_map.geojson", "z": "$pref_map.population", "text": "$pref_map.prefecture_name" }
  ],
  "layout": { "map": { "center": { "lat": 36.5, "lon": 137 }, "zoom": 4 } }
}

• Functions starting with ST_ (the spatial extension) are loaded automatically when SQL uses them
• Coordinates go longitude first (ST_Point(longitude, latitude))
• Detailed boundaries are heavy. Narrow the area, or coarsen the shapes first with ST_SimplifyPreserveTopology(geometry, 0.001) or similar
• Maps in pages get Queria’s basemap. In AI chat there is no basemap; only the shapes and points are drawn

For spatial SQL such as joining boundaries and facilities, see the cookbook recipe for geographic data. For a map that counts the population and facilities around a point on the spot, put a gis block in a page.

When a chart does not draw

• A column that is not in the result cannot be drawn. Use the SQL alias as the column name in $<name>.<column>
• If numbers come as strings, the axis is not numeric. CAST them in SQL
• Too many rows make it heavy. Aggregate in SQL before charting