[postgis-users] upload a folder of raster files

Giannis Giakoumidakis ggiakoumidakis at yahoo.com
Tue Sep 11 02:31:25 PDT 2012 

That works perfect, thanks!

I noticed that something happens here. It seems that the new tables created can't hold all the information (srid, geometry etc) from the first loaded raster with raster2pgsql. You can see this from the sql created for these tables, they have no contraints at all. Also, in the "raster_columns" the most columns of these tables are empty. The weird is that when we see these in QGIS, they have all the geometry information and they are placed where they should be.

How this can happen? Anyway, I need all the information missing from these tables, to make queries. I guess there is no way to add contraints and geometry to all after the split. Is there any way that the splitted tables can hold all the information from the main table during the split? I think no. What's your opinion?
 
Thanks again for the help.





________________________________
 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: Monday, September 10, 2012 10:21 PM
Subject: RE: [postgis-users] upload a folder of raster files
 
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.
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
> >
> 
> 
> 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120911/39d6791c/attachment.html>

More information about the postgis-users mailing list