Re: capturar valores de campos en variables usando consulta espacial

Hola Alvaro
gracias quedo trabajando perfectamente ya estan rotando los objetos
correctamente al añadirlos al plano

sin embargo ahora como el dato se lee de un campo para la rotacion en QGIS
por momentos se hace lento

esto se ha solucionado en parte aumentando temp_buffer de 8 a 16 Mb q
parametros me sugieren modificar para el caso de un uso intensivo de select
que estan devolviendo alrededor de 5000 registros por consulta y se graban
inserts de arededor de 50 registros por vez

lamentablemente no se de que forma QGis hace los insert

gracias por las sugerencias q pudieran brindarme

2015-09-22 13:04 GMT-05:00 Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>:

> jvenegasperu . escribió:
>
> > -- aqui hago un select por cada variable por favor ayudenme como podria
> > poner cada campo en una variable
> >
> > gidagua = (select gid from ap_dist_agua b where
> > st_intersects(st_buffer(new.the_geom,0.5),b.the_geom) and new.the_geom &&
> > b.the_geom limit 1);
> > iniagua = (select st_startpoint(b.the_geom) from ap_dist_agua b where
> > st_intersects(st_buffer(new.the_geom,0.5),b.the_geom) and new.the_geom &&
> > b.the_geom limit 1);
> > finagua = (select st_endpoint(b.the_geom) from ap_dist_agua b where
> > st_intersects(st_buffer(new.the_geom,0.5),b.the_geom) and new.the_geom &&
> > b.the_geom limit 1);
> > xiagua = (select st_x(st_startpoint(b.the_geom)) from ap_dist_agua b
> > where st_intersects(st_buffer(new.the_geom,0.5),b.the_geom) and
> > new.the_geom && b.the_geom limit 1);
> > xfagua = (select st_x(st_endpoint(b.the_geom)) from ap_dist_agua b
> where
> > st_intersects(st_buffer(new.the_geom,0.5),b.the_geom) and new.the_geom &&
> > b.the_geom limit 1);
> > yiagua = (select st_y(st_startpoint(b.the_geom)) from ap_dist_agua b
> > where st_intersects(st_buffer(new.the_geom,0.5),b.the_geom) and
> > new.the_geom && b.the_geom limit 1);
> > yfagua = (select st_y(st_endpoint(b.the_geom)) from ap_dist_agua b
> where
> > st_intersects(st_buffer(new.the_geom,0.5),b.the_geom) and new.the_geom &&
> > b.the_geom limit 1);
> > rotagua = (select rotacion from ap_dist_agua b where
> > st_intersects(st_buffer(new.the_geom,0.5),b.the_geom) and new.the_geom &&
> > b.the_geom limit 1);
>
> esta parte es fácil. Usa un record.
>
> DECLARE
> r record
> ...
>
> select into r
> gid,
> st_startpoint(b.the_geom) as startpoint,
> st_endpoint(b.the_geom) as endpoint
> from ap_dist_agua where ...
>
> gidagua = r.gid;
> iniagua = r.startpoint;
> finagua = r.endpoint;
> xiagua = st_x(r.startpoint);
> yfagua = st_x(r.startpoint);
> ... etc ...
>
> El rotagua seguramente se puede agregar en la misma consulta de forma
> similar.
>
>
> > -- aqui uso el gid del agua anterior
> >
> > gidagua2 = (select gid from ap_dist_agua b where
> > st_intersects(st_buffer(new.the_geom,0.5),b.the_geom) and new.the_geom &&
> > b.the_geom and b.gid != gidagua limit 1);
> > iniagua2 = (select st_startpoint(b.the_geom) from ap_dist_agua b where
> > st_intersects(st_buffer(new.the_geom,0.5),b.the_geom) and new.the_geom &&
> > b.the_geom and b.gid != gidagua limit 1);
> > finagua2 = (select st_endpoint(b.the_geom) from ap_dist_agua b where
> > st_intersects(st_buffer(new.the_geom,0.5),b.the_geom) and new.the_geom &&
> > b.the_geom and b.gid != gidagua limit 1);
> > xiagua2 = (select st_x(st_startpoint(b.the_geom)) from ap_dist_agua b
> > where st_intersects(st_buffer(new.the_geom,0.5),b.the_geom) and
> > new.the_geom && b.the_geom and b.gid != gidagua limit 1);
> > xfagua2 = (select st_x(st_endpoint(b.the_geom)) from ap_dist_agua b
> where
> > st_intersects(st_buffer(new.the_geom,0.5),b.the_geom) and new.the_geom &&
> > b.the_geom and b.gid != gidagua limit 1);
> > yiagua2 = (select st_y(st_startpoint(b.the_geom)) from ap_dist_agua b
> > where st_intersects(st_buffer(new.the_geom,0.5),b.the_geom) and
> > new.the_geom && b.the_geom and b.gid != gidagua limit 1);
> > yfagua2 = (select st_y(st_endpoint(b.the_geom)) from ap_dist_agua b
> where
> > st_intersects(st_buffer(new.the_geom,0.5),b.the_geom) and new.the_geom &&
> > b.the_geom and b.gid != gidagua limit 1);
> > rotagua2 = (select rotacion from ap_dist_agua b where
> > st_intersects(st_buffer(new.the_geom,0.5),b.the_geom) and new.the_geom &&
> > b.the_geom and b.gid != gidagua limit 1);
>
> Misma cosa; acá puedes usar un nuevo r2 o bien usar el mismo r que en la
> consulta anterior, ya que extrajiste todos los valores de interés.
>
> > -- aqui uso los gid del agua 1 y 2
> > gidagua3 = (select gid from ap_dist_agua b where
> > st_intersects(st_buffer(new.the_geom,0.5),b.the_geom) and new.the_geom &&
> > b.the_geom and b.gid != gidagua and b.gid != gidagua2 limit 1);
> > iniagua3 = (select st_startpoint(b.the_geom) from ap_dist_agua b where
> > st_intersects(st_buffer(new.the_geom,0.5),b.the_geom) and new.the_geom &&
> > b.the_geom and b.gid != gidagua and b.gid != gidagua2 limit 1);
> > finagua3 = (select st_endpoint(b.the_geom) from ap_dist_agua b where
> > st_intersects(st_buffer(new.the_geom,0.5),b.the_geom) and new.the_geom &&
> > b.the_geom and b.gid != gidagua and b.gid != gidagua2 limit 1);
> > xiagua3 = (select st_x(st_startpoint(b.the_geom)) from ap_dist_agua b
> > where st_intersects(st_buffer(new.the_geom,0.5),b.the_geom) and
> > new.the_geom && b.the_geom and b.gid != gidagua and b.gid != gidagua2
> limit
> > 1);
> > xfagua3 = (select st_x(st_endpoint(b.the_geom)) from ap_dist_agua b
> where
> > st_intersects(st_buffer(new.the_geom,0.5),b.the_geom) and new.the_geom &&
> > b.the_geom and b.gid != gidagua and b.gid != gidagua2 limit 1);
> > yiagua3 = (select st_y(st_startpoint(b.the_geom)) from ap_dist_agua b
> > where st_intersects(st_buffer(new.the_geom,0.5),b.the_geom) and
> > new.the_geom && b.the_geom and b.gid != gidagua and b.gid != gidagua2
> limit
> > 1);
> > yfagua3 = (select st_y(st_endpoint(b.the_geom)) from ap_dist_agua b
> where
> > st_intersects(st_buffer(new.the_geom,0.5),b.the_geom) and new.the_geom &&
> > b.the_geom and b.gid != gidagua and b.gid != gidagua2 limit 1);
> > rotagua3 = (select st_y(st_endpoint(b.the_geom)) from ap_dist_agua b
> > where st_intersects(st_buffer(new.the_geom,0.5),b.the_geom) and
> > new.the_geom && b.the_geom and b.gid != gidagua and b.gid != gidagua2
> limit
> > 1);
>
> Ni idea si es necesario hacer un nuevo select o basta con hacer cálculos
> sobre los valores que ya extrajiste.
>
>
>
> --
> Álvaro Herrera http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

--
José Mercedes Venegas Acevedo
cel Mov RPM #955853768

mails: jvenegasperu(at)gmail(dot)com