[postgis-users] postgis-users Digest, Vol 136, Issue 18
Ketty Adoch
kettya298 at gmail.com
Tue Jun 25 05:33:22 PDT 2013
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-request at lists.osgeo.org>wrote:
> Send postgis-users mailing list submissions to
> postgis-users at 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-request at lists.osgeo.org
>
> You can reach the person managing the list at
> postgis-users-owner at 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.francois at irstea.fr>
> To: "PostGIS Users Discussion" <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] Raster pixel count too high
> Message-ID:
> <
> 3CB901080554B04881D30F111F62D93002CE0A1A at 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-bounces at lists.osgeo.org [mailto:
> postgis-users-bounces at 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 <kib at dmi.dk <mailto:kib at 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 <kib at dmi.dk <mailto:kib at 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 at lists.osgeo.org <mailto:
> postgis-users at 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 at lists.osgeo.org <mailto:
> postgis-users at 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 at lists.osgeo.org <mailto:
> postgis-users at 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 at lists.osgeo.org <mailto:postgis-users at 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 <kib at dmi.dk>
> To: "postgis-users at lists.osgeo.org" <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] Raster pixel count too high
> Message-ID: <51C82DDF.8070007 at 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 <kib at dmi.dk<mailto:kib at 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 <kib at dmi.dk<mailto:kib at 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 at lists.osgeo.org<mailto:postgis-users at 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 at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org<mailto:postgis-users at 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 at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org<mailto:postgis-users at 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 <oehm8895 at uni-landau.de>
> To: postgis-users at lists.osgeo.org
> Subject: [postgis-users] Trying to restore a .backup file
> Message-ID: <51C8603C.5080101 at 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 <humberto at pastoraldacrianca.org.br>
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] Trying to restore a .backup file
> Message-ID: <1372093814.2842.35.camel at 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 at 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 <andreasft at gmail.com>
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] Raster pixel count too high
> Message-ID:
> <
> CAGMz7DkGgc6-t_-LSX+mC6cviUb2HQnqP0bZGOahXAgqBeiSKw at 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 <kib at 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 <kib at 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 <kib at 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 at 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 at lists.osgeo.org
> >>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> >>>
> >>>
> >>
> >>
> >> _______________________________________________
> >> postgis-users mailing listpostgis-users at 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 at lists.osgeo.org
> >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> >>
> >>
> >
> >
> > _______________________________________________
> > postgis-users mailing listpostgis-users at 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 at 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 at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
> End of postgis-users Digest, Vol 136, Issue 18
> **********************************************
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130625/5d83ce15/attachment.html>
More information about the postgis-users
mailing list