[postgis-users] Query/View permission deny - strange behaviour

Hello I have problems with postgres/postgis query/view permissions. Postgresql 9.4 Postgis 2.1 I created a user pippo inherit. I created a group catasto_editors inherit and a group basi_viewers. I granted catasto_editors to pippo and basi_viewers to catasto_editors. Then two schemas basi and c

Re: [postgis-users] Query/View permission deny - strange behaviour

Hello, I'm going crazy with this problems... I'll report real names and statements *Here is the user login pippo * ** CREATE ROLE pippo LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; GRANT sitarpa_catasto_scarichi_editors_ud TO pippo; GRANT sitarpa_viewers TO pip

Re: [postgis-users] Query/View permission deny - strange behaviour

Sorry for my poor thecnical language Select for me is execute the view.. Yesterday I found a solution I thought the user (or the group role he is granted to) that execute the view must be granted to select the tables in the view. Instead the owner of the view must be granted to select tables fro

[postgis-users] Upgrade both Postgres/postgis and Debian major releases - how to?

Hello list We have a Debian 8.9 server with Postgres 9.4.12/postgis 2.1 We are trying to upgrade debian to 9.1, Postgres to 9.6 and postgis to 2.3.1. The problem is similar to what is described here http://blog.cleverelephant.ca/2016/08/postgis-upgrade.html

Re: [postgis-users] Upgrade both Postgres/postgis and Debian major releases - how to?

Dear Regina Obe thanks for your reply and sorry for my unclear explanation. I try to be more clear: 1) before the debian 9.1 upgrade ve made an upgrade to Postgres 9.4/Postgis 2.3 with jessie backports postgresql-9.4-postgis-2.3 (2.3.1+dfsg-1~bpo8+1). Then we upgraded debian to 9.1. This automatic

Re: [postgis-users] Upgrade both Postgres/postgis and Debian major releases - how to?

I guess this is the best way to go... https://postgis.net/docs/postgis_installation.html#hard_upgrade -- View this message in context: http://postgis.17.x6.nabble.com/Upgrade-both-Postgres-postgis-and-Debian-major-releases-how-to-tp5011462p5011465.html Sent from the PostGIS - User mailing li

Re: [postgis-users] Upgrade both Postgres/postgis and Debian major releases - how to?

Yes we are trying this way with what described in https://postgis.net/docs/postgis_installation.html#hard_upgrade But it's a strange thing that the postgres9.4/postgis2.3 extension is removed too... So for example a server with 4 different postgres services (9.4 and 9.6) with the same postgis ext

Re: [postgis-users] Upgrade both Postgres/postgis and Debian major releases - how to?

Hello we made a hard upgrade and the result seems ok, but a long errors list reported by the process.. In order, in debian 8.9/postgresql 9.4.12/postgis 2.3 we made a custom dump pg_dump -Fc -b -v -f "/somepath/olddb.backup" olddb then we upgraded debian to 9, postgresql to 9.6/postgis 2.3 and r

Re: [postgis-users] Upgrade both Postgres/postgis and Debian major releases - how to?

Spatial ref sys table is in public with grant select to public.. The isn't difference in permission (AFAIK) between the old and new table... Thanks Andrea -- View this message in context: http://postgis.17.x6.nabble.com/Upgrade-both-Postgres-postgis-and-Debian-major-releases-how-to-tp5011462p

Re: [postgis-users] FYI: Solution to Using PostGIS in ArcGIS.

Hello all does anybody know if it's possible to load postgis layers in Arcgis 9.2? I saw zigGIS was discontinued, any other solution? Thanks Pietro 2012/11/7 Birgit Laggner > Hi, > > we work with ArcGIS 10.1, now, and since, we don't need ArcSDE for > communication with PostgreSQL db and PostG

[postgis-users] Why postgis doesn't use index during query?

Hello I have a table with about 6.000.000 polilines records. The geometry value is in the_geom column. I made an index with this instruction * CREATE INDEX idx_polilineegb_geom ON gauss_b.polilineegb USING gist (the_geom ); * If I try a query like this: *

Re: [postgis-users] Why postgis doesn't use index during query?

define a geographic bounding box and test > your data against it: > > SELECT the_geom, layer, id > FROM gauss_b.polilineegb > where the_geom && 'BOX(2365385 5075215, 2366019 5075752)'::box2d; > > Nicolas > > > > On 22 January 2013 15:38, Pi

Re: [postgis-users] Why postgis doesn't use index during query?

Is there any reference list for the GIST operators? Thnx Pietro 2013/1/22 Pietro Rossin > Superthank you! > It works and very fast! > Bie > Pietro > > > 2013/1/22 Nicolas Ribot-2 [via PostGIS] <[hidden > email]<http://user/SendEmail.jtp?type=node&node=50

