Consulta base de datos espacial en Postgis

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Consulta base de datos espacial en Postgis

Dannenberg Guillermo Adrián
Buenas tarde, quizas alguien me pueda dar una mano, ya que arranque hace
poco en el tema.

En una base de datos espacial en Postgis, tengo dos tablas:
Una tabla llamada forestal_ln la cual contiene LINESTRING.
Una tabla llamada forestal que cuenta con algunos campos y dos campos
geométricos: 'centroide' que contiene geometría de tipo POINT y
'geometria' que contiene geométrica de tipo MULTIPOLYGON.
En donde cada centroide de la tabla forestal se encuentra encerrado por
varias líneas de la tabla forestal_ln.
Les agradecería mucho si pudieran orientarme en:
1) ¿qué procesos podría correr para asegurarme de que esas lineas
verdaderamente encierran un solo centroide? Verificar la topologia.
2) (más importante) ¿cómo puedo crear los polígonos a partir de esas
lineas y actualizar con esos polígonos creados el campo geometria de la
tabla forestal?

Los datos con los cuales estoy trabajando:

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 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));

Gracias y saludos..

Guillermo A. Dannenberg

_______________________________________________
Argentina mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/argentina
Reply | Threaded
Open this post in threaded view
|

Re: Consulta base de datos espacial en Postgis

Hernan Alperin
Hola

 primero podés convertir los linestring a polygon como explican acá

 pero tu ejemplo tiene lineas de 2 puntos y se necesitan 4 puntos al menos para armar polygons (triángulos, punto incial debe ser = punto final, ser cerrados)

halpe=# select id, ST_MakePolygon(geometria) from forestal_ln;
ERROR:  lwpoly_from_lwlines: shell must have at least 4 points

-h
 
 

2017-06-27 13:42 GMT-03:00 Dannenberg Guillermo Adrián <[hidden email]>:
Buenas tarde, quizas alguien me pueda dar una mano, ya que arranque hace poco en el tema.

En una base de datos espacial en Postgis, tengo dos tablas:
Una tabla llamada forestal_ln la cual contiene LINESTRING.
Una tabla llamada forestal que cuenta con algunos campos y dos campos geométricos: 'centroide' que contiene geometría de tipo POINT y 'geometria' que contiene geométrica de tipo MULTIPOLYGON.
En donde cada centroide de la tabla forestal se encuentra encerrado por varias líneas de la tabla forestal_ln.
Les agradecería mucho si pudieran orientarme en:
1) ¿qué procesos podría correr para asegurarme de que esas lineas verdaderamente encierran un solo centroide? Verificar la topologia.
2) (más importante) ¿cómo puedo crear los polígonos a partir de esas lineas y actualizar con esos polígonos creados el campo geometria de la tabla forestal?

Los datos con los cuales estoy trabajando:

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 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));

Gracias y saludos..

Guillermo A. Dannenberg

_______________________________________________
Argentina mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/argentina


_______________________________________________
Argentina mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/argentina