I am trying to display Aircraft Positions from my FlightRadar24 ADBS Scanner.
My data is stored in a MySQL Database.
I am using this Query in the GeoMap Visualisation:
SELECT REPLACE(REPLACE(REPLACE(c1.READING,"aircraft.",""),".lon",""),".lat","") as Aircraft , c1.VALUE as lat, c2.VALUE as lon
FROM `current` c1
join `current` c2 on c1.device = c2.device
and c1.`READING` Like '%lat%'
and c2.`READING` Like '%lon%'
WHERE c1.DEVICE = 'FR24_aircraft'
GROUP BY Aircraft
The problem is, that my corrdinates are shown incorrect.
I tried to convert “lat” and “lon” to numeric, but this also doesn’t help.
If i inspect the Data, the JSON output just looks fine:
{
"state": "Done",
"series": [
{
"refId": "A",
"meta": {
"executedQueryString": "SELECT REPLACE(REPLACE(REPLACE(c1.READING,\"aircraft.\",\"\"),\".lon\",\"\"),\".lat\",\"\") as Aircraft , c1.VALUE as lat, c2.VALUE as lon \nFROM `current` c1\n join `current` c2 on c1.device = c2.device \n and c1.`READING` Like '%lat%'\n and c2.`READING` Like '%lon%' \n WHERE c1.DEVICE = 'FR24_aircraft'\n GROUP BY Aircraft",
"transformations": [
"convertFieldType",
"convertFieldType"
]
},
"fields": [
{
"name": "Aircraft",
"type": "string",
"typeInfo": {
"frame": "string",
"nullable": true
},
"config": {
"unit": "none",
"color": {
"mode": "fixed"
},
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
}
]
},
"mappings": []
},
"values": [
"0",
"1",
"10",
"11",
"12",
"13",
"14",
"15",
"16",
"17",
"18",
"19",
"2",
"20",
"21",
"22",
"23",
"24",
"25",
"26",
"27",
"28",
"29",
"3",
"30",
"31",
"32",
"33",
"34",
"35",
"36",
"37",
"38",
"39",
"4",
"40",
"41",
"42",
"43",
"44",
"45",
"46",
"47",
"48",
"5",
"51",
"52",
"6",
"7",
"8",
"9"
],
"entities": {},
"state": {
"scopedVars": {
"__series": {
"text": "Series",
"value": {
"name": "Series (A)"
}
},
"__field": {
"text": "Field",
"value": {}
}
},
"seriesIndex": 0
}
},
{
"name": "lat",
"type": "string",
"typeInfo": {
"frame": "string",
"nullable": true
},
"config": {
"unit": "none",
"color": {
"mode": "fixed"
},
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
}
]
},
"mappings": []
},
"values": [
"51.461404",
"51.728027",
"51.209929",
"53.805222",
"53.610488",
"51.687276",
"53.610255",
"53.365391",
"53.823697",
"54.126343",
"52.312271",
"53.897296",
"52.583592",
"53.564588",
"52.436254",
"53.395065",
"53.261858",
"53.505569",
"51.960526",
"54.276901",
"53.630676",
"52.264664",
"52.157867",
"51.136795",
"54.610989",
"53.200642",
"52.147677",
"52.569489",
"52.898071",
"52.582103",
"53.391041",
"52.87912",
"53.465012",
"54.371571",
"52.994156",
"52.492164",
"51.682574",
"53.707363",
"51.57015",
"53.787186",
"54.411896",
"51.593536",
"51.891174",
"51.778656",
"51.708643",
"53.832123",
"53.832123",
"51.216888",
"52.285904",
"54.037046",
"54.201841"
],
"entities": {},
"state": {
"scopedVars": {
"__series": {
"text": "Series",
"value": {
"name": "Series (A)"
}
},
"__field": {
"text": "Field",
"value": {}
}
},
"seriesIndex": 1
}
},
{
"name": "lon",
"type": "string",
"typeInfo": {
"frame": "string",
"nullable": true
},
"config": {
"unit": "none",
"color": {
"mode": "fixed"
},
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
}
]
},
"mappings": []
},
"values": [
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043",
"11.450043"
],
"entities": {},
"state": {
"calcs": {
"sum": 583.9521930000001,
"max": 11.450043,
"min": 11.450043,
"logmin": 11.450043,
"mean": 11.450043,
"last": 11.450043,
"first": 11.450043,
"lastNotNull": 11.450043,
"firstNotNull": 11.450043,
"count": 51,
"nonNullCount": 51,
"allIsNull": false,
"allIsZero": false,
"range": 0,
"diff": 0,
"delta": 0,
"step": 0,
"diffperc": 0,
"previousDeltaUp": true
},
"scopedVars": {
"__series": {
"text": "Series",
"value": {
"name": "Series (A)"
}
},
"__field": {
"text": "Field",
"value": {}
}
},
"seriesIndex": 2
}
}
],
"length": 51
}
],
"annotations": [],
"request": {
"app": "dashboard",
"requestId": "Q287",
"timezone": "browser",
"panelId": 2,
"dashboardId": 5,
"range": {
"from": "2021-11-28T03:27:27.516Z",
"to": "2021-11-28T09:27:27.516Z",
"raw": {
"from": "now-6h",
"to": "now"
}
},
"timeInfo": "",
"interval": "1m",
"intervalMs": 60000,
"targets": [
{
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "SELECT REPLACE(REPLACE(REPLACE(c1.READING,\"aircraft.\",\"\"),\".lon\",\"\"),\".lat\",\"\") as Aircraft , c1.VALUE as lat, c2.VALUE as lon \nFROM `current` c1\n join `current` c2 on c1.device = c2.device \n and c1.`READING` Like '%lat%'\n and c2.`READING` Like '%lon%' \n WHERE c1.DEVICE = 'FR24_aircraft'\n GROUP BY Aircraft",
"refId": "A",
"select": [
[
{
"params": [
"id"
],
"type": "column"
}
]
],
"table": "history",
"timeColumn": "TIMESTAMP",
"timeColumnType": "timestamp",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
],
"datasource": "FHEM"
}
],
"maxDataPoints": 726,
"scopedVars": {
"__interval": {
"text": "1m",
"value": "1m"
},
"__interval_ms": {
"text": "60000",
"value": 60000
}
},
"startTime": 1638093026577,
"rangeRaw": {
"from": "now-6h",
"to": "now"
},
"endTime": 1638093026848
},
"timeRange": {
"from": "2021-11-28T03:27:27.516Z",
"to": "2021-11-28T09:27:27.516Z",
"raw": {
"from": "now-6h",
"to": "now"
}
},
"timings": {
"dataProcessingTime": 0
},
"structureRev": 2
}
Does anyone have an idea what i am doing wrong?