6

I've got a table called "MargamParkMap" - which is made up of polygons. One of the attributes in the table is "authors".

I've created a view with the aim of merging the polygons by author. What I've got is this:

SELECT 
row_number() OVER (ORDER BY "MargamParkMap".author) AS qgisid,
st_union(ST_MakeValid(ST_Multi("MargamParkMap".geom))) AS geom,
"MargamParkMap".author
FROM "MargamPark"."MargamParkMap"

GROUP BY "MargamParkMap".author;

So - row_number() is generating a new column with a unique ID, and the geom column is made up of the union of the original geom column.

If I run the query on it's own, it returns a lovely little table with all the right stuff in.

However, in QGIS - when I go to the view there are now 2 of them:

two postGIS layers of the same view

that is a really small picture...

Anyways - one has the correct geometry type, multipolygon, and SRID, the other doesn't...Is there any way of only showing the correct view?

(geometry_columns view shows the srid as 0, and type as geometry)

Also, is there a way of automatically selecting the Feature ID as the qgisid?

(PostGIS version 2.3.0, QGIS version 2.16.3)

  • Not an answer but a note. For read/write access to a PostGIS table from any GIS, that table has to have a primary key in a DB. QGIS recognizes tables with primary keys and shows no warning, but views can not have a primary key (it is a PostgreSQL limitation). That is why you have no selected feature ID. However it is possible to edit views if you set up triggers that will pass your editing requests to real tables. – Yaroslav Jan 26 '17 at 13:38
1

From the postgres-side it's not possible to solve this, as postgres doesn't support primary keys on views. Those keys are used by QGIS to determine the feature id column.

You could export the data into a new table like this

SELECT 
row_number() OVER (ORDER BY "MargamParkMap".author) AS qgisid,
st_union(ST_MakeValid(ST_Multi("MargamParkMap".geom))) AS geom,
"MargamParkMap".author
FROM "MargamPark"."MargamParkMap"
INTO
newtable
GROUP BY "MargamParkMap".author;

ALTER TABLE newtable
  ADD PRIMARY KEY (qgisid);
  • Okay, except then you'd be breaking the link with the original data. – Skipper Mar 22 '17 at 19:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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