<!DOCTYPE html><html><head>
<style type="text/css">body { font-family:'Times New Roman'; font-size:13px}</style>
</head>
<body><div>Hola. Ante todo, vengo de trabajar en MicroStation Geographics, mi intención es migrar toda la información a una base de datos postgis y en ese tramo se me presentó este problema. <br></div><div><br></div><div>Tengo un conjunto de tablas en las cuales muchos datos se repiten, sobre todo en las tablas con datos geométricos de tipo LINESTRING<br>Aquí se presentan 2 cuestiones:<br>1) construir polígonos a partir de lineas y los centroides <br>2) simplificar los elementos linea que se repiten en las tablas, de manera de actualizar 1 linea y que esta se actulice en cada lugar donde se replica.<br><br>Las tablas con polígonos y centroides son:<br>forestal<br>mineral<br>particular<br>predio<br>Las tablas de lineas son:<br>forestal_ln<br>mineral_ln<br>particular_ln<br>predio_ln<br><br>Para el punto número 1, por ejemplo para trabajar sobre la tabla predio, la cuestión sería actualizar el campo geometría de la tabla predio con un <br><br>polígono construido a partir de las líneas que rodean al centroide correspondiente. Para el caso de predio deberían construirse 2 polígonos.<br><br>Para el punto número 2, la cuestión sería poder encontrar un mecanismo por el cual al actualizar por ejemplo el registro <br><br>SELECT ST_AsText(geometria) FROM predio_ln WHERE id=1;<br> st_astext<br>---------------------------------------------<br> LINESTRING(5500030 6500290,5500030 6500250)<br>(1 row)<br><br>Se actualicen automáticamente la geometría de la línea homologa en las tablas en donde se encuentren y viceversa. <br><br>SELECT ST_AsText(geometria) FROM forestal_ln WHERE id=2;<br> st_astext<br>---------------------------------------------<br> LINESTRING(5500030 6500290,5500030 6500250)<br>(1 row)<br><br>SELECT ST_AsText(geometria) FROM mineral_ln WHERE id=4;<br> st_astext<br>---------------------------------------------<br> LINESTRING(5500030 6500290,5500030 6500250)<br>(1 row)<br><br>Para éste caso actualizando por ejemplo el campo geometria del registro en la tabla forestal_ln, se actualice en forma autoática el campo geometria de la línea correspondiente en la tabla mineral_ln y el campo geometria de la linea correspondiente en la tabla predio_ln <br><br>Abajo les dejo los datos de ejemplo, desde ya muchas gracias. Saludos<br><br><br>CREATE TABLE forestal(<br>id serial NOT NULL,<br>tipo varchar(6),<br>densidad real,<br>CONSTRAINT forestal_pkey PRIMARY KEY(id)<br>);<br>SELECT AddGeometryColumn ( 'forestal', 'centroide', 22185, 'POINT', 2);<br>SELECT AddGeometryColumn ( 'forestal', 'geometria', 22185, 'MULTIPOLYGON', 2);<br><br>INSERT INTO forestal (tipo,densidad,centroide) VALUES('PMT',85.2,ST_GeomFromText('POINT(5500060 6500180)',22185));<br>INSERT INTO forestal (tipo,densidad,centroide) VALUES('PCRTA',52.42,ST_GeomFromText('POINT(5500080 6500270)',22185));<br>INSERT INTO forestal (tipo,densidad,centroide) VALUES('CA',42.26,ST_GeomFromText('POINT(5500120 6500100)',22185));<br>INSERT INTO forestal (tipo,densidad,centroide) VALUES('AMJ',86.6,ST_GeomFromText('POINT(5500210 6500190)',22185));<br>INSERT INTO forestal (tipo,densidad,centroide) VALUES('ODM',8.6,ST_GeomFromText('POINT(5500220 6500070)',22185));<br>INSERT INTO forestal (tipo,densidad,centroide) VALUES('EMDF',27.8,ST_GeomFromText('POINT(5500220 6500110)',22185));<br>INSERT INTO forestal (tipo,densidad,centroide) VALUES('DHRT',36.27,ST_GeomFromText('POINT(5500220 6500280)',22185));<br>INSERT INTO forestal (tipo,densidad,centroide) VALUES('FM',73.24,ST_GeomFromText('POINT(5500230 6500260)',22185));<br><br><br>CREATE TABLE mineral(<br>id serial NOT NULL,<br>clasificacion varchar(16),<br>uso_actual decimal(10,2),<br>CONSTRAINT mineral_pkey PRIMARY KEY(id)<br>);<br>SELECT AddGeometryColumn ( 'mineral', 'centroide', 22185, 'POINT', 2);<br>SELECT AddGeometryColumn ( 'mineral', 'geometria', 22185, 'MULTIPOLYGON', 2);<br><br>INSERT INTO mineral (clasificacion,uso_actual,centroide) VALUES('AD24_06',26.5,ST_GeomFromText('POINT(5500090 6500130)',22185));<br>INSERT INTO mineral (clasificacion,uso_actual,centroide) VALUES('AD24_03',46.35,ST_GeomFromText('POINT(5500140 6500200)',22185));<br>INSERT INTO mineral (clasificacion,uso_actual,centroide) VALUES('AD24_03',61.3,ST_GeomFromText('POINT(5500130 6500270)',22185));<br>INSERT INTO mineral (clasificacion,uso_actual,centroide) VALUES('AD08_01',53.21,ST_GeomFromText('POINT(5500220 6500100)',22185));<br>INSERT INTO mineral (clasificacion,uso_actual,centroide) VALUES('AB03_01',3.2,ST_GeomFromText('POINT(5500230 6500180)',22185));<br>INSERT INTO mineral (clasificacion,uso_actual,centroide) VALUES('AB03_03',96.2,ST_GeomFromText('POINT(5500230 6500260)',22185));<br><br><br>CREATE TABLE particular(<br>id serial NOT NULL,<br>dispone varchar(16),<br>CONSTRAINT particular_pkey PRIMARY KEY(id)<br>);<br>SELECT AddGeometryColumn ( 'particular', 'centroide', 22185, 'POINT', 2);<br>SELECT AddGeometryColumn ( 'particular', 'geometria', 22185, 'MULTIPOLYGON', 2);<br><br>INSERT INTO particular (dispone,centroide) VALUES('MB4902',ST_GeomFromText('POINT(5500230 6500145)',22185));<br><br><br>CREATE TABLE predio(<br>id serial NOT NULL,<br>id_oficial varchar(20),<br>propietario varchar(100),<br>CONSTRAINT predio_pkey PRIMARY KEY(id)<br>);<br>SELECT AddGeometryColumn ( 'predio', 'centroide', 22185, 'POINT', 2);<br>SELECT AddGeometryColumn ( 'predio', 'geometria', 22185, 'MULTIPOLYGON', 2);<br><br>INSERT INTO predio (id_oficial,propietario,centroide) VALUES('02925','Pepe',ST_GeomFromText('POINT(5500150 6500150)',22185));<br>INSERT INTO predio (id_oficial,propietario,centroide) VALUES('03008','Juan',ST_GeomFromText('POINT(5500160 6500270)',22185));<br><br><br>CREATE TABLE predio_ln(<br>id serial NOT NULL,<br>salida varchar(20),<br>CONSTRAINT predio_ln_pkey PRIMARY KEY(id)<br>);<br>SELECT AddGeometryColumn ( 'predio_ln', 'geometria', 22185, 'LINESTRING', 2);<br>ALTER TABLE predio_ln ADD COLUMN kilometro INTEGER;<br><br>INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('cartilon',500,ST_GeomFromText('LINESTRING(5500030 6500290,5500030 6500250)',22185));<br>INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('cartilon',630,ST_GeomFromText('LINESTRING(5500030 6500230,5500030 6500050)',22185));<br>INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('azul',30,ST_GeomFromText('LINESTRING(5500030 6500050,5500160 6500050)',22185));<br>INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('azul',30,ST_GeomFromText('LINESTRING(5500160 6500050,5500280 6500050)',22185));<br>INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('juan',630,ST_GeomFromText('LINESTRING(5500280 6500090,5500280 6500050)',22185));<br>INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('juan',630,ST_GeomFromText('LINESTRING(5500280 6500090,5500280 6500140)',22185));<br>INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('juan',630,ST_GeomFromText('LINESTRING(5500280 6500150,5500280 6500140)',22185));<br>INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('juan',630,ST_GeomFromText('LINESTRING(5500280 6500150,5500280 6500220)',22185));<br>INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('melester',30,ST_GeomFromText('LINESTRING(5500190 6500220,5500280 6500220)',22185));<br>INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('melester',30,ST_GeomFromText('LINESTRING(5500190 6500230,5500100 6500230)',22185));<br>INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('melester',30,ST_GeomFromText('LINESTRING(5500190 6500220,5500190 6500230)',22185));<br>INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('melester',30,ST_GeomFromText('LINESTRING(5500100 6500230,5500030 6500230)',22185));<br>INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('melester',30,ST_GeomFromText('LINESTRING(5500030 6500250,5500120 6500250)',22185));<br>INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('melester',30,ST_GeomFromText('LINESTRING(5500180 6500250,5500120 6500250)',22185));<br>INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('melester',30,ST_GeomFromText('LINESTRING(5500180 6500250,5500280 6500250)',22185));<br>INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('juan',500,ST_GeomFromText('LINESTRING(5500280 6500250,5500280 6500270)',22185));<br>INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('juan',500,ST_GeomFromText('LINESTRING(5500280 6500290,5500280 6500270)',22185));<br>INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('vastal',30,ST_GeomFromText('LINESTRING(5500280 6500290,5500090 6500290)',22185));<br>INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('vastal',30,ST_GeomFromText('LINESTRING(5500030 6500290,5500090 6500290)',22185));<br><br><br>CREATE TABLE particular_ln(<br>id serial NOT NULL,<br>CONSTRAINT particular_ln_pkey PRIMARY KEY(id)<br>);<br>SELECT AddGeometryColumn ( 'particular_ln', 'geometria', 22185, 'LINESTRING', 2);<br><br>INSERT INTO particular_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500150,5500280 6500140)',22185));<br>INSERT INTO particular_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500150,5500160 6500140)',22185));<br>INSERT INTO particular_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500140,5500280 6500140)',22185));<br>INSERT INTO particular_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500150,5500280 6500150)',22185));<br><br><br>CREATE TABLE forestal_ln(<br>id serial NOT NULL,<br>CONSTRAINT forestal_ln_pkey PRIMARY KEY(id)<br>);<br>SELECT AddGeometryColumn ( 'forestal_ln', 'geometria', 22185, 'LINESTRING', 2);<br><br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500030 6500230,5500030 6500050)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500030 6500290,5500030 6500250)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500030 6500050,5500160 6500050)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500050,5500280 6500050)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500090,5500280 6500050)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500090,5500280 6500140)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500150,5500280 6500140)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500150,5500280 6500220)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500190 6500220,5500280 6500220)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500190 6500230,5500100 6500230)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500190 6500220,5500190 6500230)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500100 6500230,5500030 6500230)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500030 6500250,5500120 6500250)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500180 6500250,5500120 6500250)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500180 6500250,5500280 6500250)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500250,5500280 6500270)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500290,5500280 6500270)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500290,5500090 6500290)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500030 6500290,5500090 6500290)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500090 6500290,5500180 6500270)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500180 6500270,5500220 6500270)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500220 6500270,5500120 6500250)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500220 6500270,5500280 6500270)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500100 6500230,5500100 6500170)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500100 6500170,5500030 6500050)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500100 6500170,5500160 6500170)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500170,5500160 6500150)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500150,5500160 6500140)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500140,5500280 6500140)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500140,5500160 6500090)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500090,5500280 6500090)',22185));<br>INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500090,5500160 6500050)',22185));<br><br><br>CREATE TABLE mineral_ln(<br>id serial NOT NULL,<br>CONSTRAINT mineral_ln_pkey PRIMARY KEY(id)<br>);<br>SELECT AddGeometryColumn ( 'mineral_ln', 'geometria', 22185, 'LINESTRING', 2);<br><br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500030 6500230,5500030 6500050)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500030 6500050,5500160 6500050)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500050,5500280 6500050)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500030 6500290,5500030 6500250)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500090,5500280 6500050)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500090,5500280 6500140)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500150,5500280 6500140)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500150,5500280 6500220)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500190 6500220,5500280 6500220)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500190 6500230,5500100 6500230)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500190 6500220,5500190 6500230)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500100 6500230,5500030 6500230)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500030 6500250,5500120 6500250)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500180 6500250,5500120 6500250)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500180 6500250,5500280 6500250)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500250,5500280 6500270)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500290,5500280 6500270)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500290,5500090 6500290)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500030 6500290,5500090 6500290)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500180 6500250,5500180 6500270)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500180 6500270,5500220 6500270)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500220 6500270,5500280 6500270)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500100 6500230,5500100 6500170)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500100 6500170,5500160 6500170)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500170,5500190 6500170)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500170,5500160 6500150)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500190 6500170,5500190 6500220)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500150,5500160 6500140)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500140,5500280 6500140)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500140,5500160 6500090)',22185));<br>INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500090,5500160 6500050)',22185));<br><br><br></div></body></html>