[QGIS-it-user] Qgis&Postgresql - Vista editabile con geometria

[Falz]

Condivido riportando una breve demo didattica per concludere il discorso
delle viste editabili, da usare ad esempio in Qgis, il codice qui sotto può
essere copiato e incollato direttamente nella shell di Postgresql:

/*Places table*/
create table myschema.places
(id serial primary key,
nameplace character varying(100),
sectornumber integer);
select AddGeometryColumn('myschema','places','geom',3064,'POLYGON',2);

/*Site table*/
create table myschema.site
(id serial primary key,
verified boolean,
name character varying(100),
address character varying(100),
note text);
select AddGeometryColumn('myschema','site','geom',3064,'POINT',2);
create index idx_site_geom on myschema.site using gist (geom);


/*Myview view*/
create view myschema.myview as
select
id,
geom,
verified,
name,
address,
note,
(select p.nameplace from myschema.places p where
st_intersects(myschema.site.geom, p.geom)) as place,
(select p.sectornumber from myschema.places p where
st_intersects(myschema.site.geom, p.geom)) as sector,
st_astext(st_transform(geom,4326)) as wgs84_ddd
from myschema.site;


/*Rule for Deleting*/
create or replace rule site_delete as
on delete to myschema.myview do instead
delete from myschema.site where site.id = old.id;

/*Rule for Updating*/
create or replace rule site_update as
on update to myschema.myview do instead
update myschema.site
set
verified = new.verified,
name = new.name,
address = new.address,
note = new.note,
geom = new.geom
where site.id = new.id;

/*Rule for Inserting*/
create or replace rule site_insert as
on insert to myschema.myview do instead
insert into myschema.site (id, verified, name, address, note, geom)
values
(nextval('myschema.site_id_seq'::regclass), new.verified, new.name,
new.address, new.note, st_force2d(new.geom))
returning /*lista i campi della tabella ordinati secondo l'ordine della
vista*/
site.id,
site.geom,
site.verified,
site.name,
site.address,
site.note,
''::character varying(100) as place,
null::integer as sector,
''::text as wgs84_ddd;




-----
Falz
--
Sent from: http://osgeo-org.1560.x6.nabble.com/QGIS-Italian-User-f5250612.html