Importing multiple shapefiles into single PostGIS table

Question

I have a folder full of shapefiles that I want to import into a single PostGIS table. This table should have the following columns:

id, filename, multipolygon

I can bulk import all shapefiles using the SPIT plugin for QGIS, but each shapefile has different attributes, thus they are placed in different tables and I cannot append them into just one. I don't really want any of the attributes, I just want each polygon to be associated with the file from which it came.

I've tried two basic strategies, and neither looks very promising to me:

Import all files normally and use SQL queries to extract the information I want from each table. My SQLfu is not strong enough to do this yet. In particular, how do I get the name of the file associated with the corresponding rows?
Programmatically generate .dbf files with the associated filename as an attribute. Then import all files normally and append to a single table. Seems quite hacky.

I realize very similar questions have been posted before, but none of them seem to address this issue. I am just starting with GIS tools

Try merge shapefiles in QGIS: Vector - Data Management Tools - Merge shapefiles to one, then import this shapefile in PostGIS. — spatialhast, Feb 07 '14 at 08:33
@HasT Thanks for the suggestion. I tried to do that and I can successfully get all of the polygons into a single table, but it's not clear how I can compute the association between polygon <--> filename. — geomnerd, Feb 07 '14 at 08:39
Try QGIS plugin for merging shapefiles Merge shapes, - there is option add file name to column (http://gis.stackexchange.com/questions/22175/how-to-add-field-with-filename-when-merging-shapefiles-with-ogr2ogr) — spatialhast, Feb 07 '14 at 11:30
Does this http://gis.stackexchange.com/questions/7803/bulk-load-multiple-shapefiles-into-postgis?rq=1 help? — Martin F, Feb 07 '14 at 20:47
I haven't had time to comment for a few days. @HasT, I was using the plugin that came with QGIS, which does not have the option to add file name to column. I found the one you refer to, and it does what I needed, so thanks! martin f I looked through that thread and saw a couple of things that were promising, but it didn't contain all the info I needed. Thanks though! — geomnerd, Feb 10 '14 at 19:07

Sorin Călinică · Accepted Answer · 2014-02-11T14:38:00.787

One possible solution to your problem could be this:

1) Create a new schema in your PostgreSQL. Let's call it temp.

2) Let SPIT plugin for QGIS do its job, but import all the shapefiles in the temp schema.

3) Run this script:

DO $$
DECLARE
    cTable CURSOR FOR SELECT demo.table_name FROM 
    (SELECT * FROM information_schema.tables WHERE table_schema = 'temp') as demo;
    tStatement text;
    nFlag integer;
BEGIN
    tStatement := '';
    nFlag := 0;
    FOR table_record IN cTable LOOP
        -- for every table in the temp schema add a new column
        -- having the table name inserted in each table row
        EXECUTE 'ALTER TABLE temp.' || table_record.table_name || 
        E' ADD COLUMN source_file character varying(64) DEFAULT \'' 
        || table_record.table_name || E'\'::character varying';

        -- build the UNION statement for all the tables
    IF nFlag > 0 THEN
        tStatement := 'SELECT geom, source_file FROM temp.' || table_record.table_name || ' UNION ALL ' || tStatement;      
    ELSE
        nFlag := 1;
        tStatement := 'SELECT geom, source_file FROM temp.' || table_record.table_name; 
    END IF;
    END LOOP;
    -- create a new table (in an other schema) based on UNION result
    tStatement := 'CREATE TABLE public.demo_union WITH OIDS AS ' || tStatement; 
    -- RAISE NOTICE 'sql statement: %', tStatement;
    EXECUTE tStatement;
END$$;

This way you will obtain a demo_union table inside your public schema, after that you can drop the temp schema:

enter image description here

Importing multiple shapefiles into single PostGIS table

1 Answers1

Related