convex hullextentspostgispostgresqlsql
Please help,
I have a polygon layer representing building footprints. I want to create new view in postgis that will contain rectangle representing bounding box and also another view that will have convex hulls polygon of all records in buildings table.
I'm creating something like this:
CREATE OR REPLACE VIEW xxx.extent AS SELECT ST_ConvexHull(buildings_2013.geom) as geom from xxx.buildings_2013;
but I won't create spatial view. Could you please help?
I can confirm that it is not possible to simply select a listed view.
It seems necessary to select the "primary key column" first. Then the table turns selectable:
The documentation page on ST_ConvexHull gives a good example. You should just need:
SELECT ST_ConvexHull(
ST_Collect(r.the_geom::geometry)
) AS the_geom
FROM restaurants AS r;
To store the result in a table that can't be visualised in (e.g.) QGIS, you need to use this statement as part of the definition of a view, or as part of a CREATE TABLE or INSERT INTO. Software differs on whether you need to define your own UUID column. There are good answers on StackOverflow about generating these. The simplest way to create a new table with your result is:
CREATE TABLE ch AS
SELECT ST_ConvexHull(
ST_Collect(r.the_geom::geometry)
) AS the_geom
FROM restaurants AS r;
You can also predefine a schema and indexes and use INSERT INTO.
Best Answer
What do you mean by no spatial view. You mean you are not seeing it in geometry_columns table or your viewer is not loading it? If you are using PostGIS 1.5 or lower, you have to manually add the view to the table geometry_columns table. In PostGIS 2.0+, it will show in list but won't show type. To get type you need to do cast it.
Also many views like QGIS for example are finicky and won't load something if it has no id it recognizes as a key. You can try throwing in the primary key like gid or something in the view. So PostGIS 2+ definition should look Something like:
replace 4326 with the SRID of your geom column. It has to be a constant for casting unfortunately so you can't just do geometry(POLYGON, ST_SRID(geom).