Sometimes, we just want to visualize the contents of a PostGIS table containing some x/y data but no actual geometries in QGIS. But there the problems arise: We don’t have the right to add a geometry column, the table doesn’t have a suitable ID or OIDs (QGIS demands a unique integer ID) and we can’t or don’t want to mess with the database anyway. Loading the table with “Add PostGIS Layer” will result in a non-spatial layer (or fail if you use an older QGIS versions).
RT Sql Layer Plugin to the rescue!
I presented this plugin in a previous post. It allows you to execute any SQL SELECT statement, even really complex ones. Luckily, this time we don’t need anything fancy, only the two functions row_number() and makepoint():
row_number() over (order by col1)::int AS my_id,
makepoint(x,y) as the_geom
Have you ever wondered how to comfortable visualize PostGIS queries? Meet “RT Sql Layer” a powerful and comfortable QGIS plugin that allows building and visualizing queries on your PostGIS data.
RT Sql Layer comes with a graphic query builder:
RT Sql Layer Query Builder dialog
It allows saving/loading of queries to speed up your work flow.
The query results will be loaded as a new layer:
Loaded query layer
RT Sql Layer is available through Faunalia Plugin Repository.
For another great example on what can be achieved with this plugin, read Carson Farmer’s post on “pgRouting, OpenStreetMap, and QGIS” where he describes how to build your own routing database and visualize routing results in QGIS with RT Sql Layer.
More on RT Sql Layer: How to create Point Layers from x/y Data on the fly with PostGIS and QGIS
For all of us who couldn’t attend FOSS4G in Barcelona this year there are some of the great workshops available online:
BostonGIS has compiled a great cheatsheet for PostGIS 1.5. You’ll find both a web version and a PDF version here: http://www.bostongis.com/postgis_quickguide.bqg.
Additionally, they have published an extensive comparison of SQL Server 2008 R2, Oracle 11G R2, and PostgreSQL/PostGIS 1.5 spatial features: http://www.bostongis.com/PrinterFriendly.aspx?content_name=sqlserver2008r2_oracle11gr2_postgis15_compare.