Hi,
is there a way to link geojson files to queries?
we need to represent statistical data for areas. Create a new geojson for every value or set of values is not viable, we have hundreds of maps and thousands of values.
It could be very useful link table to geojson data with a key field.
Where are the values coming from that you are trying to join to geojson files? And how do they tie rogether? Do the name of the files contain the value?
Can you share some examples?
Data are in PostgreSQL tables. Geojson files contain only polygons and ID of territories that permits to link territory to data.
These maps have been made with Apache Superset, where I can choose map, dataset and field that contains territory ID.
1 Like
Please post sample data from postgres (table schema and sample data) and sample data from geojson files minus sensitive data.
So you want to marry the postgres data with the files?
where are these files located in relation to your postgres server?
in postgres there are tables with:
ID_1 DATA
01 1200
02 2000
03 4500
in geojson files are the fields:
ID_1 NAME GEOM
01 Piemonte [polygon]
02 Valle d’Aosta [polygon]
Colors are a gradient for values 1200, 2000, 4000 etc. in Tooltip we can see Territory name and value.
This thing is already working in Apache Superset, I’m investigating how to do this with Grafana.
2 Likes
there are many tables?
Tables with data are hundreds, with dozens of data columns.
When we create chart we choose what column to show in map.
This is possible in grafana if you could answer the questions I am asking you and leave out any Apache Superset related stuff, it just clouds the conversation.
Where are these files in relation to the postgres server?
usually geojson files are this format
{
"type": "FeatureCollection",
"metadata": {
"generated": 1655487609000,
"url": "https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/4.5_week.geojson",
"title": "USGS Magnitude 4.5+ Earthquakes, Past Week",
"status": 200,
"api": "1.10.3",
"count": 96
},
"features": [
{
"type": "Feature",
"properties": {
"mag": 5.1,
"place": "Kepulauan Babar, Indonesia",
"time": 1655484301880,
"updated": 1655485364040,
"tz": null,
"url": "https://earthquake.usgs.gov/earthquakes/eventpage/us7000hi6l",
"detail": "https://earthquake.usgs.gov/earthquakes/feed/v1.0/detail/us7000hi6l.geojson",
"felt": null,
"cdi": null,
"mmi": null,
"alert": null,
"status": "reviewed",
"tsunami": 0,
"sig": 400,
"net": "us",
"code": "7000hi6l",
"ids": ",us7000hi6l,",
"sources": ",us,",
"types": ",origin,phase-data,",
"nst": null,
"dmin": 2.094,
"rms": 0.86,
"gap": 71,
"magType": "mb",
"type": "earthquake",
"title": "M 5.1 - Kepulauan Babar, Indonesia"
},
"geometry": {
"type": "Point",
"coordinates": [
129.3538,
-7.1598,
149.33
]
},
"id": "us7000hi6l"
}
but yours is different. Are you saying these are files saved to disk or this geojson stuff is in a database?
here there is a sample of ours geojson:
{
"type": "FeatureCollection",
"name": "toscana_comuni_2002",
"crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:EPSG::32632" } },
"features": [
{ "type": "Feature",
"properties": {
"ID_0": 0,
"ID_1": "051017",
"NAME_1": "Cortona" },
"geometry": {
"type": "MultiPolygon",
"coordinates":
[...]
as you can see, geojson contains only maps. We have thousands of maps and thousands of columns, we can’t create a specific geojson for every data to represent.
2 Likes
Approach #1
Have you looked at the option of importing these geojson files into postgres and enabling postgis? That would be the cleanest approach I would recommend.
That is what I did
then
app.get('/postgis', (req, res) => {
db.one('SELECT geo FROM public.stores', 123)
.then((data) => {
//return res.json(data.geo)
return res.status(200).send(data.geo)
})
.catch((error) => {
console.log('ERROR:', error)
})
})
Approach #2
Write a simple node/express rest api that reads these geojson files and provide the url to grafana. But not sure if you get new geojson data dynamically or you always have a static list
app.get('/geo', (req, res) => {
let rawdata = fs.readFileSync('./data/earthquakes.geojson');
let geo = JSON.parse(rawdata);
return res.json(geo)
})
Approach #3
…
Thanks for suggestions.
#1: it could be viable, but how can I then load GeoJSON in Grafana? when I choose GeoJSON as layer type it expects a file.
that is what I also thought that is has to be a file but look at the label carefully
GeoJSON URL. URL so this is what we did. might be a bit overkill but works perfectly for us
app.get('/postgis', (req, res) => {
db.one('SELECT geo FROM public.stores', 123)
.then((data) => {
//return res.json(data.geo)
return res.status(200).send(data.geo)
})
.catch((error) => {
console.log('ERROR:', error)
})
})
I think it might be best to review your architectural approach to this whole thing. You have data scattered in a few places. They need to be centralized. you already have postgres so I would highly recommend you have it centralized there.
Thank you for your very precious suggestions.
I am new to Grafana: where do I have to put code fragments in order to create these web services as you did?
1 Like
So what I did was I created a simple rest api using nodejs/express.js
see here
If you have resources of someone that can create this simple rest api using node if not you can write it in your preferred in house web technology.
Ok, so I have to use an external software to realize a web service. There isn’t a way to do only with Grafana.
Fine, I understand. Thank you for the very complete lesson!
there might be a plugin to aggregate geojson files. not sure how many you have. let’s try json plugins
ehm, at the moment 2400 and counting. Every year in Italy some territory changes, and we have to represent data for over 25 years in at least 5 levels of detail, from Country to village.
mama mia! Mannaggia! 
As an example, please visit: https://nubia.istitutodegliinnocenti.it/index1.jsf
it’s still under development.
As you will see, maps may vary over years.
At left there are data to visualize.
As a simpler example, please see: https://www.minoritoscana.it/dati-del-territorio
I think your solution is very elegant for automated software, but difficult to let it use to my users. In Superset they choose maps, fields and values in a very simple way when they have to create a single map.
1 Like