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

migrated from stackoverflow.com Jun 16 '14 at 13:19

This question came from our site for professional and enthusiast programmers.

  • PostgreSQL 9.3 and PostGIS 2.1 – albus2011 Jun 16 '14 at 12:54
up vote 3 down vote accepted

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;
  • 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? – albus2011 Jun 17 '14 at 8:50
  • 1
    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) – mtn.biker Jun 17 '14 at 10:06
  • 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 Jun 17 '14 at 10:32
  • 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... – albus2011 Jun 17 '14 at 10:46

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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