54

In PostGIS 2.0, I am seeking an efficient way to create a spatially enabled view that is viewable in QGIS.

I followed the context of Part 2 of @Mike Toews answer to How to create a table from existing tables (applying new schemas) in postgis DB?, and the view shows up as a point layer (in my case) in the "Add PostGIS Layers" dialog. But after creating the view and spatially enabling it, I received the following error when trying to add the view to QGIS:

You must select a table in order to add a layer

Can someone explain why the spatial view will not load into QGIS, and what I need to do in order to make it loadable as a layer in QGIS?

I'm currently using QGIS v1.8 from the OSGEO installer

I view is built from the geonames database, loaded into PostGIS. The geoname table/spatial data can be selected/viewed in QGIS just fine.

When I try to select the "mt_view", QGIS sees the view in the dialog, but won't let me select it. It gives a dotted box around the area, but does not highlight and select the view.

QGIS cannot select the mt_view as a layer

I just noticed that when I run my first query:

CREATE OR REPLACE VIEW data.test AS
SELECT * FROM data.geoname
WHERE admin1='MT'

the view shows up in the QGIS Add Layer Dialog, but is unselectable. Then when I run:

SELECT Populate_Geometry_Columns('data.test'::regclass);

I get the result of "0", where according to the documentation, I think it should be returning "1".

Improve this question
5
  • 2
    I confirm the problem. With QGIS 1.8, PostGIS 2.0, and a view created from a table with a primary key, tha view gets listed in the avaiable layers but the error message "You must select a table in order to add a layer" appears.
    – giohappy
    Dec 3, 2012 at 17:46
  • 2
    Is there another way to set automatically de primary key in Postgis Add Layer window when we work with views? It's very hard work to select a primary key field in this screen for any layers/views if you imagine a normal GIS user and a view have lots integer fields.
    – user13835
    Dec 28, 2012 at 12:18
  • @Pgimenez, I would suggest starting a new question with what you stated above, and then reference this questions url in your own. That will give it much wider visibility.
    – RyanKDalton
    Dec 28, 2012 at 18:24
  • There is no way to visualize a convexhull in qgis 1.8 lisboa? I have tried following vievs in Postgis 1.5 I Was told to use an id column to publish it in Qgis. If you not use an id column, u cannot load it anyway because of an error. 1. create or replace view con_hull_baeume3(bid) as select '0', ST_ConvexHull(ST_Collect(geom)) FROM baeume; 2. create or replace view con_hull_baeume4(bid) as select bid, ST_ConvexHull(ST_Collect(geom)) FROM baeume group by bid; but this one make a convexhull on each of the geometrypoints. I mean just use 'select ST_ConvexHull(ST_Collect(geom)) FROM baeume;' works
    – ulan
    Mar 22, 2013 at 8:53
  • 1
    Welcome! Please post this as a new question, since it doesn't seem to answer the original one. At least if it is a question, since the last statement isn't clear whether you succeeded or not.
    – lynxlynxlynx
    Mar 22, 2013 at 9:25

4 Answers 4

Reset to default
59

I can confirm that it is not possible to simply select a listed view.

It seems necessary to select the "primary key column" first. Then the table turns selectable:

enter image description here

Improve this answer
5
  • 1
    Wow, I would have never seen that. Thanks so much! I've edited the title to reflect the issue was with QGIS, not the PostGIS view.
    – RyanKDalton
    May 18, 2012 at 22:03
  • It appears this is an issue for QGIS 1.8 (which I haven't used yet). These steps are not required for 1.7.
    – Mike T
    May 19, 2012 at 21:54
  • That's right, 1.7 tried to guess the primary key. But it didn't work well.
    – underdark ♦
    Sep 3, 2012 at 11:14
  • on a side note, @underdark, does QGIS accepts other types of PKs?
    – George Silva
    Nov 9, 2012 at 15:49
  • 1
    In QGIS3 the column is headed "feature id" not "primary key column" and (at least for me) it is off the edge of the the default window (i.e. not visible) and no indication that there were more columns! I think I will log a feature request that one gets prompted for these details (if needed) after selection. It would appear that the developers assume that people will make sure there is a unique key in the first column.
    – Russell Fulton
    Apr 7, 2018 at 20:46
14

(Updating my answer slightly)

As long as the OBJECTID (row_number) field is first in the column definition of the view, QGIS will pick it up without asking you to add from the 'add vector layer' menu.

CREATE OR REPLACE VIEW dqmt.addressverify AS 
 SELECT row_number() OVER (ORDER BY newaddresses.addressid) AS objectid,
    newaddresses.addressid
  , geom
   FROM dqmt.newaddresses;

I just fixed some old views that were broken as per the original question, but moving the OBJECTID field to the start of the view fixed the problem!

Improve this answer
7
  • I've used the "row_number () over()" syntax for many views, too. I usually have used "gid" for my primary key field. Do all of your other views use "objectid" as their primary key?
    – RyanKDalton
    Nov 9, 2016 at 21:03
  • usually objectid - which worked in the example above - but will try gid... is that something QGIS recognizes before any other 'id' field?
    – DPSSpatial_BoycottingGISSE
    Nov 9, 2016 at 22:18
  • GID doesn't have any affect on this...
    – DPSSpatial_BoycottingGISSE
    Nov 9, 2016 at 22:54
  • Just dreaming up possibilities. thanks for checking
    – RyanKDalton
    Nov 10, 2016 at 3:25
  • (Late to this, but...) at a guess, I think that if a table that's being loaded has a PRIMARY KEY defined, QGIS routinely picks it up: it never fails to 'pick' the PK on any of my tables that have a PK, although almost all my PKs are VARCHAR. That can't happen with a VIEW because PostgreSQL VIEWs can't have CONSTRAINTs. Pretty sure that's why it's necessary to select the PK for VIEWs by hand when importing to QGIS through the 'Add PostGIS Layers' button, which is why I import them using a Python script.
    – GT.
    Feb 14, 2017 at 1:27
4

Does your view show up in the geometry_columns table? According to the manual the view should automatically show up in 2.0 but I haven't tried it.

Try running the following to check:

SELECT f_geometry_column As col_name, type, srid, coord_dimension As ndims 
FROM geometry_columns
WHERE f_table_name = 'my_spatial_table' AND f_table_schema = 'my_schema';
Improve this answer
1
  • Yes, running this check against my view does return: the_geom,Point,4269,2
    – RyanKDalton
    May 18, 2012 at 18:37
3

Is your view using an unique integer field like a "gid" or PK equivalent from one of the tables in your view?

I know that in previous versions of QGIS I have had problems with views being added to the map when there was no unique integer field. However I did receive a different error message pointing that issue out, but it may be worth checking to cover off all bases.

Improve this answer
1
  • Yes, the geonames table has "geonameid" that is a unique ID and has a PRIMARY KEY constraint set on that field.
    – RyanKDalton
    May 18, 2012 at 18:41

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