Re: [postgis-users] Errors restoring a db

[Humberto Cereser Ibanez]

Hi Paolo,

If your sql is trying to use 'copy' command, I experencied that the
superuser postgres can run it and stop the errors that you showed.

Humberto Ibanez

2015-12-22 4:39 GMT-02:00 Paolo Cavallini :

> Hi all,
> I'm getting errors while restoring a db from a 1.5 dump to a
> POSTGIS="2.1.4 r12966" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6
> March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1"
> LIBJSON="UNKNOWN" RASTER:
>
> ...
> psql:geosisma_geo_restore.sql:8262499: invalid command \N
> psql:geosisma_geo_restore.sql:8262500: invalid command \.
> psql:geosisma_geo_restore.sql:8262504: ERROR:  syntax error at or near
> "98424"
> LINE 1: 98424 98424 98424 RT1303152TO SENZA  F572
> ^
> psql:geosisma_geo_restore.sql:8262507: invalid command \N
> psql:geosisma_geo_restore.sql:8262508: invalid command \.
> psql:geosisma_geo_restore.sql:8262513: ERROR:  syntax error at or near "1"
> LINE 1: 1 12.9.2010 010620E610010001030001000500...
> ^
> Any explanation or possible solution?
> Thanks a lot.
> --
> Paolo Cavallini - www.faunalia.eu
> QGIS & PostGIS courses: http://www.faunalia.eu/training.html
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Topology: cannot delete slivers (or gaps)

[Humberto Cereser Ibanez]

I was with the same problem, getting gaps in some adjacent polygons
while trying to simplify polygons with Postgis.
Thank Guillaume / Rémi / Sandro by the contributions.
Illuminated by this thread, I created a script in which I avoided gaps
from the bad polygons. Really the most appropriate tool, GRASS, became
this work easier.

On Fri, 2014-11-07 at 13:14 -0800, Guillaume Drolet wrote:
 I finally finished cleaning my topology, using a mix of SQL commands,
 functions, and also using Sandro's QGIS topology editor plugin. 
 
 In parallel, I also tried going the GRASS route, importing my original
 shapefile in GRASS and applying the cleaning function (v.clean ...) as
 suggested by Remi. I injected the result into PostGIS and created a
 topology. Most of the 23 slivers were there in the topology so one of two
 things: 1) GRASS didn't fix them (maybe I didn't choose an appropriate
 tolerance) or 2) they were created by PostGIS when building the topology.
Simplifying polygons with QGIS and GRASS
   Note: SRID = 4326
1) Open shapefile in QGIS
2) Create New Mapset
Import Extent from opened Shapefile
3) Check the smaller area of the polygons involved
select ST_Area (geo_polygon) from table order by
ST_Area(geo_polygon);
Smaller area = 0.005471
4) Import shapefile into GRASS
v.in.ogr dsn = /home/humberto/shapefiles/pr/41MUE250GC_SIR.shp
output = prGrass snap = 0.0001 min_area = 0.004 -o
Settings of parameters: Snapping threshold for boundaries = 0.0001
and Minimum size of area to be imported (squere units) = 0.004
Note: The 0.004 value was chosen to allow importing of the smaller
area = 0.005 and all areas greater than it, avoiding any small ring that
would be arising from this importing.
5) Simplify the Shapefile polygon imported
v.generalize input=prGrass@elimSlivers type=area layer=1 -c
type=boundary method=douglas threshold=0.001 look_ahead=7 reduction=50
slide=0.5 angle_thresh=3 degree_thresh=0 closeness_thresh=0
betweeness_thresh=0 alpha=1.0 beta=1.0 iterations=1
output=prGrassGeneralized
Settings of parameters: threshold = 0.001
Note: the threshold value 0.001 was chosen by trial
6) Export the simplified polygon to Postgis
   v.out.ogr.pg.py input=prGrassGeneralized@elimSlivers type=area
