headers
Jayadevan M | 1 Aug 2012 08:39

Error while trying a hard upgrade of postgis database

Hello all,

We are trying to upgrade from PostgreSQL (with GIS) from 8.2 to 9..1

1) When we execute postgis_restore.pl, we get an error.

D:\Applns\PostgreSQL\9.1\utils>postgis_restore.pl postgis.sql tnrsp_9.1  d:\publicbackupnew_29june.backup -E=UNICODE > restore.log
Converting postgis.sql to ASCII on stdout...
  Reading list of functions to ignore...
  Writing manifest of things to read from dump file...
pg_restore: [archiver] input file does not appear to be a valid archive
D:\Applns\PostgreSQL\9.1\utils\postgis_restore.pl: pg_restore returned an error


2) But if we execute pg_restore from command prompt directly, it is working (loads all data except GIS. GIS data throws errors). A typical error would be
"pg_restore: [archiver (db)] could not execute query: ERROR:  type "geometry" is only a shell
LINE 24:     the_geom geometry,
                      ^"


Version information -
Source PostgreSQL -
PostgreSQL 8.2.13 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)

Source GIS -
POSTGIS="1.1.6" GEOS="2.2.3-CAPI-1.1.1" PROJ="Rel. 4.5.0, 22 Oct 2006" USE_STATS (procs from 1.3.1 need upgrade)

Target PostgreSQL -
PostgreSQL 9.1.4, compiled by Visual C++ build 1500, 32-bit

Target GIS -
"POSTGIS="2.0.0 r9605" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER"


Any clues as to what we are doing wrong/how to fix this?

Thank you.
Jayadevan






DISCLAIMER:

"The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."




_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Permalink | Reply | 0 comments |
headers
Yamini Singh | 1 Aug 2012 10:04

Re: postgis-users Digest, Vol 125, Issue 28

Hi Tom,

I am working with multipolygons because some of my states have holes in it and some have islands. I am really thankful to the solution provided by you. But does your solution also take care of the holes and islands. Because i want to calculate the distance of all points (on outermost boundary of multipolygons) from centroid.
or example, if there are holes inside the polygon then the distance to centroid should only be for the points on the outermost boundary of that polygon and should not include points of the holes as such for calculation. Similarly, if there are islands then it should considers the outermost points for calculations.
I hope i have clarified my problem better. 


Thanks 
Yamini

> From: postgis-users-request <at> postgis.refractions.net
> Subject: postgis-users Digest, Vol 125, Issue 28
> To: postgis-users <at> postgis.refractions.net
> Date: Tue, 31 Jul 2012 12:00:01 -0700
>
> ------------------------------
>
> Message: 2
> Date: Mon, 30 Jul 2012 22:05:23 +0200
> From: Tom van Tilburg <tom.van.tilburg <at> gmail.com>
> Subject: Re: [postgis-users] distance from centroid to the points that
> form the outer boundary of the polygon
> To: postgis-users <at> postgis.refractions.net
> Message-ID: <5016E903.4080301 <at> gmail.com>
> Content-Type: text/plain; charset="iso-8859-1"; Format="flowed"
>
> Hi Yamini,
>
> Are you sure that multipolygons is what you want to work with? Since a
> multipolygon can consist of multiple polygons (obviously) who share a
> common centroid that can be way outside your actual polygons.
>
> That said, I would make use of ST_Dumppoints to do that.
> Something like:
> ============
> WITH pointsdump AS
> (
> SELECT gid, ST_Centroid(geom) AS centre, (ST_Dumppoints(geom)).geom
> AS point FROM polygontable
> )
>
> SELECT gid, ST_Distance(centre, point) AS distance FROM pointsdump
> ===========
>
> Now for every point in a geometry with a known gid you will see the
> distance to it's centroid.
> You can do statistics on these numbers with something like:
> SELECT gid, avg(distance) avg, max(distance) max, etc... FROM
> results GROUP BY gid
>
> Cheers,
> Tom
>
> On 30-7-2012 15:04, Yamini Singh wrote:
> >
> > Hi All,
> >
> > I am looking for suggestions for the following problem I have at hand.
> >
> > 1.I have a table that contain multipolygons
> >
> > 2.I want to calculate the maximum distance of points that form the
> > polygon from the centroid of the polygon. For example of the polygon
> > is formed of 50 points then I want to have distance of all the 50
> > points from the centroid of the polygon.
> >
> > I am not sure if someone has done this before. But I am finding it
> > difficult to implement it. Any help in this regards is welcome...
> >
> >
> > Best,
> >
> > YJS

_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Permalink | Reply | 1 comment |
headers
Tom van Tilburg | 1 Aug 2012 10:28
Picon
Gravatar

Re: postgis-users Digest, Vol 125, Issue 28

Hi Yamini,

I'm not sure I understand what you are trying to do.
Perhaps you can give the realworld example of your task or maybe an image that explains?

If you need something of an 'outermost ring' around your area you might consider using ST_ConcaveHull.
http://postgis.refractions.net/documentation/manual-2.0/ST_ConcaveHull.html

Cheers,
 Tom

On 1-8-2012 10:04, Yamini Singh wrote:
Hi Tom,