[postgis-users] Is it worth to load background raster into postgis?

Hi all we are building a postgis db to store shared data to e used in my agency. While for the vectorial part of our data I'm convinced of the advantage to store them into a geodb, for the raster part of data I'm not so convinced. I took a look at postgis raster reference page and I realized that p

Re: [postgis-users] Is it worth to load background raster into postgis?

advantages do not apply to raster? > > Pierre > > > -Original Message- > > From: [hidden > > email]<http://user/SendEmail.jtp?type=node&node=5002528&i=0>[mailto: > postgis-users- > > [hidden email] <http://user/SendEmail.jtp?type=node&node=5002528&

Re: [postgis-users] Is it worth to load background raster into postgis?

We don't have a WMS server yet, any suggestion for a "not too difficult to configure" solution? p 2013/2/8 Paolo Cavallini [via PostGIS] > Il 08/02/2013 14:19, Pietro Rossin ha scritto: > > Hi > > Thanks Paolo > > How can I store my images to be served as

[postgis-users] Make polygons on mixed closed/non closed linestrings?

Is it possible? I have a postgis table that is an importation of dxf data. In autocad a polygon is a closed polyline All civil buildings, in my table, are closed polylines and I can convert them on the fly to polygon by st_makepolygon() function, ok. My problem is for big buildings, in cities, whe

[postgis-users] ST_CoveredBy() query run very very very slow

Hello everybody I'm trying to select features from two spatial tables, geometry 2D. The first table is polylines from my whole area (984000 records). The second table is a polygon table, 97 records. These polygons were created from the first table with an external tool (FME). Lots of polyline

[postgis-users] ST_CoveredBy() query run very very very slow

Hello everybody I'm trying to select features from two spatial tables, geometry 2D. The first table is polylines from my whole area (984000 records). The second table is a polygon table, 97 records. These polygons were created from the first table with an external tool (FME). Lots of polyline

Re: [postgis-users] ST_CoveredBy() query run very very very slow

users-boun...@lists.osgeo.org > [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Pietro Rossin > Sent: Tuesday, May 07, 2013 10:31 AM > To: postgis-us...@postgis.refractions.net > Subject: [postgis-users] ST_CoveredBy() query run very very very slow > > Hello everybody &

[postgis-users] permission denied for relation geometry_columns on postgis 2.x

Hello all how can I solve this problem? With postgres user no problem (of course), but I made a gisadmin user which I thought I gave enough grants.. geometry_columns with postgis 2.x is not a table but a view that, if I'm not wrong, is updated by some trigger/function How can I make it usable/sele

Re: [postgis-users] permission denied for relation geometry_columns on postgis 2.x

abase you are trying to connect to. > > > > > > > > > > > > > > > > > > > > On Tue, May 21, 2013 at 8:13 AM, Pietro Rossin wrote: > >> Hello all >> how can I solve this problem? >> With postgres user no problem (of course), bu

Re: [postgis-users] permission denied for relation geometry_columns on postgis 2.x

Ok, problem solved... GRANT SELECT ON public.geometry_columns TO gisadmin (or whatever user you like); Bie 2013/5/21 Luis Carrasco [via PostGIS] > You are welcome, Pietro > > Luis > > > ___ > postgis-users mailing list > [hidden email]

[postgis-users] error on copying geometries between geometry columns

Hello I'm trying to copy polygons from one column to another to reduce vertex numbers my columns are geom (geom geometry(MultiPolygonZ,3004)) and geom_simply (geom_simply geometry(MultiPolygonZ,3004)) With this query UPDATE mytable SET geom_simply=ST_SimplifyPreserveTopology(geom, 100) I get th

Re: [postgis-users] error on copying geometries between geometry columns

Thanks Paolo It worked like this: set geom_simply=ST_Multi(ST_SimplifyPreserveTopology(geom, 100)) Bie Pietro 2013/8/12 Paolo Cavallini > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Il 12/08/2013 13:19, Pietro Rossin ha scritto: > > > UPDATE mytabl

[postgis-users] how to move geometries to a fraction of their position?

I have a geomedia mdb that I moved to postgis. All geometries (metric coordinates) are hundred times greater on x and y of what they should be.. Let's say if a coordinate sould be 3,4 it indeed is 300,400. I don't know why.. By the way, with point I updated the geometry column using this sql UPDAT

Re: [postgis-users] how to move geometries to a fraction of their position?

Hello Thanks st_scale worked really nice! To reduce x and y by 100 I used UPDATE mytable SET geom= ST_Scale(geom, 0.01, 0.01) Super! Ciao -- View this message in context: http://postgis.17.x6.nabble.com/how-to-move-geometries-to-a-fraction-of-their-position-tp5004728p5004731.html Sent from

[postgis-users] Query performance really slow..

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

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

[image: Immagine in linea 1] 2014-03-13 15:18 GMT+01:00 Pietro Rossin : > "GroupAggregate (cost=324001.58..329989.92 rows=21516 width=28454)" > " -> Sort (cost=324001.58..324055.53 rows=21582 width=28454)" > "Sort Key: bacini.codic

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

om) as geom, > descrizione FROM varie.particellepac2006 group by 2) as colture group by > 2,3,4,5; > > March 13 2014 11:00 AM, "Pietro Rossin" wrote: > > > Hi all > > I'm trying to query 2 vector layers; > > b) bacini_elementari that is river basins

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