layer=1 olayer=prgeneralized database=simp22 host=127.0.0.1 port=5432
user=name password=nonono

 
 Anyway, I got what I finally got what I need, thanks a lot to all of you who
 helped me.
 
 I'm gonna ask you one last thing if I may: I want to replace the original
 geom column (i.e. that used to build the topogeom) with a topologically
 correct one, and keep all the associated ecological attributes. Is this the
 right way to do it:
 
 UPDATE syshiera.de_20k SET geom = topogeom::geometry; ?
 
 Thanks.
 
 
 
 --
 View this message in context: 
 http://postgis.17.x6.nabble.com/Topology-cannot-delete-slivers-or-gaps-tp5007250p5007286.html
 Sent from the PostGIS - User mailing list archive at Nabble.com.
 ___
 postgis-users mailing list
 postgis-users@lists.osgeo.org
 http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Thanks,

Humberto

___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Query performance really slow..

[Humberto Cereser Ibanez]

Hello Pietro,
Em Ter, 2014-03-18 às 05:46 -0700, Pietro Rossin escreveu:
 Hello everybody
 
 my query came to a result!
 I followed the example by Paul Ramsey and I used a select case/else
 
 This is the query
 ***
 SELECT sum(st_area(geom)) as areacoltura, descrizione, codice_bac,
 nome, codice_cor FROM
 (SELECT
 a.descrizione,
 b.codice_bac,
 b.nome,
 b.codice_cor,
 CASE 
 WHEN ST_Within(a.geom,b.geom) 
 THEN a.geom
 ELSE ST_Multi(ST_Intersection(a.geom,b.geom))
 END AS geom
 FROM varie.particellepac2006 a
 JOIN idrologia.bacini_elementari b
 ON a.geom  b.geom) as foo
 group by descrizione, codice_bac, nome, codice_cor
 *
 
 Execution time was: 3865564 ms (still really slow...)
 6922 rows result.
 
 
 Explain is:
 GroupAggregate  (cost=31670075.75..32322147.79 rows=20522
 width=29555)
   Output: sum(st_area(CASE WHEN ((a.geom  b.geom) AND
 _st_contains(b.geom, a.geom)) THEN a.geom ELSE
 st_multi(st_intersection(a.geom, b.geom)) END)), a.descrizione,
 b.codice_bac, b.nome, b.codice_cor
   -  Sort  (cost=31670075.75..31673194.73 rows=1247592 width=29555)
 Output: a.geom, b.geom, a.descrizione, b.codice_bac, b.nome,
 b.codice_cor
 Sort Key: a.descrizione, b.codice_bac, b.nome, b.codice_cor
 -  Nested Loop  (cost=0.00..5497.61 rows=1247592
 width=29555)
   Output: a.geom, b.geom, a.descrizione, b.codice_bac,
 b.nome, b.codice_cor
   -  Seq Scan on idrologia.bacini_elementari b
 (cost=0.00..140.32 rows=332 width=28397)
 Output: b.id, b.geom, b.objectid, b.codice_bac,
 b.nome, b.ordine, b.quota_medi, b.area_kmq, b.codice_cor
   -  Index Scan using sidx_particellepac2006_geom on
 varie.particellepac2006 a  (cost=0.00..16.10 rows=3 width=1158)
 Output: a.id_pac, a.geom, a.cod_nazion, a.foglio,
 a.particella, a.sub, a.prog_polig, a.area_colt, a.cod_coltur,
 a.anno_foto, a.mese_foto, a.cod_variet, a.istatp, a.stato_colt,
 a.descrizione, a.shape_leng, a.shape_area
 Index Cond: (a.geom  b.geom)
 
 
 
 Is there anything I can do to make it perform faster?
 
 
 
 Is there some parameter to tune in Server Configuration?
 
 I use Winxp32bit 4Gb ram, dual operon 252.
I suggest you take advantage of your 64bit processor.
http://h18000.www1.hp.com/products/quickspecs/12357_na/12357_na.PDF
My DBMS PostgreSql/PostGis runs over a Debian 64bit. 
 
 
 Thanks!
 
 Pietro
 
 
 
Good luck,

Humberto Cereser Ibanez


___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Query performance really slow..

[Humberto Cereser Ibanez]

Hi Pietro Rossin,


