5

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'm just starting with GIS tools so any help is greatly appreciated!

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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