Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

In PostGIS 2.0, what is the most 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 this question, 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 please 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?

UPDATE 1: (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

UPDATE 2: 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". Any thoughts?

share|improve this question
    
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
1  
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

3 Answers 3

up vote 23 down vote accepted

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

share|improve this answer
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 Nov 9 '12 at 15:49

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';
share|improve this answer
    
Yes, running this check against my view does return: the_geom,Point,4269,2 –  RyanDalton May 18 '12 at 18:37

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.

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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