[postgis-users] postgis-users Digest, Vol 136, Issue 18
Humberto Cereser Ibanez
humberto at pastoraldacrianca.org.br
Tue Jun 25 06:14:37 PDT 2013
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-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
> **********************************************
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
More information about the postgis-users
mailing list