I am working with multipolygons because some of my states have holes in it and some have islands. I am really thankful to the solution provided by you. But does your solution also take care of the holes and islands. Because i want to calculate the distance of all points (on outermost boundary of multipolygons) from centroid.
or example, if there are holes inside the polygon then the distance to centroid should only be for the points on the outermost boundary of that polygon and should not include points of the holes as such for calculation. Similarly, if there are islands then it should considers the outermost points for calculations.
I hope i have clarified my problem better. 


Thanks 
Yamini


_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Permalink | Reply | 0 comments |
headers
SIG CEN L-R | 2 Aug 2012 08:29

Re: st_value/st_world2rastercoordx error

Thanks a lot Bborie,

As you suggested there was a null geometry in my table and the second "error" you discovered was that I used non tiled raster in the database.
http://si.cenlr.org/2012/08/01/postgis-raster-suite

I forgot to "reply to all" so I put our exchange below

Thanks again,

Mathieu

--
Mathieu BOSSAERT
Responsable du système d'information du CEN L-R
04 67 29 90 65 - sig <at> cenlr.org


De: "Bborie Park" <bkpark <at> ucdavis.edu>
À: "Mathieu Bossaert (CEN L-R)" <sig <at> cenlr.org>
Envoyé: Mercredi 1 Août 2012 19:05:47
Objet: Re: [postgis-users] st_value/st_world2rastercoordx error

Ah.  If each table only has one row, then definitely do not use
ST_Intersects.  ST_Intersects should be used in the normal situation (1
table = many rows).  Also, one thing to keep in mind is that a single
field in PostgreSQL can only hold 1 GB of data.  I don't know if your
rasters exceed 1GB in size, but that is something to keep in mind.

I'll have to add a check for when the geometry is null in that
function... I'll ticket it.

-bborie

On 08/01/2012 03:16 AM, SIG CEN L-R wrote:
Hi bborie,

Thanks for the answer. It helps me a lot for this problem and generally with postgis ratser.

Basically my ratsers were stored in the database as only one tile (one raster = one table with one row)

=> when i tried to enhance my query by using the st-intersects() operator, the execution grew up!

So now I store my rasters with indexed tile (100x100px). The query is now very fast!

For the second problem, you were right, one of my row had a null geometry value!

Thanks again,



Mathieu Bossaert

--
Mathieu BOSSAERT
Responsable du système d'information du CEN L-R
04 67 29 90 65 - sig <at> cenlr.org



De: "Bborie Park" <bkpark <at> ucdavis.edu>
À: "Mathieu Bossaert (CEN L-R)" <sig <at> cenlr.org>, "PostGIS Users Discussion" <postgis-users <at> postgis.refractions.net>
Envoyé: Mardi 31 Juillet 2012 16:06:56
Objet: Re: [postgis-users] st_value/st_world2rastercoordx error

Can you isolate the geometry that is causing the NaN?  Maybe something like

SELECT
  rowid,
  ST_X(geometry)
  ST_Y(geometry)
FROM mygeometrytable

The error message indicates that somehow a NaN is being passed to ST_World2RasterCoordX...

Also, your query isn't ideal.  You should add another WHERE clause dealing with the intersection of the rast and the geometry.

UPDATE export.tous_point_espece_selon_format_esri SET
pente_mnt = ST_VALUE(rast, geometrie, true)
FROM raster.pente_mnt30
WHERE code_insee LIKE '30%'
AND ST_Intersects(geometrie, rast)

-bborie

On Tue, Jul 31, 2012 at 2:09 AM, Mathieu Bossaert (CEN L-R) <sig <at> cenlr.org> wrote:
Good morning,

first of all I want to thank the PostGIS community for the great job she does.

I am a french user of postgis since 2006 and it helps us a lot in our mission to preserve landscapes and biodiversity.
Since a few month we use rasters function in order to characterize species distribution.

I have a problem with 1 of 5 raster table containing a dem.
When I try to populate an elevation attribute of a point layer from this raster I get an error. To workaround this problem I use pg-script to run this update line after line.

Is there a way to run this command, skipping the error.

Here is the query :

UPDATE export.tous_point_espece_selon_format_esri SET pente_mnt = ST_VALUE(rast, geometrie, true)
FROM raster.pente_mnt30
WHERE code_insee LIKE '30%'