Em Qui, 2014-03-13 às 06:59 -0700, Pietro Rossin escreveu:
 Hi all
 I'm trying to query 2 vector layers;
 b) bacini_elementari that is river basins - 327 elements
 b) particellepac2006 colture parcels - 614000 elements
 
 I want to intersect these two layers and get the sum of the area of each
 kind of colture for each river basin.
 To reduce the number of colture vectorial features I made a collect query
 (grouping by kind of colture) and I made a intersection between this
 geometry aggregation and all 327 basins.
 
 My query is:
 
 SELECT sum(st_area(ST_Intersection(bacini.geom,colture.geom))) as
 areacoltura, codice_bac, nome, codice_cor, colture.descrizione
   FROM idrologia.bacini_elementari as bacini,
   (SELECT st_collect(geom) as geom, descrizione
   FROM varie.particellepac2006
   group by 2) as colture
   group by 2,3,4,5
 
 it's 500ms that the query is running and I don't have any result yet...
 
 The two geometry column have an index (but I think it's not used in this
 query)
 
my guess is to include  and st_intersects operators as a condition on
your query:

where bacini.geom  colture.geom and st_intersects(bacini.geom,
colture.geom)

I have done this on a similar work.
lgsc.geom_buffer_0  lgp.geom_buffer_0 and
st_intersects(lgsc.geom_buffer_0, lgp.geom_buffer_0)


Best regards,

Humberto Cereser Ibanez


___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] [OT] How to construct and float8[][] array?

[Humberto Cereser Ibanez]

Hi Steve,

did you try execute the most nested subquery and the upper one to see
which one causes the ERROR?

There is also an array_cat function that support multidimensional
arrays.
http://www.postgresql.org/docs/9.1/static/arrays.html

best regards,

Humberto Cereser Ibanez


Em Sex, 2013-06-28 às 14:53 -0400, Stephen Woodbridge escreveu:
 Hi all,
 
 This is a little bit off topic but I'm trying to create a nxn array like 
 the following in SQL:
 
 {{0,1,2,3},{1,0,3,2},{2,3,0,4},{3,2,4,0}}
 
 select array_ndims('{{0,1,2,3},{1,0,3,2},{2,3,0,4},{3,2,4,0}}'::float[][]);
 -- 2
 
 select array_dims('{{0,1,2,3},{1,0,3,2},{2,3,0,4},{3,2,4,0}}'::float[][]);
 -- [1:4][1:4]
 
 select pg_typeof('{{0,1,2,3},{1,0,3,2},{2,3,0,4},{3,2,4,0}}'::float[][]);
 -- double precision[]
 
 So I came up with the following:
 
 select array_agg(arow) as dm from (
select i, array_agg(dist) as arow from (
  select a.source_id as i,
 b.source_id as j,
 st_distance(st_makepoint(a.x, a.y),
 st_makepoint(b.x, b.y)) as dist
from tsp_00 a, tsp_00 b
   order by a.source_id, b.source_id
) as foo group by i order by i
 ) as bar;
 
 But I get the following error:
 
 ERROR:  could not find array type for data type double precision[]
 
 ** Error **
 
 ERROR: could not find array type for data type double precision[]
 SQL state: 42704
 
 So the question is how can I construct float[][] object like the 
 constant using sql?
 
 I would like to use this in pgRouting to build a distance matrix and 
 pass it to our TSP solver, like:
 
 select * from pgr_tsp(
(select array_agg(arow) as dmatrix from (
  select i, array_agg(dist) as arow from (
select a.source_id as i,
   b.source_id as j,
   st_distance(st_makepoint(a.x, a.y),
   st_makepoint(b.x, b.y)) as dist
  from tsp_00 a, tsp_00 b
 order by a.source_id, b.source_id
  ) as foo group by i order by i
) as bar),
0
 );
 
 
 Thanks,
-Steve
 ___
 postgis-users mailing list
 postgis-users@lists.osgeo.org
 http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] postgis-users Digest, Vol 136, Issue 18

[Humberto Cereser Ibanez]

Hi Ketty,

I found at my Debian Squeeze box the following SQL script, but I never
launched it:
/usr/share/postgresql/9.1/contrib/postgis-1.5/uninstall_postgis.sql

best regards,

Humberto Cereser Ibanez

