[postgis-users] Topología Aplicada
Néstor Ramires
nramire1 at rosario.gov.ar
Thu Apr 20 07:31:13 PDT 2017
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.
Tengo un conjunto de tablas en las cuales muchos datos se repiten, sobre todo en las tablas con datos geométricos de tipo LINESTRING
Aquí se presentan 2 cuestiones:
1) construir polígonos a partir de lineas y los centroides
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.
Las tablas con polígonos y centroides son:
forestal
mineral
particular
predio
Las tablas de lineas son:
forestal_ln
mineral_ln
particular_ln
predio_ln
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
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.
Para el punto número 2, la cuestión sería poder encontrar un mecanismo por el cual al actualizar por ejemplo el registro
SELECT ST_AsText(geometria) FROM predio_ln WHERE id=1;
st_astext
---------------------------------------------
LINESTRING(5500030 6500290,5500030 6500250)
(1 row)
Se actualicen automáticamente la geometría de la línea homologa en las tablas en donde se encuentren y viceversa.
SELECT ST_AsText(geometria) FROM forestal_ln WHERE id=2;
st_astext
---------------------------------------------
LINESTRING(5500030 6500290,5500030 6500250)
(1 row)
SELECT ST_AsText(geometria) FROM mineral_ln WHERE id=4;
st_astext
---------------------------------------------
LINESTRING(5500030 6500290,5500030 6500250)
(1 row)
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
Abajo les dejo los datos de ejemplo, desde ya muchas gracias. Saludos
CREATE TABLE forestal(
id serial NOT NULL,
tipo varchar(6),
densidad real,
CONSTRAINT forestal_pkey PRIMARY KEY(id)
);
SELECT AddGeometryColumn ( 'forestal', 'centroide', 22185, 'POINT', 2);
SELECT AddGeometryColumn ( 'forestal', 'geometria', 22185, 'MULTIPOLYGON', 2);
INSERT INTO forestal (tipo,densidad,centroide) VALUES('PMT',85.2,ST_GeomFromText('POINT(5500060 6500180)',22185));
INSERT INTO forestal (tipo,densidad,centroide) VALUES('PCRTA',52.42,ST_GeomFromText('POINT(5500080 6500270)',22185));
INSERT INTO forestal (tipo,densidad,centroide) VALUES('CA',42.26,ST_GeomFromText('POINT(5500120 6500100)',22185));
INSERT INTO forestal (tipo,densidad,centroide) VALUES('AMJ',86.6,ST_GeomFromText('POINT(5500210 6500190)',22185));
INSERT INTO forestal (tipo,densidad,centroide) VALUES('ODM',8.6,ST_GeomFromText('POINT(5500220 6500070)',22185));
INSERT INTO forestal (tipo,densidad,centroide) VALUES('EMDF',27.8,ST_GeomFromText('POINT(5500220 6500110)',22185));
INSERT INTO forestal (tipo,densidad,centroide) VALUES('DHRT',36.27,ST_GeomFromText('POINT(5500220 6500280)',22185));
INSERT INTO forestal (tipo,densidad,centroide) VALUES('FM',73.24,ST_GeomFromText('POINT(5500230 6500260)',22185));
CREATE TABLE mineral(
id serial NOT NULL,
clasificacion varchar(16),
uso_actual decimal(10,2),
CONSTRAINT mineral_pkey PRIMARY KEY(id)
);
SELECT AddGeometryColumn ( 'mineral', 'centroide', 22185, 'POINT', 2);
SELECT AddGeometryColumn ( 'mineral', 'geometria', 22185, 'MULTIPOLYGON', 2);
INSERT INTO mineral (clasificacion,uso_actual,centroide) VALUES('AD24_06',26.5,ST_GeomFromText('POINT(5500090 6500130)',22185));
INSERT INTO mineral (clasificacion,uso_actual,centroide) VALUES('AD24_03',46.35,ST_GeomFromText('POINT(5500140 6500200)',22185));
INSERT INTO mineral (clasificacion,uso_actual,centroide) VALUES('AD24_03',61.3,ST_GeomFromText('POINT(5500130 6500270)',22185));
INSERT INTO mineral (clasificacion,uso_actual,centroide) VALUES('AD08_01',53.21,ST_GeomFromText('POINT(5500220 6500100)',22185));
INSERT INTO mineral (clasificacion,uso_actual,centroide) VALUES('AB03_01',3.2,ST_GeomFromText('POINT(5500230 6500180)',22185));
INSERT INTO mineral (clasificacion,uso_actual,centroide) VALUES('AB03_03',96.2,ST_GeomFromText('POINT(5500230 6500260)',22185));
CREATE TABLE particular(
id serial NOT NULL,
dispone varchar(16),
CONSTRAINT particular_pkey PRIMARY KEY(id)
);
SELECT AddGeometryColumn ( 'particular', 'centroide', 22185, 'POINT', 2);
SELECT AddGeometryColumn ( 'particular', 'geometria', 22185, 'MULTIPOLYGON', 2);
INSERT INTO particular (dispone,centroide) VALUES('MB4902',ST_GeomFromText('POINT(5500230 6500145)',22185));
CREATE TABLE predio(
id serial NOT NULL,
id_oficial varchar(20),
propietario varchar(100),
CONSTRAINT predio_pkey PRIMARY KEY(id)
);
SELECT AddGeometryColumn ( 'predio', 'centroide', 22185, 'POINT', 2);
SELECT AddGeometryColumn ( 'predio', 'geometria', 22185, 'MULTIPOLYGON', 2);
INSERT INTO predio (id_oficial,propietario,centroide) VALUES('02925','Pepe',ST_GeomFromText('POINT(5500150 6500150)',22185));
INSERT INTO predio (id_oficial,propietario,centroide) VALUES('03008','Juan',ST_GeomFromText('POINT(5500160 6500270)',22185));
CREATE TABLE predio_ln(
id serial NOT NULL,
salida varchar(20),
CONSTRAINT predio_ln_pkey PRIMARY KEY(id)
);
SELECT AddGeometryColumn ( 'predio_ln', 'geometria', 22185, 'LINESTRING', 2);
ALTER TABLE predio_ln ADD COLUMN kilometro INTEGER;
INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('cartilon',500,ST_GeomFromText('LINESTRING(5500030 6500290,5500030 6500250)',22185));
INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('cartilon',630,ST_GeomFromText('LINESTRING(5500030 6500230,5500030 6500050)',22185));
INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('azul',30,ST_GeomFromText('LINESTRING(5500030 6500050,5500160 6500050)',22185));
INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('azul',30,ST_GeomFromText('LINESTRING(5500160 6500050,5500280 6500050)',22185));
INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('juan',630,ST_GeomFromText('LINESTRING(5500280 6500090,5500280 6500050)',22185));
INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('juan',630,ST_GeomFromText('LINESTRING(5500280 6500090,5500280 6500140)',22185));
INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('juan',630,ST_GeomFromText('LINESTRING(5500280 6500150,5500280 6500140)',22185));
INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('juan',630,ST_GeomFromText('LINESTRING(5500280 6500150,5500280 6500220)',22185));
INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('melester',30,ST_GeomFromText('LINESTRING(5500190 6500220,5500280 6500220)',22185));
INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('melester',30,ST_GeomFromText('LINESTRING(5500190 6500230,5500100 6500230)',22185));
INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('melester',30,ST_GeomFromText('LINESTRING(5500190 6500220,5500190 6500230)',22185));
INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('melester',30,ST_GeomFromText('LINESTRING(5500100 6500230,5500030 6500230)',22185));
INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('melester',30,ST_GeomFromText('LINESTRING(5500030 6500250,5500120 6500250)',22185));
INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('melester',30,ST_GeomFromText('LINESTRING(5500180 6500250,5500120 6500250)',22185));
INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('melester',30,ST_GeomFromText('LINESTRING(5500180 6500250,5500280 6500250)',22185));
INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('juan',500,ST_GeomFromText('LINESTRING(5500280 6500250,5500280 6500270)',22185));
INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('juan',500,ST_GeomFromText('LINESTRING(5500280 6500290,5500280 6500270)',22185));
INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('vastal',30,ST_GeomFromText('LINESTRING(5500280 6500290,5500090 6500290)',22185));
INSERT INTO predio_ln (salida,kilometro,geometria) VALUES('vastal',30,ST_GeomFromText('LINESTRING(5500030 6500290,5500090 6500290)',22185));
CREATE TABLE particular_ln(
id serial NOT NULL,
CONSTRAINT particular_ln_pkey PRIMARY KEY(id)
);
SELECT AddGeometryColumn ( 'particular_ln', 'geometria', 22185, 'LINESTRING', 2);
INSERT INTO particular_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500150,5500280 6500140)',22185));
INSERT INTO particular_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500150,5500160 6500140)',22185));
INSERT INTO particular_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500140,5500280 6500140)',22185));
INSERT INTO particular_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500150,5500280 6500150)',22185));
CREATE TABLE forestal_ln(
id serial NOT NULL,
CONSTRAINT forestal_ln_pkey PRIMARY KEY(id)
);
SELECT AddGeometryColumn ( 'forestal_ln', 'geometria', 22185, 'LINESTRING', 2);
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500030 6500230,5500030 6500050)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500030 6500290,5500030 6500250)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500030 6500050,5500160 6500050)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500050,5500280 6500050)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500090,5500280 6500050)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500090,5500280 6500140)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500150,5500280 6500140)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500150,5500280 6500220)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500190 6500220,5500280 6500220)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500190 6500230,5500100 6500230)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500190 6500220,5500190 6500230)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500100 6500230,5500030 6500230)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500030 6500250,5500120 6500250)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500180 6500250,5500120 6500250)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500180 6500250,5500280 6500250)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500250,5500280 6500270)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500290,5500280 6500270)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500290,5500090 6500290)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500030 6500290,5500090 6500290)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500090 6500290,5500180 6500270)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500180 6500270,5500220 6500270)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500220 6500270,5500120 6500250)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500220 6500270,5500280 6500270)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500100 6500230,5500100 6500170)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500100 6500170,5500030 6500050)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500100 6500170,5500160 6500170)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500170,5500160 6500150)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500150,5500160 6500140)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500140,5500280 6500140)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500140,5500160 6500090)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500090,5500280 6500090)',22185));
INSERT INTO forestal_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500090,5500160 6500050)',22185));
CREATE TABLE mineral_ln(
id serial NOT NULL,
CONSTRAINT mineral_ln_pkey PRIMARY KEY(id)
);
SELECT AddGeometryColumn ( 'mineral_ln', 'geometria', 22185, 'LINESTRING', 2);
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500030 6500230,5500030 6500050)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500030 6500050,5500160 6500050)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500050,5500280 6500050)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500030 6500290,5500030 6500250)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500090,5500280 6500050)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500090,5500280 6500140)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500150,5500280 6500140)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500150,5500280 6500220)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500190 6500220,5500280 6500220)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500190 6500230,5500100 6500230)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500190 6500220,5500190 6500230)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500100 6500230,5500030 6500230)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500030 6500250,5500120 6500250)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500180 6500250,5500120 6500250)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500180 6500250,5500280 6500250)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500250,5500280 6500270)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500290,5500280 6500270)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500280 6500290,5500090 6500290)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500030 6500290,5500090 6500290)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500180 6500250,5500180 6500270)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500180 6500270,5500220 6500270)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500220 6500270,5500280 6500270)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500100 6500230,5500100 6500170)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500100 6500170,5500160 6500170)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500170,5500190 6500170)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500170,5500160 6500150)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500190 6500170,5500190 6500220)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500150,5500160 6500140)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500140,5500280 6500140)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500140,5500160 6500090)',22185));
INSERT INTO mineral_ln (geometria) VALUES(ST_GeomFromText('LINESTRING(5500160 6500090,5500160 6500050)',22185));
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170420/b8640c67/attachment.html>
More information about the postgis-users
mailing list