Hi Ketty, I found at my Debian Squeeze box the following SQL script, but I never launched it: /usr/share/postgresql/9.1/contrib/postgis-1.5/uninstall_postgis.sql
best regards, Humberto Cereser Ibanez Em Ter, 2013-06-25 às 15:33 +0300, Ketty Adoch escreveu: > Hi all, > > > How do i completely uninstall postgis-1.5.3 from ubuntu 12.04 on > postgresql-9.1 > > > Running sudo apt-get remove postgis and sudo apt-get purge postgis > does not uninstall postgis > > > Then checking for version of postgis "SELECT postgis_full_version()" > still returns postgis-1.5.3 as the running version. > > > Any pointers? > > > regards, > > > Ketty > > > Ketty Adoch > > Skype id: adockatie > > Twitter: www.twitter.com/kadoch > > > > > > On Mon, Jun 24, 2013 at 10:00 PM, > <postgis-users-requ...@lists.osgeo.org> wrote: > Send postgis-users mailing list submissions to > postgis-users@lists.osgeo.org > > To subscribe or unsubscribe via the World Wide Web, visit > > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > or, via email, send a message with subject or body 'help' to > postgis-users-requ...@lists.osgeo.org > > You can reach the person managing the list at > postgis-users-ow...@lists.osgeo.org > > When replying, please edit your Subject line so it is more > specific > than "Re: Contents of postgis-users digest..." > > > Today's Topics: > > 1. Re: Raster pixel count too high (Hugues Fran?ois) > 2. Re: Raster pixel count too high (Kim Bisgaard) > 3. Trying to restore a .backup file (Gunnar Oehmichen) > 4. Re: Trying to restore a .backup file (Humberto Cereser > Ibanez) > 5. Re: Raster pixel count too high (Andreas For? Tollefsen) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Sun, 23 Jun 2013 21:52:59 +0200 > From: Hugues Fran?ois <hugues.franc...@irstea.fr> > To: "PostGIS Users Discussion" <postgis-users@lists.osgeo.org> > Subject: Re: [postgis-users] Raster pixel count too high > Message-ID: > > <3cb901080554b04881d30f111f62d93002ce0...@nadia.grenoble.cemagref.fr> > Content-Type: text/plain; charset="iso-8859-1" > > Hello, > > I have a lot of work and I won't be able to test your data > now, but I'll try ASAP. > > On my side, I have to deal with rasters dem tiles which > overlap each other. I need to have unique tiles and I made the > two attached functions to achieve that : > 1. The first one, makegrid, simply builds a grid given a > bounding box geometry and height / width > 2. The second, unique_tiles, calls the first one to make a > whole raster made of regular tiles of the wanted size in > number of pixels from a raster table of aligned tiles (it > finds its bounding box and use it to makegrid from left bottom > corner using original pixel height and width and then use this > grid to clip raster) > > Maybe they can help you in your clipping process. You may > improve performance if you remove the st_union in unique_tiles > (needed if your raster is made of multiple tiles). > > Hugues. > > > > De : postgis-users-boun...@lists.osgeo.org > [mailto:postgis-users-boun...@lists.osgeo.org] De la part de > Andreas For? Tollefsen > Envoy? : dimanche 23 juin 2013 10:47 > ? : PostGIS Users Discussion > Objet : Re: [postgis-users] Raster pixel count too high > > > > Thanks for your answers. > > > > First to Hugues. I do not think they are perfectly aligned. > The raster we have imported seems to start just west of -180. > Hence, it is not within the limits of SRID 4326. We have to > modify it a little. > > What I do find strange is that ArcGIS counts 3600 which is the > expected pixel count. So why these two functions count so > differently on the exact same data is weird. > > Try yourself with one of the Nightlights data: > http://www.ngdc.noaa.gov/eog/dmsp/downloadV4composites.html > <http://www.ngdc.noaa.gov/eog/dmsp/downloadV4composites.html> > and our vector grid shapefile available here: > http://www.prio.no/Data/PRIO-GRID/ > <http://www.prio.no/Data/PRIO-GRID/> > > Kim: We do not directly use ST_Intersection in our script, but > ST_Clip to clip the raster according to our polygons, and only > where they ST_Intersects(), not ST_Intersection(); > > I still do not understand your suggestion. > > > > Best, > > Andreas > > 2013/6/18 Kim Bisgaard <k...@dmi.dk <mailto:k...@dmi.dk> > > > Hi, > > Because ST_Intersection() returns neighbour pixels sharing a > same value as only one > polygon. You thus get a bigger area and thus more points. > > Bit me once :-/ > > Regards, > Kim > > > > > > > > On 2013-06-18 14:59, Andreas For? Tollefsen wrote: > > Hi Kim, > > > > Thanks for your answer. However, we want raster as an > output, since we want to be able to use the summarystats > function. > > Please elaborate how you think ST_PixelAsPolygons > should solve out issue? > > Thanks. > > > > Andreas > > 2013/6/18 Kim Bisgaard <k...@dmi.dk <mailto:k...@dmi.dk> > > > > Hi, > > Try to use 'ST_PixelAsPolygons(ST_Clip(n.rast, > p.cell))' > instead of 'ST_Intersects(n.rast, p.cell)' > > Regards, > Kim > > > > On 2013-06-18 11:03, Andreas For? Tollefsen wrote: > > Hi, > > > > We are working on a raster summarystats script > to calculate various statistics for the pixels within fishnet > polygons. > > > > Our raster cell size is 0.0083333333333... x > 0.0083333333333... degrees while our quadrat polygons are 0.5 > x 0.5 decimal degrees. > > This should give us 60x60 raster pixels within > each of our polygons. ArcGIS zonal statistics returns a pixel > count of 3600 in addition to other statistics. > > However, PostGIS returns 3721 pixel count. > > > > We do not really understand why, but it seems > that our query includes some pixels that are outside of the > polygon, but still touches the vertices of the polygon and are > therefore included in the calculation. > > Are there any way of modifying our script to > return the same result as ArcGIS? > > Thanks! > > > > Andreas > > > > script: > > > > /* This query makes one raster for each > PRIO-GRID cell. Clip and union is the procedure. */ > > INSERT INTO nightlightsprio (gid, "year", > rast) > > (SELECT gid, "year", ST_Union(raster) as rast > > FROM > > (SELECT p.gid, n."year", ST_Clip(n.rast, > p.cell) as raster > > FROM nightlights n, priogridyear p > > WHERE ST_Intersects(n.rast, p.cell) > > AND n."year" = p."year" > > ) > > as priorast > > GROUP BY gid, "year"); > > > > > > /* Default BandNoDataValue is 0. Raster value > 0 means no light, not no data. Setting to NULL. This produces > correct results. */ > > UPDATE nightlightsprio2 SET rast = > ST_SetBandNoDataValue(rast, 1, NULL); > > > > > > ALTER TABLE nightlightsprio2 ADD COLUMN > nightlights_sum double precision, > > ADD COLUMN nightlights_mean double precision, > > ADD COLUMN nightlights_sd double precision, > > ADD COLUMN nightlights_min double precision, > > ADD COLUMN nightlights_max double precision, > > ADD COLUMN nightlights_count integer; > > > > UPDATE nightlightsprio2 SET nightlights_sum = > (ST_SummaryStats(rast)).sum; > > UPDATE nightlightsprio2 SET nightlights_mean = > (ST_SummaryStats(rast)).mean; > > UPDATE nightlightsprio2 SET nightlights_sd = > (ST_SummaryStats(rast)).stddev; > > UPDATE nightlightsprio2 SET nightlights_min = > (ST_SummaryStats(rast)).min; > > UPDATE nightlightsprio2 SET nightlights_max = > (ST_SummaryStats(rast)).max; > > UPDATE nightlightsprio2 SET nightlights_count > = (ST_SummaryStats(rast)).count; > > > > > > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > <mailto:postgis-users@lists.osgeo.org> > > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > <http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users> > > > > > > -- > Kim Bisgaard > > Application Development Division Phone: +45 3915 > 7562 <tel:%2B45%203915%207562> (direct) > Danish Meteorological Institute Fax: +45 3915 > 7460 <tel:%2B45%203915%207460> (division) > > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > <mailto:postgis-users@lists.osgeo.org> > > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > <http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users> > > > > > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > <mailto:postgis-users@lists.osgeo.org> > > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > <http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users> > > > > > > -- > Kim Bisgaard > > Application Development Division Phone: +45 3915 7562 > <tel:%2B45%203915%207562> (direct) > Danish Meteorological Institute Fax: +45 3915 7460 <tel:% > 2B45%203915%207460> (division) > > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > <mailto:postgis-users@lists.osgeo.org> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > <http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users> > > > > -------------- next part -------------- > A non-text attachment was scrubbed... > Name: function_makegrid.sql > Type: application/octet-stream > Size: 1282 bytes > Desc: function_makegrid.sql > URL: > > <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130623/d7bb420a/attachment-0002.obj> > -------------- next part -------------- > A non-text attachment was scrubbed... > Name: function_unique_tiles.sql > Type: application/octet-stream > Size: 3875 bytes > Desc: function_unique_tiles.sql > URL: > > <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130623/d7bb420a/attachment-0003.obj> > > ------------------------------ > > Message: 2 > Date: Mon, 24 Jun 2013 11:30:39 +0000 > From: Kim Bisgaard <k...@dmi.dk> > To: "postgis-users@lists.osgeo.org" > <postgis-users@lists.osgeo.org> > Subject: Re: [postgis-users] Raster pixel count too high > Message-ID: <51c82ddf.8070...@dmi.dk> > Content-Type: text/plain; charset="iso-8859-1" > > Hi, > > You are right, I mistook ST_intersects() for ST_intersection() > - me butting out of this thread.. > > Regards, > Kim > > > On 2013-06-23 10:46, Andreas For? Tollefsen wrote: > Thanks for your answers. > > > Kim: We do not directly use ST_Intersection in our script, but > ST_Clip to clip the raster according to our polygons, and only > where they ST_Intersects(), not ST_Intersection(); > I still do not understand your suggestion. > > Best, > Andreas > > 2013/6/18 Kim Bisgaard <k...@dmi.dk<mailto:k...@dmi.dk>> > Hi, > > Because ST_Intersection() returns neighbour pixels sharing a > same value as only one > polygon. You thus get a bigger area and thus more points. > > Bit me once :-/ > > Regards, > Kim > > > > > > On 2013-06-18 14:59, Andreas For? Tollefsen wrote: > Hi Kim, > > Thanks for your answer. However, we want raster as an output, > since we want to be able to use the summarystats function. > Please elaborate how you think ST_PixelAsPolygons should solve > out issue? > Thanks. > > Andreas > > 2013/6/18 Kim Bisgaard <k...@dmi.dk<mailto:k...@dmi.dk>> > Hi, > > Try to use 'ST_PixelAsPolygons(ST_Clip(n.rast, p.cell))' > instead of 'ST_Intersects(n.rast, p.cell)' > > Regards, > Kim > > > On 2013-06-18 11:03, Andreas For? Tollefsen wrote: > Hi, > > We are working on a raster summarystats script to calculate > various statistics for the pixels within fishnet polygons. > > Our raster cell size is 0.0083333333333... x > 0.0083333333333... degrees while our quadrat polygons are 0.5 > x 0.5 decimal degrees. > This should give us 60x60 raster pixels within each of our > polygons. ArcGIS zonal statistics returns a pixel count of > 3600 in addition to other statistics. > However, PostGIS returns 3721 pixel count. > > We do not really understand why, but it seems that our query > includes some pixels that are outside of the polygon, but > still touches the vertices of the polygon and are therefore > included in the calculation. > Are there any way of modifying our script to return the same > result as ArcGIS? > Thanks! > > Andreas > > script: > > /* This query makes one raster for each PRIO-GRID cell. Clip > and union is the procedure. */ > INSERT INTO nightlightsprio (gid, "year", rast) > (SELECT gid, "year", ST_Union(raster) as rast > FROM > (SELECT p.gid, n."year", ST_Clip(n.rast, p.cell) as raster > FROM nightlights n, priogridyear p > WHERE ST_Intersects(n.rast, p.cell) > AND n."year" = p."year" > ) > as priorast > GROUP BY gid, "year"); > > > /* Default BandNoDataValue is 0. Raster value 0 means no > light, not no data. Setting to NULL. This produces correct > results. */ > UPDATE nightlightsprio2 SET rast = ST_SetBandNoDataValue(rast, > 1, NULL); > > > ALTER TABLE nightlightsprio2 ADD COLUMN nightlights_sum double > precision, > ADD COLUMN nightlights_mean double precision, > ADD COLUMN nightlights_sd double precision, > ADD COLUMN nightlights_min double precision, > ADD COLUMN nightlights_max double precision, > ADD COLUMN nightlights_count integer; > > UPDATE nightlightsprio2 SET nightlights_sum = > (ST_SummaryStats(rast)).sum; > UPDATE nightlightsprio2 SET nightlights_mean = > (ST_SummaryStats(rast)).mean; > UPDATE nightlightsprio2 SET nightlights_sd = > (ST_SummaryStats(rast)).stddev; > UPDATE nightlightsprio2 SET nightlights_min = > (ST_SummaryStats(rast)).min; > UPDATE nightlightsprio2 SET nightlights_max = > (ST_SummaryStats(rast)).max; > UPDATE nightlightsprio2 SET nightlights_count = > (ST_SummaryStats(rast)).count; > > > > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > > > > -- > Kim Bisgaard > > Application Development Division Phone: +45 3915 7562<tel: > %2B45%203915%207562> (direct) > Danish Meteorological Institute Fax: +45 3915 7460<tel:% > 2B45%203915%207460> (division) > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > > > > > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > > > > -- > Kim Bisgaard > > Application Development Division Phone: +45 3915 7562<tel: > %2B45%203915%207562> (direct) > Danish Meteorological Institute Fax: +45 3915 7460<tel:% > 2B45%203915%207460> (division) > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > > > > > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > > > > -- > Kim Bisgaard > > Application Development Division Phone: +45 3915 7562 > (direct) > Danish Meteorological Institute Fax: +45 3915 7460 > (division) > -------------- next part -------------- > An HTML attachment was scrubbed... > URL: > > <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130624/87c77d58/attachment-0001.html> > > ------------------------------ > > Message: 3 > Date: Mon, 24 Jun 2013 17:05:32 +0200 > From: Gunnar Oehmichen <oehm8...@uni-landau.de> > To: postgis-users@lists.osgeo.org > Subject: [postgis-users] Trying to restore a .backup file > Message-ID: <51c8603c.5080...@uni-landau.de> > Content-Type: text/plain; charset=ISO-8859-15; format=flowed > > Dear Participants, > > As part of my thesis (environmental sciences) I recently > started working > with PostgreSQL (newbie here). The georeferenced database has > been > transferred from a local Windows7 desktop machine to me. > > I am using Ubuntu 12.04 64 bit with Xubuntu as a > Desktop-environment > > Postgres Version 9.1.9, Postgis 2.0.1.2, PGAdminIII as GUI. > > To restore the .backup file I built a new Database "MI" in the > GUI > without using any template from the definitions tab: > > /usr/bin/pg_restore --host localhost --port 5432 --username > "postgres" > --dbname "MI" --no-password --verbose > > "/home/gunnaroeh/Dokumente/Laenderdaten/Database/LaenderStand_02-20.backup" > pg_restore: connecting to database for restore > pg_restore: creating TABLE mzb > pg_restore: creating TABLE samplsites_mzb > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 200; 1259 > 51728 TABLE > samplsites_mzb postgres > pg_restore: [archiver (db)] could not execute query: ERROR: > type > "geometry" does not exist > LINE 14: east_north geometry(Point,31467) > ^ > Command was: CREATE TABLE samplsites_mzb ( > land character varying(50), > samplsite_id integer NOT NULL, > samplsite character var... > pg_restore: [archiver (db)] could not execute query: ERROR: > relation > "public.samplsites_mzb" does not exist > Command was: ALTER TABLE public.samplsites_mzb OWNER TO > postgres; > > > pg_restore: creating TABLE samplsites_phch > pg_restore: restoring data for table "mzb" > pg_restore: restoring data for table "samplsites_mzb" > pg_restore: [archiver (db)] Error from TOC entry 3147; 0 51728 > TABLE > DATA samplsites_mzb postgres > pg_restore: [archiver (db)] could not execute query: ERROR: > relation > "samplsites_mzb" does not exist > Command was: COPY samplsites_mzb (land, samplsite_id, > samplsite, > samplsite_name, rechtswert, hochwert, easting, northing, > latitude, longi... > pg_restore: restoring data for table "samplsites_phch" > pg_restore: creating CONSTRAINT id > pg_restore: [archiver (db)] Error from TOC entry 3145; 2606 > 59403 > CONSTRAINT id postgres > pg_restore: [archiver (db)] could not execute query: ERROR: > relation > "samplsites_mzb" does not exist > Command was: ALTER TABLE ONLY samplsites_mzb > ADD CONSTRAINT id PRIMARY KEY (samplsite_id); > > > > pg_restore: setting owner and privileges for TABLE mzb > pg_restore: setting owner and privileges for TABLE > samplsites_mzb > pg_restore: setting owner and privileges for TABLE > samplsites_phch > pg_restore: setting owner and privileges for TABLE DATA mzb > pg_restore: setting owner and privileges for TABLE DATA > samplsites_mzb > pg_restore: setting owner and privileges for TABLE DATA > samplsites_phch > pg_restore: setting owner and privileges for CONSTRAINT id > WARNING: errors ignored on restore: 4 > > Prozess beendete mit Exitcode 1. > > If I add the extensions "postgis" and "postgis_topology" to > the new db > "MI" at least the following lines are missing from the > messages: > > pg_restore: [archiver (db)] could not execute query: ERROR: > type > "geometry" does not exist. > > Also noteworthy: > > postgres=# SELECT PostGIS_version(); > ERROR: function postgis_version() does not exist > LINE 1: SELECT PostGIS_version(); > ^ > HINT: No function matches the given name and argument types. > You might > need to add explicit type casts. > > By the way, the template postgis is not available in the > definitions-tab > using the GUI. > > I am also posting to this list as I do not know, wether the > problem is > postgis related or not, > > Thank you very much, > > Gunnar > > > ------------------------------ > > Message: 4 > Date: Mon, 24 Jun 2013 14:10:14 -0300 > From: Humberto Cereser Ibanez > <humbe...@pastoraldacrianca.org.br> > To: PostGIS Users Discussion <postgis-users@lists.osgeo.org> > Subject: Re: [postgis-users] Trying to restore a .backup file > Message-ID: <1372093814.2842.35.camel@p210> > Content-Type: text/plain; charset="UTF-8" > > Dear Gunnar, > > I suggest to you recreate your MI database, add spatial > extensions and > restore your backup: > 1) drop MI database (dropdb) and create it from scratch > (createdb) > 2) execute the steps that are showed in "2.6 Creating a > spatial database > using EXTENSIONS" at > > http://postgis.net/docs/manual-2.0/postgis_installation.html#create_new_db_extensions > , and > 3) run your pg_restore > Best regards, > > Humberto Cereser Ibanez > > Em Seg, 2013-06-24 ?s 17:05 +0200, Gunnar Oehmichen escreveu: > > Dear Participants, > > > > As part of my thesis (environmental sciences) I recently > started working > > with PostgreSQL (newbie here). The georeferenced database > has been > > transferred from a local Windows7 desktop machine to me. > > > > I am using Ubuntu 12.04 64 bit with Xubuntu as a > Desktop-environment > > > > Postgres Version 9.1.9, Postgis 2.0.1.2, PGAdminIII as GUI. > > > > To restore the .backup file I built a new Database "MI" in > the GUI > > without using any template from the definitions tab: > > > > /usr/bin/pg_restore --host localhost --port 5432 --username > "postgres" > > --dbname "MI" --no-password --verbose > > > > "/home/gunnaroeh/Dokumente/Laenderdaten/Database/LaenderStand_02-20.backup" > > pg_restore: connecting to database for restore > > pg_restore: creating TABLE mzb > > pg_restore: creating TABLE samplsites_mzb > > pg_restore: [archiver (db)] Error while PROCESSING TOC: > > pg_restore: [archiver (db)] Error from TOC entry 200; 1259 > 51728 TABLE > > samplsites_mzb postgres > > pg_restore: [archiver (db)] could not execute query: ERROR: > type > > "geometry" does not exist > > LINE 14: east_north geometry(Point,31467) > > ^ > > Command was: CREATE TABLE samplsites_mzb ( > > land character varying(50), > > samplsite_id integer NOT NULL, > > samplsite character var... > > pg_restore: [archiver (db)] could not execute query: ERROR: > relation > > "public.samplsites_mzb" does not exist > > Command was: ALTER TABLE public.samplsites_mzb OWNER TO > postgres; > > > > > > pg_restore: creating TABLE samplsites_phch > > pg_restore: restoring data for table "mzb" > > pg_restore: restoring data for table "samplsites_mzb" > > pg_restore: [archiver (db)] Error from TOC entry 3147; 0 > 51728 TABLE > > DATA samplsites_mzb postgres > > pg_restore: [archiver (db)] could not execute query: ERROR: > relation > > "samplsites_mzb" does not exist > > Command was: COPY samplsites_mzb (land, samplsite_id, > samplsite, > > samplsite_name, rechtswert, hochwert, easting, northing, > latitude, longi... > > pg_restore: restoring data for table "samplsites_phch" > > pg_restore: creating CONSTRAINT id > > pg_restore: [archiver (db)] Error from TOC entry 3145; 2606 > 59403 > > CONSTRAINT id postgres > > pg_restore: [archiver (db)] could not execute query: ERROR: > relation > > "samplsites_mzb" does not exist > > Command was: ALTER TABLE ONLY samplsites_mzb > > ADD CONSTRAINT id PRIMARY KEY (samplsite_id); > > > > > > > > pg_restore: setting owner and privileges for TABLE mzb > > pg_restore: setting owner and privileges for TABLE > samplsites_mzb > > pg_restore: setting owner and privileges for TABLE > samplsites_phch > > pg_restore: setting owner and privileges for TABLE DATA mzb > > pg_restore: setting owner and privileges for TABLE DATA > samplsites_mzb > > pg_restore: setting owner and privileges for TABLE DATA > samplsites_phch > > pg_restore: setting owner and privileges for CONSTRAINT id > > WARNING: errors ignored on restore: 4 > > > > Prozess beendete mit Exitcode 1. > > > > If I add the extensions "postgis" and "postgis_topology" to > the new db > > "MI" at least the following lines are missing from the > messages: > > > > pg_restore: [archiver (db)] could not execute query: ERROR: > type > > "geometry" does not exist. > > > > Also noteworthy: > > > > postgres=# SELECT PostGIS_version(); > > ERROR: function postgis_version() does not exist > > LINE 1: SELECT PostGIS_version(); > > ^ > > HINT: No function matches the given name and argument > types. You might > > need to add explicit type casts. > > > > By the way, the template postgis is not available in the > definitions-tab > > using the GUI. > > > > I am also posting to this list as I do not know, wether the > problem is > > postgis related or not, > > > > Thank you very much, > > > > Gunnar > > _______________________________________________ > > postgis-users mailing list > > postgis-users@lists.osgeo.org > > > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > > > > > ------------------------------ > > Message: 5 > Date: Mon, 24 Jun 2013 20:18:46 +0200 > From: Andreas For? Tollefsen <andrea...@gmail.com> > To: PostGIS Users Discussion <postgis-users@lists.osgeo.org> > Subject: Re: [postgis-users] Raster pixel count too high > Message-ID: > <CAGMz7DkGgc6-t_-LSX > +mc6cviub2hqnqp0bzgoahxagqbei...@mail.gmail.com> > Content-Type: text/plain; charset="iso-8859-1" > > Ok. I was worried there was something genious about your > suggestion that I > could not grasp :) > > Andreas > > 2013/6/24 Kim Bisgaard <k...@dmi.dk> > > > Hi, > > > > You are right, I mistook ST_intersects() for > ST_intersection() - me > > butting out of this thread.. > > > > Regards, > > Kim > > > > > > On 2013-06-23 10:46, Andreas For? Tollefsen wrote: > > > > Thanks for your answers. > > > > > > Kim: We do not directly use ST_Intersection in our script, > but ST_Clip to > > clip the raster according to our polygons, and only where > they > > ST_Intersects(), not ST_Intersection(); > > I still do not understand your suggestion. > > > > Best, > > Andreas > > > > 2013/6/18 Kim Bisgaard <k...@dmi.dk> > > > >> Hi, > >> > >> Because ST_Intersection() returns neighbour pixels sharing > a same value > >> as only one > >> polygon. You thus get a bigger area and thus more points. > >> > >> Bit me once :-/ > >> > >> Regards, > >> Kim > >> > >> > >> > >> > >> > >> On 2013-06-18 14:59, Andreas For? Tollefsen wrote: > >> > >> Hi Kim, > >> > >> Thanks for your answer. However, we want raster as an > output, since we > >> want to be able to use the summarystats function. > >> Please elaborate how you think ST_PixelAsPolygons should > solve out issue? > >> Thanks. > >> > >> Andreas > >> > >> 2013/6/18 Kim Bisgaard <k...@dmi.dk> > >> > >>> Hi, > >>> > >>> Try to use 'ST_PixelAsPolygons(ST_Clip(n.rast, p.cell))' > >>> instead of 'ST_Intersects(n.rast, p.cell)' > >>> > >>> Regards, > >>> Kim > >>> > >>> > >>> On 2013-06-18 11:03, Andreas For? Tollefsen wrote: > >>> > >>> Hi, > >>> > >>> We are working on a raster summarystats script to > calculate various > >>> statistics for the pixels within fishnet polygons. > >>> > >>> Our raster cell size is 0.0083333333333... x > 0.0083333333333... > >>> degrees while our quadrat polygons are 0.5 x 0.5 decimal > degrees. > >>> This should give us 60x60 raster pixels within each of our > polygons. > >>> ArcGIS zonal statistics returns a pixel count of 3600 in > addition to other > >>> statistics. > >>> However, PostGIS returns 3721 pixel count. > >>> > >>> We do not really understand why, but it seems that our > query includes > >>> some pixels that are outside of the polygon, but still > touches the vertices > >>> of the polygon and are therefore included in the > calculation. > >>> Are there any way of modifying our script to return the > same result as > >>> ArcGIS? > >>> Thanks! > >>> > >>> Andreas > >>> > >>> script: > >>> > >>> /* This query makes one raster for each PRIO-GRID cell. > Clip and union > >>> is the procedure. */ > >>> INSERT INTO nightlightsprio (gid, "year", rast) > >>> (SELECT gid, "year", ST_Union(raster) as rast > >>> FROM > >>> (SELECT p.gid, n."year", ST_Clip(n.rast, p.cell) as raster > >>> FROM nightlights n, priogridyear p > >>> WHERE ST_Intersects(n.rast, p.cell) > >>> AND n."year" = p."year" > >>> ) > >>> as priorast > >>> GROUP BY gid, "year"); > >>> > >>> > >>> /* Default BandNoDataValue is 0. Raster value 0 means no > light, not no > >>> data. Setting to NULL. This produces correct results. */ > >>> UPDATE nightlightsprio2 SET rast = > ST_SetBandNoDataValue(rast, 1, NULL); > >>> > >>> > >>> ALTER TABLE nightlightsprio2 ADD COLUMN nightlights_sum > double > >>> precision, > >>> ADD COLUMN nightlights_mean double precision, > >>> ADD COLUMN nightlights_sd double precision, > >>> ADD COLUMN nightlights_min double precision, > >>> ADD COLUMN nightlights_max double precision, > >>> ADD COLUMN nightlights_count integer; > >>> > >>> UPDATE nightlightsprio2 SET nightlights_sum = > >>> (ST_SummaryStats(rast)).sum; > >>> UPDATE nightlightsprio2 SET nightlights_mean = > >>> (ST_SummaryStats(rast)).mean; > >>> UPDATE nightlightsprio2 SET nightlights_sd = > >>> (ST_SummaryStats(rast)).stddev; > >>> UPDATE nightlightsprio2 SET nightlights_min = > >>> (ST_SummaryStats(rast)).min; > >>> UPDATE nightlightsprio2 SET nightlights_max = > >>> (ST_SummaryStats(rast)).max; > >>> UPDATE nightlightsprio2 SET nightlights_count = > >>> (ST_SummaryStats(rast)).count; > >>> > >>> > >>> > >>> _______________________________________________ > >>> postgis-users mailing > > listpostgis-users@lists.osgeo.orghttp://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > >>> > >>> > >>> -- > >>> Kim Bisgaard > >>> > >>> Application Development Division Phone: +45 3915 7562 > (direct) > >>> Danish Meteorological Institute Fax: +45 3915 7460 > (division) > >>> > >>> > >>> _______________________________________________ > >>> postgis-users mailing list > >>> postgis-users@lists.osgeo.org > >>> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > >>> > >>> > >> > >> > >> _______________________________________________ > >> postgis-users mailing > > listpostgis-users@lists.osgeo.orghttp://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > >> > >> > >> -- > >> Kim Bisgaard > >> > >> Application Development Division Phone: +45 3915 7562 > (direct) > >> Danish Meteorological Institute Fax: +45 3915 7460 > (division) > >> > >> > >> _______________________________________________ > >> postgis-users mailing list > >> postgis-users@lists.osgeo.org > >> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > >> > >> > > > > > > _______________________________________________ > > postgis-users mailing > > listpostgis-users@lists.osgeo.orghttp://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > > > > > > -- > > Kim Bisgaard > > > > Application Development Division Phone: +45 3915 7562 > (direct) > > Danish Meteorological Institute Fax: +45 3915 7460 > (division) > > > > > > _______________________________________________ > > postgis-users mailing list > > postgis-users@lists.osgeo.org > > > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > > > > > -------------- next part -------------- > An HTML attachment was scrubbed... > URL: > > <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130624/3ef358ba/attachment-0001.html> > > ------------------------------ > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > > > End of postgis-users Digest, Vol 136, Issue 18 > ********************************************** > > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users