Re: [postgis-users] out of memory
This could be solved by ticket #826 http://trac.osgeo.org/postgis/ticket/826 Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Bborie Park Sent: Friday, October 26, 2012 8:26 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] out of memory The output size makes sense since the loader split the input raster into 100x100 tiles. 1591 / 100 = 15.91 ... 16 x 100 1446 / 100 = 14.46 ... 15 x 100 So, when unioning the tiles back together, the unioned raster should be 1600 x 1500 (don't know where you got 1450 though). -bborie On Thu, Oct 25, 2012 at 10:43 PM, Mahavir Trivedi mahavir.triv...@gmail.com wrote: hi i split the image (tiff) into 100 X 100 tile .(RASTER IMAGE 500 MB input ) but problem occurred when i export it then output size increase. (image input size = 1591 X 1446) (image output size = 1600 X 1450) can i change blocksize of server ? if yes then how ? my system XP Windows 64-bit 4 GB RAM thanks mahavir ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Bborie Park Programmer Center for Vectorborne Diseases UC Davis 530-752-8380 bkp...@ucdavis.edu ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Finding the right nearest polygon
Hi, Paul suggests a two steps approach to find the nearest polygon (or line) using the KNN operator at the end of this page: http://blog.opengeo.org/2011/09/28/indexed-nearest-neighbour-search-in-postgis/ Tell me if I'm wrong but I guess this technique does not guarantee that you actually find the nearest geometry... There is always a chance (tiny I admit) that the bounding box (or the centroid) of the nearest polygon is the 101th one and hence not selected by the query... If I'm right, is there another technique that guarantee that the nearest one you find is the right one? (Without computing the distance between every polygons...) Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] raster loading and ST_Value performance
Q1: What effect does different tile size have on storage and performance? Biggest tiles = longer to load in memory and less efficient indexing I suggest you experiment with much smaller tiles: 100x100 and 10x10 Q2: We will need to be getting raster values from a large number of point tables over a large number of raster tables. Is there any method to speed up the queries? If your rasters are all of the same size and cover the same footprint I would suggest to make them different bands of the same raster. So for the same point you will search for the right tile only once. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] loading raster into DB takes more then an hour
And CPU and hard drive types? I get very different times on different machines for the same dataset. -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Bborie Park Sent: Monday, September 17, 2012 2:55 PM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] loading raster into DB takes more then an hour Yes, no and maybe. You haven't given sufficient information about your environment to provide any informed advice. What is your raster2pgsql command? Are you piping the output from raster2pgsql directly to psql? Or loading from a file containing the output? What OS? PostgreSQL version? -bborie On 09/17/2012 09:10 AM, G. Allegri wrote: I'm loading a DEM (float, single band, 27654x17623) through raster2pgsql and it takes more then hour to complete. It happens both with tiled loading (256x256) and single block. Is it normal? giovanni ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Bborie Park Programmer Center for Vectorborne Diseases UC Davis 530-752-8380 bkp...@ucdavis.edu ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] upload a folder of raster files
If all your rasters have the same footprint the extent geometry should be the same. Pierre -Original Message- From: Giannis Giakoumidakis [mailto:ggiakoumida...@yahoo.com] Sent: Friday, September 14, 2012 12:02 PM To: Pierre Racine; PostGIS Users Discussion Subject: Re: [postgis-users] upload a folder of raster files Ok I created the QueryTables and I made the select to Add contraints to each table. One last question: I see no differences in the extent geometry column of the raster_columns view between all the tables I created, it is exactly the same in all. It seems like it is the same raster in every table, with different name. It shouldn't have every table different geometry or it is right the way it is now? From: Pierre Racine pierre.rac...@sbf.ulaval.ca To: Giannis Giakoumidakis ggiakoumida...@yahoo.com; PostGIS Users Discussion postgis-users@postgis.refractions.net Sent: Tuesday, September 11, 2012 8:51 PM Subject: RE: [postgis-users] upload a folder of raster files 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? You should just read all the blog post. I explain how to add the constraints on all the tables. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] upload a folder of raster files
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? You should just read all the blog post. I explain how to add the constraints on all the tables. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS 2.0 Raster Mosaic...
Put the -C option just in the last command. If you put it in the first, the max extent constraint prevent the table from accepting more tiles (unless you put -C with -x). Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Brian Fitzgerald Sent: Tuesday, September 11, 2012 1:10 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] PostGIS 2.0 Raster Mosaic... I just tried the following (following your example) and get the following error: ./raster2pgsql -d -s 2273 -t 256x256 -C -F /home/rcgeoadmin/temp/979801.tif public.testmosaic /home/rcgeoadmin/temp/979801mosaic.sql psql -d mydb -f 979801mosaic.sql ./raster2pgsql -a -s 2273 -t 256x256 -M -F /home/rcgeoadmin/temp/979903.tif public.testmosaic /home/rcgeoadmin/temp/979903mosaic.sql psql -d mydb -f 979903mosaic.sql ERROR: postgres@minty /home/rcgeoadmin/temp $ psql -d mydb -f 979903mosaic.sql BEGIN psql:979903mosaic.sql:2: ERROR: new row for relation testmosaic violates check constraint enforce_max_extent_rast psql:979903mosaic.sql:3: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:979903mosaic.sql:4: ERROR: current transaction is aborted, commands ignored until end of transaction block . On Tue, Sep 11, 2012 at 12:57 PM, Markus Innerebner markus.innereb...@inf.unibz.it wrote: I am doing the same thing with success: use my script: cheers Markus * param 1: name the directory in which the files are located param2: name of the table in which the files are loaded * more raster2pgHydroalp.sh #!/bin/bash # this script imports raster ascii file into postgis # isFirst=true; cd $1; for i in $(\ls *.asc); do echo $i if $isFirst then raster2pgsql -d -s 32632 -C -F $i $2 | sudo su - postgres -c 'psql -d hydroalp'; isFirst=false else raster2pgsql -a -s 32632 -M -F $i $2 | sudo su - postgres -c 'psql -d hydroalp' fi done cd .. *** -- Ph D. Student Markus Innerebner DIS Research Group - Faculty of Computer Science Free University Bozen-Bolzano Dominikanerplatz 3 - Room 211 I - 39100 Bozen Phone: +39-0471-016143 tel:%2B39-0471-016143 Mobile: +39-333-9392929 tel:%2B39-333-9392929 gpg --fingerprint - pub 1024D/588F6308 2007-01-09 Key fingerprint = 6948 947E CBD2 89FD E773 E863 914F EB1B 588F 6308 sub 2048g/BF4877D0 2007-01-09 On Sep 11, 2012, at 6:43 PM, Brian Fitzgerald wrote: 979903mosaic.sql ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- -- Brian Fitzgerald, PMP, GISP bkf...@gmail.com 803.426.3469 --- ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] RE : PostGIS 2.0 Raster Mosaic...
Why did you remove the -F option in the second command? You're trying to append
less columns in an existing table.
-Original Message-
From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-
boun...@postgis.refractions.net] On Behalf Of Brian Fitzgerald
Sent: Monday, September 10, 2012 1:13 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] RE : PostGIS 2.0 Raster Mosaic...
Here is what I tried without luck:
Step 1 : ./raster2pgsql -s 2273 -t 256x256 -F -I
/home/rcgeoadmin/temp/979903.tif public.testmosaic
/home/rcgeoadmin/temp/979903mosaic.sql
Step 2: psql -d mydb -f 979903mosaic.sql
Step 3: ./raster2pgsql -s 2273 -t 256x256 -a
/home/rcgeoadmin/temp/979801.tif public.testmosaic
/home/rcgeoadmin/temp/979801mosaic.sql
Step 4: psql -d mydb -f 979801mosaic.sql
On Mon, Sep 10, 2012 at 12:02 PM, Francois Hugues
hugues.franc...@irstea.fr wrote:
I may be wrong but I think the problem comes from the index. You use
the option -I to create it but you cannot create it more than once. You should
just remove the -I option from the second time you use your command and I
think it should work.
Hugues.
Message d'origine
De: postgis-users-boun...@postgis.refractions.net de la part de Brian
Fitzgerald
Date: lun. 10/09/2012 17:57
À: PostGIS Users Discussion
Objet : [postgis-users] PostGIS 2.0 Raster Mosaic...
... trying to create a raster mosaic using the raster2pgsql tool... I
imported a tif image and now I want to append a second adjacent tiff to
the
first one... figured I could just run the same command with -F -I -t
256x256 etc and tack on the -a? I tried this and got an error saying:
'testmosaic_raster_gist' already exists... anyone know how to
accomplish
this?
- Brian
--
--
Brian Fitzgerald, PMP, GISP
bkf...@gmail.com
803.426.3469
---
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
--
--
Brian Fitzgerald, PMP, GISP
bkf...@gmail.com
803.426.3469
---
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
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:ggiakoumida...@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.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
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
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
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
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
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
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
[postgis-users] FME 2013 will soon read and write rasters stored in PostGIS
http://geospatialelucubrations.blogspot.ca/2012/08/fme-2013-will-soon-read-and-write.html ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] irregular tiling with raster2pgsql - imported raster has nodata value on the edge
This is ticket #826 http://trac.osgeo.org/postgis/ticket/826 Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Jakub Hettler Sent: Monday, August 27, 2012 10:51 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] irregular tiling with raster2pgsql - imported raster has nodata value on the edge Hi Bborie, I think, that not all rasters are divisible without remainder (except 1x1 tile) :) My problem is this (this is my raster after raster2psql displayed with tiles) http://sdrv.ms/OjbhoD I need to cut the last tiles on the east and south a make it smaller by the red line But I think it is impossible now, but i will find the solution :) I think about the function which will find the line of nodata value and then I make the polygon and intersect it with this polygon. :D But if I understand it, if I cut last tiles and these will be smaller than others, I wont be able to load this raster into QGIS (PostGIS raster driver) Thanks Jakub 2012/8/26 Bborie Park bkp...@ucdavis.edu Currently, that behavior is as expected due to the application of the specified tile size from the upper-left corner. To minimize the tiles with large swaths of NODATA, you should try to use a tile size that is cleanly divisible (no remainder) by the raster's width and height or a size with a large remainder. I usually recommend tile sizes of 100x100 or smaller though sizes around 50x50 are even faster. In terms of performance, the smaller the time size, the better the performance. -bborie On Sun, Aug 26, 2012 at 10:22 AM, Jakub Hettler jakub.hett...@gmail.com wrote: Hi all, I would like to ask if there is a way how to import tiled raster into PostGIS through raster2pgsql, but I need to make tiled raster, say 256x256, but at the end of raster I have last tiles (on the east and south side) which is on half out of the original raster (raster ends fe in the middle of last tile). If I try to load imported raster into QGIS or use gdal_translate I have nodata value on this place in the raster. Is there a way how to import the raster with the smaller last tiles on the east and south edges? Thanks Jakub ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Bborie Park Programmer Center for Vectorborne Diseases UC Davis 530-752-8380 bkp...@ucdavis.edu ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Advantages and disadvantages of storing raster in PostGIS raster
For ST_Union you can test my temporary solution: http://geospatialelucubrations.blogspot.ca/2012/07/a-slow-yet-1000x-faster-alternative-to.html Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Jakub Hettler Sent: Thursday, August 23, 2012 4:12 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] Advantages and disadvantages of storing raster in PostGIS raster That was one of my questions, how to make analasys on tiled rasters? Is that possible or not, I tried to make ST_Union on 200 tiles and I stopped it after a few minutes :) I am testing raster automatic publication of OGC services through PostGIS raster and image mosaic JDBC plugin and Java a I am quiet satisfied, but I have only small rasters (max 30 Mb), I expected that it will be really slow (reading raster from database, merging together and visualize them), but I am quite surprised. But I expect that the same rasters will be faster from file system. Thanks Jakub 2012/8/23 Bborie Park bkp...@ucdavis.edu For my work, PostGIS raster provides a standardized method to store, manage and query various datasets, specifically longitudinal climate data and DEM for California. This permits my end-users (researchers and various decision support software) to use the SQL knowledge they already know to get the data that they're looking for without needing additional software (ENVI, ERDAS Imagine, ArcGIS). The biggest limitation to PostGIS raster in 2.0 (and trunk) is that there isn't advanced map algebra support (needed for fast unioning and model output). I hope to start working developing that support in the next week or two... as I need it for my work. -bborie On 08/23/2012 11:34 AM, Jakub Hettler wrote: Hi, just a simple question, I try to find some work on this issue, but nothing found. Could somebody provide some information? Thanks a lot Jakub ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Bborie Park Programmer Center for Vectorborne Diseases UC Davis 530-752-8380 bkp...@ucdavis.edu ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Clip
So the big square, in your image, is a whole 64x64 tile? I gave a try to the same kind of query here and everything is fine. Are you sure the circle is the same geometry used to clip? What about SELECT ST_PostGIS_Full_Version() Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Paolo Cavallini Sent: Thursday, July 26, 2012 1:33 AM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] ST_Clip Il 26/07/2012 01:54, Pierre Racine ha scritto: Is this table tiled? yes, -t 64x64 Why does your WHERE is about the rid and not ST_Intersects(rast, geom)? To make the clip on a tile only, and test how it works, I started following the example in http://www.postgis.org/docs/RT_ST_Clip.html , but got stuck early. Thanks. -- Paolo Cavallini - Faunalia www.faunalia.eu Full contact details at www.faunalia.eu/pc Nuovi corsi QGIS e PostGIS: http://www.faunalia.it/calendario ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Clip
What happen if do a query like this: SELECT (gv).geom, (gv).val FROM (SELECT ST_PixelAsPolygons(ST_AsRaster(ST_Buffer(ST_Centroid(ST_Envelope(rast)), 0.5000), rast, ST_BandPixelType(rast, 1), 1, -1)) gv FROM hdr WHERE rid = 4 ) foo and display the resulting geometries labelled with the values? This is the first step of the clipping: creating a rasterized version of the buffer geometry aligned on the raster. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Paolo Cavallini Sent: Thursday, July 26, 2012 10:53 AM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] ST_Clip Il 26/07/2012 16:33, Pierre Racine ha scritto: So the big square, in your image, is a whole 64x64 tile? not; I tried with a smaller buffer, I obtained a smaller square. I gave a try to the same kind of query here and everything is fine. Are you sure the circle is the same geometry used to clip? it should be: I relaunched the same command, just removing the ST_Clip What about SELECT ST_PostGIS_Full_Version() SELECT ST_PostGIS_Full_Version(); ERROR: function st_postgis_full_version() does not exist SELECT PostGIS_Full_Version(); POSTGIS=2.0.1 r9979 GEOS=3.3.3-CAPI-1.7.4 PROJ=Rel. 4.7.1, 23 September 2009 GDAL=GDAL 1.9.0, released 2011/12/29 LIBXML=2.8.0 RASTER Thanks a lot. -- Paolo Cavallini - Faunalia www.faunalia.eu Full contact details at www.faunalia.eu/pc Nuovi corsi QGIS e PostGIS: http://www.faunalia.it/calendario ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Clip
Now what if you symbolize the value of the raster properly (1 = white and -1 = black) to display the shape of the buffer? -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Paolo Cavallini Sent: Thursday, July 26, 2012 2:04 PM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] ST_Clip Il 26/07/2012 19:54, Paolo Cavallini ha scritto: Il 26/07/2012 19:39, Pierre Racine ha scritto: What happen if do a query like this: SELECT (gv).geom, (gv).val FROM (SELECT ST_PixelAsPolygons(ST_AsRaster(ST_Buffer(ST_Centroid(ST_Envelope(rast)), 0.5000), rast, ST_BandPixelType(rast, 1), 1, -1)) gv FROM hdr WHERE rid = 4 ) foo and display the resulting geometries labelled with the values? This is the first step of the clipping: creating a rasterized version of the buffer geometry aligned on the raster. If I redo the query with the same buffer size as my previous one, I obtain a grid that encloses exactly the buffer; the raster is smaller. See attached. Thanks. -- Paolo Cavallini - Faunalia www.faunalia.eu Full contact details at www.faunalia.eu/pc Nuovi corsi QGIS e PostGIS: http://www.faunalia.it/calendario ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_AsRaster and gdal_translate
If he does that he will not get the right value from his polygon layer...
The right way to create an exportable raster from a polygon table it to:
1) Rasterize and align properly your geometries (adding the gridx and gridy
parameters to ST_AsRaster and the pixeltype and value to burn the right value):
CREATE TABLE rasters.bdtq_20k_hydro_so_gfsm_aea_subset_1000m AS
SELECT ST_AsRaster(hyd.geom, 1000.0, 1000.0, 0.0, 0.0, '32BUI'::text,
nameOfTheColumnContainingTheValueToBurn ) AS rast
FROM hydrology.bdtq_20k_hydro_so_gfsm_aea_subset AS hyd;
2) Union the table.
If your raster is very small you can do:
CREATE TABLE rast2export AS
SELECT ST_Union(rast) rast
FROM rasters.bdtq_20k_hydro_so_gfsm_aea_subset
If your raster is big, ST_Union is too slow. You have to use the method I
describe in my blog
http://geospatialelucubrations.blogspot.ca/2012/07/a-slow-yet-1000x-faster-alternative-to.html
3) Then you can GDAL_translate using mode=1
Sorry it is so complicated...
In the future there should just be a ST_UnionToRaster() function, planned in
the specifications, that rasterize all the geometries to a common raster and
then export. So just two minimal steps.
Pierre
-Original Message-
From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-
boun...@postgis.refractions.net] On Behalf Of Bborie Park
Sent: Wednesday, July 25, 2012 4:23 PM
To: postgis-users@postgis.refractions.net
Subject: Re: [postgis-users] ST_AsRaster and gdal_translate
For exporting, instead of using the rasters in
bdtq_20k_hydro_so_gfsm_aea_subset_1000m, you may want to try
something
like...
CREATE TABLE foo AS
SELECT 1 AS rid, ST_AsRaster((
SELECT
ST_Collect(geom)
FROM hydrology.bdtq_20k_hydro_so_gfsm_aea_subset
), 1000.0, 1000.0
) AS rast
The above creates one raster using the collection of geometries, which
can then be exported with gdal_translate.
-bborie
On 07/25/2012 12:57 PM, Guillaume Drolet DRF wrote:
Hi,
I have been struggling with this since yesterday and now I think it is
time for me to request some help from the community :
I am trying to convert a geometry table (polygons) to a raster that will
have 1000 m-pixels, and then export that raster to a TIF file using
gdal_translate.
I've tried loads of different queries but nothing seems to work... Here
are examples of some the queries I tried:
CREATE TABLE rasters.bdtq_20k_hydro_so_gfsm_aea_subset_1000m AS
SELECT ST_AsRaster(hyd.geom, 1000.0, 1000.0) AS rast
FROM hydrology.bdtq_20k_hydro_so_gfsm_aea_subset AS hyd;
ALTER TABLE rasters.bdtq_20k_hydro_so_gfsm_aea_subset_1000m ADD
COLUMN
rid serial PRIMARY KEY;
CREATE INDEX
ON rasters.bdtq_20k_hydro_so_gfsm_aea_subset_1000m
USING gist(st_convexhull(rast));
SELECT AddRasterConstraints('rasters'::name,
'bdtq_20k_hydro_so_gfsm_aea_subset_1000m'::name,
'rast'::name);
Then at the command line:
gdal_translate PG:host=localhost port=5432 dbname=testspdb
user=postgres password=*** schema=rasters
table=bdtq_20k_hydro_so_gfsm_aea_subset_1000m mode=2
E:\temp\hydro1000m.tif
I get this error:
ERROR 1: Error, the table
rasters.bdtq_20k_hydro_so_gfsm_aea_subset_1000m contains tiles with
different size, and irregular blocking is not supported yet
GDALOpen failed - 1
Error, the table rasters.bdtq_20k_hydro_so_gfsm_aea_subset_1000m
contains tiles
with different size, and irregular blocking is not supported yet
My problem seems to be related to the irregular blocking of my raster. I
though I could create a raster with regular block and pixel size but I
didn't figure how to do this yet. I tried to achieve this by creating an
empty raster:
CREATE TABLE rasters.test_rasterize (
rid serial PRIMARY KEY,
rast raster
);
INSERT INTO rasters.test_rasterize VALUES (
1,
ST_MakeEmptyRaster(45, 62, 1828552.00, 1422569.00, 1000.0, 1000.0,
0.0, 0.0, 3175)
);
-- 45 and 62 are the approximate numbers of 1000 m pixels in x and y
that fit inside the extent of my geometry layer
UPDATE rasters.test_rasterize
SET rast = ST_AsRaster(geom, 1000.0, 1000.0)
FROM hydrology.bdtq_20k_hydro_so_gfsm_aea_subset;
When I call gdal_translate with rasters.test_rasterize, I get this:
Input file size is 2, 4
0...10...20...30...40...50...60...70...80...90...100 - done.
I'm getting desperate and will greatly appreciate any hints!
If that can help, I'm using PostGIS 2.0.0 r9605 and GDAL 1.9.0
Many thanks,
Guillaume
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
--
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkp...@ucdavis.edu
___
postgis-users mailing list
Re: [postgis-users] ST_AsRaster and gdal_translate
Forget to mention:
When you CREATE TABLE rast2export, add a 'rid' column with any number. The GDAL
driver is still dependent on that in GDAL 1.9.0 (and BTW the source table was
wrong in my last mail):
CREATE TABLE rast2export AS
SELECT 1 rid, ST_Union(rast) rast
FROM rasters.bdtq_20k_hydro_so_gfsm_aea_subset_1000m
-Original Message-
From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-
boun...@postgis.refractions.net] On Behalf Of Pierre Racine
Sent: Wednesday, July 25, 2012 4:40 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] ST_AsRaster and gdal_translate
If he does that he will not get the right value from his polygon layer...
The right way to create an exportable raster from a polygon table it to:
1) Rasterize and align properly your geometries (adding the gridx and gridy
parameters to ST_AsRaster and the pixeltype and value to burn the right
value):
CREATE TABLE rasters.bdtq_20k_hydro_so_gfsm_aea_subset_1000m AS
SELECT ST_AsRaster(hyd.geom, 1000.0, 1000.0, 0.0, 0.0, '32BUI'::text,
nameOfTheColumnContainingTheValueToBurn ) AS rast
FROM hydrology.bdtq_20k_hydro_so_gfsm_aea_subset AS hyd;
2) Union the table.
If your raster is very small you can do:
CREATE TABLE rast2export AS
SELECT ST_Union(rast) rast
FROM rasters.bdtq_20k_hydro_so_gfsm_aea_subset
If your raster is big, ST_Union is too slow. You have to use the method I
describe
in my blog
http://geospatialelucubrations.blogspot.ca/2012/07/a-slow-yet-1000x-faster-
alternative-to.html
3) Then you can GDAL_translate using mode=1
Sorry it is so complicated...
In the future there should just be a ST_UnionToRaster() function, planned in
the
specifications, that rasterize all the geometries to a common raster and then
export. So just two minimal steps.
Pierre
-Original Message-
From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-
boun...@postgis.refractions.net] On Behalf Of Bborie Park
Sent: Wednesday, July 25, 2012 4:23 PM
To: postgis-users@postgis.refractions.net
Subject: Re: [postgis-users] ST_AsRaster and gdal_translate
For exporting, instead of using the rasters in
bdtq_20k_hydro_so_gfsm_aea_subset_1000m, you may want to try
something
like...
CREATE TABLE foo AS
SELECT 1 AS rid, ST_AsRaster((
SELECT
ST_Collect(geom)
FROM hydrology.bdtq_20k_hydro_so_gfsm_aea_subset
), 1000.0, 1000.0
) AS rast
The above creates one raster using the collection of geometries, which
can then be exported with gdal_translate.
-bborie
On 07/25/2012 12:57 PM, Guillaume Drolet DRF wrote:
Hi,
I have been struggling with this since yesterday and now I think it is
time for me to request some help from the community :
I am trying to convert a geometry table (polygons) to a raster that will
have 1000 m-pixels, and then export that raster to a TIF file using
gdal_translate.
I've tried loads of different queries but nothing seems to work... Here
are examples of some the queries I tried:
CREATE TABLE rasters.bdtq_20k_hydro_so_gfsm_aea_subset_1000m AS
SELECT ST_AsRaster(hyd.geom, 1000.0, 1000.0) AS rast
FROM hydrology.bdtq_20k_hydro_so_gfsm_aea_subset AS hyd;
ALTER TABLE rasters.bdtq_20k_hydro_so_gfsm_aea_subset_1000m ADD
COLUMN
rid serial PRIMARY KEY;
CREATE INDEX
ON rasters.bdtq_20k_hydro_so_gfsm_aea_subset_1000m
USING gist(st_convexhull(rast));
SELECT AddRasterConstraints('rasters'::name,
'bdtq_20k_hydro_so_gfsm_aea_subset_1000m'::name,
'rast'::name);
Then at the command line:
gdal_translate PG:host=localhost port=5432 dbname=testspdb
user=postgres password=*** schema=rasters
table=bdtq_20k_hydro_so_gfsm_aea_subset_1000m mode=2
E:\temp\hydro1000m.tif
I get this error:
ERROR 1: Error, the table
rasters.bdtq_20k_hydro_so_gfsm_aea_subset_1000m contains tiles with
different size, and irregular blocking is not supported yet
GDALOpen failed - 1
Error, the table rasters.bdtq_20k_hydro_so_gfsm_aea_subset_1000m
contains tiles
with different size, and irregular blocking is not supported yet
My problem seems to be related to the irregular blocking of my raster. I
though I could create a raster with regular block and pixel size but I
didn't figure how to do this yet. I tried to achieve this by creating an
empty raster:
CREATE TABLE rasters.test_rasterize (
rid serial PRIMARY KEY,
rast raster
);
INSERT INTO rasters.test_rasterize VALUES (
1,
ST_MakeEmptyRaster(45, 62, 1828552.00, 1422569.00, 1000.0, 1000.0,
0.0, 0.0, 3175)
);
-- 45 and 62 are the approximate numbers of 1000 m pixels in x and y
that fit inside the extent of my geometry layer
UPDATE rasters.test_rasterize
SET rast = ST_AsRaster(geom, 1000.0, 1000.0)
FROM
Re: [postgis-users] ST_Clip
Is this table tiled? Why does your WHERE is about the rid and not ST_Intersects(rast, geom)? Pierre From: postgis-users-boun...@postgis.refractions.net [postgis-users-boun...@postgis.refractions.net] On Behalf Of Paolo Cavallini [cavall...@faunalia.it] Sent: Wednesday, July 25, 2012 6:42 PM To: PostGIS Users Discussion Subject: [postgis-users] ST_Clip Hi all. I may well be tired, but I cannot understand why: CREATE TABLE clip2 AS SELECT rid, ST_Clip(rast, 1,ST_Buffer(ST_Centroid(ST_Envelope (rast)),5000)) AS rast from hdr WHERE rid = 4; does not result in a circle, but in a square, contained in the buffer but smaller. See attached pictures, with the buffer in green, and the clipped raster inside. Any hint? Thanks. -- Paolo Cavallini - Faunalia www.faunalia.eu Full contact details at www.faunalia.eu/pc Nuovi corsi QGIS e PostGIS: http://www.faunalia.it/calendario ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Better way of removing pixelation with postgis raster
In my idea staying with the algebra functions should be doable, even with complex speckles. As long as the mask is big enough (finding speckles of max N pixels takes a 2N x 2N matrix) one should be able to run a neighbourhood scan outwards from the centre pixel. With some smart iterations that might work, I just need to start learning plpsql or something... Would you agree that that kind of method would be faster than vectorizing? I can't answer this as I haven't played much with ST_MapAlgebraFctNgb.. It all depends on the size of the window. Give it a try! plpgsql is very easy and you can start from the example in the doc. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] where to publish my scripts?
There is a section called Examples of Spatial SQL in http://trac.osgeo.org/postgis/wiki/UsersWikiMain I think this is the best place. Otherwise if you can come up with a robust, generic ST_RemoveSpeckle(rast, more parameter) plpgsql function I can add it to the trunk in the script/plpgsql folder. Those are plpgsql prototypes to be implemented in C if we find time of fund. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Nicolas Ribot Sent: Thursday, July 19, 2012 11:21 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] where to publish my scripts? Hi Tom, I personally publish these kind of scripts to the PostGIS wiki. Nicolas On 19 July 2012 17:18, Tom van Tilburg tom.van.tilb...@gmail.com wrote: ok. I will try a bit of plpgsql'ing. That reminds me to another question: is there any place that people can drop their own scripts for a wider audience? I know there is some postgis wiki but it doesn't seem very active or structured. I would love to share my scripts and see some scripts from others. chrs, Tom On 19-7-2012 15:57, Pierre Racine wrote: In my idea staying with the algebra functions should be doable, even with complex speckles. As long as the mask is big enough (finding speckles of max N pixels takes a 2N x 2N matrix) one should be able to run a neighbourhood scan outwards from the centre pixel. With some smart iterations that might work, I just need to start learning plpsql or something... Would you agree that that kind of method would be faster than vectorizing? I can't answer this as I haven't played much with ST_MapAlgebraFctNgb.. It all depends on the size of the window. Give it a try! plpgsql is very easy and you can start from the example in the doc. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Export from schema with pgsql2shp broken for Windows 64?
Hi, Would it be possible that exporting a geometry table to a shapefile with pgsql2shp from a schema different then public does not work for the Windows 64 version of PostGIS 2.0? It works fine on my other Windows 32 bit installation. I could not find anything on this on the web or in trac about this problem. Here is my command and the output: pgsql2shp -f c:/temp/targetfile2.shp -g geom -u postgres -P secret foundry03 ovlp.bug03 bug03 Initializing... Table bug03 does not exist Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Better way of removing pixelation with postgis raster
Hi Tom, Im trying to create a landuse map for a customer that requires the removal of pixelation (isolated islands of pixels, possible larger than 1 pixel). To make things complicated, different pixelclasses have different tresholds for removal. For instance, class 1 is removed when the combined pixels are less than 4, whereas class2 is removed when the combined pixels are less than 50. Furthermore, some classes count diagonal neighbours as 'touching' whereas other classes discard them. My solution is given below. As you can see there is an expensive buffer-union-buffer trio to make sure I connect the correct polygons before I can calculate their area. This calculation is part of a big raster calculation. All of the calculations are going very fast, except this one. Anyone with an idea to make it faster? In the end all I need is removal of isolated islands of pixels so any matrix calculation would be fine as well. I think you could easily implement a filter function to remove the 1 pixel type of island as a custom function of ST_MapAlgebraFctNgb(). See http://www.postgis.org/documentation/manual-svn/RT_ST_MapAlgebraFctNgb.html The size of the window should be 3x3 in this case. Return the pixel value if at least one neighbour has a value, null otherwise. I am 99% sure ST_MapAlgebraFctNgb would be faster on big rasters than vectorizing/union/rasterizing... However I have no idea how to implement such a filter to remove bigger islands (or speckles?) spanning on many pixels. Maybe with a little of research... Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Extracting cell values into a matrix
I need to do an ordered weighted averaging with the input rasters: assuming they all have the same extension and characteristics (like pixel size), for position (1,1) I take the value of every raster, the I sort those values to assign the weights and finally I compute the average to store it in the (1,1) position of the output raster. And so on with the rest of the positions. How many overlapping raster do you have? How do you determine the right weight? ST_Union can compute the mean of many overlapping rasters. Maybe a simple modification of the code (it is written in plpgsql) would do what you want. If there are not so many overlaping raster ST_MapAlgebra(raster, raster) could also be used depending on how you determine the right weight. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Get ST_Value results from a tiled raster without all of the NULL results
SELECT ST_Value(rast, ST_PointFromText('POINT(-59 -1)', 4326)) FROM
modis.test
Did you try adding:
WHERE ST_Intersects(rast, ST_PointFromText('POINT(-59 -1)', 4326))
Pierre
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Raster data insertion in postgis database
I am using multiple threads to upload the raster tiles simultaneously in a single table in postgis database. 2500 rows should be created from first thread and 2450 rows from the second thread..giving a total of 4950 rows. What do you mean by threads. You mean threads as in multiple threading programming or you just mean steps? At last are you using raster2pgsql? Or your program create the WKB itself? You know you can load multiple raster in one command using wildcards. I am wondering if it is possible to insert multiple rows simultaneously in a single table in postgis database. Please help This is more a PostgreSQL question. I think each INSERT should be treated independently if you enclose each of them with BEGIN and COMMIT. Otherwise they are treated as a transaction block. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Extracting cell values into a matrix
Hi, I need to develop a tool that is able to connect with PostGIS and extract the cell values from a raster stored in it creating a matrix with them. It could be done with queries launched from that tool, using ST_Value(), for each cell, but the number of queries would be huge. You would better be creating a polygon encompassing the pixels you want and ST_Clip the raster to this polygon and then get all the values with (ST_PixelAsPolygons()).val, (ST_PixelAsPolygons()).x, (ST_PixelAsPolygons()).y But could you explain what you intent to do with the values of the matrix? Maybe there is a simpler way to do it in SQL... Maybe just adding a parameter to ST_Neighborhood stating that the neighbourhood should be a square instead of a circle would also do what you want. Bborie? Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Finding highest cell value in raster using ST_Value
Good find Bborie! I would add that we should fill a ticket like ST_Summary should report the x and y raster coordinates of the max and the min. So four new columns... Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Bborie Park Sent: Monday, June 25, 2012 4:32 PM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] Finding highest cell value in raster using ST_Value Egge-Jan, That is an intriguing question with no clean/simple solution. The fastest approach may be to call ST_Reclass() to create a 1BB mask based upon that value and the pass the resulting raster to ST_PixelAsPolygons() which will give you what you want. Yes, the above is a round-about way to do this. This is something I'll (ST_PixelofValue?) see about adding for PostGIS 2.1. -bborie On 06/25/2012 01:16 PM, e.j.h.polle wrote: Hi, OK - with ST_SummaryStats it is easy to retrieve the highest value in the raster, but then I only have this value. Now I want to retrieve (information about) the cell/pixel having this value, to be able to visualize the highest point on the map. My original query (see first post) does give me this information (after 45 seconds...). How would I go about to get hold of the pixel with the highest value, knowing this value? Cheers, Egge-Jan On Sunday, June 24, 2012 8:47:58 PM UTC+1, DavidRA wrote: Why don't you use ST_SummaryStats instead ST_Value? That way you can get easily any basic stat, included the maximum value of the raster. It would be something like: SELECT (ST_SummaryStats(rast)).max FROM raster_table; -- View this message in context: http://postgis.17.n6.nabble.com/Finding-highest-cell-value-in-raster-using- ST-Value-tp4998545p4998554.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users On Sunday, June 24, 2012 8:47:58 PM UTC+1, DavidRA wrote: Why don't you use ST_SummaryStats instead ST_Value? That way you can get easily any basic stat, included the maximum value of the raster. It would be something like: SELECT (ST_SummaryStats(rast)).max FROM raster_table; -- View this message in context: http://postgis.17.n6.nabble.com/Finding-highest-cell-value-in-raster-using- ST-Value-tp4998545p4998554.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users On Sunday, June 24, 2012 8:47:58 PM UTC+1, DavidRA wrote: Why don't you use ST_SummaryStats instead ST_Value? That way you can get easily any basic stat, included the maximum value of the raster. It would be something like: SELECT (ST_SummaryStats(rast)).max FROM raster_table; -- View this message in context: http://postgis.17.n6.nabble.com/Finding-highest-cell-value-in-raster-using- ST-Value-tp4998545p4998554.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Bborie Park Programmer Center for Vectorborne Diseases UC Davis 530-752-8380 bkp...@ucdavis.edu ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Finding highest cell value in raster using ST_Value
But try SELECT ST_Centroid((ST_PixelAsPolygons(ST_Reclass(rast))).geom) FROM yourRaster and let us know the difference in terms of performance... You could be surprised! Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of e.j.h.polle Sent: Monday, June 25, 2012 5:34 PM To: postgis-us...@googlegroups.com Cc: PostGIS Users Discussion Subject: Re: [postgis-users] Finding highest cell value in raster using ST_Value Hi Bborie, Pierre, Thanks for adding the ticket. (http://trac.osgeo.org/postgis/ticket/1889) And I guess I am looking forward to PostGIS 2.1 then :-) (But I am still a little proud of my 'round-about way' to locate the cell with the highest value...) Cheers, Egge-Jan Op maandag 25 juni 2012 21:39:29 UTC+1 schreef Bborie Park het volgende: Too many columns. I was thinking that this is better served in a separate function as that value could be repeated. I'm adding a ticket for that separate function. On 06/25/2012 01:36 PM, Pierre Racine wrote: Good find Bborie! I would add that we should fill a ticket like ST_Summary should report the x and y raster coordinates of the max and the min. So four new columns... Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net mailto:postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net mailto:boun...@postgis.refractions.net ] On Behalf Of Bborie Park Sent: Monday, June 25, 2012 4:32 PM To: postgis-users@postgis.refractions.net mailto:postgis- us...@postgis.refractions.net Subject: Re: [postgis-users] Finding highest cell value in raster using ST_Value Egge-Jan, That is an intriguing question with no clean/simple solution. The fastest approach may be to call ST_Reclass() to create a 1BB mask based upon that value and the pass the resulting raster to ST_PixelAsPolygons() which will give you what you want. Yes, the above is a round-about way to do this. This is something I'll (ST_PixelofValue?) see about adding for PostGIS 2.1. -bborie On 06/25/2012 01:16 PM, e.j.h.polle wrote: Hi, OK - with ST_SummaryStats it is easy to retrieve the highest value in the raster, but then I only have this value. Now I want to retrieve (information about) the cell/pixel having this value, to be able to visualize the highest point on the map. My original query (see first post) does give me this information (after 45 seconds...). How would I go about to get hold of the pixel with the highest value, knowing this value? Cheers, Egge-Jan On Sunday, June 24, 2012 8:47:58 PM UTC+1, DavidRA wrote: Why don't you use ST_SummaryStats instead ST_Value? That way you can get easily any basic stat, included the maximum value of the raster. It would be something like: SELECT (ST_SummaryStats(rast)).max FROM raster_table; -- View this message in context: http://postgis.17.n6.nabble.com/Finding-highest-cell-value-in- raster-using- http://postgis.17.n6.nabble.com/Finding-highest-cell-value-in- raster-using- ST-Value-tp4998545p4998554.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net mailto:postgis- us...@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users http://postgis.refractions.net/mailman/listinfo/postgis-users On Sunday, June 24, 2012 8:47:58 PM UTC+1, DavidRA wrote: Why don't you use ST_SummaryStats instead ST_Value? That way you can get easily any basic stat, included the maximum value of the raster. It would be something like: SELECT (ST_SummaryStats(rast)).max FROM raster_table; -- View this message in context: http://postgis.17.n6.nabble.com/Finding-highest-cell-value-in- raster-using- http://postgis.17.n6.nabble.com/Finding-highest-cell-value-in- raster-using- ST-Value-tp4998545p4998554.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net mailto:postgis- us...@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users http://postgis.refractions.net/mailman/listinfo
[postgis-users] ST_SummaryStat on nodata raster
Hi, Seems to me that when computing summary stats on a null raster (having only nodata values and the count of significant pixel is 0), then the sum, the mean, the stddev, the min and the max should be null. However this: SELECT ST_SummaryStats(ST_AddBand(ST_MakeEmptyRaster(10, 10, 0, 0, 1, 1, 0, 0, 4326), '16BSI'::text, 0, 0)); returns that: (0,0,0,-1,0,0) Confusing... If zero pixel were taken into account in the stats, then there should be no results for the other metrics. If I fetch only the min, the max, or the average, how do I know there are actually not significant? Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] RE : How does ST_Slope works ?
If some functions can't handle all the pixels, this is a big limit for the use of tiled rasters if some function can't handle all the pixels although tiling and indexing raster is a major improvement (thanks to postgis).It's going to be tricky if we need to have both tiled and untiled version for the same raster. Yes. Right now only the functions dependent on neighbours are not working fine on tiled coverages. All the others do not have this problem. We have been thinking about different ways to support neighbourhood functions that I can't remember here. Maybe David could? One of the way is to pass the parameters of the coverage we are working on (schema, table, raster column) to the neighbour function so it could search for neighbour pixels values outside the current tile. Comments (and funding) are very welcome on this. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How does ST_Slope works ?
1. To know which Pixeltype I had to use, I tried to use st_bandpixeltype which told me my pixels were 32BSI type but my results using this type are 0 or 1 and if I try another type like 32BF, results seems to be more useful. Does someone have an explanation about this weird difference ? What is the unit used by st_slope ? The results are in radian so rounded to 0 or 1 when the pixel type is integer. 2. My raster is a tiled one and as you can see on both pictures, ST_Slope gives no results for pixels located at the border of each tile. I can understand it is impossible to find the slope for the pixels which make the border of the raster, but is there not a way to have complete results for the tile in the middle of the raster and thus use the neighboring tiles. Not yet. The only way is to load the raster untiled or to ST_Union them which might be slow. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] postgis installation issue
Yogesh, The first official release of PostGIS with raster support is 2.0. There is nothing to support before that. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Yogesh Dahiya Sent: Thursday, June 07, 2012 8:12 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] postgis installation issue I am trying to install postgis-pg90-binaries-2.0.0svn which has older version of raster support(which I required to build my app) but getting error saying current transaction aborted , commands ignored until end of transaction block. And is there documentation for older raster support. Thanks ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] spaces between tiles
Joan, The ST_HillShade function does not support tiled coverage... Depending on how big is your coverage (how many pixels by how many pixels) you could import it non tiled and then use ST_Tile to tile in the DB. ST_Tile is not part of PostGIS 2.0. You will find it here: http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql/st_tile.sql Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Joan Sent: Thursday, May 31, 2012 11:53 AM To: PostGIS Users Discussion Subject: [postgis-users] spaces between tiles Hi guys, I have just run the st_hillshade function. A row of pixels around the resulting raster are missing, causing spaces between tiles. What could be the problem? Regards Joan ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_HillShade
Propably because all the resulting values were greater than 15 and were clamped to 15. Use a better pixeltype (32BF). Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Paolo Cavallini Sent: Wednesday, May 30, 2012 6:42 AM To: PostGIS Users Discussion Subject: [postgis-users] ST_HillShade Not my lucky day. Why CREATE TABLE shade as select rid, ST_HillShade(rast,1,'4BUI',300,45,255,1) as rast from dtm; returns a raster with all value=15? Thanks a lot. -- Paolo Cavallini - Faunalia www.faunalia.eu Full contact details at www.faunalia.eu/pc ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] NoDataValue
You have to load with -C if you want the table to be listed properly in raster_columns. http://postgis.refractions.net/documentation/manual-svn/using_raster.xml.html#RT_Loading_Rasters Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Paolo Cavallini Sent: Wednesday, May 30, 2012 6:32 AM To: PostGIS Users Discussion Subject: [postgis-users] NoDataValue Hi all. If I SELECT nodata_values from raster_columns where r_table_name='basin_toscana'; nodata_values --- (1 riga) then UPDATE basin_toscana SET rast = ST_SetBandNoDataValue(rast,1, 65535); why I still get: SELECT nodata_values from raster_columns where r_table_name='basin_toscana'; nodata_values --- (1 riga) ? Also, I get: SELECT srid from raster_columns where r_table_name='basin_toscana'; srid -- 0 (1 riga) in spite of having imported with raster2pgsql -s 3003 -I -M basin_toscana.tif basin.sql Thanks for any hint. All the best. -- Paolo Cavallini - Faunalia www.faunalia.eu Full contact details at www.faunalia.eu/pc ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] NoDataValue
What Bborie means is that you can apply the constraints AFTER the data has been loaded with the AddRasterConstraints function. Sorry I did not mention this. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Paolo Cavallini Sent: Wednesday, May 30, 2012 12:58 PM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] NoDataValue Il 30/05/2012 18:55, Bborie Park ha scritto: Did you apply the raster constraints on your table? The view raster_columns is a constraint-based view so if your table has none of the constraints expected, there won't be any info in raster_columns. Thanks, this has been already pointed out, and is indeed effective. All the best. -- Paolo Cavallini - Faunalia www.faunalia.eu Full contact details at www.faunalia.eu/pc ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Rasterize a vector
There are more parameters to ST_AsRaster to align the raster you produce. You can align on an existing raster or provide all the parameters (scalex, scaley, gridx, gridy) to align it. See http://postgis.refractions.net/documentation/manual-svn/RT_ST_AsRaster.html Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Paolo Cavallini Sent: Tuesday, May 29, 2012 9:42 AM To: PostGIS Users Discussion Subject: [postgis-users] Rasterize a vector Hi all. I'm testing the ST_AsRaster command to convert a vector of adjacent polygons into a raster with different cell values according to one of the variables. I want the result to align to an existing raster. Sounds an easy task, but it is proving elusive. create table rasterizzo6 as select id as rid, St_asRaster(geom, 100, 100, '4BUI') as rast from province; creates a raster with irregular tiles, so it cannot be read by QGIS, and it is not aligned to an existing rasters. Are there more detailed instructions available? Thanks. -- Paolo Cavallini - Faunalia www.faunalia.eu Full contact details at www.faunalia.eu/pc ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Rasterize a vector
Yes, that's what I was trying, without success. Let's say I have two adjacent polygons, and I want a raster where the cells have the value of one of the fields of the the polygons (say, the id). Moreover, I want the resulting raster to be aligned to an existing raster (this is a very common task in GIS, easily done in GRASS, GDAL, SAGA, etc.). I do not see an option in: ST_AsRaster(geometry geom, raster ref, text pixeltype, double precision value=1, double precision nodataval=0, boolean touched=false); to write in each cell the value of the raster. Moreover, a simple CREATE TABLE rasterized as select b.rid, ST_AsRaster(a.geom, b.rast, '4BUI') as rast from province a, hdr b; results in (N geometries * N tiles) records, whereas I needed (N tiles) records, and for each tile a raster with a different value for each province. There should not be N tiles. Just N rasters... The most important is: At the end do you want all geometries to be burned into the same raster or you want one raster per geometries? Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Rasterize a vector
Basically (I use the old sub query style): SELECT ST_Union(ST_AsRaster(g.geom, ref.rast, '32BUI', g.id, 0))) rast FROM (SELECT ST_MakeEmptyRaster(1, 1, 0, 0, 1, -1, 0, 0, 0) AS rast) ref, mygeomtable g however if your raster is high resolution ST_Union might be impractically slow. The other technique untill we get a faster ST_Union is to use ST_MapAlgebraFct and write your own function returning the geometry value from the intersection with the centroid of the pixel. See this example http://postgis.refractions.net/pipermail/postgis-users/2012-April/033875.html I agree this is relatively cumbersome but it should be much faster than ST_Union. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Bborie Park Sent: Tuesday, May 29, 2012 2:11 PM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] Rasterize a vector On 05/29/2012 11:07 AM, Paolo Cavallini wrote: Il 29/05/2012 20:05, Pierre Racine ha scritto: The most important is: At the end do you want all geometries to be burned into the same raster or you want one raster per geometries? same raster - see gdal_rasterize for an example. thanks a lot. Run an ST_Union upon the set of output rasters. -bborie -- Bborie Park Programmer Center for Vectorborne Diseases UC Davis 530-752-8380 bkp...@ucdavis.edu ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] From raster to vector in PostGIS
This is for vectorizing. He wants to rasterize... -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of DavidRA Sent: Tuesday, May 29, 2012 2:29 PM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] From raster to vector in PostGIS Have you tried ST_DumpAsPolygons() function? -- View this message in context: http://postgis.17.n6.nabble.com/From-raster-to- vector-in-PostGIS-tp4997864p4997907.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Rasterize a vector
same error: ERROR: rt_raster_gdal_rasterize: Unable to add band to GDALDataset CONTEXT: PL/pgSQL function st_asraster line 26 at RETURN Isn't that because he has to set GDAL_DATA or PROJSO? Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] get unique values from raster intersecting a polygon
You want one value per polygon or one value per red point? It is not clear to me from your drawing. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Francois Hugues Sent: Saturday, May 26, 2012 5:29 PM To: postgis-users@postgis.refractions.net Subject: [postgis-users] get unique values from raster intersecting a polygon Hello list, I have to get values from raster where they intersect polygons from a layer with numerous polygons which have shared boundaries, but I don't want to have duplicate values from raster. The final result must be something like : gid | val. To explain my case, I made a picture. I think ST_intersection could help but I don't know what will be the exact result. In my example, we have a grid (a DEM for example), each red point is the value in the centre of each cell and two polygons A and B. I think cells totally within polygons are not a problem, but in green I have highlighted some ambiguous cases : - 1 and 2 are cells shared by both polygons but not in the same proportions and I would like to get value 1 for polygon A but not for B and value 2 for polygon B but not for A - What will be the result for cell number 3 and for the other green cell without number (and for all the ones which are not totally within a polygon) ? I think the value is the same for the whole cell (st_dumpaspolygon) and will be returned whatever the part of the cell covered by the polygon, but if someone could confirm. If st_intersection use st_DumpAsPolygon, could it be better to work directly with this polygonized table using its own gist index and dealing with shared areas of cells between different polygons ? Hugues. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] RE : get unique values from raster intersecting apolygon
I this case the right query looks like this: SELECT gid, ST_Value(rast, geom) val FROM yourraster table, yourpointtable WHERE ST_Intersects(rast, geom) Make sure to tile and index your raster properly (smaller is better). You can do this at import time. The result for cell outside any polygon will be null. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Francois Hugues Sent: Monday, May 28, 2012 9:06 AM To: PostGIS Users Discussion Subject: [postgis-users] RE : get unique values from raster intersecting apolygon I want one value per red point (which are raster data). The gid of one polygon will appear as many times as necessary regarding how it intersects the raster. Hugues. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Pierre Racine Date: lun. 28/05/2012 14:24 À: PostGIS Users Discussion Objet : Re: [postgis-users] get unique values from raster intersecting apolygon You want one value per polygon or one value per red point? It is not clear to me from your drawing. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Francois Hugues Sent: Saturday, May 26, 2012 5:29 PM To: postgis-users@postgis.refractions.net Subject: [postgis-users] get unique values from raster intersecting a polygon Hello list, I have to get values from raster where they intersect polygons from a layer with numerous polygons which have shared boundaries, but I don't want to have duplicate values from raster. The final result must be something like : gid | val. To explain my case, I made a picture. I think ST_intersection could help but I don't know what will be the exact result. In my example, we have a grid (a DEM for example), each red point is the value in the centre of each cell and two polygons A and B. I think cells totally within polygons are not a problem, but in green I have highlighted some ambiguous cases : - 1 and 2 are cells shared by both polygons but not in the same proportions and I would like to get value 1 for polygon A but not for B and value 2 for polygon B but not for A - What will be the result for cell number 3 and for the other green cell without number (and for all the ones which are not totally within a polygon) ? I think the value is the same for the whole cell (st_dumpaspolygon) and will be returned whatever the part of the cell covered by the polygon, but if someone could confirm. If st_intersection use st_DumpAsPolygon, could it be better to work directly with this polygonized table using its own gist index and dealing with shared areas of cells between different polygons ? Hugues. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] RE : RE : get unique values from raster intersecting apolygon
What is not clear in your description is: How do you want the value associated to each polygon to be computed from the many pixel the polygon is intersecting with? Do you want: -the raster value corresponding to one point inside the polygon -the mean of all the pixel centroid falling inside the polygon -the area weighted mean of all the raster values inside the polygon -the value most represented inside the polygon -etc... There are many ways to compute this value. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Francois Hugues Sent: Monday, May 28, 2012 12:40 PM To: PostGIS Users Discussion Subject: [postgis-users] RE : RE : get unique values from raster intersecting apolygon Thanks but i think the way I made my picture was not really clear and I understand now how you read it ! I already know the existence of st_value for points. Here, the red point on my picture are the values of the raster and not a table of points : if it is build from a DEM, you have the elevation measure based on a regular pattern and finally you get a grid and in the centre of each cell the value measured (my red points) For my problem I have two table : one made of polygons, the other a raster made of tiles. I want to get value where polygons intersect raster but a celle must not appear twice. My raster will be totally covered by polygons and at the end I want a gid|val table with as many lines as my raster has values. I hope it is clearer now but, for sure, my english does not help ! Thanks, Hugues. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Pierre Racine Date: lun. 28/05/2012 16:56 À: PostGIS Users Discussion Objet : Re: [postgis-users] RE : get unique values from raster intersecting apolygon I this case the right query looks like this: SELECT gid, ST_Value(rast, geom) val FROM yourraster table, yourpointtable WHERE ST_Intersects(rast, geom) Make sure to tile and index your raster properly (smaller is better). You can do this at import time. The result for cell outside any polygon will be null. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Francois Hugues Sent: Monday, May 28, 2012 9:06 AM To: PostGIS Users Discussion Subject: [postgis-users] RE : get unique values from raster intersecting apolygon I want one value per red point (which are raster data). The gid of one polygon will appear as many times as necessary regarding how it intersects the raster. Hugues. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Pierre Racine Date: lun. 28/05/2012 14:24 À: PostGIS Users Discussion Objet : Re: [postgis-users] get unique values from raster intersecting apolygon You want one value per polygon or one value per red point? It is not clear to me from your drawing. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Francois Hugues Sent: Saturday, May 26, 2012 5:29 PM To: postgis-users@postgis.refractions.net Subject: [postgis-users] get unique values from raster intersecting a polygon Hello list, I have to get values from raster where they intersect polygons from a layer with numerous polygons which have shared boundaries, but I don't want to have duplicate values from raster. The final result must be something like : gid | val. To explain my case, I made a picture. I think ST_intersection could help but I don't know what will be the exact result. In my example, we have a grid (a DEM for example), each red point is the value in the centre of each cell and two polygons A and B. I think cells totally within polygons are not a problem, but in green I have highlighted some ambiguous cases : - 1 and 2 are cells shared by both polygons but not in the same proportions and I would like to get value 1 for polygon A but not for B and value 2 for polygon B but not for A - What will be the result for cell number 3 and for the other green cell without number (and for all the ones which are not totally within a polygon) ? I think the value is the same for the whole cell (st_dumpaspolygon) and will be returned whatever the part of the cell covered by the polygon, but if someone could confirm. If st_intersection use st_DumpAsPolygon, could it be better to work directly with this polygonized table using its own gist index and dealing with shared areas of cells between different polygons ? Hugues
Re: [postgis-users] RE : RE : RE : get unique values from raster intersecting apolygon
If what you want is the mean of all the pixel centroid falling inside the polygon you do a query like this: SELECT (ST_SummaryStatsAgg(ST_Clip(rast, geom))).* FROM clip_build, nclheat WHERE ST_Intersects(rast, geom) If what you want is the area weighted mean of all the raster values inside the polygon you do a query like this: SELECT gt.id, (aws).weightedmean FROM (SELECT ST_AreaWeightedSummaryStats(gv) aws FROM (SELECT ST_Intersection(rt.rast, gt.geom) gv FROM rasttable rt, geomtable gt WHERE ST_Intersects(rt.rast, gt.geom) ) foo GROUP BY gt.id ) foo2 In the first case each pixel value is taken into account only once. In the second case only the proportion of the pixel intersecting each polygon is taken into account for each polygon. The first case should be faster than the second case. See this older post for more details: http://postgis.refractions.net/pipermail/postgis-users/2012-April/033831.html Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Francois Hugues Sent: Monday, May 28, 2012 3:31 PM To: PostGIS Users Discussion Subject: [postgis-users] RE : RE : RE : get unique values from raster intersecting apolygon Ok ! I don't know if I'll really manage to do better but I'll try one last time before stopping to disturb everybody. I want all the values intersecting the polygons but I don't want a pixel to be returned twice or more (if my raster was totally covered I would have as many couple gid/value as number of pixels in my raster). I attached one more picture to this message. The numbers are the values associated to each pixel of the raster. Regarding this picture, I would like a result like this (I didn't report all values especially those at the borders of both polygon, values 15 and 20 because I don't know which polygon cover the most the pixel), the interesting ones are from a-13 to b-23 gid|val A |2 A |4 A |5 A |6 A |8 A |9 A |10 A |11 A |12 A |13 A |17 A |18 B |16 B |19 B |22 B |23 B |24 B |25 B |26 B |27 B |30 B |31 B |32 B |33 Hugues. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Pierre Racine Date: lun. 28/05/2012 20:26 À: PostGIS Users Discussion Objet : Re: [postgis-users] RE : RE : get unique values from raster intersecting apolygon What is not clear in your description is: How do you want the value associated to each polygon to be computed from the many pixel the polygon is intersecting with? Do you want: -the raster value corresponding to one point inside the polygon -the mean of all the pixel centroid falling inside the polygon -the area weighted mean of all the raster values inside the polygon -the value most represented inside the polygon -etc... There are many ways to compute this value. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Francois Hugues Sent: Monday, May 28, 2012 12:40 PM To: PostGIS Users Discussion Subject: [postgis-users] RE : RE : get unique values from raster intersecting apolygon Thanks but i think the way I made my picture was not really clear and I understand now how you read it ! I already know the existence of st_value for points. Here, the red point on my picture are the values of the raster and not a table of points : if it is build from a DEM, you have the elevation measure based on a regular pattern and finally you get a grid and in the centre of each cell the value measured (my red points) For my problem I have two table : one made of polygons, the other a raster made of tiles. I want to get value where polygons intersect raster but a celle must not appear twice. My raster will be totally covered by polygons and at the end I want a gid|val table with as many lines as my raster has values. I hope it is clearer now but, for sure, my english does not help ! Thanks, Hugues. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Pierre Racine Date: lun. 28/05/2012 16:56 À: PostGIS Users Discussion Objet : Re: [postgis-users] RE : get unique values from raster intersecting apolygon I this case the right query looks like this: SELECT gid, ST_Value(rast, geom) val FROM yourraster table, yourpointtable WHERE ST_Intersects(rast, geom) Make sure to tile and index your raster properly (smaller is better). You can do this at import time. The result for cell outside any polygon will be null. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users
Re: [postgis-users] RE : get unique values from raster intersecting apolygon
In PostGIS you can easily create a grid with ST_MakeEmptyRaster() and
ST_PixelAsPolygons()
Pierre
-Original Message-
From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-
boun...@postgis.refractions.net] On Behalf Of pcr...@pcreso.com
Sent: Monday, May 28, 2012 3:45 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] RE : get unique values from raster intersecting
apolygon
You could try something like gdal2xyz.py on you raster to create points for
each
raster cell which can be imported directly into postgis as point geometries,
like:
psql create table xyz (id serial,x decimal, y decimal, z decimal)
gdal2xyz.py raster file | psql copy xyz (x,y,z) from stdin...
psql select addgeometrycolumn()
psql update xyz set geom=setsrid(makepoint(x,y),srid)
The result is then a simple spatial join using the polygons the new points,
based on ST_Contains().
HTH,
Brent Wood
--- On Tue, 5/29/12, Francois Hugues hugues.franc...@irstea.fr wrote:
From: Francois Hugues hugues.franc...@irstea.fr
Subject: [postgis-users] RE : get unique values from raster intersecting
apolygon
To: PostGIS Users Discussion postgis-users@postgis.refractions.net
Date: Tuesday, May 29, 2012, 1:05 AM
I want one value per red point (which are raster data).
The gid of one polygon will appear as many times as necessary
regarding how it intersects the raster.
Hugues.
Message d'origine
De: postgis-users-boun...@postgis.refractions.net de la part de Pierre
Racine
Date: lun. 28/05/2012 14:24
À: PostGIS Users Discussion
Objet : Re: [postgis-users] get unique values from raster intersecting
apolygon
You want one value per polygon or one value per red point? It is not
clear to me from your drawing.
Pierre
-Original Message-
From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-
users-
boun...@postgis.refractions.net] On Behalf Of Francois Hugues
Sent: Saturday, May 26, 2012 5:29 PM
To: postgis-users@postgis.refractions.net
Subject: [postgis-users] get unique values from raster intersecting a
polygon
Hello list,
I have to get values from raster where they intersect polygons from a
layer with
numerous polygons which have shared boundaries, but I don't want to
have
duplicate values from raster. The final result must be something like
:
gid | val.
To explain my case, I made a picture.
I think ST_intersection could help but I don't know what will be the
exact result.
In my example, we have a grid (a DEM for example), each red point is
the value
in the centre of each cell and two polygons A and B. I think cells
totally
within
polygons are not a problem, but in green I have highlighted some
ambiguous
cases :
- 1 and 2 are cells shared by both polygons but not in the same
proportions and I
would like to get value 1 for polygon A but not for B and value 2 for
polygon B
but not for A
- What will be the result for cell number 3 and for the other green
cell
without
number (and for all the ones which are not totally within a polygon)
? I
think the
value is the same for the whole cell (st_dumpaspolygon) and will be
returned
whatever the part of the cell covered by the polygon, but if someone
could
confirm.
If st_intersection use st_DumpAsPolygon, could it be better to work
directly with
this polygonized table using its own gist index and dealing with
shared
areas of
cells between different polygons ?
Hugues.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-Inline Attachment Follows-
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Aspect artifacts?
Yeah. ST_Union is good for unioning overlapping rasters (e.g. computing the mean of a temperature series) but not for merging adjacent rasters. We will have to write something else for that. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Paolo Cavallini Sent: Friday, May 25, 2012 12:53 PM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] ST_Aspect artifacts? Il 25/05/2012 18:29, Pierre Racine ha scritto: Not yet... I imagined, thanks. So I tried to merge the rasters with ST_Union, but I had to stop after ~2h with pg@100% CPU (a small raster, 1300 tiles 64x64). Urgh! All the best. -- Paolo Cavallini See: http://www.faunalia.it/pc ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS 2.0 on CentOS 5
Thanks a lot J. for your nice instructions. I could compile without too much problems. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Gold, Jack L (US SSA) Sent: Monday, May 21, 2012 1:29 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] PostGIS 2.0 on CentOS 5 You'll need gcc-g++ among a few other things. If you want pgAdminIII to work, it gets trickier since you have to have postgresql-9.1. Easy solution is to get the 9.1 postgres from the PGDG repo at their site. Once you have that, you'll need to build swig-1.3.29(used for gdal java libs, I believe), proj.4-4.8, geos-3.3.2, and gdal-1.9.0. 1. yum install swig (That should get you the latest and greatest swig build). 2. Build Proj.4 a. get proj-4.8.0.tar.gz b. tar -xvf proj-4.8.0.tar.gz c. cd proj-4.8.0 d. ./configure --without-jni e. make; make install 3. Build Geos a. get geos-3.3.2.tar.bz2 b. tar -xvf geos-3.3.2.tar.bz2 c. cd geos-3.3.2 d. ./configure --enable-python e. make;make install 4. Build GDal a. get gdal-1.9.0.tar.gz b. tar -xvf gdal-1.9.0.tar.gz c. cd gdal-1.9.0 d. make; make install e. chmod 755 tools/geos-config 5. Build PostGIS-2.0 a. get the postgis-2.0 version you want b. tar -xvf postgis-2.0.tar.gz (or whatever version you're using) c. ./configure --with-raster --with-pgconfig=/usr/pgsql- 9.1/bin/pg_config --with-geosconfig=path to geos extracted directory/tools/geos-config d. make; make install You might need to tweak which configuration parameters you are using for your build, but this is how I built it. Note, the CentOS version of pgAdminIII will not work with postgresql-9.1, so you'll need to build it yourself. The easiest way to do this is to hook up with the rpmforge yum repo and try to install it. Otherwise, building pgAdminIII requires a bit of playing with as well. J. Gold -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Gold, Jack L (US SSA) Sent: Monday, May 21, 2012 1:16 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] PostGIS 2.0 on CentOS 5 It's not hard to build for CentOS 5. That's where I'm running it, but I don't use the RPMs. -J Gold -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Devrim GÜNDÜZ Sent: Monday, May 21, 2012 12:37 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] PostGIS 2.0 on CentOS 5 On Mon, 2012-05-21 at 12:18 -0400, Pierre Racine wrote: No. Dependencies like Gdal, etc. are too old on RHEL 5. And how hard is it to compile everything? Compile for building RPMs? If you expect it from me (as the packager), no, I'm not doing it :-) OTOH, I have no idea how hard it is. That said, RHEL 5 is 5 years old. Time to upgrade. Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Does -a option create when table does not exist?
Hi, Does the -a option of shp2pgsql and raster2pgsql creates the table when the table does not exist? If not would not that be useful for batch? Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Does -a option create when table does not exist?
Right... There seems to be no way to do that... -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Bborie Park Sent: Tuesday, May 22, 2012 3:18 PM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] Does -a option create when table does not exist? -a does not create the table if the table does not exist. Neither loader attempts to access any database so the loader will never know if a table exists. -bborie On 05/22/2012 12:02 PM, Pierre Racine wrote: Hi, Does the -a option of shp2pgsql and raster2pgsql creates the table when the table does not exist? If not would not that be useful for batch? Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Bborie Park Programmer Center for Vectorborne Diseases UC Davis 530-752-8380 bkp...@ucdavis.edu ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] optimize query
Is your raster tiled? How? Is it indexed? Does your query actually use the index? It is maybe not a good idea to get the srid for ST_Intersects from the raster. Try to hardcode it to see if it makes a difference. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Gold, Jack L (US SSA) Sent: Tuesday, May 22, 2012 5:17 PM To: PostGIS Users Discussion (postgis-users@postgis.refractions.net) Subject: [postgis-users] optimize query I'm finding a search query we wrote is running really slow. Any ideas on how to optimize this better? Or is this the best I can do? SELECT ST_Value(rast, ST_Transform(ST_SetSRID(ST_Point(66.12, 37.32), 4326),ST_SRID(rast))) FROM dted_elevations WHERE ST_Intersects(rast, ST_Transform(ST_SetSRID(ST_Point(66.12, 37.32), 4326), ST_SRID(rast))); --Jack Gold ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS 2.0 on CentOS 5
Anybody knows where CentOS 5 binaries are available (if they exists)? No. Dependencies like Gdal, etc. are too old on RHEL 5. And how hard is it to compile everything? ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Uploading a folder with many files
I agree we should have an option to load all the rasters in different tables
mostly if you tile them. However this little function should help you split
your table into many:
--
-- SplitTable
-- Split a table into a series of table which names are composed of the
concatenation of a prefix
-- and the value of a column.
-- sourcetablename - The name of the table to split into multiple table
-- targettableschema - The schema in which to create the new set of table
-- targettableprefix - The prefix of the set of table names to create.
-- suffixcolumnname - The name of the column providing the suffix to each name.
--
-- Example to split the table 'test' into a set of table starting with 't_' and
-- ending with the value of the column 'rid' to be created in the 'public'
schema.
--
-- SELECT SplitTable('test', 'public', 't_', 'rid');;
--
CREATE OR REPLACE FUNCTION SplitTable(sourcetablename text, targettableschema
text, targettableprefix text, suffixcolumnname text)
RETURNS int AS
$BODY$
DECLARE
newtablename text;
uniqueid RECORD;
BEGIN
FOR uniqueid IN EXECUTE 'SELECT DISTINCT ' || quote_ident(suffixcolumnname)
|| ' AS xyz123 FROM ' || sourcetablename LOOP
newtablename := targettableschema || '.' || targettableprefix ||
uniqueid.xyz123;
EXECUTE 'CREATE TABLE ' || quote_ident(newtablename) || ' AS SELECT * FROM
' || sourcetablename || ' WHERE ' || suffixcolumnname || ' = ' ||
uniqueid.xyz123;
END LOOP;
RETURN 1;
END;
$BODY$
LANGUAGE plpgsql VOLATILE STRICT;
Pierre
-Original Message-
From: Giannis Giakoumidakis [mailto:ggiakoumida...@yahoo.com]
Sent: Monday, May 14, 2012 10:27 AM
To: Pierre Racine; PostGIS Users Discussion
Subject: Re: [postgis-users] Uploading a folder with many files
This works but it loads all different raster data in one table, so in the
raster
columns view it appears only one row about these layers. I dont think this is
what I want, because my aim is to load each raster layer in QGIS. Is it
possible
somehow to load many raster layers at once, everyone in a seperate table??
Thanks.
From: Pierre Racine pierre.rac...@sbf.ulaval.ca
To: Giannis Giakoumidakis ggiakoumida...@yahoo.com; PostGIS Users
Discussion postgis-users@postgis.refractions.net
Sent: Thursday, May 10, 2012 5:10 PM
Subject: RE: [postgis-users] Uploading a folder with many files
The PostGIS raster loader, raster2pgsql, supports wildcard so you can do:
raster2pgsql -I -C -F c:/temp/*.tif schema.table| psql -U postgres -d batabase
Pierre
-Original Message-
From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-
boun...@postgis.refractions.net] On Behalf Of Giannis Giakoumidakis
Sent: Thursday, May 10, 2012 5:19 AM
To: Post GIS Users Discussion
Subject: [postgis-users] Uploading a folder with many files
I want to upload to a database a big number of raster files (.tif), for
example
500, in a folder. They are numbered in a row, for example c001, c002,...,
cc500.
Is there any way to upload all with one command or the whole folder at once,
or
I have to do this one by one? Thanks.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS Raster with Map Server
Did you try to do gdalinfo and/or gdal_translate with the same parameters? Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of abhishek bansal Sent: Saturday, May 12, 2012 1:19 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] PostGIS Raster with Map Server And url I am using for mapserver is http://localhost/cgi- bin/mapserv.exe?map=/ms4w/Apache/htdocs/raster_wmsServerPostGIS.map SERVICE=WMSVERSION=1.1.1REQUEST=GetMapLAYERS=rasterLayer,point s1,waterwaysSTYLES=SRS=EPSG:4326BBOX=68.820812,8.082365,96.07060 2,34.585531WIDTH=800HEIGHT=600FORMAT=image/png I am able to see waterways and points1 whicha re my vector layers but not rasterlayer. I am also not able to visualize this rasterlaer in QGIS. I am stuck at this point. On Sat, May 12, 2012 at 10:11 AM, abhishek bansal discover...@gmail.com wrote: Thanks for your reply sorry that was a typo in the mail... that didn't help also!! On Sat, May 12, 2012 at 3:51 AM, pcr...@pcreso.com wrote: It looks like you are missing a quote in your data statement after 'asdf and my have an extra one at the end? That might help... Brent Wood --- On Sat, 5/12/12, abhishek bansal discover...@gmail.com wrote: From: abhishek bansal discover...@gmail.com Subject: [postgis-users] PostGIS Raster with Map Server To: postgis-users@postgis.refractions.net Date: Saturday, May 12, 2012, 2:27 AM hello all, I am very new to all this Geo-spatial world. I am using map server to display my maps in browser. I am also using Postgres and PostGIS raster to store my tiff images in database. now I need to configure mapserver map file to display this tiff file which is stored in database. I googled a lot and found this technique which is not working for me i.e. my raster image does not show up while using GetMap. I created my raster using following command raster2pgsql -s 4326 -I -C -M my.tif -F -t 100x100 public.asdf elev.sql than I imported this sql file into my PostGIS database using postgres PG Admin 3. My map file layer config is as follows: LAYER METADATA wms_titlerasterlayer ##required END NAME rasterlayer CONNECTIONTYPE postgis DATA PG:host=localhost port:5432 dbname='postgis' user='postgres' password='something' schema='public' table='asdf mode='2'' PROCESSING NODATA=0 PROCESSING SCALE=AUTO STATUS ON TYPE RASTER PROCESSING BANDS=1,2,3 OFFSITE 71 74 65 END I found this in postGIS documentation. this return me a empty image in browser. Plz help !! -- Thanks Regards, Abhishek Bansal http://abhishekbansal.miyuri.com Jaypee Institute of Information Technology, Noida -Inline Attachment Follows- ___ postgis-users mailing list postgis-users@postgis.refractions.net http://mc/compose?to=postgis- us...@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Thanks Regards, Abhishek Bansal http://abhishekbansal.miyuri.com Jaypee Institute of Information Technology, Noida -- Thanks Regards, Abhishek Bansal http://abhishekbansal.miyuri.com Jaypee Institute of Information Technology, Noida ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS Raster with Map Server
If http://www.gdal.org/gdal_translate.html does not work fine there is not much to do with MapServer. So give it a try first. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of abhishek bansal Sent: Sunday, May 13, 2012 7:30 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] PostGIS Raster with Map Server no i dont know about them.. but anyhow now i am able to get that raster layer. (I think there was some permission issue in database tables). But now I am having artifacts in my raster image. I can see orthogonal black lines in my output image. Are they because of tiling ..I am not able to get that.. please help ! Thank You On Sun, May 13, 2012 at 4:53 PM, Pierre Racine pierre.rac...@sbf.ulaval.ca wrote: Did you try to do gdalinfo and/or gdal_translate with the same parameters? Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis- users- boun...@postgis.refractions.net] On Behalf Of abhishek bansal Sent: Saturday, May 12, 2012 1:19 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] PostGIS Raster with Map Server And url I am using for mapserver is http://localhost/cgi- bin/mapserv.exe?map=/ms4w/Apache/htdocs/raster_wmsServerPostGIS.map SERVICE=WMSVERSION=1.1.1REQUEST=GetMapLAYERS=rasterLayer,point s1,waterwaysSTYLES=SRS=EPSG:4326BBOX=68.820812,8.082365,96.07060 2,34.585531WIDTH=800HEIGHT=600FORMAT=image/png I am able to see waterways and points1 whicha re my vector layers but not rasterlayer. I am also not able to visualize this rasterlaer in QGIS. I am stuck at this point. On Sat, May 12, 2012 at 10:11 AM, abhishek bansal discover...@gmail.com wrote: Thanks for your reply sorry that was a typo in the mail... that didn't help also!! On Sat, May 12, 2012 at 3:51 AM, pcr...@pcreso.com wrote: It looks like you are missing a quote in your data statement after 'asdf and my have an extra one at the end? That might help... Brent Wood --- On Sat, 5/12/12, abhishek bansal discover...@gmail.com wrote: From: abhishek bansal discover...@gmail.com Subject: [postgis-users] PostGIS Raster with Map Server To: postgis-users@postgis.refractions.net Date: Saturday, May 12, 2012, 2:27 AM hello all, I am very new to all this Geo-spatial world. I am using map server to display my maps in browser. I am also using Postgres and PostGIS raster to store my tiff images in database. now I need to configure mapserver map file to display this tiff file which is stored in database. I googled a lot and found this technique which is not working for me i.e. my raster image does not show up while using GetMap. I created my raster using following command raster2pgsql -s 4326 -I -C -M my.tif -F -t 100x100 public.asdf elev.sql than I imported this sql file into my PostGIS database using postgres PG Admin 3. My map file layer config is as follows: LAYER METADATA wms_titlerasterlayer ##required END NAME rasterlayer CONNECTIONTYPE postgis DATA PG:host=localhost port:5432 dbname='postgis' user='postgres' password='something' schema='public' table='asdf mode='2'' PROCESSING NODATA=0 PROCESSING SCALE=AUTO STATUS ON TYPE RASTER PROCESSING BANDS=1,2,3 OFFSITE 71 74 65 END I found this in postGIS documentation. this return me a empty image in browser. Plz help !! -- Thanks Regards, Abhishek Bansal http://abhishekbansal.miyuri.com Jaypee Institute of Information Technology, Noida -Inline Attachment Follows- ___ postgis-users mailing list postgis-users@postgis.refractions.net http://mc/compose?to=postgis- us...@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Storing image file metadata in PostGIS
Billy, The raster2pgsql loader has a -R option to load rasters as out-db file. In this case a RASTER column is created storing only the bounding box of the raster and the location of the file in the file system. You can then use most of the PostGIS raster function to extract metadata or interact with geometries (like ST_Intersecst(rast, geom)). ST_BandPath(rast) will get you the path to the raster file. I think most functions accessing the pixel value now works seamlessly but they should be slower than if the complete raster (with pixel values) would be stored in the DB. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Billy Newman Sent: Saturday, May 05, 2012 4:32 PM To: postgis-users@postgis.refractions.net Subject: [postgis-users] Storing image file metadata in PostGIS Hello all, I am currently using a pain old Oracle (not Oracle Spatial) database to store metadata for a huge amount of imagery data. I store this metadata for file discovery purposes. I.E. when a request is made for imagery I can quickly hit the DB to get all overlapping image files. I can then grab those off the file system and serve them out. I am using imageio-ext (Java) that sits on top of gdal to pull the imagery's metadata. Then just sending that to the DB. I am probably doing this the hard way and wondering if PostGIS can help simplify things. I am wondering if PostGIS has support to store metadata for images, and what that support really gives me. Currently I am serving out images in only EPSG:4326 so I want to do any transformations (if the image is not in EPSG:4326) to the metadata before storing it in the database. Can PostGIS read an image file and extract the metadata, transform the metadata into an EPSG:4326 lat/lon bounding box and store it? This is my current data model in Oracle if that helps: DataSource (this contains general info about this group of image files) ImageFile ( each data source has 0 - N image files) double minLat double maxLat double minLon double maxLon Its as simple as that. Although I am doing quite a bit of work using imageio-ext, and gdal to try and extract, transform, and store the metadata. Is this less complex in PostGIS or essentially the same? Thanks! Billy ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] calculate profile on raster
I need to calculate a raster profile along a linestring. The only way I've found is using ST_Intersection(rast,geom) but it seems to provide poor perfomances (even with ST_Intersects), probably because of the rast-geom conversion. Is there a better way to do it? My query is: select st_intersection(rast,the_geom) from dem,profile where st_intersects (rast,the_geom) I have the gist index both on rast and the_geom. Did you tile your raster? Smaller is better... Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] tile size
Depends on what you want to do: Raster/vector analysis in vector space (with ST_Intersection(geometry, raster)): smaller is better Raster/vector analysis in raster space (with ST_Clip or ST_Intersection(raster, raster)): tile approximately the size of polygons is better No analysis: depends on your application. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Stephen Crawford Sent: Wednesday, May 02, 2012 11:04 AM To: PostGIS Users Discussion Subject: [postgis-users] tile size Is there any rule of thumb for choosing a tile size for a raster? I have a grid that is 2145 x 1377 Thanks, Steve -- Stephen Crawford Center for Environmental Informatics The Pennsylvania State University ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] tile size
Mostly map algebra, doing a mean or sum over 7 rasters, etc. Possibly clipping to state borders at final step. Is 100 x 100 too small? MapAlgebra is not really affected by tile size. ST_MapAlgebraFctNgb is however, as it does not get neighbour pixel values from neighbour tiles. So you will generally end up with an edge effect. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Changing band pixel type
CREATE TABLE newrasttable AS SELECT ST_MapAlgebraExpr(rast, '8BUI', '[rast]') rast FROM oldrasttable Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of George Washington Sent: Monday, April 30, 2012 7:33 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] Changing band pixel type Hi, I imported a raster image in postgis 2.0 (Win 7) and it shows a pixel type of 32BF, an overkill for my needs. Is there a way of changing the pixel type to, say, 8BUI or 16BUI? I found st_bandpixeltype to access the type, but not yet a way to set it. Many thanks ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] MapAlgebra on three Rasters
I would suggest you create intermediate tables with CREATE TABLE xxx AS and see the result of each operation one after the other in order to isolate the problem. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of JamesH Sent: Wednesday, April 25, 2012 7:34 PM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] MapAlgebra on three Rasters Sorry, I should have explained what I'm trying to do with the query. I'm taking the two rasters density and euclidean, normalising them both before multiplying normalised density by 0.5, multiplying normalised euclidean by 0.3 and then adding these together. Then I am producing a slope surface, normalising that and then multiplying the normalised slope by 0.2 before using it along with the result of the first calculation to add effectively all three rasters together. Hope this explains better. Kind Regards, James - GIS Undergraduate -- View this message in context: http://postgis.17.n6.nabble.com/MapAlgebra-on- three-Rasters-tp4917643p4918408.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Simple Point Density Surface
Use ST_MapAlgebraFct:
http://postgis.refractions.net/documentation/manual-svn/RT_ST_MapAlgebraFct.html
Create a function that take all the georeference parameters of the raster
(width, height, ulx, uly, scalex, scaley, skewx, skewy, srid), the schema, the
table and the name of the geometry column of the point table and a radius
parameter, much like the following example, but returning the number of
intersecting geometries instead:
-- Custom function, to be executed, returning the value of the last geometry
intersecting with the pixel shape
CREATE OR REPLACE FUNCTION ST_FirstGeomValue4ma(pixel FLOAT, pos INTEGER[],
VARIADIC args TEXT[])
RETURNS FLOAT
AS $$
DECLARE
pixelgeom text;
result float4;
query text;
BEGIN
-- Reconstruct the pixel shape
pixelgeom =
ST_AsText(ST_PixelAsPolygon(ST_MakeEmptyRaster(args[1]::integer,
args[2]::integer, args[3]::float, args[4]::float, args[5]::float,
args[6]::float, args[7]::float, args[8]::float, args[9]::integer),
pos[1]::integer, pos[2]::integer));
-- Intersects
query = 'SELECT ' || quote_ident(args[13]) || '
FROM ' || quote_ident(args[10]) || '.' ||
quote_ident(args[11]) || '
WHERE ST_Intersects(ST_GeomFromText(' ||
quote_literal(pixelgeom) || ', '|| args[9] || '), ' || quote_ident(args[12]) ||
') LIMIT 1';
--RAISE NOTICE 'query = %', query;
EXECUTE query INTO result;
RETURN result;
END; $$
LANGUAGE 'plpgsql' IMMUTABLE;
-- Actual creation of the raster. The year has to be provided.
CREATE TABLE snow_2010 AS
SELECT ST_MapAlgebraFct(
rast,
'ST_FirstGeomValue4ma(float,integer[],text[])'::regprocedure,
ST_Width(rast)::text,
ST_Height(rast)::text,
ST_UpperLeftX(rast)::text,
ST_UpperLeftY(rast)::text,
ST_ScaleX(rast)::text,
ST_ScaleY(rast)::text,
ST_SkewX(rast)::text,
ST_SkewY(rast)::text,
ST_SRID(rast)::text,
'public', 'snow', 'geom', '2010'
) rast
FROM (SELECT ST_AddBand(ST_MakeEmptyRaster(89, 89, -3474442 - (26 * 198500) -
(198500/2), 8833946 + (198500/2), 198500, -198500, 0, 0, 9), '16BSI'::text,
-9, -9) rast) foo;
So in your case only the last (year) parameter has to change and the query
inside the custom function.
Pierre
-Original Message-
From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-
boun...@postgis.refractions.net] On Behalf Of JamesH
Sent: Wednesday, April 25, 2012 1:15 PM
To: postgis-users@postgis.refractions.net
Subject: [postgis-users] Simple Point Density Surface
Hi all,
I am looking to take a point dataset and generate a point density raster in
PostGIS, similar to the ArcGIS Point Density tool in Spatial Analyst.
What I believe I need to do is to define a neightbourhood around each output
raster cell centre and calculate a density value for each pixel based on the
number of points that fall within that neighbourhood.
Ideally to recreate the same results, I would like to define a neighbourhood
with a radius of 8 cells - most likely a square neighbourhood.
I have been considering this for a while but I really cannot understand how
to achieve this.
Any advise or help would be greatly appreciated.
Kind Regards,
James Holmes
-
GIS Undergraduate
--
View this message in context:
and create a
http://postgis.17.n6.nabble.com/Simple-Point-
Density-Surface-tp4917343p4917343.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Geom/Rast Intersection St9bad_alloc
James, Sorry for this late answer. -What is the type of the geometries you are intersecting with the raster? -If they are polygons, what proportion of your raster does all your geometry layer represent? If this proportion is very high (your polygons cover like 90% of the raster extent and your raster are big) then you are better to compute the summary stat in raster space like this: SELECT (ST_SummaryStatsAgg(ST_Clip(rast, geom))).* FROM clip_build, nclheat WHERE ST_Intersects(rast, geom) ST_SummaryStatsAgg is not installed with PostGIS 2.0. You will find here: http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql/st_summarystatsagg.sql In this case your tiles should be about the same size of the average size of your polygons. If the area covered by your polygons or if those geometries are lines you might instead do the process in vector space and do like you did using ST_AreaWeightedSummaryStats.sql http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql/st_areaweightedsummarystats.sql In this case smaller tiles are always better (10x10). The result of ST_Intersection is vectors and it is done on one band at a time. If you want a three band raster as a result then maybe you want to ST_Clip instead. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of JamesH Sent: Tuesday, April 24, 2012 11:42 AM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] Geom/Rast Intersection St9bad_alloc I am attempting to intersect a vector dataset of building polygons with a 3-Band Thermal tiff to produce a dataset that shows the thermal readings for each polygon. If I took the 3 band jpeg of this raster and used ST_AsTiff to convert it to a single band tiff would this calculation be valid? How could I place this into the query? SELECT gid, ST_Intersection(geom, rast, 1) FROM clip_build, nclheat WHERE ST_Intersects(rast, geom) any help greatly appreciated, Kind Regards, James - GIS Undergraduate -- View this message in context: http://postgis.17.n6.nabble.com/Geom-Rast- Intersection-St9bad-alloc-tp4912994p4913835.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] help needed
I tried retrieving a raster img using the gdal_translate. The problem now is that the retrieve works fine when I export only one of the bands, but gives a seg err. when no such option is given. You will have to report this to the GDAL mailing list. Secondly I wish to publish the raster data as a WCS . Does Geoserver support a PostGIS db for a raster source. Support for PostGIS Raster in Geoserver is provided as an extension to Geotools. See this: http://docs.geotools.org/latest/userguide/library/coverage/pgraster.html I don't know the actual status of the project. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Raster import is causing Postgres to close
When you say I open the sql file in Postgres , what do you mean exactly? How do you load the file in the db? Any error message? Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of JamesH Sent: Thursday, April 19, 2012 7:51 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] Raster import is causing Postgres to close Hi all, I am trying to import a Thermal Imagery 3-Band TIFF (approx. 27MB). Currently in raster2pgsql I am using: raster2pgsql -s 27700 -I -C C:\Users\Data\thermal.tif C:\Users\Data\Thermal.sql This executes fine in CMD, but when I open the sql file in Postgres it doesn't load and causes Postgres to stop working and close. I thought perhaps I needed to tile it, so I tried breaking it into 64x64 tiles but this had the same result. Can anyone advise on what could be causing this? Kind Regards, James - GIS Undergraduate -- View this message in context: http://postgis.17.n6.nabble.com/Raster-import- is-causing-Postgres-to-close-tp4897915p4897915.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Raster import is causing Postgres to close
When you say I open the sql file in Postgres , what do you mean exactly? How do you load the file in the db? In the SQL query window for the database, I select File Open and then select the sql file. Normally, once I've done this the code displays in the window and I then Execute the query to create the new table. Any error message? Yes, a pop-up that says 'pgAdmin III - PostgreSQL Tools has stopped working. A problem caused the program to stop working correctly. Windows will close the program and notify you if a solution is available.' First you have to differentiate pgAdmin from PostgreSQL. The problem does not occur in PostgreSQL. It occurs in pgAdmin... Your SQL file is too big to be handled by pgAdmin. Just go back to your shell where you did raster2pgsql and do (replacing yourpostgresrole and yourDatabase with the correct values) psql -U yourpostgresrole -f C:\Users\Data\Thermal.sql yourDatabase That's the way to load (or execute) large sql files into PostgreSQL. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Euclidean Distance Raster
I am wanting to create a Raster in PostGIS that displays the Euclidean Distance between Points that I have in a table. It can be done in ArcGIS, using the Euclidean Distance tool but I'm aiming to create it through PostGIS. There is no easy tool to do that right now. You will have to use ST_MapAlgebraFct and write your own custom function. Let us know your results. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Method to remove overlaps in a layer
So I reprocessed the two polygons below in a topology have tolerance=0. However
I still get the two same geometries in ovlp.test03_topo. I was expecting three
polygons: 1 non-overlapping part for each and 1 representing the overlapping
shared part with I don't know which attribute.
I though topology would prevent the insertion of any overlapping part and
remove (or aggregate) them. I thought at least the mbr column of the topo3.face
table would contain the three geometries but no, they are just mbrs...
How can I fix my overlapping problem with topology?
Pierre
So as of r9630 this topology of yours builds.
Of course your input geometries are not going to being fully
represented as the tolerance of 10 units on the topology is
chewing them badly.
Use tolerance with care !
Let me know how your next topos go :)
--strk;
,--o-.
| __/ |Delivering high quality PostGIS 2.0 !
| / 2.0 |http://strk.keybit.net - http://vizzuality.com
`-o--'
On Wed, Apr 11, 2012 at 02:53:07PM -0400, Pierre Racine wrote:
An even simpler example leads to another type of error:
SELECT CreateTopology('topo3',0, 10);
DROP TABLE IF EXISTS ovlp.test03;
CREATE TABLE ovlp.test03 AS
SELECT 1 id, ST_GeomFromText('POLYGON((0 1, 2 2, 2 0, 0 1))') geom
UNION ALL
SELECT 2 id, ST_GeomFromText('POLYGON((1 1, 3 2, 3 0, 1 1))') geom
CREATE TABLE ovlp.test03_topo (id integer);
SELECT AddTopoGeometryColumn('topo3', 'ovlp', 'test03_topo', 'topo',
'POLYGON'); -- 1
INSERT INTO ovlp.test03_topo (id, topo)
SELECT id, topology.toTopoGeom(geom, 'topo3', 1)
FROM ovlp.test03
gives:
** Error **
ERROR: SQL/MM Spatial exception - edge crosses node.
SQL state: P0001
Context: PL/pgSQL function topogeo_addpoint line 96 at assignment
PL/pgSQL function topogeo_addlinestring line 125 at assignment
SQL statement SELECT array_cat(edges, array_agg(x)) FROM ( select
topology.TopoGeo_addLinestring(atopology, rec.geom, tol) as x ) as foo
PL/pgSQL function topogeo_addpolygon line 27 at assignment
SQL statement INSERT INTO topo3.relation(topogeo_id, layer_id,
element_type, element_id) SELECT 2, 1, 3, topogeo_addPolygon('topo3',
'01030001000400F03FF03F000
0084000400840
F03FF03F'::geometry, 10);
PL/pgSQL function totopogeom line 129 at EXECUTE statement
Pierre
-Original Message-
From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-
boun...@postgis.refractions.net] On Behalf Of Pierre Racine
Sent: Wednesday, April 11, 2012 2:40 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Method to remove overlaps in a layer
So I have been creating a very simple example based on the example in the
toTopoGeom() documentation page:
http://www.postgis.org/documentation/manual-svn/toTopoGeom.html
SELECT CreateTopology('topo1',31467, 10);
CREATE TABLE ovlp.test02_topo (id integer);
SELECT AddTopoGeometryColumn('topo1', 'ovlp', 'test02_topo', 'topo',
'POLYGON');
INSERT INTO ovlp.test02_topo (id, topo)
SELECT id, topology.toTopoGeom(geom, 'topo1', 1)
FROM ovlp.test02
And I get:
** Error **
ERROR: interpolate_point4d: invalid F (1)
SQL state: XX000
Context: PL/pgSQL function st_modedgesplit line 94 at assignment
PL/pgSQL function topogeo_addpoint line 91 at assignment
PL/pgSQL function topogeo_addlinestring line 132 at assignment
SQL statement SELECT array_cat(edges, array_agg(x)) FROM ( select
topology.TopoGeo_addLinestring(atopology, rec.geom, tol) as x ) as foo
PL/pgSQL function topogeo_addpolygon line 27 at assignment
SQL statement INSERT INTO topo1.relation(topogeo_id, layer_id,
element_type, element_id) SELECT 5, 1, 3, topogeo_addPolygon('topo1',
'010320EB7A01000880FD384B41F46154
41D84B4B41004050695441B55D4B415C6354
41BD594B41965B5441C8694B41625554
410080E15C4B410080474D54410080ED404B416C5054
410080FD384B41F4615441'::geometry, 10);
PL/pgSQL function totopogeom line 129 at EXECUTE statement
I join a picture of the original layer to be converted to a topologic
layer.
I'm still at beta 5... Should upgrading solve that particular problem?
Pierre
So what would be the normal/easiest steps to convert a messy polygon
coverage into a clean topology? Is it documented somewhere?
My guess:
1- SET search_path TO topology,public;
You shouldn't need this, when you load topology.sql you should
get topology already appended to the end of the search_path
associated with the database.
2- SELECT CreateTopoGeom('test')
Yep.
3- SELECT toTopoGeom(geom, 'test', 1
Re: [postgis-users] PostGIS Raster / Geom intersection perfromance.
try this: SELECT (pvc).* FROM (SELECT ST_ValueCount(rast,1,false) AS pvc FROM (SELECT st_clip(rast, geom) FROM temprast, nucleos WHERE tag = '28079000101' AND ST_Intersects(rast, geom) ) foo2 ) AS foo ORDER BY (pvc).value; -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of LewInMadrid Sent: Thursday, April 12, 2012 3:14 PM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] PostGIS Raster / Geom intersection perfromance. When I try the same command with a tiled raster the indexes don't seem to be used. If I specify the tile directly with rid=3, the query takes about 2 second. If not, it takes around 30seconds. SELECT (pvc).* FROM (SELECT ST_ValueCount(rast,1,false) AS pvc FROM ( SELECT st_clip(rast, (SELECT geom FROM nucleos WHERE tag = '28079000101')) rast FROM temprast where rid=3) foo2 ) AS foo ORDER BY (pvc).value; What can I do to make it use the raster index? -- View this message in context: http://postgis.17.n6.nabble.com/PostGIS-Raster- Geom-intersection-perfromance-tp4748527p4862412.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] extraction of pixel from raster
You will have to work with ST_Reclass(). See the doc: http://postgis.refractions.net/documentation/manual-svn/RT_ST_Reclass.html and mostly the third example. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of jyoti gajrani Sent: Wednesday, April 11, 2012 7:36 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] extraction of pixel from raster hello can anybody plz help me in solving my problem -- I hv imported a raster in POSTGIS2.0 in a table. now i want to extract all pixels with only particular color like blue or some specific from that. how can i do that? Jyoti ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] NDVI Calculation from two bands within one Raster
Your CASE syntax is wrong: http://www.postgresql.org/docs/9.1/interactive/functions-conditional.html#FUNCTIONS-CASE Should look like: ST_MapAlgebraExpr(a.rast, 4, b.rast, 3, 'CASE WHEN ([rast1] + [rast2]) = 0 THEN 999 ELSE ([rast1] - [rast2]) /([rast1] + [rast2])::float END') FROM ndvi a, ndvi b; Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of JamesH Sent: Wednesday, April 11, 2012 10:56 AM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] NDVI Calculation from two bands within one Raster Sorry to keep reviving this thread, espcially with a basic question. I am using: SELECT ST_MapAlgebraExpr(a.rast, 4, b.rast, 3, '([rast1] - [rast2]) /([rast1] + [rast2])::float') WHEN (rast1 + rast2 = 0) THEN = 999 ELSE ([rast1] - [rast2]) /([rast1] + [rast2])::float END' FROM ndvi a, ndvi b; Where the WHEN statement is being used to prevent the error division by zero. However this returns: syntax error at or near WHEN Can anyone advise on where I'm structuring this query wrongly. Regards, James - GIS Undergraduate -- View this message in context: http://postgis.17.n6.nabble.com/NDVI- Calculation-from-two-bands-within-one-Raster-tp4656995p4832397.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] NDVI Calculation from two bands within one Raster
Are you using ST_dumpAsPolygons() to display in OpenJump? Depending on the GDAL version you're using, ST_DumpAsPolygons() might be converting floats to integers. Try looking at individual pixel values with ST_Value() or us ST_PixelAsPolygons() Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of JamesH Sent: Wednesday, April 11, 2012 12:39 PM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] NDVI Calculation from two bands within one Raster Thanks, that has worked and can visualise through OpenJump. The Raster it produces (below), has only values of zero or one in its attributes. I'm assuming in the calculation its not keeping the values as Floats but its converting it to integer? Must be rounding them to either zero or one. Is there a way to solve this i.e. keep the pixels as floating integers? http://postgis.17.n6.nabble.com/file/n4832782/ndvi.jpg Kind Regards, James - GIS Undergraduate -- View this message in context: http://postgis.17.n6.nabble.com/NDVI- Calculation-from-two-bands-within-one-Raster-tp4656995p4832782.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] NDVI Calculation from two bands within one Raster
Add a pixeltype parameter to your ST_MapAlgebraExpr call. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of JamesH Sent: Wednesday, April 11, 2012 1:44 PM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] NDVI Calculation from two bands within one Raster Are you using ST_dumpAsPolygons() to display in OpenJump? Try looking at individual pixel values with ST_Value() or us ST_PixelAsPolygons() I was using DumpAsPolygons. Used ST_PixelAsPolygons and this returned the same, either zero, one or NullData. If it helps my GDAL version is: GDAL=GDAL 1.9.0, released 2011/12/29 Try looking at individual pixel values with ST_Value() Examined and in the final output, St_ValueCount returns: (0, 32980) (1, 519) Looked closer at the calculation with ST_ValueCount and [rast1] - [rast2] = (0, 17458) [rast1]+[rast2] = (0, 16223) so at some point these must be dividing by zero? Could this be affecting the NDVI? Kind Regards, James - GIS Undergraduate -- View this message in context: http://postgis.17.n6.nabble.com/NDVI- Calculation-from-two-bands-within-one-Raster-tp4656995p4832963.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Method to remove overlaps in a layer
So I have been creating a very simple example based on the example in the
toTopoGeom() documentation page:
http://www.postgis.org/documentation/manual-svn/toTopoGeom.html
SELECT CreateTopology('topo1',31467, 10);
CREATE TABLE ovlp.test02_topo (id integer);
SELECT AddTopoGeometryColumn('topo1', 'ovlp', 'test02_topo', 'topo', 'POLYGON');
INSERT INTO ovlp.test02_topo (id, topo)
SELECT id, topology.toTopoGeom(geom, 'topo1', 1)
FROM ovlp.test02
And I get:
** Error **
ERROR: interpolate_point4d: invalid F (1)
SQL state: XX000
Context: PL/pgSQL function st_modedgesplit line 94 at assignment
PL/pgSQL function topogeo_addpoint line 91 at assignment
PL/pgSQL function topogeo_addlinestring line 132 at assignment
SQL statement SELECT array_cat(edges, array_agg(x)) FROM ( select
topology.TopoGeo_addLinestring(atopology, rec.geom, tol) as x ) as foo
PL/pgSQL function topogeo_addpolygon line 27 at assignment
SQL statement INSERT INTO topo1.relation(topogeo_id, layer_id, element_type,
element_id) SELECT 5, 1, 3, topogeo_addPolygon('topo1',
'010320EB7A01000880FD384B41F4615441D84B4B41004050695441B55D4B415C635441BD594B41965B5441C8694B41625554410080E15C4B410080474D54410080ED404B416C5054410080FD384B41F4615441'::geometry,
10);
PL/pgSQL function totopogeom line 129 at EXECUTE statement
I join a picture of the original layer to be converted to a topologic layer.
I'm still at beta 5... Should upgrading solve that particular problem?
Pierre
So what would be the normal/easiest steps to convert a messy polygon
coverage into a clean topology? Is it documented somewhere?
My guess:
1- SET search_path TO topology,public;
You shouldn't need this, when you load topology.sql you should
get topology already appended to the end of the search_path
associated with the database.
2- SELECT CreateTopoGeom('test')
Yep.
3- SELECT toTopoGeom(geom, 'test', 1)
FROM mymessyone;
You didn't create a layer, see AddTopoGeometryColumn.
The third argument is a layer id, as returned by that function.
or
SELECT ST_CreateTopoGeo('test', geom)
FROM mymessyone;
This one only works starting with an empty topology so
you'll need to pass it a full collection:
SELECT ST_CreateTopoGeo('test', ST_Collect(geom)) FROM mymessyone;
But I'd recommend using toTopoGeom instead, to keep the linking
between attributes and geometries.
What happens when a polygon to be added to the topology overlaps a
polygon already in the topology?
Two overlapping rectangles would produce a total of 3 faces.
If you're returning TopoGeometry objects (your step 2) both
of them will be defined by 2 faces, having one face in common.
attachment: overlaps.jpeg___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Method to remove overlaps in a layer
An even simpler example leads to another type of error:
SELECT CreateTopology('topo3',0, 10);
DROP TABLE IF EXISTS ovlp.test03;
CREATE TABLE ovlp.test03 AS
SELECT 1 id, ST_GeomFromText('POLYGON((0 1, 2 2, 2 0, 0 1))') geom
UNION ALL
SELECT 2 id, ST_GeomFromText('POLYGON((1 1, 3 2, 3 0, 1 1))') geom
CREATE TABLE ovlp.test03_topo (id integer);
SELECT AddTopoGeometryColumn('topo3', 'ovlp', 'test03_topo', 'topo',
'POLYGON'); -- 1
INSERT INTO ovlp.test03_topo (id, topo)
SELECT id, topology.toTopoGeom(geom, 'topo3', 1)
FROM ovlp.test03
gives:
** Error **
ERROR: SQL/MM Spatial exception - edge crosses node.
SQL state: P0001
Context: PL/pgSQL function topogeo_addpoint line 96 at assignment
PL/pgSQL function topogeo_addlinestring line 125 at assignment
SQL statement SELECT array_cat(edges, array_agg(x)) FROM ( select
topology.TopoGeo_addLinestring(atopology, rec.geom, tol) as x ) as foo
PL/pgSQL function topogeo_addpolygon line 27 at assignment
SQL statement INSERT INTO topo3.relation(topogeo_id, layer_id, element_type,
element_id) SELECT 2, 1, 3, topogeo_addPolygon('topo3',
'01030001000400F03FF03F084000400840F03FF03F'::geometry,
10);
PL/pgSQL function totopogeom line 129 at EXECUTE statement
Pierre
-Original Message-
From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-
boun...@postgis.refractions.net] On Behalf Of Pierre Racine
Sent: Wednesday, April 11, 2012 2:40 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Method to remove overlaps in a layer
So I have been creating a very simple example based on the example in the
toTopoGeom() documentation page:
http://www.postgis.org/documentation/manual-svn/toTopoGeom.html
SELECT CreateTopology('topo1',31467, 10);
CREATE TABLE ovlp.test02_topo (id integer);
SELECT AddTopoGeometryColumn('topo1', 'ovlp', 'test02_topo', 'topo',
'POLYGON');
INSERT INTO ovlp.test02_topo (id, topo)
SELECT id, topology.toTopoGeom(geom, 'topo1', 1)
FROM ovlp.test02
And I get:
** Error **
ERROR: interpolate_point4d: invalid F (1)
SQL state: XX000
Context: PL/pgSQL function st_modedgesplit line 94 at assignment
PL/pgSQL function topogeo_addpoint line 91 at assignment
PL/pgSQL function topogeo_addlinestring line 132 at assignment
SQL statement SELECT array_cat(edges, array_agg(x)) FROM ( select
topology.TopoGeo_addLinestring(atopology, rec.geom, tol) as x ) as foo
PL/pgSQL function topogeo_addpolygon line 27 at assignment
SQL statement INSERT INTO topo1.relation(topogeo_id, layer_id,
element_type, element_id) SELECT 5, 1, 3, topogeo_addPolygon('topo1',
'010320EB7A01000880FD384B41F46154
41D84B4B41004050695441B55D4B415C6354
41BD594B41965B5441C8694B41625554
410080E15C4B410080474D54410080ED404B416C5054
410080FD384B41F4615441'::geometry, 10);
PL/pgSQL function totopogeom line 129 at EXECUTE statement
I join a picture of the original layer to be converted to a topologic layer.
I'm still at beta 5... Should upgrading solve that particular problem?
Pierre
So what would be the normal/easiest steps to convert a messy polygon
coverage into a clean topology? Is it documented somewhere?
My guess:
1- SET search_path TO topology,public;
You shouldn't need this, when you load topology.sql you should
get topology already appended to the end of the search_path
associated with the database.
2- SELECT CreateTopoGeom('test')
Yep.
3- SELECT toTopoGeom(geom, 'test', 1)
FROM mymessyone;
You didn't create a layer, see AddTopoGeometryColumn.
The third argument is a layer id, as returned by that function.
or
SELECT ST_CreateTopoGeo('test', geom)
FROM mymessyone;
This one only works starting with an empty topology so
you'll need to pass it a full collection:
SELECT ST_CreateTopoGeo('test', ST_Collect(geom)) FROM mymessyone;
But I'd recommend using toTopoGeom instead, to keep the linking
between attributes and geometries.
What happens when a polygon to be added to the topology overlaps a
polygon already in the topology?
Two overlapping rectangles would produce a total of 3 faces.
If you're returning TopoGeometry objects (your step 2) both
of them will be defined by 2 faces, having one face in common.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] problem in displaying raster in Geoserver
I suggest you write to the GeoServer users list: geoserver-us...@lists.sourceforge.net Have a look at this post: http://osgeo-org.1560.n6.nabble.com/geoserver-2-1-3-and-postgis-raster-tt4534809.html#none Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of jyoti gajrani Sent: Friday, April 06, 2012 7:31 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] problem in displaying raster in Geoserver Hi, I hv imported a raster in PostGIS in the form of table and done some queries on it and now i want to display that result in Geoserver. can i do this? or some other method to display it in form of image? Thanks Jyoti ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Integration of GPS data and raster time series
In case someone is interested in the management of raster time series, I solved importing all the images in the same table, same rast column. I upload images with the file name, then a trigger massages the file name to transform it in a timestamp data type. At this point each tile has its timestamp and can be easily queried as a kind of spatio-temporal object (including retrieving a temporal profile). Simple and fast. Thanks Ferdinando, that's the way to go. Did you spatially tile each raster as well? Like 10 pixels x 10 pixels? Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] summarizing a polygon values in a raster
I have been investigating the apparent disappearance of ST_PixelAsPolygons. I have updated the database to 2.0 and preformed an apparently successful soft upgrade on the database. Still when, when trying to use the function i get: ERROR: function st_pixelaspolygons(raster) does not exist when using \df to list functions, it seems that there are two functions called ST_PixelAsPolygon (no plural for Polygon): public | st_pixelaspolygon| geometry | rast raster, band integer, x integer, y integer public | st_pixelaspolygon| geometry | rast raster, x integer, y integer Peter, There should be two function names: ST_PixelAsPolygon() and ST_PixelAsPolygons(). the first return just one pixel and the second convert a whole tile (or raster) to a set of row, each row being a geometry representing a pixel. You can find both of them in rtpostgis.sql Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Reclassify a Slope Surface
I am trying to reclassify a slope surface with a reverse normalisation (lower the slope value, the closer to 1 it becomes). It can be done in ArcGIS in the Raster Calculator using: (0.404234915971756 - slope) / 0.404234915971756 where 0.404234915971756 is the maximum value (minimum value of the data is zero so I don't need to include this in the calculation). Wanting to recreate this process in PostGIS so can anyone advise on how I could do the calculation for: (max. value - cell value)/ max. value for each cell in the raster? Cannot get my head around it. 1) Compute the slope with ST_Slope() 2) Determine the max slope value with ST_SummaryStat() 3) Normalize with ST_MapAlgebraExpr() You can eventually do all that in one complex query. And read the doc: http://postgis.refractions.net/documentation/manual-svn/RT_reference.html Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Reclassify a Slope Surface
For ST_MapAlgebraExpr,: you can use the term [rast] to refer to the pixel value of the original band Does this mean rast can be used as the value for each individual pixel? For example, something like (max.value - [rast]) / max.value. Or have I misinterpreted how [rast] can be used? You have well interpreted. However you have to construct the expression as a string parameter casting the maxvalue to text. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] summarizing a polygon values in a raster
Thanks for your suggestion Pierre. I am working with v2.0 and it seems that ST_PixelsAsPolygons() is not available. The singular Pixel version works though. Any idea why that would be? Try ST_PixelAsPolygons()... (without the firs 's') ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] summarizing a polygon values in a raster
I would like to create a raster of an area-weighted average value derived from a polygon layer. The raster template is a 2km grid and the polygon layer is also a grid but at 10km. If some one could recommend a (high level) strategy for this if be grateful. 1) Create a vector grid having a x and a y like this: CREATE TABLE yourgrid AS SELECT (gvxy).geom geom, (gvxy).x x, (gvxy).y y FROM (SELECT ST_PixelsAsPolygons(rast) gvxy FROM (SELECT ST_MakeEmptyRaster(your own parameters here covering your polygon extent)) foo1) foo2 2) Make sure your polygon layer is indexed CREATE INDEX yourpolylayer_geom_idx ON yourpolylayer USING gist (geom ); 3) Intersect mygrid with your polygon layer and generate summary stats at the same time: CREATE TABLE weightedvectorgrid AS SELECT g.x, g.y, g.geom, (aws).weightedmean mean FROM (SELECT ST_AreaWeightedSummaryStats(intgeom, p.value) aws, g.x, g.y, g.geom FROM (SELECT ST_Intersection(g.geom, p.geom) intgeom, p.value, g.x, g.y, g.geom FROM yourgrid g, yourpolylayer p WHERE ST_Intersects(g.geom, p.geom) ) foo GROUP BY g.x, g.y, g.geom ) foo2 The ST_AreaWeightedSummaryStats() function is not part of the PostGIS release but is available as part of the source tree: http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql/st_areaweightedsummarystats.sql 4) Union all the geometry as a raster (this one might be a bit slow depending on the size of your grid) CREATE TABLE weightedrastergrid AS SELECT ST_Union(ST_AsRaster(geom, 'MEAN')) rast FROM weightedvectorgrid I wrote all these without testing so you might have some adjustment to do. Let me know about your progress... Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Slope
SELECT ST_AsBinary((gv).geom), (gv).val FROM (SELECT ST_DumpAsPolygons(ST_Slope(ST_MapAlgebraExpr(ST_AddBand(ST_MakeEmpt yRaster(10, 10, 0, 0, 0.1, 0.1, 0, 0, 4269), '8BUI'::text, 1, 0), '32BUI', '([rast.x] - 1) * 10 + [rast.y]'), 1, '8BUI'::text)) gv FROM height_newcastle ) foo Well now that you got ST_Slope working you have to pass it your own raster SELECT ST_AsBinary((gv).geom), (gv).val FROM (SELECT ST_DumpAsPolygons(ST_Slope(rast) gv FROM height_newcastle) foo ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Slope
Does SELECT ST_AsBinary((gv).geom), (gv).val FROM (SELECT ST_DumpAsPolygons(ST_Slope(rast) gv FROM height_newcastle) foo calculate the slope surface from height_newcastle and then display it in OpenJump? Yes... Do I not need to use '([rast.x] - 1) * 10 + [rast.y]') to assign the unique values anymore? ST_MapAlgebraExpr(ST_AddBand(ST_MakeEmptyRaster(10, 10, 0, 0, 0.1, 0.1, 0, 0, 4269), '8BUI'::text, 1, 0), '32BUI', '([rast.x] - 1) * 10 + [rast.y]'), 1, '8BUI'::text) was just a test raster. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] NDVI Calculation from two bands within one Raster
SELECT ST_MapAlgebraExpr(a.rast, 4, b.rast, 3, '(a.rast - b.rast) /(a.rast + b.rast)::float') FROM nclraster1 a, nclraster1 b; instead of rast1 - rast2 etc. Returns an error of ERROR: missing FROM-clause entry for table a LINE 1: SELECT ((a.rast - b.rast) /(a.rast + b.rast)::float)::double... Can anyone explain this? In the expression, you don't refer to the pixels of a.rast with a.rast but with rast1. so your query should be: SELECT ST_MapAlgebraExpr(a.rast, 4, b.rast, 3, '(rast1 - rast2) /(rast1 + rast2)::float') FROM nclraster1 a, nclraster1 b; Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS 2.0.0 Released
That's a great news! I would like again to thanks, for the raster development: -Bborie Park from the Center for Vectorborne Diseases at the University of California, our main developer since one year, -Sandro Santilli now from Vizzuality, -Jorge Arevalo from DEIMOS Space, -David Zwarg from Azavea, -Mateusz Loskot from CadCorp, -Regina and Leo Obe from Paragon Corporation and -the other members of the PostGIS team for all their work on the raster extension. That has been great to work on this release with you guys! Congratulation everybody for making PostGIS 2.0.0 alive! Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Paul Ramsey Sent: Tuesday, April 03, 2012 5:04 PM To: postgis-de...@postgis.refractions.net; PostGIS Users Discussion Subject: [postgis-users] PostGIS 2.0.0 Released The PostGIS development team is super excited, can hardly believe that they are actually doing this, aren't maybe even sure that they are ready to make this kind of commitment, not so young, and not when we have so much more living to do, but: PostGIS 2.0.0 is complete and available for download. http://postgis.org/download/postgis-2.0.0.tar.gz The development process for 2.0 has been very long, but has resulted in a release with a number of exciting new features. * Raster data and raster/vector analysis in the database * Topological models to handle objects with shared boundaries * PostgreSQL typmod integration, for an automagical geometry_columns table * 3D and 4D indexing * Index-based high performance nearest-neighbour searching * Many more vector functions including * ST_Split * ST_Node * ST_MakeValid * ST_OffsetCurve * ST_ConcaveHull * ST_AsX3D * ST_GeomFromGeoJSON * ST_3DDistance * Integration with the PostgreSQL 9.1 extension system * Improved commandline shapefile loader/dumper * Multi-file import support in the shapefile GUI * Multi-table export support in the shapefile GUI * A geo-coder optimized for free US Census TIGER (2010) data We are greatly indebted to our large community of beta testers who valiantly tested PostGIS 2.0.0 and reported bugs so we could squash them before release time. And also we want to thank our parents for making PostGIS possible. Yours, The PostGIS development team ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] raster aggregate functions - mean band
I got a raster table with 30 rasters, each raster represents one day including bands with temperature-values. Is there a possibility to get the mean of all the values of band1 from all rasters with an aggregate-function like: SELECT MEAN_BAND(band1, rast) AS new_band FROM table; I tried to find something like that but I could just find MapAlgebra-functions using up to 2 rasters as input. Put all your raster (not tiled) in the same table and do: SELECT ST_Union(rast, 'MEAN') FROM yourrasttable This might be slow if your rasters are big. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Slope
However when trying to export this into a new table to view in QGIS, the table does not have any raster geometry to it? Where am I going wrong? What do you mean by raster geometry? Just add a name to the raster: CREATE TABLE slope AS( SELECT rid, rast, ST_Slope(ST_MapAlgebraExpr(ST_AddBand(ST_MakeEmptyRaster(10, 10, 0, 0, 0.1, 0.1, 0, 0, 4269), '8BUI'::text, 1, 0), '32BUI', '([rast.x] - 1) * 10 + [rast.y]'), 1, '8BUI'::text) slope FROM nclheights ); ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] raster efficiency
Do you suggest to cut them before with gdal and have something like 1000 small tiles rather than my 14 big ones? Yes. I wrote a ST_Tile function prototype to retile your raster if you don't want to reload them: http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql/st_tile.sql Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Slope
Do you know which was the last version of PostGIS raster that works with the plugin if you use QGIS? I am running on Windows 7 I know that Mauricio de Paulo wrote to us very recently saying: I've ran tests on windows xp, qgis 1.7.4, gdal 1.9, postgis 2.0RC1 with success. I tested single tiled raster and with 512x512 tiles. I didn't test multi color raster data yet, but it seems that the gdal 1.9 driver is working properly with qgis and postgis rc1. So last PostGIS RC SHOULD work with QGIS... You'll have to find out with Mauricio what's going wrong with the QGIS plugin in your specific case. I use OpenJump... Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Slope
-Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of JamesH Sent: Monday, April 02, 2012 11:12 AM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] ST_Slope Ok I'm trying OpenJump as I need to get going with this for my dissertation. Now I'm connected to the datastore, I am trying to run the following query: SELECT ST_AsBinary(rast), slope FROM (SELECT rid, rast, ST_Slope(ST_MapAlgebraExpr(ST_AddBand(ST_MakeEmptyRaster(10, 10, 0, 0, 0.1, 0.1, 0, 0, 4269), '8BUI'::text, 1, 0), '32BUI', '([rast.x] - 1) * 10 + [rast.y]'), 1, '8BUI'::text) slope FROM nclheights ); AS foo To display your slope raster in OpenJump: SELECT ST_AsBinary((gv).geom), (gv).val FROM (SELECT ST_DumpAsPolygons(ST_Slope(ST_MapAlgebraExpr(ST_AddBand(ST_MakeEmptyRaster(10, 10, 0, 0, 0.1, 0.1, 0, 0, 4269), '8BUI'::text, 1, 0), '32BUI', '([rast.x] - 1) * 10 + [rast.y]'), 1, '8BUI'::text)) gv FROM nclheights ) foo Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] raster efficiency
I found quite to calculate a profile: it takes approx 1-2 secondes to calculate a profile of 4-6 points. Is there any thing that I can do to fasten the process? How do you compute your profile? What is the best strategy: having more small rasters or a single big one? For raster/vector operations, smaller raster tiles (10x10) is generally preferable/faster. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How to generate polygon grid for tiled map services
I'm wondering if there is a formula to generate 20 tables (one for each zoom level) of square polygons, representing a tile grid of popular TMS like OSM or MapQuest? I guess there shoud be a way to convert x,y,z tile coordinates to lat/lon coordinates in PostGIS. With PostGIS Raster you can now easily create a vector grid with ST_MakeEmptyRaster() and ST_PixelAsPolygons(). SELECT (gvxy).geom, (gvxy).x, (gvxy).y FROM (SELECT ST_PixelAsPolygons(ST_MakeEmptyRaster(width, height, ulx, uly, scalex, scaley, 0, 0, srid)) gvxy) foo Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Load tiles on a raster table breaks alignment constraint
Am I doing something wrong? What is the correct way to load a raster table with existing tiles without breaking alignment constraint? We should probably have a tolerance parameter in the ST_SameAlignment check... I would suggest you just ST_SnapToGrid(raster) all your tiles and then try to reapply the alignment constraint. http://postgis.refractions.net/documentation/manual-svn/RT_ST_SnapToGrid.html Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
