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