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