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?

  • What error are you getting? Is your schema actually called xxx? – LR1234567 Jul 10 '14 at 18:32
  • Schema is called xxx. I got no error, that statement is creating view but it is ordinary table,no spatial one. – Roman Jul 10 '14 at 18:41
  • What is it the output exactly? – Jakub Kania Jul 10 '14 at 23:24
  • Output is simple table. Problem was it was geometry table but qGIS didn't see that geometry. – Roman Jul 11 '14 at 5:18

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:

  SELECT gid, ST_ConvexHull(buildings_2013.geom)::geometry(POLYGON,4326) As geom 
   FROM xxx.buildings_2013;

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).

  • By spatial view I meant viewing it in qGIS like a shapefile. I saw geometry column but qGIS were not able to load it. No it's working just fine! Thank you very much for your help. – Roman Jul 11 '14 at 5:00
  • One more thing, your statement creating convex hull for every record, how to process it for all all layer? – Roman Jul 11 '14 at 5:09
  • CREATE OR REPLACE VIEW AS SELECT 1 as gid, ST_ConvexHULL(st_Collect(geom))::geometry(POLYGON,4326) as geom FROM xxx should do the job – simplexio Jul 11 '14 at 9:46
  • Wow, thanks! I was almost there, didnt know about '1' after SELECT. I was putting column that exist in building table. – Roman Jul 11 '14 at 10:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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