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

  • 2
    Try merge shapefiles in QGIS: Vector - Data Management Tools - Merge shapefiles to one, then import this shapefile in PostGIS. Feb 7, 2014 at 8: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 7, 2014 at 8:39
  • Try QGIS plugin for merging shapefiles Merge shapes, - there is option add file name to column (gis.stackexchange.com/questions/22175/…) Feb 7, 2014 at 11:30
  • Does this gis.stackexchange.com/questions/7803/… help?
    – Martin F
    Feb 7, 2014 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, 2014 at 19:07

1 Answer 1


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 $$
    cTable CURSOR FOR SELECT demo.table_name FROM 
    (SELECT * FROM information_schema.tables WHERE table_schema = 'temp') as demo;
    tStatement text;
    nFlag integer;
    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;      
        nFlag := 1;
        tStatement := 'SELECT geom, source_file FROM temp.' || table_record.table_name; 
    END IF;
    -- 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;

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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