[postgis-users] Raster data insertion in postgis database
Hi, I am using multiple threads to upload the raster tiles simultaneously in a single table in postgis database. 2500 rows should be created from first thread and 2450 rows from the second thread..giving a total of 4950 rows. What i am getting is around 3006 rows. I am wondering if it is possible to insert multiple rows simultaneously in a single table in postgis database. Please help Regards, Gautam ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Changing regional setting for a numeric field in a single database
Hi, on trying to import data from csv I am now having a problem with regional settings. At the moment it looks like this; db=#show client_encoding; UTF8 db=#show lc_numeric; de_DE.utf8 The problem is that I have getting this error; ERROR: invalid input syntax for type numeric: "2.000,000" I have defined this field as... numeric(11,4) and thus the problem is that the decimal and thousand separators are mixed up. Would anyone know how I can set this properly to this one database? Thanks for any help, Rob___ 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
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). And yes, I'd do the upgrade on Unix first. Once you go to windows you'll be alone in the dark ... --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
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] Raster data insertion in postgis database
Hello, I'm sorry but I don't understand your problem because I don't see what exactly is your data : - files you have to import to postgis using raster2psql ? (you may use -a option within an existing table and *.ext to import all of ones in one time) - different postgis tables with tiles for each one ? (you may use create a new table unioning your tables from two select * ) -something else ? Hugues. De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de gautam kumar Envoyé : jeudi 28 juin 2012 12:47 À : postgis-users@postgis.refractions.net Objet : [postgis-users] Raster data insertion in postgis database Hi, I am using multiple threads to upload the raster tiles simultaneously in a single table in postgis database. 2500 rows should be created from first thread and 2450 rows from the second thread..giving a total of 4950 rows. What i am getting is around 3006 rows. I am wondering if it is possible to insert multiple rows simultaneously in a single table in postgis database. Please help Regards, Gautam ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Unable to add the postgis 2.0 shapefile loader plug-in to postgres 8.4.12
Hi, I have installed postgis 2.0 over Postgres 8.4.12. However, I could not see the postgis shapefile loader plugin in the pgadmin III plugins. However, I can see it from the start menu. I checked the plugins.ini. It is consistent with what described at http://www.postgresonline.com/journal/archives/145-PgAdmin-III-Plug-in-Registration-PostGIS-Shapefile-and-DBF-Loader.html. For my installation "postgisgui\shp2pgsql-gui.exe" is actually located at "D:\PostgreSQL\8.4\bin" and PG bin path is rightfully specified at "D:\PostgreSQL\8.4\bin". Is there anything that would have gone wrong? Thanks. ___ 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
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. >> 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. 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. --Lee -- Lee Hachadoorian PhD, Earth & Environmental Sciences (Geography) Research Associate, CUNY Center for Urban Research http://freecity.commons.gc.cuny.edu/ ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Unable to add the postgis 2.0 shapefile loader plug-in to postgres 8.4.12
Problem solved. I was only looking at plugins in wrong location. Thanks. From: Melpati, Muni Sent: Thursday, June 28, 2012 11:06 AM To: 'postgis-users@postgis.refractions.net' Subject: Unable to add the postgis 2.0 shapefile loader plug-in to postgres 8.4.12 Hi, I have installed postgis 2.0 over Postgres 8.4.12. However, I could not see the postgis shapefile loader plugin in the pgadmin III plugins. However, I can see it from the start menu. I checked the plugins.ini. It is consistent with what described at http://www.postgresonline.com/journal/archives/145-PgAdmin-III-Plug-in-Registration-PostGIS-Shapefile-and-DBF-Loader.html. For my installation "postgisgui\shp2pgsql-gui.exe" is actually located at "D:\PostgreSQL\8.4\bin" and PG bin path is rightfully specified at "D:\PostgreSQL\8.4\bin". Is there anything that would have gone wrong? Thanks. ___ 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
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
[postgis-users] Unable to add the postgis 2.0 shapefile loader plug-in to postgres 8.4.12
This problem is solved. I was looking at wrong plugins.ini file. Thanks. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Problem with using query layer in ArcGIS 10
Hi I was querying posgis layers from ArcGIS 10. I found that if add postgis layer with huge number of records, I am having trouble with accessing its attributes. When I try to open the table I get "Could not load data from the data source. If you can correct the problem, press the refresh button to reload data. Possible problems can include bad network connection, invalid field, etc. The SQL statement was not a select statement. The operation is not supported by this implementation." I found similar problem somewhere in GIS forums where the user had to change the MAXBLOBSIZE value to -1 (unlimited) in the SDE SERVER CONFIG FILE. Is there similar fix to postgis/postgres? I appreciate your support. Thanks. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Create topologic layers from simple geometries
On 27 June 2012 23:51, celati laurent wrote: > Hello Mike, > I renaming my table "Land_cover" table to "land_cover". This fixed the object naming problem; your new issue is not related. > I have a new error message : > INSERT INTO nei_topo(nei, topo) > SELECT topology.toTopoGeom(the_geom, 'public', 1) > FROM land_cover > WHERE gid BETWEEN 1 and 8; > > ERREUR: INSERT a plus de colonnes cibles que d'expressions > LINE 1: INSERT INTO nei_topo(nei, topo) > Could you throw light for me? This is a basic SQL error saying that you want to insert data into two columns (nei, topo), but you are selecting three columns. Remove the last one, and it should work. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Create topologic layers from simple geometries
Actually -- - Original Message - > From: Mike Toews > To: PostGIS Users Discussion > Cc: > Sent: Thursday, June 28, 2012 2:53 PM > Subject: Re: [postgis-users] Create topologic layers from simple geometries > > On 27 June 2012 23:51, celati laurent > wrote: >> Hello Mike, >> I renaming my table "Land_cover" table to "land_cover". > > This fixed the object naming problem; your new issue is not related. > >> I have a new error message : >> INSERT INTO nei_topo(nei, topo) >> SELECT topology.toTopoGeom(the_geom, 'public', 1) >> FROM land_cover >> WHERE gid BETWEEN 1 and 8; >> >> ERREUR: INSERT a plus de colonnes cibles que d'expressions >> LINE 1: INSERT INTO nei_topo(nei, topo) >> Could you throw light for me? > > This is a basic SQL error saying that you want to insert data into two > columns (nei, topo), but you are selecting three columns. Remove the > last one, and it should work. > > -Mike I think the count goes the other way -- insert has two named columns (nei and topo) ... The select has one column -- output from topology.toTopoGeom Provide a value for nei (gid? some other value?) or remove it from the insert and let it default to whatever the nei_topo table says. HTH, Greg W. (not familiar w/ the topology tools so I hope I am not off base) ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
