0

I have a Postgres db storaging data from a mobile sensor node, and want to show the location in a QGIS MapCanvas.

Data is in a column in a jsonb format (Location and temperature). I'm trying to use a virtual layer to extract the data from Postgres db layer (not using PostGIS, I'm keeping the original db with no geometry data). There is no way to extract the data from json column manually, and I can't find any solution in PyQGIS. When I create a virtual layer using a query, the layer shows an error.

vlayer = QgsVectorLayer( "?query=SELECT (object ->> 'longitud')::real AS longitud FROM device_up", "vlayer", "virtual" )

object is the jsonb column. Has anybody worked with something similar?

1 Answer 1

0

A recipe considering you have data with spatial information but no geometry column in your table.

Basic table creation in PostGIS. First statement loads PostGIS spatial functions you will be able to use within QGIS. It does not change your data structure in your tables

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB);
INSERT INTO test(data) VALUES ('{"longitude": 1.46, "latitude": 44.38, "temperature": 16.7}');

Then, you can try to add table (not virtual) by creating geometry on the fly to visualise spatial info in QGIS.

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "yourdb", "youruser", "yourpassword")
sql = "SELECT id, ST_SetSRID(ST_MakePoint((data->>'longitude')::float, (data->>'latitude')::float), 4326) AS geom , data->>'temperature' AS temperature FROM test"
uri.setDataSource('', f'({sql})', 'geom', '', 'id')
vlayer = QgsVectorLayer(uri.uri(), 'myLayer','postgres')
QgsProject.instance().addMapLayer(vlayer)
1
  • Thank you so much, that was I looking for, It would be very difficult to me to find the setDataSource recipe in the web. Apr 9, 2021 at 20:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.