Thursday, January 31, 2013

PostGIS 1.5 - Batch Populate Geometry Columns


When creating new spatial tables – say as the result of an analysis – you should register and add constraints to your table. At least this is the proper thing to do… If you are like me, I get a little lazy and the next thing I know I have 50 tables without proper registration. This is fine if you do not plan on using the tables again or simply plan to dump out as a shapefile, but if you plan on keeping them around and using again, you should register and add constraints.

You can do this on one table simply by running: 
SELECT Populate_Geometry_Columns('schema.table'::regclass);

However, if you have many tables, you can run the following on an entire schema:
WITH
AS (SELECT table_schematable_name
                FROM information_schema.tables
                WHERE table_schema 'foo' AND table_type 'BASE TABLE')

SELECT Populate_Geometry_Columns((table_schema::text || '.' || table_name::text)::regclass)FROM t;

No comments:

Post a Comment