Re: [postgis-users] Spoke too soon
Thanks for the advice on the workaround. It is good to know that I was not doing anything fundamentally wrong. If necessary I will see if making a numeric field with a number for each image, which can be used instead of the filename text, helps. D -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Paragon Corporation Sent: 05 February 2012 16:39 To: 'PostGIS Users Discussion' Cc: 'David Zwarg' Subject: Re: [postgis-users] Spoke too soon Darrell, I'm having similar issues using our alpha3 build and latest comiled from http://vbkto.dyndns.org/sdk/ . There is an example in the FAQ with a text field and that used to work when I tried it when I wrote that FAQ item a while ago on windows. I think it might be because of some changes in GDAL driver or raster that broke string quoting. I was hoping this was fixed already. I've cc'd Jorge since he manages the raster driver. I think David Zwarg was planning to work on this at the code sprint which should be starting soon. Here is what I used to use which worked a while back: gdal_translate -of PNG "PG:host=localhost port=5441 dbname=postgis20_sampler user=postgres password='whatever' schema='aerials' table=test_100_100 mode='2' where="filename='23128870.jpg'"" C:\23128870.png and now I get a: ERROR 1: Error browsing database for PostGIS Raster properties GDALOpen failed - 1 Error browsing database for PostGIS Raster properties I haven't checked my logs to see what query its running. could be one of the field changes in the view. As a work around, try this: First figure out the rid range of the file with an SQL Query like: SELECT max(rid), min(rid) FROM aerials.test_100_100 WHERE filename='23128870.jpg'; gdal_translate -of PNG "PG:host=localhost port=5441 dbname=postgis20_sampler user=postgres password='whatever' schema='aerials' table=test_100_100 mode='2' where='rid between 1 and 2500'" C:\23128870.png > -Original Message- > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of > Andy Colson > Sent: Sunday, February 05, 2012 10:30 AM > To: PostGIS Users Discussion > Subject: Re: [postgis-users] Spoke too soon > > On 02/05/2012 09:13 AM, Darrel Maddy wrote: > > I would be grateful if someone could help me with the syntax for > > filenames when using the WHERE clause in gdal_translate > > > > I have > > gdal_translate -of GTiff "PG:host=localhost port=5432 > dbname=testcuda > > user=postgres password=H$%£"! schema=public table=rast100 > > where='filename="Heights_11_10.tif"' mode=2" Height10.tif > > > > I tried various forms of the 'filename=' clause (and > simplifying the filename to H10.tif) but without success (the form > given on the FAQ does not work in windows either). What does work is: > > > > gdal_translate -of GTiff "PG:host=localhost port=5432 > dbname=testcuda > > user=postgres password= H$%£"! schema=public table=rast100 > > where='rid>someno AND rid > > > Obviously I can work with the latter but it requires me to > know the start and end rid (or do a query to find it). > Ideally I would like to be able to just download the image via the > filename. > > > > Thanks in anticipation of your help. > > > > Darrel > > > > > > > > sql uses single quote for a string, and double quote for column names. > > where='filename="Heights_11_10.tif"' > > that probably meant: if column filename matched column > Heights_11_10.tif. > > Try switching the quotes: > > where="filename='Heights_11_10.tif' " > > -Andy > ___ > 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] FW: Spoke too soon
Sorry Andy, I seem to have sent the reply to your personal email - was wondering why it had not appeared. D -Original Message- From: Darrel Maddy Sent: 05 February 2012 15:49 To: 'Andy Colson' Subject: RE: [postgis-users] Spoke too soon Thanks for trying, unfortunately I tried this (actually It was one of the options I already tried) just now but to no avail, it produces the same error C:\Temp>download.bat ERROR 1: Error browsing database for PostGIS Raster properties GDALOpen failed - 1 Error browsing database for PostGIS Raster properties I should confirm that running this as an sql query inside PG does select the tiles requested so there is no error in the filename or the 'filename' column name. The rest of the gdal call works fine with the rid clause. All very curious. Darrel -Original Message- From: Andy Colson [mailto:a...@squeakycode.net] Sent: 05 February 2012 15:30 To: PostGIS Users Discussion Cc: Darrel Maddy Subject: Re: [postgis-users] Spoke too soon On 02/05/2012 09:13 AM, Darrel Maddy wrote: > I would be grateful if someone could help me with the syntax for > filenames when using the WHERE clause in gdal_translate > > I have > gdal_translate -of GTiff "PG:host=localhost port=5432 dbname=testcuda > user=postgres password=H$%£"! schema=public table=rast100 > where='filename="Heights_11_10.tif"' mode=2" Height10.tif > > I tried various forms of the 'filename=' clause (and simplifying the filename > to H10.tif) but without success (the form given on the FAQ does not work in > windows either). What does work is: > > gdal_translate -of GTiff "PG:host=localhost port=5432 dbname=testcuda > user=postgres password= H$%£"! schema=public table=rast100 > where='rid>someno AND rid > Obviously I can work with the latter but it requires me to know the start and > end rid (or do a query to find it). Ideally I would like to be able to just > download the image via the filename. > > Thanks in anticipation of your help. > > Darrel > > > sql uses single quote for a string, and double quote for column names. where='filename="Heights_11_10.tif"' that probably meant: if column filename matched column Heights_11_10.tif. Try switching the quotes: where="filename='Heights_11_10.tif' " -Andy ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Spoke too soon
I would be grateful if someone could help me with the syntax for filenames when using the WHERE clause in gdal_translate I have gdal_translate -of GTiff "PG:host=localhost port=5432 dbname=testcuda user=postgres password=H$%£"! schema=public table=rast100 where='filename="Heights_11_10.tif"' mode=2" Height10.tif I tried various forms of the 'filename=' clause (and simplifying the filename to H10.tif) but without success (the form given on the FAQ does not work in windows either). What does work is: gdal_translate -of GTiff "PG:host=localhost port=5432 dbname=testcuda user=postgres password= H$%£"! schema=public table=rast100 where='rid>someno AND ridmailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Darrel Maddy Sent: 05 February 2012 10:11 To: PostGIS Users Discussion Subject: Re: [postgis-users] Rv: Postgis for Postgres 64 bits Apologies, I did not notice that gdal_translate could take a where clause in the connection string. Everything now works as I wanted. many thanks Darrel On 4 Feb 2012, at 22:03, "Darrel Maddy" wrote: > Thanks again for the rapid response. I have been using gdal_translate as you > suggest to reconstitute images, although, as yet, I have only successfully > done this when the tiled target image is in a table of its own. I am probably > being a bit slow but have not yet figured out how to extract one target image > from a table which contains multiple tiled images using only gdal_translate - > ideally I do not want to have to create a separate table for the target image > but rather simply extract the image of interest from a PG table containing > all of the images (hope that makes sense). > > Darrel > > > > -Original Message- > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of > Paragon Corporation > Sent: 04 February 2012 21:44 > To: 'PostGIS Users Discussion' > Subject: Re: [postgis-users] Rv: Postgis for Postgres 64 bits > > > >> "First thing 1000x1000 blocks is too big. If you are going to be >> doing a lot of ST_Value calls, you probably want to chunk your tiles >> to 100x100 or below. >> >> ST_Value currently requires doing a memcopy for each call so the >> bigger your tiles the more memcopying it will have to do. >> >> On top of that the bigger your tiles, the less useful ST_Intersects >> is. Its kind of the same issue with big geometries -- like if you >> stuffed all of africa in one record, and then were searching for a >> city in Africa, the ST_Intersects will not be that useful." >> >> Thanks for this. I did do a few tests. This is what I got: >> >> Tile SizeTime >> 2000350873ms >> 1000235415 >> 500288298 >> 300234895 >> 200231374 >> >> I guess I was concerned that at 200x200 the images were split across >> 3344 rows as opposed to the 176 of the 1000x1000. I am learning >> here, so I will give 100x100 a go (tomorrow) - I just worry about >> when I need to put the images back together (images will be uploaded >> as tiles and then the originals - model outputs - will be deleted). >> The geometry is composed of >> 100 points (this is for testing only - final configuration has yet to >> be determined) - I figured it would be faster if they were all on >> the same tile, but may be not? >> >> Thanks >> >> Darrel >> > > Bborie probably has more info on this since he's working it and can hopefully > chime in. Right now the ST_Union you'd use in the db is not very efficient > since its using plpgsql. Doing it with the gdal_translate is a lot faster and > you can do basic where conditions with that like where file_name='...'. > > In 2.0 ST_Union will be done mostly in C so should be faster. > > As far as reconstituting, yes and no. If your tiles are smaller and you > don't need the full tile, doing clip operations and union subset that covers > a geometric region will be faster again because of the whole memcopy issue. > > However if you need to export the tile -- reconsitute the original, then you > are right a bigger tile would be better, though you may want to try > gdal_translate as that I think is much more efficient for export. > http://www.postgis.org/documentation/manual-svn/RT_FAQ.html#id2997931 > > A lot fo these performance issues we'll be dealing with in the 2.1 release, > but for 2.0, we are just focussing on functionality and stability. > > Hope that helps, > Regina > > >
Re: [postgis-users] Rv: Postgis for Postgres 64 bits
Apologies, I did not notice that gdal_translate could take a where clause in the connection string. Everything now works as I wanted. many thanks Darrel On 4 Feb 2012, at 22:03, "Darrel Maddy" wrote: > Thanks again for the rapid response. I have been using gdal_translate as you > suggest to reconstitute images, although, as yet, I have only successfully > done this when the tiled target image is in a table of its own. I am probably > being a bit slow but have not yet figured out how to extract one target image > from a table which contains multiple tiled images using only gdal_translate - > ideally I do not want to have to create a separate table for the target image > but rather simply extract the image of interest from a PG table containing > all of the images (hope that makes sense). > > Darrel > > > > -Original Message- > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Paragon > Corporation > Sent: 04 February 2012 21:44 > To: 'PostGIS Users Discussion' > Subject: Re: [postgis-users] Rv: Postgis for Postgres 64 bits > > > >> "First thing 1000x1000 blocks is too big. If you are going to be >> doing a lot of ST_Value calls, you probably want to chunk your tiles >> to 100x100 or below. >> >> ST_Value currently requires doing a memcopy for each call so the >> bigger your tiles the more memcopying it will have to do. >> >> On top of that the bigger your tiles, the less useful ST_Intersects >> is. Its kind of the same issue with big geometries -- like if you >> stuffed all of africa in one record, and then were searching for a >> city in Africa, the ST_Intersects will not be that useful." >> >> Thanks for this. I did do a few tests. This is what I got: >> >> Tile SizeTime >> 2000350873ms >> 1000235415 >> 500288298 >> 300234895 >> 200231374 >> >> I guess I was concerned that at 200x200 the images were split across >> 3344 rows as opposed to the 176 of the 1000x1000. I am learning here, >> so I will give 100x100 a go (tomorrow) - I just worry about when I >> need to put the images back together (images will be uploaded as tiles >> and then the originals - model outputs - will be deleted). The >> geometry is composed of >> 100 points (this is for testing only - final configuration has yet to >> be determined) - I figured it would be faster if they were all on the >> same tile, but may be not? >> >> Thanks >> >> Darrel >> > > Bborie probably has more info on this since he's working it and can hopefully > chime in. Right now the ST_Union you'd use in the db is not very efficient > since its using plpgsql. Doing it with the gdal_translate is a lot faster and > you can do basic where conditions with that like where file_name='...'. > > In 2.0 ST_Union will be done mostly in C so should be faster. > > As far as reconstituting, yes and no. If your tiles are smaller and you > don't need the full tile, doing clip operations and union subset that covers > a geometric region will be faster again because of the whole memcopy issue. > > However if you need to export the tile -- reconsitute the original, then you > are right a bigger tile would be better, though you may want to try > gdal_translate as that I think is much more efficient for export. > http://www.postgis.org/documentation/manual-svn/RT_FAQ.html#id2997931 > > A lot fo these performance issues we'll be dealing with in the 2.1 release, > but for 2.0, we are just focussing on functionality and stability. > > Hope that helps, > Regina > > > ___ > 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
Re: [postgis-users] Rv: Postgis for Postgres 64 bits
Thanks again for the rapid response. I have been using gdal_translate as you suggest to reconstitute images, although, as yet, I have only successfully done this when the tiled target image is in a table of its own. I am probably being a bit slow but have not yet figured out how to extract one target image from a table which contains multiple tiled images using only gdal_translate - ideally I do not want to have to create a separate table for the target image but rather simply extract the image of interest from a PG table containing all of the images (hope that makes sense). Darrel -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Paragon Corporation Sent: 04 February 2012 21:44 To: 'PostGIS Users Discussion' Subject: Re: [postgis-users] Rv: Postgis for Postgres 64 bits > "First thing 1000x1000 blocks is too big. If you are going to be > doing a lot of ST_Value calls, you probably want to chunk your tiles > to 100x100 or below. > > ST_Value currently requires doing a memcopy for each call so the > bigger your tiles the more memcopying it will have to do. > > On top of that the bigger your tiles, the less useful ST_Intersects > is. Its kind of the same issue with big geometries -- like if you > stuffed all of africa in one record, and then were searching for a > city in Africa, the ST_Intersects will not be that useful." > > Thanks for this. I did do a few tests. This is what I got: > > Tile Size Time > 2000 350873ms > 1000 235415 > 500 288298 > 300 234895 > 200 231374 > > I guess I was concerned that at 200x200 the images were split across > 3344 rows as opposed to the 176 of the 1000x1000. I am learning here, > so I will give 100x100 a go (tomorrow) - I just worry about when I > need to put the images back together (images will be uploaded as tiles > and then the originals - model outputs - will be deleted). The > geometry is composed of > 100 points (this is for testing only - final configuration has yet to > be determined) - I figured it would be faster if they were all on the > same tile, but may be not? > > Thanks > > Darrel > Bborie probably has more info on this since he's working it and can hopefully chime in. Right now the ST_Union you'd use in the db is not very efficient since its using plpgsql. Doing it with the gdal_translate is a lot faster and you can do basic where conditions with that like where file_name='...'. In 2.0 ST_Union will be done mostly in C so should be faster. As far as reconstituting, yes and no. If your tiles are smaller and you don't need the full tile, doing clip operations and union subset that covers a geometric region will be faster again because of the whole memcopy issue. However if you need to export the tile -- reconsitute the original, then you are right a bigger tile would be better, though you may want to try gdal_translate as that I think is much more efficient for export. http://www.postgis.org/documentation/manual-svn/RT_FAQ.html#id2997931 A lot fo these performance issues we'll be dealing with in the 2.1 release, but for 2.0, we are just focussing on functionality and stability. Hope that helps, Regina ___ 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] Rv: Postgis for Postgres 64 bits
"First thing 1000x1000 blocks is too big. If you are going to be doing a lot of ST_Value calls, you probably want to chunk your tiles to 100x100 or below. ST_Value currently requires doing a memcopy for each call so the bigger your tiles the more memcopying it will have to do. On top of that the bigger your tiles, the less useful ST_Intersects is. Its kind of the same issue with big geometries -- like if you stuffed all of africa in one record, and then were searching for a city in Africa, the ST_Intersects will not be that useful." Thanks for this. I did do a few tests. This is what I got: Tile Size Time 2000350873ms 1000235415 500 288298 300 234895 200 231374 I guess I was concerned that at 200x200 the images were split across 3344 rows as opposed to the 176 of the 1000x1000. I am learning here, so I will give 100x100 a go (tomorrow) - I just worry about when I need to put the images back together (images will be uploaded as tiles and then the originals - model outputs - will be deleted). The geometry is composed of 100 points (this is for testing only - final configuration has yet to be determined) - I figured it would be faster if they were all on the same tile, but may be not? Thanks Darrel ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Rv: Postgis for Postgres 64 bits
I am no db expert, indeed I am new to PG and postgis so I guess I have some preconceptions (and a lot to learn)! If you are correct though it would suggest there is little point to having a 64 bit version - so why do they do it? On a matter of performance however I do have a (probably dumb) question. I have two machines running the same version of PG/PostGIS2 under windows 64 with the following hardware: Machine 1: intel 980X, 12Gb RAM and Agility SSD (running in SATAII mode as SATAIII is not supported on the motherboard) Machine 2: intel 2600k (overclocked to 4.4GHz), 16Gb RAM and Corsair Force 4 SSD running over SATAIII I have a table of 11 rasters (3601x3134, originally tifs) each tiled into 1000x1000 blocks and I have a simple geometry shape file. My first ever SQL query looks like this: SELECT rid, gid, ST_Value(rast, 1, geom), filename, ST_X(geom) As X_coord, ST_Y(geom) As Y_coord FROM public.rasters, public.mypoints As A WHERE ST_Intersects(rasters.rast,1, A.geom) ORDER BY rid ASC ; as I am interested in drawing cross-sections of the 'height' data contained within the grids. Each intersect draws data from only one tile. Now the query works as intended (apologies if it can be done better) but the timings are: 1) ~35s 2) ~235s (n.b. these are the best timings - I did try different tiling sizes but both smaller and larger took longer) Now this result baffles me as I expected the 2600k to be much closer as watching a CPU monitor the code does not seem to be heavily multithreaded. I would be grateful for any suggestions as to why the 2600k is performing badly in comparison (and of course I would welcome any suggestions that might speed up the result - noting that ultimately this extraction could have as many as 250,000 rasters to work on. Thanks Darrel ps sorry for asking questions at the weekend but this is the only time I get to work on this stuff! -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Andy Colson Sent: 04 February 2012 19:52 To: PostGIS Users Discussion Subject: Re: [postgis-users] Rv: Postgis for Postgres 64 bits On 02/04/2012 01:14 PM, Darrel Maddy wrote: > Dear Regina, > > I would be happy to contribute $100 into any fund which secures a 64-bit > windows build of PostGIS2 -especially as I am about to install it on a > machine with 64Gb RAM :) > > Count now at 1 ? > > Thanks > > Darrel > http://www.ncl.ac.uk/gps/staff/profile/darrel.maddy#tab_profile > > I really wonder if its faster. I'd bet not. If you consider the architecture, I'd bet, for all but the very largest of datasets, 32 bit PG running on top 64 bit os is faster. Consider: you want the os disk cache to be as large as possible. You want a bunch of shared mem and I'd bet around 2 gig would be a sweet spot. More shared mem wouldn't necessarily be a benefit. (There are upper bounds recommended for shared_buffers, especially on windows). PG spawns a separate client for each connection, so each one of them could access up to 2 gig. (so things like work_mem could be large). And last, 32bit programs are faster because they move less data around (32bit pointers instead of 64bit). Taken all together PostMaster + (clients * postgres.exe) + os cache could easly be more than 2 gig using 32 bit PG on 64 bit windows. The only benefit of using 64bit PG would be a larger shared mem. The online manual says: "The useful range for shared_buffers on Windows systems is generally from 64MB to 512MB" http://www.postgresql.org/docs/9.1/static/runtime-config-resource.html The best use of ram, is as a disk cache, which 64 bit windows should use as much as possible (well, I assume). After that there is nothing in Postgres (Per Client) that is going to run faster with more than 2 gig of ram. Has anyone ever done any benchmarks? -Andy ___ 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] Rv: Postgis for Postgres 64 bits
Dear Regina, I would be happy to contribute $100 into any fund which secures a 64-bit windows build of PostGIS2 -especially as I am about to install it on a machine with 64Gb RAM :) Count now at 1 ? Thanks Darrel http://www.ncl.ac.uk/gps/staff/profile/darrel.maddy#tab_profile -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Paragon Corporation Sent: 04 February 2012 02:19 To: 'PostGIS Users Discussion' Subject: Re: [postgis-users] Rv: Postgis for Postgres 64 bits What people are interested in seeing a 64-bit windows build of PostGIS and willing to pay for it? We've heard a lot of people mention it, but unfortunately as Bborie said, we've run into some build issues with getting GEOS and GDAL to compile so its not trivial. A lot of the pieces we do have compiling under mingw-64. I think the issues are not insurmountable, but will take a lot of our time and possibly time of other PostGIS devs to help us out which we'd have to fund. We were thinking of having a pledge drive similar to what Sandro had for topology to collect enough money to focus on the effort. Would people be interested in say putting up $100 each for that? If we get about 20-30 people willing to do that, I think we can jump over the mingw-64 hurdles and then later eventually have a VC++ native build. Perhaps even have some funds to package in pgrouting (for 32bit and 64-bit). We'd be willing to set aside time from our consulting work to work on it, if we could replace the lost consulting income. Would people be interested in it? As it stands I think mingw-64 is still the easiest since there are too many pieces of the PostGIS environment that assume a unix like toolchain. Once we have it working, we'd be willing to do the work of continually making the build available as we currently do with the 32-bit windows stack builder builds. Thanks, Regina and Leo http://www.postgis.us > -Original Message- > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of > Bborie Park > Sent: Friday, February 03, 2012 7:51 PM > To: postgis-users@postgis.refractions.net > Subject: Re: [postgis-users] Rv: Postgis for Postgres 64 bits > > Carlos, > > There is no official 64-bit Windows build of PostGIS (any version > including 2.0) available due to build issues with mingw. You could > try building it yourself with Visual Studio but you might be on your > own. > > -bborie > > On 02/03/2012 11:45 AM, CARLOS MUÑOZ wrote: > > Great, > > > > For windows in 64 bits Its possible Postgis for Postgres? > > > > Thank you ;) > > > > > > Carlos J. Muñoz M. > > Student Master GIS > > > > > > - Mensaje reenviado - > > De: "pcr...@pcreso.com" > > Para: CARLOS MUÑOZ; PostGIS Users > > Discussion > > Enviado: Viernes, 3 de febrero, 2012 14:37:30 > > Asunto: Re: [postgis-users] Postgis for Postgres 64 bits > > > > > > > > For Linux, it has been available for some years. If you need 64bit > > support, you might consider upgrading :-) > > > > Cheers > > > > Brent Wood > > > > > > --- On Sat, 2/4/12, CARLOS MUÑOZ wrote: > > > > > >> From: CARLOS MUÑOZ > >> Subject: [postgis-users] Postgis for Postgres 64 bits > >> To: > >> > "postgis-users@postgis.refractions.net" >> ions.net> > >> Date: Saturday, February 4, 2012, 8:31 AM > >> > >> > >> hello, > >> > >> > >> When is Postgis for Postgres in 64 bits? > >> > >> > >> Thank you > >> > >> > >> Carlos J. Muñoz M. > >> > >> -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 > > -- > 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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Installation of PL/R: Updata
Dear Volkmar, I contacted the author (Joe Conway) of the PL/R package and he confirmed that the dll had not yet been compiled for PG9.1.2 - clearly being a decent chap he quickly produced a dll for me which I am glad to report works as intended (I think he will do a little more testing before releasing it into the wild) - sometime the open source community make me glad to be alive! :). Thumbs up also for Regina/Leo and the PostGIS in action book, Ch10 had me using PL/R in no time - so now I only have to learn postgresql, postgis and R - sounds like some fun days ahead. No doubt I'll be back... Darrel From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Volkmar Herbst Sent: 30 January 2012 07:18 To: 'PostGIS Users Discussion' Subject: Re: [postgis-users] Installation of PL/R HI, I did install R several times and run also often into this issue. Try the 32bit one and set the path C:\Program Files\R\R-2.13.1\bin\i386 in your environment. Create new variable R_HOME value->C:\Program Files\R\R-2.13.1 Make sure that plr.dll is in C:\Program Files (x86)\PostgreSQL\9.0\lib. Restart postgres after that. If you receive errors check if you did not install it already by using plr_environ() in postgres. These are the few things I can think of. May be you have already tried them all.. Volkmar Von: postgis-users-boun...@postgis.refractions.net<mailto:postgis-users-boun...@postgis.refractions.net> [mailto:postgis-users-boun...@postgis.refractions.net]<mailto:[mailto:postgis-users-boun...@postgis.refractions.net]> Im Auftrag von Darrel Maddy Gesendet: Samstag, 28. Januar 2012 18:45 An: postgis-users@postgis.refractions.net<mailto:postgis-users@postgis.refractions.net> Betreff: [postgis-users] Installation of PL/R Apologies if this is not the right mail list for this but my confusion arises because of my 32 bit PostGIS install! I have postgis2SVN sitting on top of postgresql 9.1 (32 bit) on a 64 bit version of windows (working fine). I installed R-2.14.1 and tried to follow the instructions given here http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut01 Now I tried both the 32bit and 64bit dll from http://www.joeconway.com/plr/ I ran the sql script supplied but get ERROR: could not load library "C:/Program Files (x86)/PostgreSQL/9.1/lib/plr.dll": The specified procedure could not be found. Needless to say plr.dll is in that location (I have tried both versions). Any suggestions would be welcome (and apologies if this is a dumb question). Thanks Darrel ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Installation of PL/R
Volkmar, Many thanks for trying to help. Unfortunately I already did all of those things. To make matters worse I just tried the procedure on my 32bit windows system in my office and I am getting the same issue. The dll is in the right place and the system variable and PATH are setup as per the instructions. I also checked that plr is not already installed. I have stopped and restarted the PG service (including complete reboots) but to no avail. Is this a problem with PG9.1? Either that or I am systematically doing something wrong :( . The download site gives me the plr-8.3.0.13 zip from a link saying Win32 plr.dll for Postgres 9.0.x but above it includes 9.1 in the section title. I will persevere as I would like to do analysis from within postgresql/postgis2 if at all possible. Best wishes Darrel From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Volkmar Herbst Sent: 30 January 2012 07:18 To: 'PostGIS Users Discussion' Subject: Re: [postgis-users] Installation of PL/R HI, I did install R several times and run also often into this issue. Try the 32bit one and set the path C:\Program Files\R\R-2.13.1\bin\i386 in your environment. Create new variable R_HOME value->C:\Program Files\R\R-2.13.1 Make sure that plr.dll is in C:\Program Files (x86)\PostgreSQL\9.0\lib. Restart postgres after that. If you receive errors check if you did not install it already by using plr_environ() in postgres. These are the few things I can think of. May be you have already tried them all.. Volkmar Von: postgis-users-boun...@postgis.refractions.net<mailto:postgis-users-boun...@postgis.refractions.net> [mailto:postgis-users-boun...@postgis.refractions.net]<mailto:[mailto:postgis-users-boun...@postgis.refractions.net]> Im Auftrag von Darrel Maddy Gesendet: Samstag, 28. Januar 2012 18:45 An: postgis-users@postgis.refractions.net<mailto:postgis-users@postgis.refractions.net> Betreff: [postgis-users] Installation of PL/R Apologies if this is not the right mail list for this but my confusion arises because of my 32 bit PostGIS install! I have postgis2SVN sitting on top of postgresql 9.1 (32 bit) on a 64 bit version of windows (working fine). I installed R-2.14.1 and tried to follow the instructions given here http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut01 Now I tried both the 32bit and 64bit dll from http://www.joeconway.com/plr/ I ran the sql script supplied but get ERROR: could not load library "C:/Program Files (x86)/PostgreSQL/9.1/lib/plr.dll": The specified procedure could not be found. Needless to say plr.dll is in that location (I have tried both versions). Any suggestions would be welcome (and apologies if this is a dumb question). Thanks Darrel ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Installation of PL/R
Apologies if this is not the right mail list for this but my confusion arises because of my 32 bit PostGIS install! I have postgis2SVN sitting on top of postgresql 9.1 (32 bit) on a 64 bit version of windows (working fine). I installed R-2.14.1 and tried to follow the instructions given here http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut01 Now I tried both the 32bit and 64bit dll from http://www.joeconway.com/plr/ I ran the sql script supplied but get ERROR: could not load library "C:/Program Files (x86)/PostgreSQL/9.1/lib/plr.dll": The specified procedure could not be found. Needless to say plr.dll is in that location (I have tried both versions). Any suggestions would be welcome (and apologies if this is a dumb question). Thanks Darrel ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Using raster2pgsql on a system withno postgresqlinstall
Dear Regina, Great. I'll take a look at the new binaries sometime later in the week. Darrel BTW I bought a copy of your excellent book - I will work my way through it over time, but for now Chapter 13 is essential reading! From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Paragon Corporation Sent: 08 January 2012 20:23 To: 'PostGIS Users Discussion' Subject: Re: [postgis-users] Using raster2pgsql on a system withno postgresqlinstall Darrel, BTW -- We put up newer windows binaries (just yesterday) which I think have some raster2pgsql fixes and also has the officially released geos3-3-2 (not the dev one anymore) I forgot about geos. I've been deliberating whether I should compile libgdal with/wo Geos dependency. I think the last batch I did - it may not be dependent on GEOS. Bborie / Jorge, does it make a difference anymore if I compile w/wout GEOS? e.g. are their some formats that need geos if I don't or other idiosyncracies. I think it used to. Thanks, Regina http://www.postgis.us From: postgis-users-boun...@postgis.refractions.net<mailto:postgis-users-boun...@postgis.refractions.net> [mailto:postgis-users-boun...@postgis.refractions.net]<mailto:[mailto:postgis-users-boun...@postgis.refractions.net]> On Behalf Of Darrel Maddy Sent: Sunday, January 08, 2012 3:07 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] Using raster2pgsql on a system withno postgresqlinstall Dear Regina, Many thanks for this - I did not look at the tutorial as it relates to 1.5 and I am using 2 (silly me). However, thankfully you did point me in the right direction, although your enhanced list was a little short. I started to systematically look at which files are needed by raster2pgsql and determined the following list is needed on the client machine (n.b. this also includes the necessary files to connect to a PG db). Here is the directory listing from my 'working' (remote system) directory. 02/12/2011 02:07 419,328 psql.exe 21/12/2011 10:23 771,642 raster2pgsql.exe 21/12/2011 10:23 212,992 ssleay32.dll 02/12/2011 02:07 180,736 pg_restore.exe 02/12/2011 02:07 373,248 pg_dump.exe 21/12/2011 10:23 794,226 shp2pgsql.exe 21/12/2011 10:2312,288 comerr32.dll 21/12/2011 10:23 634,880 krb5_32.dll 21/12/2011 10:2383,906 libintl-8.dll 02/12/2011 02:07 167,936 libpq.dll 21/12/2011 10:23 1,032,192 libeay32.dll 21/12/2011 10:23 9,622,528 libgdal-1.dll 21/12/2011 10:23 926,208 libiconv-2.dll 21/12/2011 10:23 451,584 libgeos_c-1.dll 21/12/2011 10:23 1,789,952 libgeos-3-3-2dev.dll 21/12/2011 10:23 100,352 zlib1.dll My batch file is not working as intended at present but I will resolve that later - the executables seem to all work fine on their own, so I think this is a complete list (but I guess only real use will tell). Best wishes and many thanks again for your help. Darrel From: postgis-users-boun...@postgis.refractions.net<mailto:postgis-users-boun...@postgis.refractions.net> [mailto:postgis-users-boun...@postgis.refractions.net]<mailto:[mailto:postgis-users-boun...@postgis.refractions.net]> On Behalf Of Paragon Corporation Sent: 08 January 2012 01:25 To: 'PostGIS Users Discussion' Subject: Re: [postgis-users] Using raster2pgsql on a system with no postgresqlinstall Darrel, This should work fine. I haven't tried it, but can't see why it would be any different from shp2pgsql. It should be pretty much the same story as shp2pgsql except you'll need to copy the libgdal-1.dll as well to the folder. I'm not sure if you have tried it before --- but here is a list of files as I recall you need for all the postgis executables to work. I'll update for 1.5. Way at the bottom of this tutorial we have listed the files you need to copy to the folder: http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut01 So add to that list libgdal-1.dll, raster2pgsql.exe Hope that helps, Regina http://www.postgis.us From: postgis-users-boun...@postgis.refractions.net<mailto:postgis-users-boun...@postgis.refractions.net> [mailto:postgis-users-boun...@postgis.refractions.net]<mailto:[mailto:postgis-users-boun...@postgis.refractions.net]> On Behalf Of Darrel Maddy Sent: Saturday, January 07, 2012 6:36 PM To: postgis-users@postgis.refractions.net<mailto:postgis-users@postgis.refractions.net> Subject: [postgis-users] Using raster2pgsql on a system with no postgresqlinstall Dear all, I may be asking to do the impossible here but here goes. We are about to deploy a landscape evolution model across a condor system (comprising ~1000 computers) in order t
Re: [postgis-users] Using raster2pgsql on a system with no postgresqlinstall
Dear Regina, Many thanks for this - I did not look at the tutorial as it relates to 1.5 and I am using 2 (silly me). However, thankfully you did point me in the right direction, although your enhanced list was a little short. I started to systematically look at which files are needed by raster2pgsql and determined the following list is needed on the client machine (n.b. this also includes the necessary files to connect to a PG db). Here is the directory listing from my 'working' (remote system) directory. 02/12/2011 02:07 419,328 psql.exe 21/12/2011 10:23 771,642 raster2pgsql.exe 21/12/2011 10:23 212,992 ssleay32.dll 02/12/2011 02:07 180,736 pg_restore.exe 02/12/2011 02:07 373,248 pg_dump.exe 21/12/2011 10:23 794,226 shp2pgsql.exe 21/12/2011 10:2312,288 comerr32.dll 21/12/2011 10:23 634,880 krb5_32.dll 21/12/2011 10:2383,906 libintl-8.dll 02/12/2011 02:07 167,936 libpq.dll 21/12/2011 10:23 1,032,192 libeay32.dll 21/12/2011 10:23 9,622,528 libgdal-1.dll 21/12/2011 10:23 926,208 libiconv-2.dll 21/12/2011 10:23 451,584 libgeos_c-1.dll 21/12/2011 10:23 1,789,952 libgeos-3-3-2dev.dll 21/12/2011 10:23 100,352 zlib1.dll My batch file is not working as intended at present but I will resolve that later - the executables seem to all work fine on their own, so I think this is a complete list (but I guess only real use will tell). Best wishes and many thanks again for your help. Darrel From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Paragon Corporation Sent: 08 January 2012 01:25 To: 'PostGIS Users Discussion' Subject: Re: [postgis-users] Using raster2pgsql on a system with no postgresqlinstall Darrel, This should work fine. I haven't tried it, but can't see why it would be any different from shp2pgsql. It should be pretty much the same story as shp2pgsql except you'll need to copy the libgdal-1.dll as well to the folder. I'm not sure if you have tried it before --- but here is a list of files as I recall you need for all the postgis executables to work. I'll update for 1.5. Way at the bottom of this tutorial we have listed the files you need to copy to the folder: http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut01 So add to that list libgdal-1.dll, raster2pgsql.exe Hope that helps, Regina http://www.postgis.us From: postgis-users-boun...@postgis.refractions.net<mailto:postgis-users-boun...@postgis.refractions.net> [mailto:postgis-users-boun...@postgis.refractions.net]<mailto:[mailto:postgis-users-boun...@postgis.refractions.net]> On Behalf Of Darrel Maddy Sent: Saturday, January 07, 2012 6:36 PM To: postgis-users@postgis.refractions.net<mailto:postgis-users@postgis.refractions.net> Subject: [postgis-users] Using raster2pgsql on a system with no postgresqlinstall Dear all, I may be asking to do the impossible here but here goes. We are about to deploy a landscape evolution model across a condor system (comprising ~1000 computers) in order to undertake sensitivity analysis. The output data will comprise hundreds of thousands of GTiff files (each with about 0.5M cells at this stage). My idea was to get the individual 'clients' (which run windows) to write output files directly into the db on our linux-based server. Now I just tried a quick check using a batch file that takes *.tif, uses raster2pgsql to create the sql file and issue the psql command to write the files into a database table. This works (of course) on my home windows-based server but each of the clients in the condor system will not have postgres installed. I created a directory on a windows laptop which does not have a PG install and included the executables I thought necessary and an appropriate batch file but, I guess not surprisingly, it did not work. Is there a way to do this without requiring additional software to be installed on the clients? or will I need to just upload files to a server directory and then run the command in batch mode directly on the server? I guess I am just looking to let the condor machines do more of the work. Alternatively is the source code for raster2psql available for direct integration into the model code? (sorry I am using the windows version to test postgis2). I guess I could also be missing an equally obvious alternative? Hope that makes sense. Darrel ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Using raster2pgsql on a system with no postgresql install
Dear all, I may be asking to do the impossible here but here goes. We are about to deploy a landscape evolution model across a condor system (comprising ~1000 computers) in order to undertake sensitivity analysis. The output data will comprise hundreds of thousands of GTiff files (each with about 0.5M cells at this stage). My idea was to get the individual 'clients' (which run windows) to write output files directly into the db on our linux-based server. Now I just tried a quick check using a batch file that takes *.tif, uses raster2pgsql to create the sql file and issue the psql command to write the files into a database table. This works (of course) on my home windows-based server but each of the clients in the condor system will not have postgres installed. I created a directory on a windows laptop which does not have a PG install and included the executables I thought necessary and an appropriate batch file but, I guess not surprisingly, it did not work. Is there a way to do this without requiring additional software to be installed on the clients? or will I need to just upload files to a server directory and then run the command in batch mode directly on the server? I guess I am just looking to let the condor machines do more of the work. Alternatively is the source code for raster2psql available for direct integration into the model code? (sorry I am using the windows version to test postgis2). I guess I could also be missing an equally obvious alternative? Hope that makes sense. Darrel ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Problem with Raster
Dear Bborie, Many thanks for your response. Date: Thu, 29 Dec 2011 11:51:13 -0800 From: Bborie Park mailto:bkp...@ucdavis.edu>> "What version of GDAL are you using? You'll want to be using what will be 1.9 (currently beta 2). But there is an additional question about whether or not GDAL's PostGIS Raster driver has been updated to the PostGIS Raster's current (and probably final) format for the raster_columns view." A quick check revealed I was using the commands built from GDAL1.8 which we use in our modelling programs to create GTiffs for matrix storage. I downloaded prebuilt binaries for 1.9 from http://www.gisinternals.com/sdk/PackageList.aspx?file=release-1600-x64-gdal-mapserver.zip . These include postgis raster support. Needless to say everything now works as intended. "You may be better off considering the use of ST_AsPNG, ST_AsJPEG and ST_AsTIFF. You should be able to glean the important bits needed from the following links A ASP.NET based viewer http://www.bostongis.com/blog/index.php?/archives/174-Minimalist-Web-based-ASP.NET-PostGIS-2.0-Spatial-GeometryRaster-Viewer.html A PHP based viewer http://www.bostongis.com/blog/index.php?/archives/175-Minimalist-Web-based-PHP-PostGIS-2.0-Spatial-GeometryRaster-Viewer.html " I will take a look at these when I have a little more time as it would be useful to be able to extract the images I need directly into a viewer (openJump/QGis and even Arc) for visualisation. I have not yet been able to construct a query for rasters in openjump (I am new to SQL also!), albeit I have had no problem with vector data. I'm sure I will have more questions before I am done! Best wishes Darrel Prof Darrel Maddy Chair of Quaternary Science Newcastle University UK ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Problem with Raster (2nd attempt)
Dear all, Apologies if this has been covered elsewhere but I am new to PostGIS and need to resolve this, hopefully simple issue. I am trying to get raster images into PostGIS (this is version 2 sitting on top of Postgres 9.1 under windows). I run the command: C:\tmp>raster2pgsql -I -C -r *.tif -F public.blockedtif >blocktif.sql Followed by: C:\tmp>psql -U postgres -d Testing -f blocktif.sql This writes the table (blockedtif) into the public schema of Testing and there is a rast column (although it appears empty, but the table size says otherwise - so I am assuming is normal?). When I issue the command: C:\tmp> gdalinfo "PG:host=localhost port 5432 dbname=Testing user=postgres password=*** schema=public table=blockedtif" I keep getting the error ERROR 1: Sorry, but table blockedtif doesn't seem to have regular blocking arrangement. As I am specifying the Constraint -r (which can be confirmed in the dbase as enforce_regular_blocking_rast) I do not understand this error. I have tried this with the Kauai.bill file used in the postgis book with the same error. I am not entirely certain what to do next as I need to be able to export the rasters from the dbase as well. Any suggestions would be appreciated. If I have been an idiot (entirely likely) do not be afraid to tell me - I have broad shoulders! Many Thanks Darrel ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