And here is the error (sorry it's in french)

ERREUR: syntaxe en entrée invalide pour l'entier : « NaN »
CONTEXTE : PL/pgSQL function "st_world2rastercoordx" lors de la conversion de la valeur de retour au type de retour de la fonction PL/pgSQL function "st_value" line 13 at RETURN

Thanks again,

Mathieu Bossaert

--
Mathieu BOSSAERT
Responsable du système d'information du CEN L-R
04 67 29 90 65 - sig <at> cenlr.org



_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users




--
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkpark <at> ucdavis.edu
_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Permalink | Reply | 0 comments |
headers
Neil Best | 2 Aug 2012 19:23

Re: Rasterize a vector

Paolo, were you able to get something working satisfactorily?  If I read the
thread correctly you wanted a separate raster output for each geometry in
the input.  I am more interested in the case of creating a raster coverage
for a set of geometries, US counties for example, using an ID as the value. 
It sounds like this distinction is not important, rather there was a problem
with the construction of any output from these two cases that prevented it
from being read by GDAL.  Do I have that right?  What is the state of the
art on this front?  I am reluctant to try it if it is going to involve
repeated impact of my forehead and the wall.  Thanks.

Neil

--
View this message in context: http://postgis.17.n6.nabble.com/Rasterize-a-vector-tp4997893p4999260.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
Permalink | Reply | 1 comment |
headers
Paolo Cavallini | 2 Aug 2012 19:28
Picon
Favicon
Gravatar

Re: Rasterize a vector

Il 02/08/2012 19:23, Neil Best ha scritto:
> Paolo, were you able to get something working satisfactorily?  
not in PG
> If I read the
> thread correctly you wanted a separate raster output for each geometry in
> the input.  I am more interested in the case of creating a raster coverage
> for a set of geometries, US counties for example, using an ID as the value. 
this was also my aim
> It sounds like this distinction is not important, rather there was a problem
> with the construction of any output from these two cases that prevented it
> from being read by GDAL.  Do I have that right?  What is the state of the
> art on this front?  I am reluctant to try it if it is going to involve
> repeated impact of my forehead and the wall.
from what I understood it is still far easier to do it outside the DB
(with GDALTools, GRASS, SAGA or similar).
heppy if I'm wrong.
All the best.

--

-- 
Paolo Cavallini - Faunalia
www.faunalia.eu
Full contact details at www.faunalia.eu/pc
Nuovi corsi QGIS e PostGIS: http://www.faunalia.it/calendario
Permalink | Reply | 0 comments |
headers
CHANDLER DOUGLAS COLEMAN | 2 Aug 2012 23:31
Picon

area and distance

Hello,
I am a newbie to PostGIS.  I am hoping some one can explain to me why the following simple query doesn't return any info:

SELECT f.fact_name
FROM factory AS f, inner_area AS i
WHERE i.name = 'Dewsbury'
AND ST_WITHIN (f.geom,i.geom);

I have geom in as a column and I have 'Dewsbury' as a polygon record in inner_area and there are 5 records in factory that fall within this polygon.  But the resulting table shows that none were selected.

My assumption here is that the SQL statement is correct, but that something is wrong with the tables:  could it be how I imported the two tables from shapefiles.

Thanks,
Chandler
_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Permalink | Reply | 2 comments |
headers
Jean-Daniel Sylvain | 2 Aug 2012 23:41
Picon

Vector overviews in postgis

Hi,

Is it possible to create overviews in postgis to optimise the speed of display for very big vectorial dataset (10 000 000) ?

The aim is to be able to look quickly the dataset via a GIS tool like Quantum GIS or GV SIG or any suggestions.

Jean-Daniel Sylvain 

_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Permalink | Reply | 3 comments |
headers
Muhammad Imran | 2 Aug 2012 23:46
Picon
Favicon

Re: area and distance

Hi Chandler,

Did you check projection system of the both Geometries? Just to make sure, use ST_TRANSFORM as inner function.

regards

Muhammad Imran

--- On Fri, 8/3/12, CHANDLER DOUGLAS COLEMAN <CHANDLER.D.COLEMAN <at> stu.mmu.ac.uk> wrote:

From: CHANDLER DOUGLAS COLEMAN <CHANDLER.D.COLEMAN <at> stu.mmu.ac.uk>
Subject: [postgis-users] area and distance
To: "postgis-users <at> postgis.refractions.net" <postgis-users <at> postgis.refractions.net>
Date: Friday, August 3, 2012, 3:01 AM

#yiv946520484 P {margin-top:0;margin-bottom:0;}
Hello,
I am a newbie to PostGIS.  I am hoping some one can explain to me why the following simple query doesn't return any info:

SELECT f.fact_name
FROM factory AS f, inner_area AS i
WHERE i.name = 'Dewsbury'
AND ST_WITHIN (f.geom,i.geom);

I have geom in as a column and I have 'Dewsbury' as a polygon record in inner_area and there are 5 records in factory that fall within this polygon.  But the resulting table shows that none were selected.

My assumption here is that the SQL statement is correct, but that something is wrong with the tables:  could it be how I imported the two tables from shapefiles.

Thanks,
Chandler

-----Inline Attachment Follows-----

_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Permalink | Reply | 1 comment |
headers
georgew | 3 Aug 2012 02:43
Picon
Favicon

SAGA GIS connection to Postgis

Hi, I would be delighted to hear from anyone who has succeeded in using SAGA
GIS for Windows 7 64 with Postgis 2.0 and Postgres 9.1 for Win 7 64. (or
even a 32 bit version)
All I have succeeded in doing is connect to the database and list tables in
the database but I cannot retrieve any data from any of the tables and
visualise it in SAGA. (SAGA relies on ODBC to access the database).
Many thanks

--
View this message in context: http://postgis.17.n6.nabble.com/SAGA-GIS-connection-to-Postgis-tp4999265.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
Permalink | Reply | 0 comments |

Gmane