[postgis-users] Error in raster import with WKTRaster
Dear users, I have a problem while trying to import a raster into PostGIS (2.0.0SVN), using WKTRaster (0.1.6d), on a Debian system. This is a raster exported from GRASS as GeoTiff, using a float32 format (this is a density map). I then used: mathieu@Caribou$ /usr/lib/postgresql/8.4/bin/gdal2raster.py -r RoadDensity_2002_2006_CV_UTM19_25m.tif -t roaddensity_2002_2006_cv_utm19_25m -s 26919 -k 50x50 -I > RoadDensity_2002_2006_CV_UTM19_25m.sql and finally to import into PostGIS, as the postgres user: postgres@Caribou:/home/mathieu$ psql -f RoadDensity_2002_2006_CV_UTM19_25m.sql geodb BEGIN psql:RoadDensity_2002_2006_CV_UTM19_25m.sql:2: NOTICE: CREATE TABLE créera des séquences implicites « roaddensity_2002_2006_cv_utm19_25m_rid_seq » pour la colonne serial « roaddensity_2002_2006_cv_utm19_25m.rid » psql:RoadDensity_2002_2006_CV_UTM19_25m.sql:2: NOTICE: CREATE TABLE / PRIMARY KEY créera un index implicite « roaddensity_2002_2006_cv_utm19_25m_pkey » pour la table « roaddensity_2002_2006_cv_utm19_25m » CREATE TABLE psql:RoadDensity_2002_2006_CV_UTM19_25m.sql:3: ERREUR: la colonne « nan » n'existe pas LIGNE 1 : ...','rast',26919, ARRAY['32BF'], false, true, ARRAY[nan], 25.0... ^ psql:RoadDensity_2002_2006_CV_UTM19_25m.sql:4: ERREUR: la transaction est annulée, les commandes sont ignorées jusqu'à la fin du bloc de la transaction psql:RoadDensity_2002_2006_CV_UTM19_25m.sql:5: ERREUR: la transaction est annulée, les commandes sont ignorées jusqu'à la fin du bloc de la transaction psql:RoadDensity_2002_2006_CV_UTM19_25m.sql:6: ERREUR: la transaction est annulée, les commandes sont ignorées jusqu'à la fin du bloc de la transaction ... And it continues for every line (or until I stop it). In the end, the table is not imported at all. (sorry if parts of the ouputs are in French: the first error message is simply: "the column "nan" does not exist", and the next ones says "the transaction is cancelled, all commands will be ignored until the end of the bloc of the transaction") Note that this is the exact same approach that I am generally using, without any problem for other maps (including density maps as float32). I am new to PostGIS and the SQL world, so it might be an easy one... Yet, I still have no idea what's wrong here. I welcome any pointer! All the best, Mathieu. -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Error in raster import with WKTRaster
Hi Jorge, Thx for the answer! Seems I did a nice mess with the install, because I did install it with the --with-raster configure option! I'll try to fix it as soon as possible (I'm not on the same computer right now, so that it'll have to wait a bit). Do you think it would be safer to reinstall everything, in order to have a fresh install? I will also check the NAs of the map (that's weird, too: there are no NAs on this map, but I guess they are internally set anyway). What puzzled me is that it worked for other maps with the same procedure... I'll let you know, thx again! Mathieu. Le 11/02/2011 05:15, Jorge Arévalo a écrit : Hi Mathieu, On Fri, Feb 11, 2011 at 1:20 AM, Mathieu Basille wrote: Dear users, I have a problem while trying to import a raster into PostGIS (2.0.0SVN), using WKTRaster (0.1.6d), on a Debian system. This is a raster exported from GRASS as GeoTiff, using a float32 format (this is a density map). I then used: mathieu@Caribou$ /usr/lib/postgresql/8.4/bin/gdal2raster.py -r RoadDensity_2002_2006_CV_UTM19_25m.tif -t roaddensity_2002_2006_cv_utm19_25m -s 26919 -k 50x50 -I> RoadDensity_2002_2006_CV_UTM19_25m.sql and finally to import into PostGIS, as the postgres user: postgres@Caribou:/home/mathieu$ psql -f RoadDensity_2002_2006_CV_UTM19_25m.sql geodb BEGIN psql:RoadDensity_2002_2006_CV_UTM19_25m.sql:2: NOTICE: CREATE TABLE créera des séquences implicites « roaddensity_2002_2006_cv_utm19_25m_rid_seq » pour la colonne serial « roaddensity_2002_2006_cv_utm19_25m.rid » psql:RoadDensity_2002_2006_CV_UTM19_25m.sql:2: NOTICE: CREATE TABLE / PRIMARY KEY créera un index implicite « roaddensity_2002_2006_cv_utm19_25m_pkey » pour la table « roaddensity_2002_2006_cv_utm19_25m » CREATE TABLE psql:RoadDensity_2002_2006_CV_UTM19_25m.sql:3: ERREUR: la colonne « nan » n'existe pas LIGNE 1 : ...','rast',26919, ARRAY['32BF'], false, true, ARRAY[nan], 25.0... ^ psql:RoadDensity_2002_2006_CV_UTM19_25m.sql:4: ERREUR: la transaction est annulée, les commandes sont ignorées jusqu'à la fin du bloc de la transaction psql:RoadDensity_2002_2006_CV_UTM19_25m.sql:5: ERREUR: la transaction est annulée, les commandes sont ignorées jusqu'à la fin du bloc de la transaction psql:RoadDensity_2002_2006_CV_UTM19_25m.sql:6: ERREUR: la transaction est annulée, les commandes sont ignorées jusqu'à la fin du bloc de la transaction ... And it continues for every line (or until I stop it). In the end, the table is not imported at all. (sorry if parts of the ouputs are in French: the first error message is simply: "the column "nan" does not exist", and the next ones says "the transaction is cancelled, all commands will be ignored until the end of the bloc of the transaction") Note that this is the exact same approach that I am generally using, without any problem for other maps (including density maps as float32). I am new to PostGIS and the SQL world, so it might be an easy one... Yet, I still have no idea what's wrong here. I welcome any pointer! All the best, Mathieu. -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users The error is caused because the NODATA value for the band is not a number. Probably, the GetNodata function for this band returns NULL, and this situation is not managed in the loader code. Let me check it and open a new ticket if needed. Anyway, you're using PostGIS 2.0SVN. With this version you don't need WKTRaster 0.1.6d separately. Actually, WKTRaster 0.1.6d is not longer mantained, and it probably will cause errors (like this) if you use it with PostGIS from trunk. The raster extension (now "PostGIS Raster" instead of WKTRaster) is included as official part of PostGIS. You can enable it by compiling PostGIS in this way: ./configure --with-raster make make install createdb createlang plpgsql psql -f postgis/postgis.sql -d psql -f spatial_ref_sys.sql -d psql -f raster/rt_pg/rtpostgis.sql -d Now, you can load raster data in with the new version of the loader script: raster2pgsql.py (in /raster/scripts/python). Same syntax than the older gdal2wktraster.py script. Further information at http://gis4free.wordpress.com/2010/10/01/wkt-raster-is-now-postgis-raster/ Best regards, -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'
Re: [postgis-users] Error in raster import with WKTRaster
I will do this. Thanks a lot for the advices! I'll let you know how this turns out (hopefully within the day) All the best, Mathieu. Le 11/02/2011 09:02, Jorge Arévalo a écrit : Hi Mathieu 2011/2/11 Mathieu Basille: Hi Jorge, Thx for the answer! Seems I did a nice mess with the install, because I did install it with the --with-raster configure option! I'll try to fix it as soon as possible (I'm not on the same computer right now, so that it'll have to wait a bit). Do you think it would be safer to reinstall everything, in order to have a fresh install? I think you should recompile PostGIS with PostGIS Raster again (first svn update) and then create a new and clean database and follow the instructions to postgis-raster-enable it ./configure --with-raster make make install createdb createlang plpgsql psql -f postgis/postgis.sql -d psql -f spatial_ref_sys.sql -d psql -f raster/rt_pg/rtpostgis.sql -d I will also check the NAs of the map (that's weird, too: there are no NAs on this map, but I guess they are internally set anyway). What puzzled me is that it worked for other maps with the same procedure... I'll let you know, thx again! Mathieu. Check the NODATA with gdalinfo, just in case. Best regards, -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Error in raster import with WKTRaster
Dear Jorge, The NoData value seems indeed at the origin of the problem: $ gdalinfo RoadDensity_2002_2006_CV_UTM19_25m.tif | grep NoData NoData Value=nan In my other density maps, there was no 'NoData value' (the same command returns nothing). It thus seems that I have some NAs in my map, which are not handled correctly. After his request, I sent the map to Pierre Racine who is going to have a look at it to find the problem. Thanks a lot, Mathieu. Le 2011-02-11 09:02, Jorge Arévalo a écrit : > Hi Mathieu > > 2011/2/11 Mathieu Basille : >> Hi Jorge, >> >> Thx for the answer! Seems I did a nice mess with the install, because I did >> install it with the --with-raster configure option! I'll try to fix it as >> soon as possible (I'm not on the same computer right now, so that it'll have >> to wait a bit). Do you think it would be safer to reinstall everything, in >> order to have a fresh install? >> > > I think you should recompile PostGIS with PostGIS Raster again (first > svn update) and then create a new and clean database and follow the > instructions to postgis-raster-enable it > > ./configure --with-raster > make > make install > createdb > createlang plpgsql > psql -f postgis/postgis.sql -d > psql -f spatial_ref_sys.sql -d > psql -f raster/rt_pg/rtpostgis.sql -d > >> I will also check the NAs of the map (that's weird, too: there are no NAs on >> this map, but I guess they are internally set anyway). What puzzled me is >> that it worked for other maps with the same procedure... >> >> I'll let you know, thx again! >> Mathieu. >> > > Check the NODATA with gdalinfo, just in case. > > Best regards, > -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Error in raster import with WKTRaster
Dear Jorge, Thx for this. Shall I do anything about it? I uploaded the problematic raster for Pierre here: http://ase-research.org/tmp/postgis/ Pierre, are you working on this? Sorry if I'm a bit confused, I'm not quite sure what's the best to do as I'm not familiar with the PostGIS Raster organisation... And I don't want to add more work for the people involved in PostGIS Raster than required. All the best, Mathieu. Le 12/02/2011 09:17, Jorge Arévalo a écrit : 2011/2/11 Mathieu Basille: Dear Jorge, The NoData value seems indeed at the origin of the problem: $ gdalinfo RoadDensity_2002_2006_CV_UTM19_25m.tif | grep NoData NoData Value=nan In my other density maps, there was no 'NoData value' (the same command returns nothing). It thus seems that I have some NAs in my map, which are not handled correctly. After his request, I sent the map to Pierre Racine who is going to have a look at it to find the problem. Thanks a lot, Mathieu. Hello, I've created a new ticket for this: http://trac.osgeo.org/postgis/ticket/828 Best regards, -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Error in raster import with WKTRaster
Dear Jorge, Seems to work for me. I'm not sure if I did it correctly, here is what I did: 1) download the raster2pgsql.py file at http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/python/raster2pgsql.py?rev=6815 2) place it in /usr/lib/postgresql/8.4/bin/ for convenience (this is where shp2pgsql lives) and create a link in /usr/bin/ 3) convert my GeoTiff again: raster2pgsql -r RoadDensity_2002_2006_CV_UTM19_25m.tif -t roaddensity_2002_2006_cv_utm19_25m -s 26919 -k 50x50 -I > RoadDensity_2002_2006_CV_UTM19_25m.sql I noticed that I had to use the -t option now (it was absent in my gdal2raster.py loader). 4) load it in the DB as usual, using the postgres user: psql -f RoadDensity_2002_2006_CV_UTM19_25m.sql geodb And it proceeded without any error or warnings. I haven't tested it thoroughly though, but it seems to work fine now! Thanks a lot for the correction! Mathieu. Le 2011-02-14 07:20, Jorge Arévalo a écrit : > 2011/2/14 Jorge Arévalo : >> 2011/2/12 Mathieu Basille : >>> Dear Jorge, >>> >>> Thx for this. Shall I do anything about it? I uploaded the problematic >>> raster for Pierre here: >>> >>> http://ase-research.org/tmp/postgis/ >>> >>> Pierre, are you working on this? >>> >>> Sorry if I'm a bit confused, I'm not quite sure what's the best to do as I'm >>> not familiar with the PostGIS Raster organisation... >>> And I don't want to add more work for the people involved in PostGIS Raster >>> than required. >>> >>> All the best, >>> Mathieu. >>> >>> >>> >> >> Hi Mathieu, >> >> I think is enough. The loader should know how to deal with that. >> >> About PostGIS Raster, basically there's a roadmap >> (http://trac.osgeo.org/postgis/wiki/WKTRaster/PlanningAndFunding) and >> a list of known bugs and new required features >> (http://trac.osgeo.org/postgis/report/17). >> >> So, if you found a new problem (like your case) or a lack of >> something, you can use the list to ask for it, and a new ticket will >> be created when needed (like your case, again). >> >> Apart from this, the only thing you need is patience :-) >> >> Thanks again for reporting this, and best regards, >> >> -- >> Jorge Arévalo >> Internet & Mobilty Division, DEIMOS >> jorge.arev...@deimos-space.com >> http://es.linkedin.com/in/jorgearevalo80 >> http://mobility.grupodeimos.com/ >> http://gis4free.wordpress.com >> http://geohash.org/ezjqgrgzz0g >> > > > Hello again, > > I think the loader problem is solved in r6815. Mathieu, could you > please update your working copy of PostGIS? You don't need to rebuild. > Just execute the loader again, and test the generated SQL, please. > > Best regards, > -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How do you use PostGIS Raster?
Dear Pierre, Here, we are using PostGIS to analyze large datasets combining rasters and vectors. Basically, we are looking at movement and habitat selection in a predator-prey system, using GPS-collar data over large areas. The major use of PostGIS Raster is for the intersection of prey steps (line segments or buffers around these segments) with different raster: Landsat (landcover type), slope, road density, relative probability of occurrence of the predator, etc. Note that we first tried to use ArcGIS for this step, without success, due to many bugs and the need to correct them by hand (which was largely too time-consuming). We also used PostGIS Raster to intersect predator locations with the Landsat map to estimate Resource Selection Functions (RSF) in order to build maps of relative probability of occurrence. In the end, information of the intersections were used in R to characterize movements of the preys on the landscape. Best, Mathieu Basille. Le 03/06/2011 08:28, Pierre Racine a écrit : Hi all, I'm preparing a presentation and I need some use cases for PostGIS Raster. I would be grateful if people already using the raster side of PostGIS would describe what they already do it. Bborie, Jorge, Regina, others? These uses cases are vital for the project. On my side we are using PostGIS raster to do raster/vector analysis over large datasets. Basically determining mean values for temperature (raster), elevation (raster) for fauna observations (point buffers). Converting everything to vector and using desktop solutions proved impracticable for datasets covering the extent of Canada. For those who did not try the raster extension yet, don't be shy to express your planned experiments or your expectations. Thanks all for your contribution to the project, Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS documentation license
Dear Regina, Did you consider the GNU Free Documentation License or the FreeBSD Documentation License? They might be more appropriate for a documentation compared to a CC licence (aimed for more general purposes). In particular, they both allow for derivative and/or commercial use. The GNU FDL would be more restrictive (with a 'share-alike' clause), and allows for 'invariant sections' which are sometimes useful in a software documentation if you want to be sure that specific parts remain unchanged. The FreeBSD DL is far more permissive and basically corresponds to an 'attribution' clause only. You can find more details on the web, e.g. on Wikipedia [1, 2]. All the best, Mathieu [1] http://en.wikipedia.org/wiki/GNU_Free_Documentation_License#Compatibility_with_Creative_Commons_licensing_terms [2] http://en.wikipedia.org/wiki/FreeBSD_Documentation_License Le 26/07/2011 15:03, Paragon Corporation a écrit : As several have pointed out the PostGIS documentation does not have a clearly defined license assigned to it. This is one of the items on our todo to finish off our OSGeo incubation process. Having it full under GPL like the rest of PostGIS is very unappealing to me. I would prefer it be under a fairly unrestricted license such as: Creative Common Attribution ShareAlike – something like this: http://creativecommons.org/licenses/by-sa/3.0/us/ And if people happen to make money off of adaptations, printed copies of it etc, more power to them. Do others have any issues, concerns, comments, preferences about this? I guess people who actually worked on the documentation should be the primary ones to decide. Thanks, Regina http://www.postgis.us <http://www.postgis.us/> ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How do you use PostGIS Raster?
Dear list, I am resurrecting this thread to honour an old promise to Pierre. This might be of interest for those interested in using PostGIS for the study of wildlife movements. First, a summary of our use of PostGIS: We are using PostGIS to analyze large datasets combining rasters and vectors. Basically, we are looking at movement and habitat selection in a predator-prey system, using GPS-collar data over large areas. The major use of PostGIS Raster is for the intersection of prey steps (line segments or buffers around these segments) with different raster: Landsat (landcover type), slope, road density, relative probability of occurrence of the predator, etc. Note that we first tried to use ArcGIS for this step, without success, due to many bugs and the need to correct them by hand (which was largely too time-consuming). We also used PostGIS Raster to intersect predator locations with the Landsat map to estimate Resource Selection Functions (RSF) in order to build maps of relative probability of occurrence. In the end, information of the intersections were used in R to characterize movements of the preys on the landscape. Second, some numbers: we generally work on rasters (LandSat, road density, DEM) of approximately 150e6 pixels (25m×25m), i.e. 12600×11900, and occasionally on a much larger raster at the scale of Québec (LandSat) of approximately 1700e6 (2×85) pixels, same resolution. We are intersecting these maps with a total of 850 000 GPS relocations of four species, i.e. slightly less line segments between each relocation (so-called steps). We generally contrast each step with 10 randomly generated steps, which means that we are working on hundreds thousand steps at a time (we generally process them by season/species). Here are some processing times on a Intel Xeon station (4 cores @2.67 GHz) running Debian Linux, with 12 GB of RAM: - Intersection of 1 000 000 steps (mean=200m) takes 7.3 h on the most complex rasters (i.e. rasters which vary a lot along the steps, hence increasing computation times). Speed seems fairly constant, with approximately 130-140 thousand steps per hour. - On simpler rasters (i.e. less variation along the steps), computation time can be up to 10 times higher (1.5e6 steps per hour). - Intersection of 57 000 buffers (i.e. polygons around points, radius = 300m) takes almost 1h to get the composition of the buffer. Hope this is useful! Best, Mathieu Basille. Le 09/06/2011 09:33, Pierre Racine a écrit : Thanks Mathieu, Could you provide us with some numbers? How many geometries do you intersect? What is the total area/length covered by those geometries? With what raster size? How long it takes? Which kind of machine are you using? I would really like to use your numbers in my presentation. Pierre -Original Message- From: Mathieu Basille [mailto:basi...@ase-research.org] Sent: Wednesday, June 08, 2011 10:09 PM To: PostGIS Users Discussion Cc: Pierre Racine; Marie-Claude Labbé Subject: Re: [postgis-users] How do you use PostGIS Raster? Dear Pierre, Here, we are using PostGIS to analyze large datasets combining rasters and vectors. Basically, we are looking at movement and habitat selection in a predator-prey system, using GPS-collar data over large areas. The major use of PostGIS Raster is for the intersection of prey steps (line segments or buffers around these segments) with different raster: Landsat (landcover type), slope, road density, relative probability of occurrence of the predator, etc. Note that we first tried to use ArcGIS for this step, without success, due to many bugs and the need to correct them by hand (which was largely too time-consuming). We also used PostGIS Raster to intersect predator locations with the Landsat map to estimate Resource Selection Functions (RSF) in order to build maps of relative probability of occurrence. In the end, information of the intersections were used in R to characterize movements of the preys on the landscape. Best, Mathieu Basille. Le 03/06/2011 08:28, Pierre Racine a écrit : Hi all, I'm preparing a presentation and I need some use cases for PostGIS Raster. I would be grateful if people already using the raster side of PostGIS would describe what they already do it. Bborie, Jorge, Regina, others? These uses cases are vital for the project. On my side we are using PostGIS raster to do raster/vector analysis over large datasets. Basically determining mean values for temperature (raster), elevation (raster) for fauna observations (point buffers). Converting everything to vector and using desktop solutions proved impracticable for datasets covering the extent of Canada. For those who did not try the raster extension yet, don't be shy to express your planned experiments or your expectations. Thanks all for your contribution to the project, Pierre ___ postgis-users mailing list pos
[postgis-users] How to keep raster_columns clean?
Dear PostGIS gurus, I just realized that the table 'raster_columns' was not updated with modifications on raster tables (e.g. removing a table, dropping a table, or changing the schema of a table). Is there any way to keep this table clean? I tried a VACUUM (as recommanded by pgAdmin itself), but it didn't seem to do the trick. Any hint? Maybe I did something wrong from the beginning? Mathieu Basille. -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How to keep raster_columns clean?
Dear Pierre, Actually, I just discovered recently the content of this table, and found it really interesting! I thus intended to use it as a convenient way to list and summarize the rasters we have in the DB (with an overview of CRS, pixel size, etc.). Given your answer, it doesn't seem to be such a good idea to rely on it yet! Best, Mathieu. Le 2011-09-15 16:48, Pierre Racine a écrit : > What do you use the raster_column table for? > > We might provide later function to 'clean' or 'synch' the raster_column table > with the database content. But we are not there yet. > > Pierre > >> -Original Message- >> From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- >> boun...@postgis.refractions.net] On Behalf Of Mathieu Basille >> Sent: Thursday, September 15, 2011 1:08 PM >> To: postgis-users@postgis.refractions.net >> Subject: [postgis-users] How to keep raster_columns clean? >> >> Dear PostGIS gurus, >> >> I just realized that the table 'raster_columns' was not updated with >> modifications on raster tables (e.g. removing a table, dropping a table, or >> changing the schema of a table). >> >> Is there any way to keep this table clean? I tried a VACUUM (as recommanded >> by pgAdmin itself), but it didn't seem to do the trick. >> >> Any hint? Maybe I did something wrong from the beginning? >> Mathieu Basille. >> >> >> -- >> >> ~$ whoami >> Mathieu Basille, Post-Doc >> >> ~$ locate >> Laboratoire d'Écologie Comportementale et de Conservation de la Faune >> + Centre d'Étude de la Forêt >> Département de Biologie >> Université Laval, Québec >> >> ~$ info >> http://ase-research.org/basille >> >> ~$ fortune >> ``If you can't win by reason, go for volume.'' >> Calvin, by Bill Watterson. >> ___ >> 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 -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] copying tables from one schema to another
You can use something along that line: CREATE SCHEMA new_schema; ALTER TABLE table_name SET SCHEMA new_schema; (either with pgdamin or with psql directly) I personally find it easier to set the schema during the importation step with shp2psql/raster2psql, e.g. using: raster2pgsql -r your_raster.tif -t data.your_raster -l 1 -k 64x64 -s 26919 -I -M -o your_raster.sql which will directly import 'your_raster' into the schema 'data'. I ran into some problems with indexes by altering schemas*, and I now tend to favour the direct import into the intended schema (and it's also quicker as it involves less code). Mathieu. * After moving a given table in a different schema, I couldn't import another table with the same name in the public schema because of the associated index, and I add to merely drop the table. There is probably a solution to this problem, but I couldn't find it and the drop was a quick and easy solution for me... Le 22/09/2011 08:16, Mr. Puneet Kishor a écrit : On Sep 22, 2011, at 6:29 AM, Robert Buckley wrote: Hi, As a follow-up to my earlier post (Re: [postgis-users] schemas and postgis data) I have another question. I had originally put all my data into the public schema. I want to move my geodata to the schema "data", so I just used to following command in the pgadmin3 sql editor create table data.new_table as (select * from public.old_table); Don't recreate the tables. Just alter the schema. Check the Pg docs for changing the schema. although this works, the schema is not copied..ie the primary key and other contraints are missing. So i have done this # first create new table and copy schema create table data.table_2(like table_1 including defaults including constraints including indexes); #then copy data into table_2 insert into data.table2(select * from public.table_1); Is there a better/quicker/safer way of doing this? possibly with pgadmin3?___ 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 -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Need help : Getting Started to PostGIS
You can have a look here for a complete install of PostGIS 2.0, with the ability to store/manage rasters: http://gis4free.wordpress.com/2011/03/10/how-to-install-and-configure-postgis-raster-on-windows/ Mathieu. Le 30/09/2011 03:02, Ben Madin a écrit : Wendi Check the book at this site. http://www.manning.com/obe/ or google postgisonline cheers Ben On 30/09/2011, at 2:08 PM, Wendi Adrian wrote: Hi everyone, I am newbie in using PostGIS database and want to build GIS database using PostGIS and PostgreSQL. Can anyone help me what step to build GIS database using PostGIS? I already downloaded installer. Please help me, Regards, Wendi ___ postgis-users mailing list postgis-users@postgis.refractions.net <mailto: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 -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Convert from character to timestamptz
Dear PostGIS users, I've been trying for hours with no result... My problem is that I import a simple shapefile (points) to PostGIS using shp2pgsql. In the attribute table, there is a 'date' column formatted as '2005-03-20 00:00:00 EST5EDT'. This column is then imported as a 'character' column into PostGIS. Now, I'd like to convert this column to a real 'timestamp with time zone'. I tried many things like: ALTER TABLE test ALTER COLUMN date SET DATA TYPE timestamptz; or SELECT TIMESTAMP WITH TIME ZONE 'date' FROM test; and all return an error, while e.g.: SELECT TIMESTAMP WITH TIME ZONE '2005-03-20 00:00:00 EST5EDT'; perfectly works... I also tried: SELECT to_timestamp(date, '-MM-DD HH24:MI:SS TZ') FROM test; which also returns an error stating that 'TZ'/'tz' are unsupported in 'to_date' (!). It works without the 'TZ', but I really need the timezone... I have to admit I'm now totally in the dark and feel I really miss something obvious. This might be a strict PostgreSQL question... In this case, I apologize, as I couldn't find my answers either after reading several times the doc on date/time for PostgreSQL... Thanks for any hint! All the best, Mathieu. -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Convert from character to timestamptz
Dear Sandro, You're right, I'm being too vague (in my defence, error messages are in French for me!). Let's try to be more specific. Attached is a simple shapefile (4 points). It imports correctly with: shp2pgsql -s 26919 -I test.shp test | psql -h localhost -d geodb -U caribou -q Now, if I try: SELECT TIMESTAMP WITH TIME ZONE 'date' FROM test; I receive the following error: ** Error ** ERREUR: syntaxe en entrée invalide pour le type timestamp with time zone : « date » [~ invalid syntax for the type timestamp with time zone] SQL state: 22007 Character: 33 With the command: ALTER TABLE test ALTER COLUMN date SET DATA TYPE timestamptz; the error is: ** Error ** ERREUR: la colonne « date » ne peut pas être convertie vers le type timestamp with time zone [~ column 'date' can't be converted to type type timestamp with time zone] SQL state: 42804 And if I try what you suggested: ALTER TABLE test ALTER COLUMN date TYPE timestamptz WITH date::timestamptz; the error is: ** Error ** ERREUR: erreur de syntaxe sur ou près de « WITH date » [~ syntax error on or near 'WITH date'] SQL state: 42601 Character: 61 Thanks again for your help! Mathieu. Le 30/09/2011 12:15, Sandro Santilli a écrit : On Fri, Sep 30, 2011 at 12:10:00PM -0400, Mathieu Basille wrote: Dear PostGIS users, I've been trying for hours with no result... My problem is that I import a simple shapefile (points) to PostGIS using shp2pgsql. In the attribute table, there is a 'date' column formatted as '2005-03-20 00:00:00 EST5EDT'. This column is then imported as a 'character' column into PostGIS. Now, I'd like to convert this column to a real 'timestamp with time zone'. I tried many things like: ALTER TABLE test ALTER COLUMN date SET DATA TYPE timestamptz; or SELECT TIMESTAMP WITH TIME ZONE 'date' FROM test; and all return an error You should report the error, when you get one. I can just "guess" that the second is invalid syntax as you're using the literal string 'date' rather than the identifier "date". And I can "guess" that the first is a syntax error (but I'm not sure). I'd write the first as: ALTER TABLE test ALTER COLUMN date TYPE timestamptz WITH date::timestamptz; Or something like that (but would really first lookup the reference). --strk; () Free GIS& Flash consultant/developer /\ http://strk.keybit.net/services.html ___________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. test.dbf Description: application/dbf test.shp Description: application/esri-shape test.shx Description: application/esri-shape ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Convert from character to timestamptz
Le 30/09/2011 17:49, Sandro Santilli a écrit : You're right, I'm being too vague (in my defence, error messages are in French for me!). See if exporting LANG=C helps there. Did you mean at the command line or in the SQL query window? I tried both with no success. I also tried to launch pgAdmin with 'LC_ALL=C pgadmin3', but error messages were still in French. What's weird is that the whole software runs in English! I was wrong about the syntax, but the idea is that you tell it how to do the conversion. Maybe it was USING instead of WITH. The definitive answer is in the PostgreSQL reference manual, under the SQL section, in the ALTER TABLE syntax. That's beautiful! I've seen this page previously, but couldn't figure out what was the correct command for my case. Now, with: ALTER TABLE test ALTER COLUMN date TYPE timestamptz USING date::timestamptz; I have now date as a 'timestamptz'! I checked, and the timezone is taken into account (winter times are GMT-5, summer times are GMT-4). I think I'm starting to understand the mistakes I made and the rationale behind this command. Sandro, thanks a lot! Mathieu. -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Problems adding postgis raster data to db
If I'm not mistaken, I would suggest you type 'q' to quit the message. The (END) is a sign that it is indeed a message... Try it, and you should see a lot of 'INSERT 0 1' appearing. Hope this helps, M Le 2011-10-04 11:07, Pierre Racine a écrit : > What if you tile the raster using the -k 100x100 -I options of raster2pgsql? > Same problem? > >> -Original Message- >> From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- >> boun...@postgis.refractions.net] On Behalf Of dan...@inwind.it >> Sent: Tuesday, October 04, 2011 7:30 AM >> To: postgis-users@postgis.refractions.net >> Subject: [postgis-users] Problems adding postgis raster data to db >> >> Hi everyone, >> i have problems executing the .sql file i created with raster2pgsql to my >> postgres9.0-postgis2.0 db. >> The creation of the .sql from a .tiff went trought without a problem, but >> when i >> try the simple command psql -U user -d db -f raster.sql all i receive is >> addrastercolumn >> >> -- >> -- >> -- >> -- >> --- >> public.tavag.rast srid:32633 pixel_types:{16BUI,16BUI,16BUI} out_db:false >> regular_blocking:true nodata_values:'{65535,65535,65535}' scale_x:'0.4999569' >> scale_y:'-0.4999569006' blocksize_x:'50' blocksize_y:'50' extent:POLYGON >> ((360718.287740393 5106518.39173093,360718.287740393 >> 5108308.73738983,364560.956473793 5108308.73738983,364560.956473793 >> 5106518.39173093,360718.287740393 5106518.39173093)) >> (1 row) >> >> (END) >> >> with the cursor flashing near the (END) i've tried waiting 30 and more >> minutes, but nothing. >> The .tiff file is 170mb and the .sql 390mb. >> >> Any ideas? >> >> Thank you, >> DC >> ___ >> 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 -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Problems adding postgis raster data to db
Short answer is: it only happens on Linux. I discussed it today with Pierre Racine, and he might have a look at it (differences between Windows and Linux versions) if he finds time for it. The main problem is that we cannot use raster2pgsql in a script, since it would require a manual intervention... Mathieu. Le 06/10/2011 03:15, dan...@inwind.it a écrit : Thank you very much you made my day, i was stuck in that point for about 2 days now. Why this isn't mentioned in none manual or guide whatever?!?!?!? Thank you again Daniele Messaggio originale Da: basi...@ase-research.org Data: 05/10/2011 17.58 A: Ogg: Re: [postgis-users] Problems adding postgis raster data to db If I'm not mistaken, I would suggest you type 'q' to quit the message. The (END) is a sign that it is indeed a message... Try it, and you should see a lot of 'INSERT 0 1' appearing. Hope this helps, M Le 2011-10-04 11:07, Pierre Racine a écrit : What if you tile the raster using the -k 100x100 -I options of raster2pgsql? Same problem? -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of dan...@inwind.it Sent: Tuesday, October 04, 2011 7:30 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] Problems adding postgis raster data to db Hi everyone, i have problems executing the .sql file i created with raster2pgsql to my postgres9.0-postgis2.0 db. The creation of the .sql from a .tiff went trought without a problem, but when i try the simple command psql -U user -d db -f raster.sql all i receive is addrastercolumn -- -- -- -- --- public.tavag.rast srid:32633 pixel_types:{16BUI,16BUI,16BUI} out_db:false regular_blocking:true nodata_values:'{65535,65535,65535}' scale_x:'0.4999569' scale_y:'-0.4999569006' blocksize_x:'50' blocksize_y:'50' extent: POLYGON ((360718.287740393 5106518.39173093,360718.287740393 5108308.73738983,364560.956473793 5108308.73738983,364560.956473793 5106518.39173093,360718.287740393 5106518.39173093)) (1 row) (END) with the cursor flashing near the (END) i've tried waiting 30 and more minutes, but nothing. The .tiff file is 170mb and the .sql 390mb. Any ideas? Thank you, DC ___ 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 -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ 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 -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Linux installation
Dear Steve, As a complement to what Paolo said: I don't know about other distros, and I'll talk only about Debian which I use on a daily basis. A fresh Debian install (stable or testing or even sid) is not up-to-date in terms of GIS packages. Examples: - QGIS: last release is 1.7; Debian only provides 1.4 in all flavours of Debian; - GDAL: last release is 1.8.1; Debian provides 1.6 for stable, 1.7 for testing, and 1.8 for experimental, which is hardly installable due to dependency troubles (but they are working on it). I don't mean that Debian is a bad choice... Especially since I'm using it as my primary platform! It is indeed very good for many reasons. But if you want cutting-edge GIS tools, you'll need to tweak Debian a bit. For example, you can find how to install most recent QGIS versions here: http://www.qgis.org/wiki/DownloadFr#Debian Back to PostGIS now. If you want the latest stable release, which is 1.5.3, a simple [sudo] aptitude install postgis under testing should do the trick. If you want to run stable (which is a good choice), you can use some apt-pinning to be able to install packages from testing, or you can use PostGIS 1.5.2 with the previous command line. If you're interested in the development version, i.e. PostGIS 2.0 with raster support, it'll be slightly more complicated to have a complete GIS platform (but not impossible I guess). For instance, you'll need to compile everything by hand from GDAL 1.8 to PostGIS 2.0 through QGIS... But in this case, there is no GIS-related reason to use Debian: compilation should be the same under any Linux distro, so just pick up the one you like (or the one for which you have people to help you around). Hope this helps, Mathieu. Le 11/11/2011 04:19, Paolo Cavallini a écrit : Il 10/11/2011 17:41, steve.tout...@inspq.qc.ca ha scritto: Hi, Sorry for cross-posting on several lists, but we are in the process of taking some important decisions, on the OS to use... For several years we have mapserver, gdal and Postgis, etc... running on a Windows server. We plan to migrate our GIS tools from the Windows server to SUSE Linux Enterprise Server 11. I heard bad comments on using SLES with mapserver, postgres/postgis, gdal, etc...Taht the installation of these tools was a pain compare to OpenSUSE or UBUNTU. Comments on using and maintaining GIS infrastructure with SLES 11 would be very appreciated. Hi all. I think having GIS packages ready and updated is the most important thing in choosing a Linux distro. Debian should be the best, as it is updated and wdely used and tested. All the best. -- Paolo Cavallini See:http://www.faunalia.it/pc This body part will be downloaded on demand. -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Convert from character to timestamptz
Le 01/10/2011 03:02, Sandro Santilli a écrit : > On Fri, Sep 30, 2011 at 09:10:27PM -0400, Mathieu Basille wrote: >> Le 30/09/2011 17:49, Sandro Santilli a écrit : >>>> You're right, I'm being too vague (in my defence, error messages are >>>> in French for me!). >>> >>> See if exporting LANG=C helps there. >> >> Did you mean at the command line or in the SQL query window? I tried >> both with no success. >> I also tried to launch pgAdmin with 'LC_ALL=C pgadmin3', but error >> messages were still in French. What's weird is that the whole >> software runs in English! > > You probably need that variable to be in the backend environment, as > that's where the messages originate from. But a user-driven language > support would make much more sense so maybe it _is_ possible to do, > seek the web about it, there might be a way to signal the desired > language from the frontend to the backend (some set variable thing) > and if not you could file a feature request for pgsql hackers :) For the record (after a long silence): I managed to get error messages in English after doing the following (under Debian Wheezy): 1) edit the postgresql.conf # nano /etc/postgresql/8.4/main/postgresql.conf Look at the 'CLIENT CONNECTION DEFAULTS' section, 'Locale and Formatting' subsection, and change the 'lc_messages' variable: lc_messages = 'en_US.UTF-8' 2) restart the PostgreSQL server: service postgresql restart Now messages are in English. Example: $ psql -h localhost -d geodb -U caribou -q -c "blabla" ERROR: syntax error at or near "blabla" LIGNE 1 : blabla ^ All the best, Mathieu. >> ALTER TABLE test >> ALTER COLUMN date TYPE timestamptz >> USING date::timestamptz; >> >> I have now date as a 'timestamptz'! > > Great ! > > --strk; > > () Free GIS & Flash consultant/developer > /\ http://strk.keybit.net/services.html > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Convert from character to timestamptz
Le 18/12/2011 22:56, Mathieu Basille a écrit : > Le 01/10/2011 03:02, Sandro Santilli a écrit : >> On Fri, Sep 30, 2011 at 09:10:27PM -0400, Mathieu Basille wrote: >>> Le 30/09/2011 17:49, Sandro Santilli a écrit : >>>>> You're right, I'm being too vague (in my defence, error messages are >>>>> in French for me!). >>>> >>>> See if exporting LANG=C helps there. >>> >>> Did you mean at the command line or in the SQL query window? I tried >>> both with no success. >>> I also tried to launch pgAdmin with 'LC_ALL=C pgadmin3', but error >>> messages were still in French. What's weird is that the whole >>> software runs in English! >> >> You probably need that variable to be in the backend environment, as >> that's where the messages originate from. But a user-driven language >> support would make much more sense so maybe it _is_ possible to do, >> seek the web about it, there might be a way to signal the desired >> language from the frontend to the backend (some set variable thing) >> and if not you could file a feature request for pgsql hackers :) > > For the record (after a long silence): I managed to get error messages in > English after doing the following (under Debian Wheezy): > > 1) edit the postgresql.conf > > # nano /etc/postgresql/8.4/main/postgresql.conf > > Look at the 'CLIENT CONNECTION DEFAULTS' section, 'Locale and Formatting' > subsection, and change the 'lc_messages' variable: > > lc_messages = 'en_US.UTF-8' Note, however, that 'en_US.UTF-8' has to be available first. Check it with: $ locale -a and install it if needed with: # dpkg-reconfigure locales (it will ask for the default locale for the system, which can be of course different from the one used by PostgreSQL) Mathieu. > 2) restart the PostgreSQL server: > > service postgresql restart > > Now messages are in English. Example: > > $ psql -h localhost -d geodb -U caribou -q -c "blabla" > ERROR: syntax error at or near "blabla" > LIGNE 1 : blabla > ^ > > All the best, > Mathieu. > > > >>> ALTER TABLE test >>> ALTER COLUMN date TYPE timestamptz >>> USING date::timestamptz; >>> >>> I have now date as a 'timestamptz'! >> >> Great ! >> >> --strk; >> >> () Free GIS & Flash consultant/developer >> /\ http://strk.keybit.net/services.html >> ___ >> postgis-users mailing list >> postgis-users@postgis.refractions.net >> http://postgis.refractions.net/mailman/listinfo/postgis-users > -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Make Check fails
It seems indeed to be related to your previous GEOS install. What command did you use to compile PostGIS? Is it possible that the path to geos-config is still linking to the previous install? You can override it by: '--with-geosconfig=/usr/bin/geos-config' (or the one related to your current install of GEOS) in the ./configure. Hope it helps. If not, please provide more details (OS, previous commands, etc.). Mathieu. Le 31/01/2012 09:33, Andreas Forø Tollefsen a écrit : Hi, I have installed GEOS 3.3.2 and GDAL 1.9. I previously had a GEOS SVN 3.4 installed, but no longer. Still i get this error when running make check. Any idea? /home/andreas/Downloads/postgis-2.0.0alpha3SVN/liblwgeom/cunit/.libs/lt-cu_testr: error while loading shared libraries: libgeos-3.4.0dev.so <http://libgeos-3.4.0dev.so>: cannot open shared object file: No such file or directory make[2]: *** [check] Error 127 make[2]: Leaving directory `/home/andreas/Downloads/postgis-2.0.0alpha3SVN/liblwgeom/cunit' make[1]: *** [check] Error 2 make[1]: Leaving directory `/home/andreas/Downloads/postgis-2.0.0alpha3SVN/liblwgeom' make: *** [check] Error 1 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Make Check fails
Le 31/01/2012 09:51, Andreas Forø Tollefsen a écrit : Hi Mathieu, Thanks. I was thinking the same. I downloaded the latest from trunk. ./autogen.sh ./configure --with-raster --with-gui Also tried --with-geosconfig and --with-gdalconfig manually with the same problem. Could it be worth a try to delete the postgis-2.0.so <http://postgis-2.0.so> file and reinstall postgis? I don't think it is related to postgis-2.0.so, but really to the mess with GEOS installs. Try to make sure that you're using the correct geos-config. If you're using a Linux OS, you can try: sudo updatedb locate geos-config See how many do you have. Mine (compiled by myself) is in '/usr/local/bin/geos-config'. You can check if it is the correct one with, e.g. less: less /usr/local/bin/geos-config It shows me somewhere that this is the 3.4.0dev version: --version) echo 3.4.0dev Then I can use: ./configure --with-raster --with-geosconfig=/usr/local/bin/geos-config Now you have to adjust it for your needs. How did you install GEOS 3.3.2? Did you use a package manager, or compile it manually? Only this will tell you which geos-config to use... Mathieu. Andreas 2012/1/31 Mathieu Basille mailto:basi...@ase-research.org>> It seems indeed to be related to your previous GEOS install. What command did you use to compile PostGIS? Is it possible that the path to geos-config is still linking to the previous install? You can override it by: '--with-geosconfig=/usr/bin/__geos-config' (or the one related to your current install of GEOS) in the ./configure. Hope it helps. If not, please provide more details (OS, previous commands, etc.). Mathieu. Le 31/01/2012 09:33, Andreas Forø Tollefsen a écrit : Hi, I have installed GEOS 3.3.2 and GDAL 1.9. I previously had a GEOS SVN 3.4 installed, but no longer. Still i get this error when running make check. Any idea? /home/andreas/Downloads/__postgis-2.0.0alpha3SVN/__liblwgeom/cunit/.libs/lt-cu___testr: error while loading shared libraries: libgeos-3.4.0dev.so <http://libgeos-3.4.0dev.so> <http://libgeos-3.4.0dev.so>: cannot open shared object file: No such file or directory make[2]: *** [check] Error 127 make[2]: Leaving directory `/home/andreas/Downloads/__postgis-2.0.0alpha3SVN/__liblwgeom/cunit' make[1]: *** [check] Error 2 make[1]: Leaving directory `/home/andreas/Downloads/__postgis-2.0.0alpha3SVN/__liblwgeom' make: *** [check] Error 1 _ postgis-users mailing list postgis-users@postgis.__refractions.net <mailto:postgis-users@postgis.refractions.net> http://postgis.refractions.__net/mailman/listinfo/postgis-__users <http://postgis.refractions.net/mailman/listinfo/postgis-users> -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/__basille <http://ase-research.org/basille> ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. _ postgis-users mailing list postgis-users@postgis.__refractions.net <mailto:postgis-users@postgis.refractions.net> http://postgis.refractions.__net/mailman/listinfo/postgis-__users <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 -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Make Check fails
I am not sure where the problem actually lies... Did you check, after the installation of GEOS-dev, that you actually had a libgeos-3.4.0dev.so file? sudo updatedb locate libgeos-3.4.0dev.so Check also for libgeos.so locate libgeos.so Mathieu. Le 31/01/2012 10:12, Andreas Forø Tollefsen a écrit : 2012/1/31 Mathieu Basille mailto:basi...@ase-research.org>> Le 31/01/2012 09:51, Andreas Forø Tollefsen a écrit : Hi Mathieu, Thanks. I was thinking the same. I downloaded the latest from trunk. ./autogen.sh ./configure --with-raster --with-gui Also tried --with-geosconfig and --with-gdalconfig manually with the same problem. Could it be worth a try to delete the postgis-2.0.so <http://postgis-2.0.so> <http://postgis-2.0.so> file and reinstall postgis? I don't think it is related to postgis-2.0.so <http://postgis-2.0.so>, but really to the mess with GEOS installs. Try to make sure that you're using the correct geos-config. If you're using a Linux OS, you can try: sudo updatedb locate geos-config See how many do you have. Mine (compiled by myself) is in '/usr/local/bin/geos-config'. You can check if it is the correct one with, e.g. less: less /usr/local/bin/geos-config It shows me somewhere that this is the 3.4.0dev version: --version) echo 3.4.0dev Then I can use: ./configure --with-raster --with-geosconfig=/usr/local/__bin/geos-config Now you have to adjust it for your needs. How did you install GEOS 3.3.2? Did you use a package manager, or compile it manually? Only this will tell you which geos-config to use... Mathieu. Hi Mathieu, I have compiled my geos myself using ./autogen.sh, ./configure, make, make install. Then i set the path to the geos-config manually when configuring postgis: ./configure --with-geosconfig=/usr/local/bin/geos-config --with raster I also tried by reinstalling the latest geos3.4.dev from trunk. However. I get the same error: Making check in liblwgeom make[1]: Entering directory `/home/andreas/Downloads/postgis-2.0.0alpha3SVN/liblwgeom' make -C cunit check make[2]: Entering directory `/home/andreas/Downloads/postgis-2.0.0alpha3SVN/liblwgeom/cunit' /bin/bash ../../libtool --mode=link gcc -g -O2 -Wall -Wmissing-prototypes -I/usr/local/include -DPOSTGIS_GEOS_VERSION=34 -o cu_tester cu_algorithm.o cu_print.o cu_misc.o cu_ptarray.o cu_geodetic.o cu_geos.o cu_measures.o cu_node.o cu_libgeom.o cu_split.o cu_surface.o cu_homogenize.o cu_out_wkt.o cu_out_wkb.o cu_out_gml.o cu_out_kml.o cu_out_geojson.o cu_out_svg.o cu_out_x3d.o cu_in_wkb.o cu_in_wkt.o cu_tester.o ../liblwgeom.la <http://liblwgeom.la> -lcunit libtool: link: gcc -g -O2 -Wall -Wmissing-prototypes -I/usr/local/include -DPOSTGIS_GEOS_VERSION=34 -o .libs/cu_tester cu_algorithm.o cu_print.o cu_misc.o cu_ptarray.o cu_geodetic.o cu_geos.o cu_measures.o cu_node.o cu_libgeom.o cu_split.o cu_surface.o cu_homogenize.o cu_out_wkt.o cu_out_wkb.o cu_out_gml.o cu_out_kml.o cu_out_geojson.o cu_out_svg.o cu_out_x3d.o cu_in_wkb.o cu_in_wkt.o cu_tester.o ../.libs/liblwgeom.so /usr/lib/libcunit.so #gcc -o cu_tester cu_algorithm.o cu_print.o cu_misc.o cu_ptarray.o cu_geodetic.o cu_geos.o cu_measures.o cu_node.o cu_libgeom.o cu_split.o cu_surface.o cu_homogenize.o cu_out_wkt.o cu_out_wkb.o cu_out_gml.o cu_out_kml.o cu_out_geojson.o cu_out_svg.o cu_out_x3d.o cu_in_wkb.o cu_in_wkt.o cu_tester.o ../.libs/liblwgeom.a -lm -lcunit -L/usr/local/lib -lgeos_c /home/andreas/Downloads/postgis-2.0.0alpha3SVN/liblwgeom/cunit/.libs/lt-cu_tester: error while loading shared libraries: libgeos-3.4.0dev.so <http://libgeos-3.4.0dev.so>: cannot open shared object file: No such file or directory make[2]: *** [check] Error 127 make[2]: Leaving directory `/home/andreas/Downloads/postgis-2.0.0alpha3SVN/liblwgeom/cunit' make[1]: *** [check] Error 2 make[1]: Leaving directory `/home/andreas/Downloads/postgis-2.0.0alpha3SVN/liblwgeom' make: *** [check] Error 1 Andreas 2012/1/31 Mathieu Basille mailto:basi...@ase-research.org> <mailto:basille@ase-research.__org <mailto:basi...@ase-research.org>>> It seems indeed to be related to your previous GEOS install. What command did you use to compile PostGIS? Is it possible that the path to geos-config is still linking to the previous install? You can override it by: '--with-geosconfig=/usr/bin/geos-config' (or the one related to your current install of GEOS) in the ./configure. Hope it helps. If not, please provide more details (OS, previous commands, etc.). Mathieu. Le 31/01/2012 09:33, Andreas Forø Tollefsen a écrit : Hi, I have
Re: [postgis-users] Make Check fails
Arf, I was just too late :) I remember a similar problem with GDAL (also compiled manually), for which the lib .so could not be found. You can first check the paths: /sbin/ldconfig -p | grep geos I have something like that: libgeos_c.so.1 (libc6) => /usr/local/lib/libgeos_c.so.1 libgeos_c.so (libc6) => /usr/local/lib/libgeos_c.so libgeos-3.4.0dev.so (libc6) => /usr/local/lib/libgeos-3.4.0dev.so If you don't have this, you can try (use sudo if needed): echo '/usr/local/lib' >> /etc/ld.so.conf ldconfig And check again. This should be enough for PostGIS to compile (at least it worked for me). Hope this helps, Mathieu. Le 31/01/2012 10:16, Andreas Forø Tollefsen a écrit : And the file exists: andreas@andreas-priogrid:~/Downloads/postgis-2.0.0alpha3SVN$ locate libgeos-3.4.0dev.so <http://libgeos-3.4.0dev.so> /home/andreas/Downloads/geos-svn/src/.libs/libgeos-3.4.0dev.so <http://libgeos-3.4.0dev.so> /usr/local/lib/libgeos-3.4.0dev.so <http://libgeos-3.4.0dev.so> 2012/1/31 Andreas Forø Tollefsen mailto:andrea...@gmail.com>> 2012/1/31 Mathieu Basille mailto:basi...@ase-research.org>> Le 31/01/2012 09:51, Andreas Forø Tollefsen a écrit : Hi Mathieu, Thanks. I was thinking the same. I downloaded the latest from trunk. ./autogen.sh ./configure --with-raster --with-gui Also tried --with-geosconfig and --with-gdalconfig manually with the same problem. Could it be worth a try to delete the postgis-2.0.so <http://postgis-2.0.so> <http://postgis-2.0.so> file and reinstall postgis? I don't think it is related to postgis-2.0.so <http://postgis-2.0.so>, but really to the mess with GEOS installs. Try to make sure that you're using the correct geos-config. If you're using a Linux OS, you can try: sudo updatedb locate geos-config See how many do you have. Mine (compiled by myself) is in '/usr/local/bin/geos-config'. You can check if it is the correct one with, e.g. less: less /usr/local/bin/geos-config It shows me somewhere that this is the 3.4.0dev version: --version) echo 3.4.0dev Then I can use: ./configure --with-raster --with-geosconfig=/usr/local/__bin/geos-config Now you have to adjust it for your needs. How did you install GEOS 3.3.2? Did you use a package manager, or compile it manually? Only this will tell you which geos-config to use... Mathieu. Hi Mathieu, I have compiled my geos myself using ./autogen.sh, ./configure, make, make install. Then i set the path to the geos-config manually when configuring postgis: ./configure --with-geosconfig=/usr/local/bin/geos-config --with raster I also tried by reinstalling the latest geos3.4.dev from trunk. However. I get the same error: Making check in liblwgeom make[1]: Entering directory `/home/andreas/Downloads/postgis-2.0.0alpha3SVN/liblwgeom' make -C cunit check make[2]: Entering directory `/home/andreas/Downloads/postgis-2.0.0alpha3SVN/liblwgeom/cunit' /bin/bash ../../libtool --mode=link gcc -g -O2 -Wall -Wmissing-prototypes -I/usr/local/include -DPOSTGIS_GEOS_VERSION=34 -o cu_tester cu_algorithm.o cu_print.o cu_misc.o cu_ptarray.o cu_geodetic.o cu_geos.o cu_measures.o cu_node.o cu_libgeom.o cu_split.o cu_surface.o cu_homogenize.o cu_out_wkt.o cu_out_wkb.o cu_out_gml.o cu_out_kml.o cu_out_geojson.o cu_out_svg.o cu_out_x3d.o cu_in_wkb.o cu_in_wkt.o cu_tester.o ../liblwgeom.la <http://liblwgeom.la> -lcunit libtool: link: gcc -g -O2 -Wall -Wmissing-prototypes -I/usr/local/include -DPOSTGIS_GEOS_VERSION=34 -o .libs/cu_tester cu_algorithm.o cu_print.o cu_misc.o cu_ptarray.o cu_geodetic.o cu_geos.o cu_measures.o cu_node.o cu_libgeom.o cu_split.o cu_surface.o cu_homogenize.o cu_out_wkt.o cu_out_wkb.o cu_out_gml.o cu_out_kml.o cu_out_geojson.o cu_out_svg.o cu_out_x3d.o cu_in_wkb.o cu_in_wkt.o cu_tester.o ../.libs/liblwgeom.so /usr/lib/libcunit.so #gcc -o cu_tester cu_algorithm.o cu_print.o cu_misc.o cu_ptarray.o cu_geodetic.o cu_geos.o cu_measures.o cu_node.o cu_libgeom.o cu_split.o cu_surface.o cu_homogenize.o cu_out_wkt.o cu_out_wkb.o cu_out_gml.o cu_out_kml.o cu_out_geojson.o cu_out_svg.o cu_out_x3d.o cu_in_wkb.o cu_in_wkt.o cu_tester.o ../.libs/liblwgeom.a -lm -lcunit -L/usr/local/lib -lgeos_c /home/andreas/Downloads/postgis-2.0.0alpha3SVN/liblwgeom/cunit/.libs/lt-cu_tester: error while loading shared libraries: libgeos-3.4.0dev.so <http://libgeos-3.4.0dev.so>: cannot open shared object file: No such file or directory make
Re: [postgis-users] Make Check fails
Le 31/01/2012 10:45, Andreas Forø Tollefsen a écrit : Thank you Mathieu! That did it. Could you explain what the commands excactly do, so i know next time? From the man page of ldconfig: "ldconfig creates, updates, and removes the necessary links and cache (for use by the run-time linker, ld.so) to the most recent shared libraries found in the directories specified on the command line, in the file /etc/ld.so.conf, and in the trusted directories (/usr/lib and /lib)." As far as I can tell, it is required to declare where the shared libraries (.so) are. And it seems that some systems (at least Debian for what I can tell) do not use /usr/local/lib, but just /usr/lib and /lib. The command: echo '/usr/local/lib' >> /etc/ld.so.conf just add '/usr/local/lib' at the end of the file /etc/ld.so.conf, so that the system can use /usr/local/lib as a path for the shared libraries. It is then re-processed by the call of ldconfig. Mathieu. Best, Andreas 2012/1/31 Mathieu Basille mailto:basi...@ase-research.org>> Arf, I was just too late :) I remember a similar problem with GDAL (also compiled manually), for which the lib .so could not be found. You can first check the paths: /sbin/ldconfig -p | grep geos I have something like that: libgeos_c.so.1 (libc6) => /usr/local/lib/libgeos_c.so.1 libgeos_c.so (libc6) => /usr/local/lib/libgeos_c.so libgeos-3.4.0dev.so <http://libgeos-3.4.0dev.so> (libc6) => /usr/local/lib/libgeos-3.4.__0dev.so <http://libgeos-3.4.0dev.so> If you don't have this, you can try (use sudo if needed): echo '/usr/local/lib' >> /etc/ld.so.conf ldconfig And check again. This should be enough for PostGIS to compile (at least it worked for me). Hope this helps, Mathieu. Le 31/01/2012 10:16, Andreas Forø Tollefsen a écrit : And the file exists: andreas@andreas-priogrid:~/__Downloads/postgis-2.0.__0alpha3SVN$ locate libgeos-3.4.0dev.so <http://libgeos-3.4.0dev.so> <http://libgeos-3.4.0dev.so> /home/andreas/Downloads/geos-__svn/src/.libs/libgeos-3.4.__0dev.so <http://libgeos-3.4.0dev.so> <http://libgeos-3.4.0dev.so> /usr/local/lib/libgeos-3.4.__0dev.so <http://libgeos-3.4.0dev.so> <http://libgeos-3.4.0dev.so> 2012/1/31 Andreas Forø Tollefsen mailto:andrea...@gmail.com> <mailto:andrea...@gmail.com <mailto:andrea...@gmail.com>>> 2012/1/31 Mathieu Basille mailto:basi...@ase-research.org> <mailto:basille@ase-research.__org <mailto:basi...@ase-research.org>>> Le 31/01/2012 09:51, Andreas Forø Tollefsen a écrit : Hi Mathieu, Thanks. I was thinking the same. I downloaded the latest from trunk. ./autogen.sh ./configure --with-raster --with-gui Also tried --with-geosconfig and --with-gdalconfig manually with the same problem. Could it be worth a try to delete the postgis-2.0.so <http://postgis-2.0.so> <http://postgis-2.0.so> <http://postgis-2.0.so> file and reinstall postgis? I don't think it is related to postgis-2.0.so <http://postgis-2.0.so> <http://postgis-2.0.so>, but really to the mess with GEOS installs. Try to make sure that you're using the correct geos-config. If you're using a Linux OS, you can try: sudo updatedb locate geos-config See how many do you have. Mine (compiled by myself) is in '/usr/local/bin/geos-config'. You can check if it is the correct one with, e.g. less: less /usr/local/bin/geos-config It shows me somewhere that this is the 3.4.0dev version: --version) echo 3.4.0dev Then I can use: ./configure --with-raster --with-geosconfig=/usr/local/bin/geos-config Now you have to adjust it for your needs. How did you install GEOS 3.3.2? Did you use a package manager, or compile it manually? Only this will tell you which geos-config to use... Mathieu. Hi Mathieu, I have compiled my geos myself using ./autogen.sh, ./configure, make, make install. Then i set the path to the geos-config manually when configuring postgis: ./configure --with-geosconfig=/usr/local/__bin/geos-config --with
Re: [postgis-users] Installing a fully functional PostGIS 2.0 on
1.1.1.jar commons-lang3-3.1/commons-lang3-3.1.jar jackcess-1.2.2.jar /usr/lib/jvm/java-6-openjdk/jre/lib/ext --install gdal-- cd /home/zoa3/gis/gdal ./configure \ --with-python --with-poppler \ --with-pg \ --with-curl \ --with-openjpeg --with-geos --with-geotiff \ --with-jpeg --with-png \ --with-expat --with-libkml --with-xerces-c \ --with-java=/usr/lib/jvm/java-1.6.0-openjdk/ --with-mdb \ --with-fgdb=/home/zoa3/gis/FileGDB_API make sudo make install sudo ldconfig ===Errors -Error1: Found when using open jdk 7 (installed with sudo apt-get install openjdk-7-jdk) ./configure \ --with-python --with-poppler \ --with-pg \ --with-curl \ --with-openjpeg --with-geos --with-geotiff \ --with-jpeg --with-png \ --with-expat --with-libkml --with-xerces-c \ --with-java=/usr/lib/jvm/java-1.7.0-openjdk-amd64 --with-mdb \ --with-fgdb=/home/zonability3/gis/FileGDB_API I receive the following messages: zoa3@ZB3:~$ gdalinfo --version gdalinfo: error while loading shared libraries: libjvm.so: cannot open shared object file: No such file or directory When I try to locate that file I get the following results: zoa3@ZB3:~$ locate libjvm.so /usr/lib/jvm/java-6-openjdk/jre/lib/amd64/cacao/libjvm.so /usr/lib/jvm/java-6-openjdk/jre/lib/amd64/jamvm/libjvm.so /usr/lib/jvm/java-6-openjdk/jre/lib/amd64/server/libjvm.so Error2 Using jdk 6 which was apparently preinstalled as I found out when I used locate in my previous try and fails on configure. ./configure \ --with-python --with-poppler \ --with-pg \ --with-curl \ --with-openjpeg --with-geos --with-geotiff \ --with-jpeg --with-png \ --with-expat --with-libkml --with-xerces-c \ --with-java=/usr/lib/jvm/java-1.6.0-openjdk/ --with-mdb \ --with-fgdb=/home/zoa3/gis/FileGDB_API checking for location of Python Makefiles... found checking where to install Python modules... /usr/lib64/python2.7/site-packages enabled checking for python setuptools... not found checking whether we should include Java support... configure: error: "Cannot find /usr/lib/jvm/java-1.6.0-openjdk//include directory." Error3:-- Trying to save myself the 11th reinstall of my entire system (as source gdal 1.9 has no uninstall functionality) I ran this without the java and mdb line. it configured properly but then gave me the following when I tried to test it: zoa3@ZB3:~/gis/gdal$ gdalinfo --version gdalinfo: error while loading shared libraries: libFileGDBAPI.so: cannot open shared object file: No such file or directory Please let me know if you have any idea what might be wrong! -- View this message in context: http://postgis.17.n6.nabble.com/Installing-a-fully-functional-PostGIS-2-0-on-Ubuntu-Linux-GEOS-GDAL-issues-tp3566227p4371098.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 -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Installing a fully functional PostGIS 2.0 on
For me, the problem lies in liFileGDBAPI, not GDAL directly. The GDAL libraries seems fine. Did you install FileGDB first? (can't tell much more since I don't know this library) Check: http://trac.osgeo.org/gdal/wiki/FileGDB Hope this help, Mathieu. Le 07/02/2012 11:34, THX1138 a écrit : I get exactly the same error, and it did find some files on the first try. zoa3@ZB3:~/gis/gdal$ locate libFileGDBAPI.so zoa3@ZB3:~/gis/gdal$ sudo locate libFileGDBAPI.so zoa3@ZB3:~/gis/gdal$ /sbin/ldconfig -p | grep gdal libgdal.so.1 (libc6,x86-64) => /usr/local/lib/libgdal.so.1 libgdal.so (libc6,x86-64) => /usr/local/lib/libgdal.so zoa3@ZB3:~/gis/gdal$ find /usr -name libgdal.so.1 /usr/local/lib/libgdal.so.1 root@ZB3:~# echo '/usr/local/lib'>> /etc/ld.so.conf root@ZB3:~# ldconfig root@ZB3:~# /sbin/ldconfig -p | grep gdal libgdal.so.1 (libc6,x86-64) => /usr/local/lib/libgdal.so.1 libgdal.so (libc6,x86-64) => /usr/local/lib/libgdal.so zoa3@ZB3:~$ gdalinfo --version gdalinfo: error while loading shared libraries: libFileGDBAPI.so: cannot open shared object file: No such file or directory -- View this message in context: http://postgis.17.n6.nabble.com/Installing-a-fully-functional-PostGIS-2-0-on-Ubuntu-Linux-GEOS-GDAL-issues-tp3566227p4373117.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 -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Installing raster in POSTGIS 2.0
Hi, Did you check the SVN documentation of PostGIS? http://postgis.refractions.net/documentation/manual-svn/postgis_installation.html I used it a while ago to install PostGIS Raster without any problem... Mathieu. Le 10/02/2012 00:27, nikhil a écrit : Hello, On Ubuntu 11.04 - the Natty Narwhal. I have tried installing various versions of POSTGIS(postgis-2.0.0alpha3SVN, postgis-2.0.0SVN, etc). but somehow the raster functions doesn't get installed. Please help. Please give me reliable link from where i can proceed step by step in installing POSTGIS 2.0 with raster capabilities. thanks Nikhil Morajkar Research Engineer, Geo-Spatial Information Science& Engineering (GISE), Advance Research Lab, Indian Institute of Technology, Bombay (IITB) -- View this message in context: http://postgis.17.n6.nabble.com/Installing-raster-in-POSTGIS-2-0-tp4378919p4382328.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 -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Installing raster in POSTGIS 2.0
Are these ERROR messages or simple NOTICE? It might be working despite the messages... Sorry I can't help more. I used it a couple of months ago without any problems, but I guess things changed since then (as I understand from the modification of file locations). It seems more a problem of PostGIS developers than users now. Please keep the list informed about your progress, as it would be very valuable information for people using Debian-based distributions. Best of luck, Mathieu. Le 10/02/2012 09:45, nikhil a écrit : Hi, Yes i have used the same tutorial.But when i execute the "psql -d yourdatabase -f postgis.sql" command. i get : spheroid does not exist. geometry does not exist. and many others. Furthermore if i try "psql -d yourdatabase -f spatial_ref_sys.sql" this command i get: spatial_ref_sys.sql table does not exist. Note: I am using "postgis-2.0.0alpha3SVN". The .sql files are not created at the locations specified by this tutorial. 1) my postgis.sql is at: /postgis/postgis.sql 2) my rtpostgis.sql is at: /raster/rt_pg/rtpostgis.sql similarly rest of the .sql files are also at other locations. I dont know where am i going wrong. Please mention the details about the dependencies if any. thanks for replying. nikhil morajkar IIT Bombay. -- View this message in context: http://postgis.17.n6.nabble.com/Installing-raster-in-POSTGIS-2-0-tp4378919p4383502.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 -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Problem in using Postgis 2.0.0b3 with raster
Here is a possibility (which is what I do): 1) for the sake of convenience, create symlinks in /usr/local/bin/ as a root: # ln -s /usr/lib/postgresql/8.4/bin/shp2pgsql /usr/local/bin/shp2pgsql # ln -s /usr/lib/postgresql/8.4/bin/raster2pgsql /usr/local/bin/raster2pgsql # ln -s /usr/lib/postgresql/8.4/bin/pgsql2shp /usr/local/bin/pgsql2shp It will allow you to call the 3 commands from any folder. 2) Create a new data base user, as postgres root: # su postgres # psql -d template1 -c "ALTER USER dbuser WITH PASSWORD 'dbpwd'" 3) If you want to be able to use the db without the need to type passwords (very handy to script things), you can create a file .pgpass in your home, with just this line: localhost:5432:*:dbuser:dbpwd And then give it the minimum permissions: chmod 600 .pgpass 4) Now test the connection, from any user: psql -h localhost -d yourdb -U dbuser -c 'SELECT current_date;' This should work! If you don't want to use step 3 (no password), just add a -W in the command, if I'm not mistaken. Hope this helps, Mathieu. Le 09/03/2012 08:34, aperi2007 a écrit : >Yes, raster2pgsql is in this folder. So i go through the terminal to >this folder and run something like that: raster2pgsql -s 4236 -I -C -M >*.tif -F -t 100x100 public.test > out.sql > >Before that, i go to the pgAdminIII, connect to the localhost and >create a new database "testing" with template "template_postgis". > > >How do i declare in the command line that the database that i want to >load the image is the new one i just created? > >What i get running the command below is: "out.sql: Permission denied". > >What is wrong with this process or what i forget to do? I will have the same trouble. After same tests I found that the creation and load of postgis rasters work only with the "postgres" account. Otherwise it return me the same your "permission denied". Andrea. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Problem in using Postgis 2.0.0b3 with raster
Well, I didn't check for most recent versions of PostGIS, but, as far as I can remember, I had to do it for postgis-2.0.0SVN-rev6066-20101013... You can still use the absolute path as well, I just find it more convenient to symlink these commands so that they work exactly as other shell commands. Mathieu. Le 09/03/2012 10:39, Pierre Racine a écrit : Is this something you have to do to make shp2pgsql as well or it is only necessary for raster2pgsql? Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Mathieu Basille Sent: Friday, March 09, 2012 8:49 AM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] Problem in using Postgis 2.0.0b3 with raster Here is a possibility (which is what I do): 1) for the sake of convenience, create symlinks in /usr/local/bin/ as a root: # ln -s /usr/lib/postgresql/8.4/bin/shp2pgsql /usr/local/bin/shp2pgsql # ln -s /usr/lib/postgresql/8.4/bin/raster2pgsql /usr/local/bin/raster2pgsql # ln -s /usr/lib/postgresql/8.4/bin/pgsql2shp /usr/local/bin/pgsql2shp It will allow you to call the 3 commands from any folder. 2) Create a new data base user, as postgres root: # su postgres # psql -d template1 -c "ALTER USER dbuser WITH PASSWORD 'dbpwd'" 3) If you want to be able to use the db without the need to type passwords (very handy to script things), you can create a file .pgpass in your home, with just this line: localhost:5432:*:dbuser:dbpwd And then give it the minimum permissions: chmod 600 .pgpass 4) Now test the connection, from any user: psql -h localhost -d yourdb -U dbuser -c 'SELECT current_date;' This should work! If you don't want to use step 3 (no password), just add a -W in the command, if I'm not mistaken. Hope this helps, Mathieu. Le 09/03/2012 08:34, aperi2007 a écrit : >Yes, raster2pgsql is in this folder. So i go through the terminal to this folder and run something like that: raster2pgsql -s 4236 -I -C -M>*.tif -F -t 100x100 public.test> out.sql> >Before that, i go to the pgAdminIII, connect to the localhost and>create a new database "testing" with template "template_postgis". > > >How do i declare in the command line that the database that i want to>load the image is the new one i just created? > >What i get running the command below is: "out.sql: Permission denied". > >What is wrong with this process or what i forget to do? I will have the same trouble. After same tests I found that the creation and load of postgis rasters work only with the "postgres" account. Otherwise it return me the same your "permission denied". Andrea. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ 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 -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ 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
This is huge news, and a great step for this incredibly powerful tool! I'm really excited to test it out! I'm sure it will have a great success in many fields. Congrats to the development team, and big thank to your parents too! ;) Mathieu. Le 03/04/2012 17:04, Paul Ramsey a écrit : 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 -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] From Debian to Windows + major upgrading of PostgreSQL and PostGIS
Dear list, I need to proceed with a sensitive (and unfortunate) migration of the computer which currently runs PostGIS from Debian to Windows. I figured out I could as well upgrade PostGIS and PostgreSQL in the process. Current versions are: - Debian Squeeze - PostgreSQL 8.4 - PostGIS 2.0 SVN r6066 from 13 October 2010 (long before the extension mechanism was implemented) Target versions thus are: - Windows 7 - PostgreSQL 9.1 - PostGIS 2.0.1 Did anyone already go through such a migration? From what I understand, I need to run a hard upgrade using pg_dump (or pg_dumpall) from PostgreSQL 9.1 first. I will then get a .sql file which contains the whole DB. I can then save it, install Windows 7 + PostgreSQL 9.1 + PostGIS 2.0.1, and use it again to restore the DB in the new PostgreSQL/PostGIS. The tricky part is that the hard drive will be totally erased in the process, so that I won't have the two installations in parallel (which would have allowed me to do some harmless tests). Is this safer then to first install PostgreSQL 9.1 and PostGIS 2.0.1 on the Debian system? I should be able to run the hard upgrade then, and thus safely check that everything is fine before wiping out the hard drive. The sequence would thus be: First on Debian: - Install PostgreSQL 9.1 - Back-up the DB using pg_dump/pg_dumpall from PostgreSQL 9.1 (and store it on an external hard drive) - Install PostGIS 2.0.1 - Create a new spatially enabled database - Do the hard upgrade using postgis_restore.pl (I'm not too sure about legacy.sql... Should I load it too?) - Check if the database loads and runs correctly - If it is successful, then back-up the DB again using pg_dump/pg_dumpall. And after the fresh install of Windows is ready: - Install PostgreSQL 9.1 - Install PostGIS 2.0.1 - Create a new spatially enabled database - Simply restore the DB using psql I found detailed explanations here for PostgreSQL [1] and PostGIS [2]. Is there anything I'm missing? Thank you for any comment on the procedure! Sincerely, Mathieu Basille. [1] http://www.postgresql.org/docs/9.1/static/upgrading.html [2] http://postgis.refractions.net/documentation/manual-2.0/postgis_installation.html#upgrading -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] From Debian to Windows + major upgrading of PostgreSQL and PostGIS
Le 28/06/2012 10:32, Sandro Santilli a écrit : On Thu, Jun 28, 2012 at 10:23:14AM -0400, Mathieu Basille wrote: Dear list, I need to proceed with a sensitive (and unfortunate) migration of the computer which currently runs PostGIS from Debian to Windows. I figured out I could as well upgrade PostGIS and PostgreSQL in the process. Current versions are: - Debian Squeeze - PostgreSQL 8.4 - PostGIS 2.0 SVN r6066 from 13 October 2010 (long before the extension mechanism was implemented) Target versions thus are: - Windows 7 - PostgreSQL 9.1 - PostGIS 2.0.1 Did anyone already go through such a migration? From what I understand, I need to run a hard upgrade using pg_dump (or pg_dumpall) from PostgreSQL 9.1 first. I will then get a .sql file which contains the whole DB. I can then save it, install Windows 7 + PostgreSQL 9.1 + PostGIS 2.0.1, and use it again to restore the DB in the new PostgreSQL/PostGIS. You'll need to invoke pg_dump using the -Fc (custom format) switch, which gives you not an SQL but an indexed dump, needed for skipping the parts you won't want to restore. postgis_restore.pl will take care of the restore (and skipping). Dear Sandro, Thanks for the additional information. I didn't understand the specifics of the -Fc switch (maybe something to add in the doc?). It seems I don't have to worry too much with the PostgreSQL/PostGIS upgrade. And yes, I'd do the upgrade on Unix first. Once you go to windows you'll be alone in the dark ... This is actually my main concern... I'm very familiar and happy with Debian, and not at all with Windows... Anyway, the migration is not my call! I will thus proceed with the upgrade first on Debian, and later switch to Windows. I'll let you know how it goes! Thanks, Mathieu --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.1 | / 2.1 |http://strk.keybit.net - http://vizzuality.com `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] From Debian to Windows + major upgrading of PostgreSQL and PostGIS
Le 28/06/2012 11:37, Lee Hachadoorian a écrit : On Thu, Jun 28, 2012 at 10:38 AM, Mathieu Basille wrote: Le 28/06/2012 10:32, Sandro Santilli a écrit : On Thu, Jun 28, 2012 at 10:23:14AM -0400, Mathieu Basille wrote: Dear list, I need to proceed with a sensitive (and unfortunate) migration of the computer which currently runs PostGIS from Debian to Windows. I You'll need to invoke pg_dump using the -Fc (custom format) switch, which gives you not an SQL but an indexed dump, needed for skipping the parts you won't want to restore. postgis_restore.pl will take care of the restore (and skipping). Dear Sandro, Thanks for the additional information. I didn't understand the specifics of the -Fc switch (maybe something to add in the doc?). It seems I don't have to worry too much with the PostgreSQL/PostGIS upgrade. Manthieu, -Fc specifies "custom format", which is basically Postgres' own dump format. It is smaller and faster to dump/restore than plain text. It also allows you at restore to selectively choose which tables or schemas you want to restore. But it can only restore to a Postgres database. Plain text, which builds the restore as SQL CREATE and INSERT statements, is slower, but can often be restored to non- Postgres databases. See http://www.postgresql.org/docs/8.4/static/app-pgdump.html. I see, thx for the explanation! For a PostGIS DB, I understand why this is the preferred format (there's not real advantage of restoring a PostGIS DB in a non-PostgreSQL DB...). I expected the PostGIS doc to mention it, but should have indeed looked at the PostgreSQL doc first! And yes, I'd do the upgrade on Unix first. Once you go to windows you'll be alone in the dark ... This is actually my main concern... I'm very familiar and happy with Debian, and not at all with Windows... Anyway, the migration is not my call! I will thus proceed with the upgrade first on Debian, and later switch to Windows. pg_dump is designed to restore between different Postgres platforms and from lower to higher versions. I have not done a Linux→Windows migration, but I have done a Windows→Linux migration, and there were no problems or incompatibilities in switching OSes. I don't see the point of restoring to a machine you are going to wipe anyway, and since the database structure and content won't have changed (even though the version has), I believe you will end up with exactly the same dump file. Dear Lee, I wonder whether the postgis_restore script would not alter the DB, especially since I still use an old PostGIS version where raster_columns was not yet a view... This is why it made sense to me to dump the whole DB again at the end of the process (to have a clean DB). But I might be wrong here. The main reason about starting the upgrade with Debian first is that I perfectly know how to restore the system (PostgreSQL/PostGIS + DB) in its current state with Debian. Which means that I can break it all, I will be able to come up with a functional system anyway (from a backup of the /main directory). I understand your point of view about switching OSes though. Still, I'm so much more familiar with Debian vs. Windows that I would rather lose some time in the upgrade process than running critical operations with Windows (i.e. upgrading PostGIS). If you wanted to be paranoid about it, rather than restoring to the machine you will wipe, maybe you have some other old piece of equipment running Debian (or that you can install Debian on) that you can restore to temporarily. Obviously, you wouldn't want to have thousands of users hitting it, but Postgres/PostGIS runs quite well on desktop-class hardware. This would also preserve access to your data while you are setting up Windows, in case something goes wrong or it takes longer than you expect. This is actually a good idea! I'm currently running Debian on my own laptop, which could serve as a basis for this upgrade (dump from the server, upgrade on my computer). I'll need to check memory requirements though (I might be short of space on my hard drive). I'll let you know how all of this work! Thanks again, Mathieu. --Lee -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] From Debian to Windows + major upgrading of PostgreSQL and PostGIS
Sorry for the late update, I was just too busy to report. As far as I can tell, the migration succeeded, and it went actually without any pain! Here is how I did with more details: 1) Backup with the version of PostGIS installed on the Debian workstation (SVN r6066 from 13 October 2010) using the command: pg_dump -h localhost -p 5432 -U [user] -Fc -b -v -N public -f "/home/postgres/db.bkp" [db] I explicitely removed the schema public for 2 reasons, first we used it as a temporary testing schema (i.e. none of the tables here were to be saved), and second I think it simplifies the migration since the old functions won't be saved either. 2) I then tested the backup on my own computer, to make sure I could use it. First list the tables: pg_restore -l db.bkp Then restore the DB: pg_restore -h localhost -p 5432 -U [user] -v -1 -d [db] db.bkp Success! 3) Format the workstation, install Windows with the last stable version of PostgreSQL + PostGIS, and finally restore the DB with the same command as above. Once again, everything was very smooth, except that I had to manually add raster constraints in raster_columns (I think that it appeared after the revision I was still using) using a command like: SELECT AddRasterConstraints('m'::name, 'raster'::name, 'rast'::name); (I did it for every raster using a very brutal approach based on the list of rasters given by 'SELECT * FROM raster_columns;' and edited in a text editor... There was probably a simpler SQL approach, but it did the trick) Finally, I ran a ANALYSE after the restore. And since then, everything seems perfect! Thanks a lot to all people who contributed to this success (this goes to the PostGIS dev as well as to Sandro and Lee who answered in this thread!). Mathieu. Le 28/06/2012 14:26, Lee Hachadoorian a écrit : On Thu, Jun 28, 2012 at 1:41 PM, Mathieu Basille wrote: I wonder whether the postgis_restore script would not alter the DB, especially since I still use an old PostGIS version where raster_columns was not yet a view... This is why it made sense to me to dump the whole DB again at the end of the process (to have a clean DB). But I might be wrong here. The main reason about starting the upgrade with Debian first is that I perfectly know how to restore the system (PostgreSQL/PostGIS + DB) in its current state with Debian. Which means that I can break it all, I will be able to come up with a functional system anyway (from a backup of the /main directory). I understand your point of view about switching OSes though. Still, I'm so much more familiar with Debian vs. Windows that I would rather lose some time in the upgrade process than running critical operations with Windows (i.e. upgrading PostGIS). Ah, so the idea is that you need to accommodate changes in how PostGIS stores things and feel more comfortable using Debian for that part of the process. Makes sense. Need to do that myself to move from 1.5 to 2.0. Still think it makes sense to do the restore to a separate Debian machine (which it sounds like you agree with) that can serve as an fallback if the Windows install is problematic. --Lee ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- ~$ whoami Mathieu Basille, Post-Doc ~$ locate Laboratoire d'Écologie Comportementale et de Conservation de la Faune + Centre d'Étude de la Forêt Département de Biologie Université Laval, Québec ~$ info http://ase-research.org/basille ~$ fortune ``If you can't win by reason, go for volume.'' Calvin, by Bill Watterson. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users