[GIS] PostgreSQL/PostGIS: create spatial view

postgispostgresqlsql

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

Best Answer

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;
Related Question