Advertisements

How to create Point Layers from x/y Data on the fly with PostGIS and QGIS

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():

select  
  row_number() over (order by col1)::int AS my_id,
  col1, 
  col2,
  x, y, 
  makepoint(x,y) as the_geom
from my_table
Advertisements
2 comments
  1. sarah said:

    sir,
    i cannot understand this plugin means how to configure it with QGIS and how to use it. i want to do routing by using pg-routing. is there any tutorial of pg-routing for A* algorithm for windows platform?

    • underdark said:

      Hi Sarah,
      Which plugin are you referring to? Do you mean RT Sql Layer? There is not much to configure. You just need to define the connection to the PostGIS db via the “Add PostGIS layer” dialog. Afterwards, you can select the database from the dropdown field in RT Sql Layer.
      I’m not aware of any pgRouting A* Tutorials for Windows, sorry.

%d bloggers like this: