<html><body><div style="color:#000; background-color:#fff; font-family:times new roman, new york, times, serif;font-size:12pt">It's something wrong here. <br><br>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.<br><br>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). <br><br>Is there a suntax error or something else?<br><br><br><br><div><span><br></span></div><div><br></div> <div style="font-family: times new roman, new york, times, serif; font-size: 12pt;"> <div style="font-family: times new roman, new york, times, serif; font-size: 12pt;"> <div dir="ltr"> <font face="Arial" size="2"> <hr size="1"> <b><span
style="font-weight:bold;">From:</span></b> Pierre Racine <Pierre.Racine@sbf.ulaval.ca><br> <b><span style="font-weight: bold;">To:</span></b> Giannis Giakoumidakis <ggiakoumidakis@yahoo.com>; PostGIS Users Discussion <postgis-users@postgis.refractions.net> <br> <b><span style="font-weight: bold;">Sent:</span></b> Friday, September 7, 2012 6:14 PM<br> <b><span style="font-weight: bold;">Subject:</span></b> RE: [postgis-users] upload a folder of raster files<br> </font> </div> <br>That's the point. You hardly can.<br><br>This is why I suggest to create an index from scratch by creating a new table generating a new number for each unique filename. <br><br>Start by counting the number of unique filenames you have:<br><br>SELECT count(*) FROM rasttable GROUP BY filename;<br><br>Then generate the unique ids like this:<br><br>CREATE TABLE ids AS<br>SELECT filename, generate_series(1,the count you previously did) id FROM rasttable GROUP BY
filename;<br><br>Then you join this ids to the raster table:<br><br>ALTER TABLE rasttable ADD COLUMN id integer;<br><br>UPDATE rasttable SET rasttable.id=ids.id<br>FROM rasttable, ids<br>WHERE rasttable.filename = ids.filename;<br><br>then you can use the SplitTable function and pass it the right id.<br><br>Pierre<br><br>> -----Original Message-----<br>> From: Giannis Giakoumidakis [mailto:<a ymailto="mailto:ggiakoumidakis@yahoo.com" href="mailto:ggiakoumidakis@yahoo.com">ggiakoumidakis@yahoo.com</a>]<br>> Sent: Friday, September 07, 2012 11:02 AM<br>> To: Pierre Racine; PostGIS Users Discussion<br>> Subject: Re: [postgis-users] upload a folder of raster files<br>> <br>> Now worked, thanks!<br>> <br>> I can't understand how this "substring(filename from 4 for 2)" works.<br>> <br>> <br>> My filenames are like that: cc00h00m15s, cc00h32m45s etc, how I create a<br>> unique for these?<br>> <br>> <br>> <br>>
<br>> ________________________________<br>> <br>> From: Pierre Racine <<a ymailto="mailto:Pierre.Racine@sbf.ulaval.ca" href="mailto:Pierre.Racine@sbf.ulaval.ca">Pierre.Racine@sbf.ulaval.ca</a>><br>> To: Giannis Giakoumidakis <<a ymailto="mailto:ggiakoumidakis@yahoo.com" href="mailto:ggiakoumidakis@yahoo.com">ggiakoumidakis@yahoo.com</a>>; PostGIS Users<br>> Discussion <<a ymailto="mailto:postgis-users@postgis.refractions.net" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>> Sent: Friday, September 7, 2012 5:37 PM<br>> Subject: RE: [postgis-users] upload a folder of raster files<br>> <br>> <br>> Ok. There was an error in the function. Fixed now. Copy again and execute.<br>> <br>> Pierre<br>> <br>> > -----Original Message-----<br>> > From: Giannis Giakoumidakis [mailto:<a ymailto="mailto:ggiakoumidakis@yahoo.com"
href="mailto:ggiakoumidakis@yahoo.com">ggiakoumidakis@yahoo.com</a>]<br>> > Sent: Friday, September 07, 2012 10:28 AM<br>> > To: Pierre Racine; PostGIS Users Discussion<br>> > Subject: Re: [postgis-users] upload a folder of raster files<br>> ><br>> > So you mean I firstly create SplitTable as it is and then I change arguments in<br>> > SELECT SplitTable(), am I right?<br>> ><br>> > All right but when I execute the query to create the function at the SQL Editor I<br>> > get an error: ERROR: syntax error at or near "(", LINE 19: ...<br>> > quote_ident(targettableschema) || '.' quote_ident(sourcetab...<br>> ><br>> > I don't change anything, only copying from the page.<br>> ><br>> > What is wrong?<br>> ><br>> ><br>> ><br>> ><br>> ><br>> ><br>> > ________________________________<br>> ><br>> > From:
Pierre Racine <<a ymailto="mailto:Pierre.Racine@sbf.ulaval.ca" href="mailto:Pierre.Racine@sbf.ulaval.ca">Pierre.Racine@sbf.ulaval.ca</a>><br>> > To: Giannis Giakoumidakis <<a ymailto="mailto:ggiakoumidakis@yahoo.com" href="mailto:ggiakoumidakis@yahoo.com">ggiakoumidakis@yahoo.com</a>>; PostGIS Users<br>> > Discussion <<a ymailto="mailto:postgis-users@postgis.refractions.net" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>> > Sent: Friday, September 7, 2012 4:20 PM<br>> > Subject: RE: [postgis-users] upload a folder of raster files<br>> ><br>> ><br>> > I never you had to change the SplitTable function, just change the argument<br>> you<br>> > pass to it. and you have to create a new id per filename. rid is not unique per<br>> > filename.<br>> ><br>> > Pierre<br>> ><br>> > > -----Original
Message-----<br>> > > From: <a ymailto="mailto:postgis-users-bounces@postgis.refractions.net" href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a> [mailto:postgis-users-<br>> > > <a ymailto="mailto:bounces@postgis.refractions.net" href="mailto:bounces@postgis.refractions.net">bounces@postgis.refractions.net</a>] On Behalf Of Giannis Giakoumidakis<br>> > > Sent: Friday, September 07, 2012 4:44 AM<br>> > > To: Pierre Racine; PostGIS Users Discussion<br>> > > Subject: Re: [postgis-users] upload a folder of raster files<br>> > ><br>> > > I firstly try with rid to see if it's working. It gives me an error:<br>> > ><br>> > > ERROR: syntax error at or near "("<br>> > > LINE 19: ... FROM ' || quote_ident(public) || '.' quote_ident(cc_all ) ...<br>> > >
^<br>> > ><br>> > > See anything wrong here??<br>> > ><br>> > ><br>> > > -----------------------------------------------------------------------<br>> > > -- SplitTable<br>> > > -- Split a table into a series of tables which names are composed of the<br>> > > -- concatenation of a prefix and the value of a column.<br>> > > --<br>> > > -- sourcetablename - Name of the table to split into multiple table<br>> > > -- targettableschema - Name of the schema in which to create the new set<br>> > > --
of table<br>> > > -- targettableprefix - Prefix of the set of table names to create.<br>> > > -- suffixcolumnname - Name of the column providing the suffix to each<br>> name.<br>> > > -----------------------------------------------------------------------<br>> > > CREATE OR REPLACE FUNCTION SplitTable(cc_all text, public text, cc_ text,<br>> rid<br>> > > text)<br>> > > RETURNS int AS<br>> > > $BODY$<br>> > > DECLARE<br>> > > newtablename text;<br>> > > uniqueid RECORD;<br>> > > BEGIN<br>> > > FOR uniqueid IN EXECUTE 'SELECT DISTINCT ' || quote_ident(rid) || '::text<br>> AS<br>> > > xyz123 FROM ' || quote_ident(public) || '.' quote_ident(cc_all) LOOP<br>> > > newtablename := cc_ || uniqueid.xyz123;<br>> > > EXECUTE
'CREATE TABLE ' || quote_ident(public) || '.' ||<br>> > > quote_ident(newtablename) || ' AS SELECT * FROM ' || cc_all || ' WHERE '<br>> ||<br>> > > rid || '::text = ' || quote_literal(uniqueid.xyz123);<br>> > > END LOOP;<br>> > > RETURN 1;<br>> > > END;<br>> > > $BODY$<br>> > > LANGUAGE plpgsql VOLATILE STRICT;<br>> > ><br>> > ><br>> > ><br>> > ><br>> > ><br>> > > ________________________________<br>> > ><br>> > > From: Pierre Racine <<a ymailto="mailto:Pierre.Racine@sbf.ulaval.ca" href="mailto:Pierre.Racine@sbf.ulaval.ca">Pierre.Racine@sbf.ulaval.ca</a>><br>> > > To: Giannis Giakoumidakis <<a ymailto="mailto:ggiakoumidakis@yahoo.com" href="mailto:ggiakoumidakis@yahoo.com">ggiakoumidakis@yahoo.com</a>>; PostGIS Users<br>> > > Discussion <<a
ymailto="mailto:postgis-users@postgis.refractions.net" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>> > > Sent: Thursday, September 6, 2012 5:34 PM<br>> > > Subject: RE: [postgis-users] upload a folder of raster files<br>> > ><br>> > ><br>> > > Then I would suggest you use the SQL method:<br>> > ><br>> > > -Create a new table creating a unique numeric id per filename (use<br>> > > generate_series()),<br>> > ><br>> > > -Join this table to the raster table so you get one numeric id per filename and<br>> > ><br>> > > -Use this field when you call SplitTable().<br>> > ><br>> > > Pierre<br>> > ><br>> > > > -----Original Message-----<br>> > > > From: Giannis Giakoumidakis [mailto:<a ymailto="mailto:ggiakoumidakis@yahoo.com"
href="mailto:ggiakoumidakis@yahoo.com">ggiakoumidakis@yahoo.com</a>]<br>> > > > Sent: Thursday, September 06, 2012 10:28 AM<br>> > > > To: Pierre Racine; PostGIS Users Discussion<br>> > > > Subject: Re: [postgis-users] upload a folder of raster files<br>> > > ><br>> > > > Anyhow, it's not so important this for me.<br>> > > ><br>> > > ><br>> > > ><br>> > > > The best would be something that reminds the names of the files but it's ok<br>> if<br>> > it<br>> > > > is as simple as cc001, cc002, cc003, ... etc or whatever it starts from cc<br>> that<br>> > > is<br>> > > > common to all the filenames and then a row of numbers.<br>> > > ><br>> > > ><br>> > > ><br>> > > ><br>> > > ><br>> > > >
________________________________<br>> > > ><br>> > > > From: Pierre Racine <<a ymailto="mailto:Pierre.Racine@sbf.ulaval.ca" href="mailto:Pierre.Racine@sbf.ulaval.ca">Pierre.Racine@sbf.ulaval.ca</a>><br>> > > > To: Giannis Giakoumidakis <<a ymailto="mailto:ggiakoumidakis@yahoo.com" href="mailto:ggiakoumidakis@yahoo.com">ggiakoumidakis@yahoo.com</a>>; PostGIS Users<br>> > > > Discussion <<a ymailto="mailto:postgis-users@postgis.refractions.net" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>> > > > Sent: Thursday, September 6, 2012 5:03 PM<br>> > > > Subject: RE: [postgis-users] upload a folder of raster files<br>> > > ><br>> > > ><br>> > > > How do you want your tables names to be created from those filenames?<br>> > > ><br>> > > > > -----Original
Message-----<br>> > > > > From: Giannis Giakoumidakis [mailto:<a ymailto="mailto:ggiakoumidakis@yahoo.com" href="mailto:ggiakoumidakis@yahoo.com">ggiakoumidakis@yahoo.com</a>]<br>> > > > > Sent: Thursday, September 06, 2012 5:19 AM<br>> > > > > To: Pierre Racine; PostGIS Users Discussion<br>> > > > > Subject: Re: [postgis-users] upload a folder of raster files<br>> > > > ><br>> > > > > Thank you very much!<br>> > > > ><br>> > > > > I need some extra help with the table name generator<br>> > > > > (public.dem_x_!_rid:~4,2!), I can't understand how it works.<br>> > > > ><br>> > > > > Note that my filenames have that form: cc00h00m15s, cc00h00m30s,<br>> > > > > cc00h00m45s, cc00h01m00s, ... , cc02h00m00s (hours, minutes, seconds<br>> > > that<br>> > > > >
rising every 15 seconds)<br>> > > > ><br>> > > > ><br>> > > > ><br>> > > > ><br>> > > > ><br>> > > > ><br>> > > > > ________________________________<br>> > > > ><br>> > > > > From: Pierre Racine <<a ymailto="mailto:Pierre.Racine@sbf.ulaval.ca" href="mailto:Pierre.Racine@sbf.ulaval.ca">Pierre.Racine@sbf.ulaval.ca</a>><br>> > > > > To: Giannis Giakoumidakis <<a ymailto="mailto:ggiakoumidakis@yahoo.com" href="mailto:ggiakoumidakis@yahoo.com">ggiakoumidakis@yahoo.com</a>>; PostGIS Users<br>> > > > > Discussion <<a ymailto="mailto:postgis-users@postgis.refractions.net" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>> > > > > Sent: Wednesday, September 5, 2012 9:07 PM<br>> > > > > Subject: RE:
[postgis-users] upload a folder of raster files<br>> > > > ><br>> > > > ><br>> > > > > Here is my answer to that:<br>> > > > ><br>> > > > > <a href="http://geospatialelucubrations.blogspot.ca/2012/09/loading-many-" target="_blank">http://geospatialelucubrations.blogspot.ca/2012/09/loading-many-</a><br>> > rasters-<br>> > > > into-<br>> > > > > separate.html<br>> > > > ><br>> > > > > Pierre<br>> > > > ><br>> > > > > > -----Original Message-----<br>> > > > > > From: <a ymailto="mailto:postgis-users-bounces@postgis.refractions.net" href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a> [mailto:postgis-<br>> > > users-<br>> > > > > > <a ymailto="mailto:bounces@postgis.refractions.net"
href="mailto:bounces@postgis.refractions.net">bounces@postgis.refractions.net</a>] On Behalf Of Giannis Giakoumidakis<br>> > > > > > Sent: Monday, September 03, 2012 6:12 AM<br>> > > > > > To: PostGIS Users Discussion<br>> > > > > > Subject: [postgis-users] upload a folder of raster files<br>> > > > > ><br>> > > > > > I want to upload to a db a big number of raster files, pe 500, inside a<br>> > > folder,<br>> > > > > > named in a row (file_1 to file_500), each one to a different table. Any<br>> > > ideas?<br>> > > > A<br>> > > > > > loop or something of the basic: raster2pgsql -s 4236 -I -C -M file_1.tif -F<br>> -<br>> > t<br>> > > > > > public.demelevation | psql -d gisdb<br>> > > > > ><br>> > > > > > Note that using wildcard (*.tif)
doesn't help, I tried but it uploads all<br>> files<br>> > in<br>> > > > one<br>> > > > > > table as different rows.<br>> > > > > ><br>> > > > > > My sql knowledge is bad, so any help would be precious. Thanks.<br>> > > > ><br>> > > > ><br>> > > > ><br>> > > ><br>> > > ><br>> > > ><br>> > ><br>> > ><br>> > ><br>> ><br>> ><br>> ><br>> <br>> <br>> <br><br><br><br> </div> </div> </div></body></html>