[Argentina] Consulta base de datos espacial en Postgis

[Hernan Alperin]

Hola

 primero podés convertir los linestring a polygon como explican acá
https://postgis.net/docs/ST_MakePolygon.html

 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 <
gdannen0 at rosario.gov.ar>:

> 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
> Argentina at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/argentina
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/argentina/attachments/20170628/bf095a4e/attachment.html>