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;

Tuesday, January 29, 2013

PostgreSQL - Determining Table and Index Size

This is somewhat of a follow up to my previous post. If you are interested in knowing the size of your postgres table and associated indexes, sequences, etc., you can run the following SQL to find out. Enjoy.

SELECT pg_size_pretty(pg_relation_size(pg_class.oid)), pg_class.relname, pg_namespace.nspname,
    
CASE pg_class.relkind WHEN 'r' THEN 'table' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 'v' THEN 'view' WHEN 't' THEN 'TOAST'
    
ELSE pg_class.relkind::text END
    FROM
pg_class
            
LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace)
    
ORDER BY pg_relation_size(pg_class.oid) DESC;