Re: capturar valores de campos en variables usando consulta espacial

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

-
Enviado a la lista de correo pgsql-es-ayuda (pgsql-es-ayuda(at)postgresql(dot)org)
Para cambiar tu suscripción:
http://www.postgresql.org/mailpref/pgsql-es-ayuda