{"id":142,"date":"2013-01-25T12:21:00","date_gmt":"2013-01-25T12:21:00","guid":{"rendered":"https:\/\/blogs.ncl.ac.uk\/geospatialengineering\/?p=142"},"modified":"2014-10-10T14:39:50","modified_gmt":"2014-10-10T13:39:50","slug":"updating-geometry-columns-for-views-in-postgis","status":"publish","type":"post","link":"https:\/\/blogs.ncl.ac.uk\/geospatialengineering\/2013\/01\/25\/updating-geometry-columns-for-views-in-postgis\/","title":{"rendered":"Updating Geometry Columns for views in PostGIS"},"content":{"rendered":"<p>The recent upgrade to PostGIS has caused some issues with geometry types when creating views from geometry that don&#8217;t use the new typemod geometry modifiers. \u00a0The following workaround correctly inserts an entry into the geometry_columns view so you can see your data in QGIS and the like:<\/p>\n<div>\n<blockquote><p>DROP VIEW myview;<\/p>\n<p>CREATE OR REPLACE VIEW myview AS (<\/p>\n<p>SELECT field1, field2, field3,<\/p>\n<p>St_Transform(the_geom, 27700)::geometry(Point, 27700) as the_geom<\/p>\n<p>FROM mytable<\/p>\n<p>);<\/p><\/blockquote>\n<p>Change the geometry type and CRS as required.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>The recent upgrade to PostGIS has caused some issues with geometry types when creating views from geometry that don&#8217;t use the new typemod geometry modifiers. \u00a0The following workaround correctly inserts an entry into the geometry_columns view so you can see your data in QGIS and the like: DROP VIEW myview; CREATE OR REPLACE VIEW myview &hellip; <a href=\"https:\/\/blogs.ncl.ac.uk\/geospatialengineering\/2013\/01\/25\/updating-geometry-columns-for-views-in-postgis\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;Updating Geometry Columns for views in PostGIS&#8221;<\/span><\/a><\/p>\n","protected":false},"author":3072,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"class_list":["post-142","post","type-post","status-publish","format-standard","hentry","category-research"],"_links":{"self":[{"href":"https:\/\/blogs.ncl.ac.uk\/geospatialengineering\/wp-json\/wp\/v2\/posts\/142","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.ncl.ac.uk\/geospatialengineering\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.ncl.ac.uk\/geospatialengineering\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.ncl.ac.uk\/geospatialengineering\/wp-json\/wp\/v2\/users\/3072"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.ncl.ac.uk\/geospatialengineering\/wp-json\/wp\/v2\/comments?post=142"}],"version-history":[{"count":2,"href":"https:\/\/blogs.ncl.ac.uk\/geospatialengineering\/wp-json\/wp\/v2\/posts\/142\/revisions"}],"predecessor-version":[{"id":512,"href":"https:\/\/blogs.ncl.ac.uk\/geospatialengineering\/wp-json\/wp\/v2\/posts\/142\/revisions\/512"}],"wp:attachment":[{"href":"https:\/\/blogs.ncl.ac.uk\/geospatialengineering\/wp-json\/wp\/v2\/media?parent=142"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.ncl.ac.uk\/geospatialengineering\/wp-json\/wp\/v2\/categories?post=142"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.ncl.ac.uk\/geospatialengineering\/wp-json\/wp\/v2\/tags?post=142"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}