PostgreSQL/PostGIS: create spatial view

Question

I have a polygon layer and a point layer.
1) I wrote a spatial query to count how many points fall in each polygon:

SELECT polR.codeR, count(pntAZ.geom) as tot_pntAZ
FROM boundaryR as polR LEFT JOIN waypointsAzim as pntAZ
ON st_contains(polR.geom,pntAZ.geom)
GROUP BY polR.codeR;

This SELECT does the job correctly.

2) I want to create a view with the centroids of boundaryR polygon layer, attaching also the result of the previuos SELECT statement. To do this I wrote the following:

CREATE OR REPLACE VIEW view_waypAZ AS 
SELECT polR.codeR, polR.nameR, ST_centroid(polR.geom) as geom
FROM boundaryR as polR;

but the result is a non-spatial view: it's just a table, but I need a spatial view of points (the centroids). So I tryed to add the geometry column:

INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'geodata', 'view_waypAZ', 'geom', ST_CoordDim(geom), ST_SRID(geom), GeometryType(geom) FROM geodata.view_waypAZ LIMIT 1;

The query runs succesfully but the added row in geometry_columns table is not complete: SRID is 0 (should be 4326) and TYPE is "geometry" (should be MULTIPOINT).

So, the question is: how can I make a spatial view of centroids from a polygon layer? And how to "attach" to it also the results of statement 1? I hope everything is clear enough.
Thanks in advance

• albus2011: PostgreSQL 9.3 and PostGIS 2.1

Answer 1

You may be simply getting hung up on terminology - it seems like your view definition is correct:

1) You don't necessarily need to insert the view geometry column into the geometry_columns table - it's not normally done for postgres views;

2) You can alter your SQL a bit to assign the view geometry an SRID:

CREATE OR REPLACE VIEW view_waypAZ AS 
SELECT polR.codeR, polR.nameR, ST_SetSRID(ST_centroid(polR.geom),4326) as geom
FROM boundaryR as polR;

3) The data type of the column is "geometry", and the result of ST_Centroid() is geometry of geometry type "POINT" (not "MULTIPOINT"). If you really need MULTIPOINT geometry type, you could apply ST_Multi() like this:

CREATE OR REPLACE VIEW view_waypAZ AS 
SELECT polR.codeR, polR.nameR, ST_SetSRID(ST_Multi(ST_centroid(polR.geom)),4326) as geom
FROM boundaryR as polR;

• albus2011: Thank you @mtn.biker, now it works! But when I try to put together the CREATE VIEW with SELECT statement (point 1 in my first post) the resulting view is correct but I can't add it to QGIS because of an error: "the layer view_waypAZ cannot be added because is not valid". Why?
• mtn.biker: After you CREATE the view, you need to grant privileges on it, just like a table. Try: GRANT SELECT ON TABLE view_waypAZ TO public; (or whatever username you sign in to the db from QGIS)
• albus2011: No way: same error. This is the complete statement: 'CREATE OR REPLACE VIEW geodata.view_waypAZ AS SELECT polR.codeR, count(pntAZ.geom) as tot_pntAZ, ST_SetSRID(ST_Multi(ST_centroid(polR.geom)),4326) as geom FROM geodata.boundaryR as polR LEFT JOIN geodata.waypointsAzim as pntAZ ON st_contains(polR.geom,pntAZ.geom) GROUP BY polR.codeR, polR.geom; GRANT SELECT ON TABLE geodata.view_waypAZ TO geouser;'
• albus2011: Furthermore, it is strange, because I cannot load the view_waypAZ on QGIS Desktop but I'm able to load it as PostGIS layer on my GeoServer site...

Answer 2

The easiest way I found is to cast the geometry type like this:

CREATE OR REPLACE VIEW view_waypAZ AS 
SELECT polR.codeR, polR.nameR, ST_centroid(polR.geom)::geometry(Point,4326) as geom
FROM boundaryR as polR;

Check out PostGIS section 4.3.4. Manually Registering Geometry Columns in geometry_columns https://postgis.net/docs/using_postgis_dbmanagement.html