49

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

  • 1
    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 '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. – user13835 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. – RyanDalton 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 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 '13 at 9:25
53

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

  • 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. – RyanDalton 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 3 '12 at 11:14
  • on a side note, @underdark, does QGIS accepts other types of PKs? – George Silva Nov 9 '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 7 '18 at 20:46
5

(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!

  • 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? – RyanDalton Nov 9 '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 Nov 9 '16 at 22:18
  • GID doesn't have any affect on this... – DPSSpatial Nov 9 '16 at 22:54
  • Just dreaming up possibilities. thanks for checking – RyanDalton Nov 10 '16 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 '17 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';
  • Yes, running this check against my view does return: the_geom,Point,4269,2 – RyanDalton May 18 '12 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.

  • Yes, the geonames table has "geonameid" that is a unique ID and has a PRIMARY KEY constraint set on that field. – RyanDalton May 18 '12 at 18:41

protected by Midavalo Oct 2 '17 at 16:51

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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