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".

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
RyanKDalton
  • 23,068
  • 17
  • 110
  • 178
  • 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 03 '12 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. –  Dec 28 '12 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 '12 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 '13 at 08: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 '13 at 09:25

4 Answers4

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

underdark
  • 84,148
  • 21
  • 231
  • 413
  • 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 '12 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 '12 at 21:54
  • That's right, 1.7 tried to guess the primary key. But it didn't work well. – underdark Sep 03 '12 at 11:14
  • on a side note, @underdark, does QGIS accepts other types of PKs? – George Silva Nov 09 '12 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 07 '18 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!

DPSSpatial_BoycottingGISSE
  • 18,790
  • 4
  • 66
  • 110
  • 1
    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 09 '16 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 09 '16 at 22:18
  • GID doesn't have any affect on this... – DPSSpatial_BoycottingGISSE Nov 09 '16 at 22:54
  • Just dreaming up possibilities. thanks for checking – RyanKDalton Nov 10 '16 at 03: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 '17 at 01:27
  • @GT. right on... qgis and even arcmap need an OID field to sort through the features... doesn't need to be PK, though... I think the row_number() can be shortened to row_number() over() which just assigns sequential values... – DPSSpatial_BoycottingGISSE Feb 14 '17 at 01:52
  • In QGIS 3 using the "data source manager" it is a bit different. If you use the "browser" and select postgis then navigate to the layers all appear and can be selected. If there is no unique index in the first column it throws and error which is displayed briefly. If you select postgressql instead you get the familiar dialogue from older version but it now appears that you have to manually select the "feature id", check the box and lastly click on the schema to make the view loadable. – Russell Fulton Apr 07 '18 at 21:35
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';
JJD
  • 1,511
  • 1
  • 17
  • 30
Ian Turton
  • 81,417
  • 6
  • 84
  • 185
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.

Ando
  • 3,039
  • 5
  • 30
  • 42
  • Yes, the geonames table has "geonameid" that is a unique ID and has a PRIMARY KEY constraint set on that field. – RyanKDalton May 18 '12 at 18:41