[postgis-users] upload a folder of raster files
Pierre Racine
Pierre.Racine at sbf.ulaval.ca
Mon Sep 10 12:21:40 PDT 2012
I added a comment to the blod post just for you...
http://geospatialelucubrations.blogspot.ca/2012/09/loading-many-rasters-into-separate.html
--------------------------------------------------
In case it is too hard to derive a unique id from the filename you can create a new column with a unique number for each filename like this:
--1 - Add an id column to the raster table
ALTER TABLE rastertable ADD COLUMN "myid" INTEGER;
--2 - Create a new sequence
CREATE SEQUENCE rast_id_seq;
--3 - Update the new column with a unique value for each unique filename
UPDATE rastertable rt SET myid=newid
FROM (SELECT filename, nextval('rast_id_seq') newid
FROM rastertable
GROUP BY filename) foo
WHERE rt.filename = foo.filename;
You can use this new identifier to split the table with the SplitTable() function.
Pierre
> -----Original Message-----
> From: Giannis Giakoumidakis [mailto:ggiakoumidakis at yahoo.com]
> Sent: Friday, September 07, 2012 11:57 AM
> To: Pierre Racine; PostGIS Users Discussion
> Subject: Re: [postgis-users] upload a folder of raster files
>
> It's something wrong here.
>
> When I make the counting, it returns "1" for every row. So I get 10 rows of "1"
> for a rasttable that I have loaded 10 rasters. So next at the generate_series I
> have to give "1" or "10"? I'm confused.
>
> Also, whatever I give here (I tried both), then it's creates normally ids table, adds
> column id at rasttable, but when I do the update rasttable it gives "error: table
> name "cc_all" specified more than once" (cc_all is yours rasttable).
>
> Is there a suntax error or something else?
>
>
>
>
>
>
>
> ________________________________
>
> 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 6:14 PM
> Subject: RE: [postgis-users] upload a folder of raster files
>
>
> That's the point. You hardly can.
>
> This is why I suggest to create an index from scratch by creating a new table
> generating a new number for each unique filename.
>
> Start by counting the number of unique filenames you have:
>
> SELECT count(*) FROM rasttable GROUP BY filename;
>
> Then generate the unique ids like this:
>
> CREATE TABLE ids AS
> SELECT filename, generate_series(1,the count you previously did) id FROM
> rasttable GROUP BY filename;
>
> Then you join this ids to the raster table:
>
> ALTER TABLE rasttable ADD COLUMN id integer;
>
> UPDATE rasttable SET rasttable.id=ids.id
> FROM rasttable, ids
> WHERE rasttable.filename = ids.filename;
>
> then you can use the SplitTable function and pass it the right id.
>
> Pierre
>
> > -----Original Message-----
> > From: Giannis Giakoumidakis [mailto:ggiakoumidakis at yahoo.com]
> > Sent: Friday, September 07, 2012 11:02 AM
> > To: Pierre Racine; PostGIS Users Discussion
> > Subject: Re: [postgis-users] upload a folder of raster files
> >
> > Now worked, thanks!
> >
> > I can't understand how this "substring(filename from 4 for 2)" works.
> >
> >
> > My filenames are like that: cc00h00m15s, cc00h32m45s etc, how I create a
> > unique for these?
> >
> >
> >
> >
> > ________________________________
> >
> > 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 5:37 PM
> > Subject: RE: [postgis-users] upload a folder of raster files
> >
> >
> > Ok. There was an error in the function. Fixed now. Copy again and execute.
> >
> > Pierre
> >
> > > -----Original Message-----
> > > From: Giannis Giakoumidakis [mailto:ggiakoumidakis at yahoo.com]
> > > Sent: Friday, September 07, 2012 10:28 AM
> > > To: Pierre Racine; PostGIS Users Discussion
> > > Subject: Re: [postgis-users] upload a folder of raster files
> > >
> > > 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.
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
> >
>
>
>
More information about the postgis-users
mailing list