t, but if you are using indexes in both columns St_Intersects will > preform the same with the boundaries before doing it with the real > geometries. > > Alexandre Neto > > > On Thu, Mar 13, 2014 at 2:42 PM, Humberto Cereser Ibanez <[hidden > email]<http://user/SendEm

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

+s17n5005881...@n6.nabble.com>: > You're doing it wrong. > > > http://gis.stackexchange.com/questions/31310/acquiring-arcgis-like-speed-in-postgis/31562#31562 > > On Thu, Mar 13, 2014 at 6:59 AM, Pietro Rossin <[hidden > email]<http://user/SendEmail.jtp?type=nod

[postgis-users] Can't load aggregation query in qgis - row_number() over() as id

Hello all I need to aggregate geometries and load them in qgis I made a query like this, without success.. SELECT row_number() over() as id, st_collect(geom) as geom, field3, field4, field5, field6 FROM schema.table where field6 is not null group by 3,4,5,6 Qgis don't load the layer with m

Re: [postgis-users] Can't load aggregation query in qgis - row_number() over() as id

4,5,6 > ) AS sub > > Cheers, > Rémi-C > > > > 2014-03-27 17:27 GMT+01:00 Pietro Rossin <[hidden > email]<http://user/SendEmail.jtp?type=node&node=5005982&i=0> > >: > >> Hello all >> I need to aggregate geometries and load them in qgis

Re: [postgis-users] Can't load aggregation query in qgis - row_number() over() as id

Sorry the second link is this https://dl.dropboxusercontent.com/u/15885512/strangequeryresult2.jpg 2014-03-28 11:14 GMT+01:00 Pietro Rossin : > Thanks Remi > of course I can materialize the query, but I don't want to create n new > tables.. > The incapsulation don't wo

Re: [postgis-users] Can't load aggregation query in qgis - row_number() over() as id

is you could force your query output geometry type to multi > of one type, > for example : ST_Multi(ST_CollectionExtract(geom,2)) enforce that output > type is multi line. > > Cheers, > Rémi-C > > > 2014-03-28 11:15 GMT+01:00 Pietro Rossin <[hidden > email]<htt

[postgis-users] function trigger to populate values based on spatial relationships

Hi all I'm trying to write a trigger function to automatically populate data for some fields based on spatial relationships I want to control if the new point falls inside a set of polygons (monitoraggio_acque.corpi_idrici_cw_tw) and if positive I want to take a value to be written in "cod_ci_sup"

Re: [postgis-users] function trigger to populate values based on spatial relationships

Hello stupid me I added the trigger to the wrong table! I realized this because I tried to insert data in that wrong table and the trigger didn't find the correct field to populate.. By the way.. The function now works perfectly Thanks and sorry for the bother ;-) -- View this mes

[postgis-users] unable to modyfy records after trigger execution

Hello everybody I'm absolutely not a postgresql/postgis guru I need to populate a postgresql/postgis table with microsoft access. In Qgis I add the geometries and then I use MS Access to add most of the tabular data. To map the user who makes the last modification to the values I made a trigger fu

Re: [postgis-users] unable to modify records after trigger execution

Hello I solved the problem... I had another function that had the asme problem, after the trigger execution no record was editable in Ms Access and the new record could be edited in qgis/pgadmin but qgis couldn't delete it I solved the block with quotation of these rows --NEW.utente_inser = (sele

Re: [postgis-users] unable to modify records after trigger execution

Hello everybody I got the answer to the trigger problem by myself digging around the web.. This is the problem with access >= 2007 http://www.postgresql.org/message-id/a434c531e37ad442815608a769550d8059425a3...@egexcmb01.oww.root.lcl The problem is that Access cannot handle date field type with