[postgis-users] upload a folder of raster files

Fri Sep 7 07:27:57 PDT 2012

So you mean I firstly create SplitTable as it is and then I change arguments in SELECT SplitTable(), am I right?

All right but when I execute the query to create the function at the SQL Editor I get an error:     ERROR:  syntax error at or near "(", LINE 19: ... quote_ident(targettableschema) || '.' quote_ident(sourcetab...

I don't change anything, only copying from the page.

What is wrong?

________________________________
 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: Friday, September 7, 2012 4:20 PM
Subject: RE: [postgis-users] upload a folder of raster files

I never you had to change the SplitTable function, just change the argument you pass to it. and you have to create a new id per filename. rid is not unique per filename.

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: Friday, September 07, 2012 4:44 AM
> To: Pierre Racine; PostGIS Users Discussion
> Subject: Re: [postgis-users] upload a folder of raster files
> 
> I firstly try with rid to see if it's working. It gives me an error:
> 
> ERROR:  syntax error at or near "("
> LINE 19: ...  FROM ' || quote_ident(public) || '.' quote_ident(cc_all ) ...
>                                                                                                ^
> 
> See anything wrong here??
> 
> 
> -----------------------------------------------------------------------
> -- SplitTable
> -- Split a table into a series of tables which names are composed of the
> -- concatenation of a prefix and the value of a column.
> --
> -- sourcetablename   - Name of the table to split into multiple table
> -- targettableschema - Name of the schema in which to create the new set
> --                     of table
> -- targettableprefix - Prefix of the set of table names to create.
> -- suffixcolumnname  - Name of the column providing the suffix to each name.
> -----------------------------------------------------------------------
> CREATE OR REPLACE FUNCTION SplitTable(cc_all text, public text, cc_ text, rid
> text)
> RETURNS int AS
> $BODY$
> DECLARE
>     newtablename text;
>     uniqueid RECORD;
> BEGIN
>     FOR uniqueid IN EXECUTE 'SELECT DISTINCT ' || quote_ident(rid) || '::text AS
> xyz123  FROM ' || quote_ident(public) || '.' quote_ident(cc_all) LOOP
>         newtablename := cc_ || uniqueid.xyz123;
>     EXECUTE 'CREATE TABLE ' || quote_ident(public) || '.' ||
> quote_ident(newtablename) || ' AS SELECT * FROM ' || cc_all || ' WHERE ' ||
> rid || '::text = ' || quote_literal(uniqueid.xyz123);
>     END LOOP;
>     RETURN 1;
> END;
> $BODY$
> LANGUAGE plpgsql VOLATILE STRICT;
> 
> 
> 
> 
> 
> ________________________________
> 
> 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, September 6, 2012 5:34 PM
> Subject: RE: [postgis-users] upload a folder of raster files
> 
> 
> Then I would suggest you use the SQL method:
> 
> -Create a new table creating a unique numeric id per filename (use
> generate_series()),
> 
> -Join this table to the raster table so you get one numeric id per filename and
> 
> -Use this field when you call SplitTable().
> 
> Pierre
> 
> > -----Original Message-----
> > From: Giannis Giakoumidakis [mailto:ggiakoumidakis at yahoo.com]
> > Sent: Thursday, September 06, 2012 10:28 AM
> > To: Pierre Racine; PostGIS Users Discussion
> > Subject: Re: [postgis-users] upload a folder of raster files
> >
> > Anyhow, it's not so important this for me.
> >
> >
> >
> > The best would be something that reminds the names of the files but it's ok if it
> > is as simple as  cc001, cc002, cc003, ... etc  or  whatever it starts from cc that
> is
> > common to all the filenames and then a row of numbers.
> >
> >
> >
> >
> >
> > ________________________________
> >
> > 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, September 6, 2012 5:03 PM
> > Subject: RE: [postgis-users] upload a folder of raster files
> >
> >
> > How do you want your tables names to be created from those filenames?
> >
> > > -----Original Message-----
> > > From: Giannis Giakoumidakis [mailto:ggiakoumidakis at yahoo.com]
> > > Sent: Thursday, September 06, 2012 5:19 AM
> > > To: Pierre Racine; PostGIS Users Discussion
> > > Subject: Re: [postgis-users] upload a folder of raster files
> > >
> > > Thank you very much!
> > >
> > > I need some extra help with the table name generator
> > > (public.dem_x_!_rid:~4,2!), I can't understand how it works.
> > >
> > > Note that my filenames have that form: cc00h00m15s, cc00h00m30s,
> > > cc00h00m45s, cc00h01m00s, ... , cc02h00m00s (hours, minutes, seconds
> that
> > > rising every 15 seconds)
> > >
> > >
> > >
> > >
> > >
> > >
> > > ________________________________
> > >
> > > 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: Wednesday, September 5, 2012 9:07 PM
> > > Subject: RE: [postgis-users] upload a folder of raster files
> > >
> > >
> > > Here is my answer to that:
> > >
> > > http://geospatialelucubrations.blogspot.ca/2012/09/loading-many-rasters-
> > into-
> > > separate.html
> > >
> > > 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: Monday, September 03, 2012 6:12 AM
> > > > To: PostGIS Users Discussion
> > > > Subject: [postgis-users] upload a folder of raster files
> > > >
> > > > I want to upload to a db a big number of raster files, pe 500, inside a
> folder,
> > > > named in a row (file_1 to file_500), each one to a different table. Any
> ideas?
> > A
> > > > loop or something of the basic: raster2pgsql -s 4236 -I -C -M file_1.tif -F -t
> > > > public.demelevation | psql -d gisdb
> > > >
> > > > Note that using wildcard (*.tif) doesn't help, I tried but it uploads all files in
> > one
> > > > table as different rows.
> > > >
> > > > My sql knowledge is bad, so any help would be precious. Thanks.
> > >
> > >
> > >
> >
> >
> >
> 
> 
> 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120907/8eec9d40/attachment.html>