Em Ter, 2013-06-25 às 15:33 +0300, Ketty Adoch escreveu:
 Hi all,
 
 
 How do i completely uninstall postgis-1.5.3 from ubuntu 12.04 on
 postgresql-9.1
 
 
 Running sudo apt-get remove postgis and sudo apt-get purge postgis
 does not uninstall postgis
 
 
 Then checking for version of postgis SELECT postgis_full_version()
 still returns postgis-1.5.3 as the running version.
 
 
 Any pointers?
 
 
 regards,
 
 
 Ketty
 
 
 Ketty Adoch
 
 Skype id: adockatie
 
 Twitter: www.twitter.com/kadoch
 
 
 
 
 
 On Mon, Jun 24, 2013 at 10:00 PM,
 postgis-users-requ...@lists.osgeo.org wrote:
 Send postgis-users mailing list submissions to
 postgis-users@lists.osgeo.org
 
 To subscribe or unsubscribe via the World Wide Web, visit
 
 http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
 or, via email, send a message with subject or body 'help' to
 postgis-users-requ...@lists.osgeo.org
 
 You can reach the person managing the list at
 postgis-users-ow...@lists.osgeo.org
 
 When replying, please edit your Subject line so it is more
 specific
 than Re: Contents of postgis-users digest...
 
 
 Today's Topics:
 
1. Re: Raster pixel count too high (Hugues Fran?ois)
2. Re: Raster pixel count too high (Kim Bisgaard)
3. Trying to restore a .backup file (Gunnar Oehmichen)
4. Re: Trying to restore a .backup file (Humberto Cereser
 Ibanez)
5. Re: Raster pixel count too high (Andreas For? Tollefsen)
 
 
 --
 
 Message: 1
 Date: Sun, 23 Jun 2013 21:52:59 +0200
 From: Hugues Fran?ois hugues.franc...@irstea.fr
 To: PostGIS Users Discussion postgis-users@lists.osgeo.org
 Subject: Re: [postgis-users] Raster pixel count too high
 Message-ID:
 
 3cb901080554b04881d30f111f62d93002ce0...@nadia.grenoble.cemagref.fr
 Content-Type: text/plain; charset=iso-8859-1
 
 Hello,
 
 I have a lot of work and I won't be able to test your data
 now, but I'll try ASAP.
 
 On my side, I have to deal with rasters dem tiles which
 overlap each other. I need to have unique tiles and I made the
 two attached functions to achieve that :
 1. The first one, makegrid, simply builds a grid given a
 bounding box geometry and height / width
 2. The second, unique_tiles, calls the first one to make a
 whole raster made of regular tiles of the wanted size in
 number of pixels from a raster table of aligned tiles (it
 finds its bounding box and use it to makegrid from left bottom
 corner using original pixel height and width and then use this
 grid to clip raster)
 
 Maybe they can help you in your clipping process. You may
 improve performance if you remove the st_union in unique_tiles
 (needed if your raster is made of multiple tiles).
 
 Hugues.
 
 
 
 De : postgis-users-boun...@lists.osgeo.org
 [mailto:postgis-users-boun...@lists.osgeo.org] De la part de
 Andreas For? Tollefsen
 Envoy? : dimanche 23 juin 2013 10:47
 ? : PostGIS Users Discussion
 Objet : Re: [postgis-users] Raster pixel count too high
 
 
 
 Thanks for your answers.
 
 
 
 First to Hugues. I do not think they are perfectly aligned.
 The raster we have imported seems to start just west of -180.
 Hence, it is not within the limits of SRID 4326. We have to
 modify it a little.
 
 What I do find strange is that ArcGIS counts 3600 which is the
 expected pixel count. So why these two functions count so
 differently on the exact same data is weird.
 
 Try yourself with one of the Nightlights data:
 http://www.ngdc.noaa.gov/eog/dmsp/downloadV4composites.html
 http://www.ngdc.noaa.gov/eog/dmsp/downloadV4composites.html
  and our vector grid shapefile available here:
 http://www.prio.no/Data/PRIO-GRID/
 http://www.prio.no/Data/PRIO-GRID/
 
 Kim: We do not directly use ST_Intersection in our script, but
 ST_Clip to clip the raster according to our polygons, and only
 where they ST_Intersects(), not ST_Intersection();
 
 I still do not understand your suggestion.
 
 
 
 Best

Re: [postgis-users] Trying to restore a .backup file

[Humberto Cereser Ibanez]

Dear Gunnar,

I suggest to you recreate your MI database, add spatial extensions and
restore your backup: 
1) drop MI database (dropdb) and create it from scratch (createdb)
2) execute the steps that are showed in 2.6 Creating a spatial database
using EXTENSIONS at
http://postgis.net/docs/manual-2.0/postgis_installation.html#create_new_db_extensions
 , and
