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.rac...@sbf.ulaval.ca> To: Giannis Giakoumidakis <ggiakoumida...@yahoo.com>; PostGIS Users Discussion <postgis-users@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:ggiakoumida...@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.rac...@sbf.ulaval.ca> > To: Giannis Giakoumidakis <ggiakoumida...@yahoo.com>; PostGIS Users > Discussion <postgis-users@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:ggiakoumida...@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.rac...@sbf.ulaval.ca> > > To: Giannis Giakoumidakis <ggiakoumida...@yahoo.com>; PostGIS Users > > Discussion <postgis-users@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-boun...@postgis.refractions.net [mailto:postgis-users- > > > boun...@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.rac...@sbf.ulaval.ca> > > > To: Giannis Giakoumidakis <ggiakoumida...@yahoo.com>; PostGIS Users > > > Discussion <postgis-users@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:ggiakoumida...@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.rac...@sbf.ulaval.ca> > > > > To: Giannis Giakoumidakis <ggiakoumida...@yahoo.com>; PostGIS Users > > > > Discussion <postgis-users@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:ggiakoumida...@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.rac...@sbf.ulaval.ca> > > > > > To: Giannis Giakoumidakis <ggiakoumida...@yahoo.com>; PostGIS Users > > > > > Discussion <postgis-users@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-boun...@postgis.refractions.net [mailto:postgis- > > > users- > > > > > > boun...@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. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
