[postgis-users] Uploading a folder with many files

[Pierre Racine]

I agree we should have an option to load all the rasters in different tables mostly if you tile them. However this little function should help you split your table into many:

----------------------------------------------------------------------------------------------------------------------
-- SplitTable
-- Split a table into a series of table which names are composed of the concatenation of a prefix
-- and the value of a column.
-- sourcetablename   - The name of the table to split into multiple table
-- targettableschema - The schema in which to create the new set of table
-- targettableprefix - The prefix of the set of table names to create.
-- suffixcolumnname  - The name of the column providing the suffix to each name.
--
-- Example to split the table 'test' into a set of table starting with 't_' and 
-- ending with the value of the column 'rid' to be created in the 'public' schema.
--
-- SELECT SplitTable('test', 'public', 't_', 'rid');;
----------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION SplitTable(sourcetablename text, targettableschema text, targettableprefix text, suffixcolumnname text)
RETURNS int AS
$BODY$
DECLARE
    newtablename text;
    uniqueid RECORD;
BEGIN
    FOR uniqueid IN EXECUTE 'SELECT DISTINCT ' || quote_ident(suffixcolumnname) || ' AS xyz123  FROM ' || sourcetablename LOOP
        newtablename := targettableschema || '.' || targettableprefix || uniqueid.xyz123;
    EXECUTE 'CREATE TABLE ' || quote_ident(newtablename) || ' AS SELECT * FROM ' || sourcetablename || ' WHERE ' || suffixcolumnname || ' = ' || uniqueid.xyz123;
    END LOOP;
    RETURN 1;
END;
$BODY$
LANGUAGE plpgsql VOLATILE STRICT;

Pierre

> -----Original Message-----
> From: Giannis Giakoumidakis [mailto:ggiakoumidakis at yahoo.com]
> Sent: Monday, May 14, 2012 10:27 AM
> To: Pierre Racine; PostGIS Users Discussion
> Subject: Re: [postgis-users] Uploading a folder with many files
> 
> This works but it loads all different raster data in one table, so in the "raster
> columns" view it appears only one row about these layers. I dont think this is
> what I want, because my aim is to load each raster layer in QGIS. Is it possible
> somehow to load many raster layers at once, everyone in a seperate table??
> Thanks.
> 
> 
> 
> ________________________________
> 
> From: Pierre Racine <Pierre.Racine at sbf.ulaval.ca>
> To: Giannis Giakoumidakis <ggiakoumidakis at yahoo.com>; PostGIS Users
> Discussion <postgis-users at postgis.refractions.net>
> Sent: Thursday, May 10, 2012 5:10 PM
> Subject: RE: [postgis-users] Uploading a folder with many files
> 
> 
> The PostGIS raster loader, raster2pgsql, supports wildcard so you can do:
> 
> raster2pgsql -I -C -F c:/temp/*.tif schema.table| psql -U postgres -d batabase
> 
> Pierre
> 
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-
> > bounces at postgis.refractions.net] On Behalf Of Giannis Giakoumidakis
> > Sent: Thursday, May 10, 2012 5:19 AM
> > To: Post GIS Users Discussion
> > Subject: [postgis-users] Uploading a folder with many files
> >
> > I want to upload to a database a big number of raster files (.tif), for example
> > 500, in a folder. They are numbered in a row, for example c001, c002,...,
> cc500.
> > Is there any way to upload all with one command or the whole folder at once,
> or
> > I have to do this one by one? Thanks.
> 
> 
>