3) run your pg_restore
Best regards,

Humberto Cereser Ibanez

Em Seg, 2013-06-24 às 17:05 +0200, Gunnar Oehmichen escreveu:
 Dear Participants,
 
 As part of my thesis (environmental sciences) I recently started working 
 with PostgreSQL (newbie here). The georeferenced database has been 
 transferred from a local Windows7 desktop machine to me.
 
 I am using Ubuntu 12.04 64 bit with Xubuntu as a Desktop-environment
 
 Postgres Version 9.1.9, Postgis 2.0.1.2, PGAdminIII as GUI.
 
 To restore the .backup file I built a new Database MI in the GUI 
 without using any template from the definitions tab:
 
 /usr/bin/pg_restore --host localhost --port 5432 --username postgres 
 --dbname MI --no-password  --verbose 
 /home/gunnaroeh/Dokumente/Laenderdaten/Database/LaenderStand_02-20.backup
 pg_restore: connecting to database for restore
 pg_restore: creating TABLE mzb
 pg_restore: creating TABLE samplsites_mzb
 pg_restore: [archiver (db)] Error while PROCESSING TOC:
 pg_restore: [archiver (db)] Error from TOC entry 200; 1259 51728 TABLE 
 samplsites_mzb postgres
 pg_restore: [archiver (db)] could not execute query: ERROR:  type 
 geometry does not exist
 LINE 14: east_north geometry(Point,31467)
  ^
  Command was: CREATE TABLE samplsites_mzb (
  land character varying(50),
  samplsite_id integer NOT NULL,
  samplsite character var...
 pg_restore: [archiver (db)] could not execute query: ERROR:  relation 
 public.samplsites_mzb does not exist
  Command was: ALTER TABLE public.samplsites_mzb OWNER TO postgres;
 
 
 pg_restore: creating TABLE samplsites_phch
 pg_restore: restoring data for table mzb
 pg_restore: restoring data for table samplsites_mzb
 pg_restore: [archiver (db)] Error from TOC entry 3147; 0 51728 TABLE 
 DATA samplsites_mzb postgres
 pg_restore: [archiver (db)] could not execute query: ERROR:  relation 
 samplsites_mzb does not exist
  Command was: COPY samplsites_mzb (land, samplsite_id, samplsite, 
 samplsite_name, rechtswert, hochwert, easting, northing, latitude, longi...
 pg_restore: restoring data for table samplsites_phch
 pg_restore: creating CONSTRAINT id
 pg_restore: [archiver (db)] Error from TOC entry 3145; 2606 59403 
 CONSTRAINT id postgres
 pg_restore: [archiver (db)] could not execute query: ERROR:  relation 
 samplsites_mzb does not exist
  Command was: ALTER TABLE ONLY samplsites_mzb
  ADD CONSTRAINT id PRIMARY KEY (samplsite_id);
 
 
 
 pg_restore: setting owner and privileges for TABLE mzb
 pg_restore: setting owner and privileges for TABLE samplsites_mzb
 pg_restore: setting owner and privileges for TABLE samplsites_phch
 pg_restore: setting owner and privileges for TABLE DATA mzb
 pg_restore: setting owner and privileges for TABLE DATA samplsites_mzb
 pg_restore: setting owner and privileges for TABLE DATA samplsites_phch
 pg_restore: setting owner and privileges for CONSTRAINT id
 WARNING: errors ignored on restore: 4
 
 Prozess beendete mit Exitcode 1.
 
 If I add the extensions postgis and postgis_topology to the new db 
 MI at least the following lines are missing from the messages:
 
 pg_restore: [archiver (db)] could not execute query: ERROR:  type 
 geometry does not exist.
 
 Also noteworthy:
 
 postgres=# SELECT PostGIS_version();
 ERROR:  function postgis_version() does not exist
 LINE 1: SELECT PostGIS_version();
 ^
 HINT:  No function matches the given name and argument types. You might 
 need to add explicit type casts.
 
 By the way, the template postgis is not available in the definitions-tab 
 using the GUI.
 
 I am also posting to this list as I do not know, wether the problem is 
 postgis related or not,
 
 Thank you very much,
 
 Gunnar
 ___
 postgis-users mailing list
 postgis-users@lists.osgeo.org
 http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users