You are here:GeoTux»Geo-Foros»Diferencias geométricas entre líneas

Estadísticas

Invitados: 37
Usuarios registrados: 3145
Usuarios en línea:
-
Registrados hoy:
-

Registro

Redifusión (RSS)

Blogs y Noticias:
Recibe las actualizaciones en Geo-Noticias y Geo-Blogs

Recibir por e-mail
Recibir Geo-Noticias y Geo-Blogs por e-mail

¿Qué es esto?

Welcome, Guest
Username Password: Remember me

Diferencias geométricas entre líneas
(1 viewing) (1) Guest
  • Page:
  • 1

TOPIC: Diferencias geométricas entre líneas

Diferencias geométricas entre líneas 11 Jul 2017 16:08 #1237

  • nramire1
  • OFFLINE
  • Visitante casual
  • Posts: 6
  • Karma: 1
Hola
Tengo dos tablas con geometría de líneas la tabla forestal_ln con sus respectivos datos y la tabla mineral_ln con sus datos.
¿Como puedo obtener una tercer tabla con las líneas que se encuentran en la tabla mineral_ln y no están en la tabla forestal_ln?
Abajo dejo los datos con los cuales estoy trabajando.
Saludos


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

Re: Diferencias geométricas entre líneas 11 Jul 2017 16:13 #1238

  • nramire1
  • OFFLINE
  • Visitante casual
  • Posts: 6
  • Karma: 1
Utilizando esta sentencia obtengo los 28 registros de la tabla mineral_ln que si están "duplicados" en la tabla forestal_ln

SELECT mineral_ln.id FROM mineral_ln, forestal_ln WHERE ST_Contains(mineral_ln.geometria, forestal_ln.geometria);

¿Qué tendría que modificar en la consulta para obtener los tres registros (id: 20, 25, 27 de la tabla mineral_ln) que no aparecen en esos 28 registros?

Re: Diferencias geométricas entre líneas 10 Ago 2017 05:45 #1241

  • samtux
  • OFFLINE
  • Entusiasta
  • Posts: 40
  • Karma: 1
Completando la respuesta @nramire1. Para generar una tercera tabla que seleccione los elementos que no contiene la capa minerales en forestal, sería como lo siguiente:

 
SELECT id, geometria FROM mineral_ln m
WHERE id NOT IN (
SELECT DISTINCT m.id
FROM mineral_ln m, forestal_ln f
WHERE ST_CONTAINS(f.geometria ,m.geometria)
)
 


Aquí la salida en QGIS usando Spatialite:



Para la descarga del proyecto QGIS use:

https://www.dropbox.com/s/olhta30ahap94h...tal_mineral.zip?dl=1
Ing. Samuel Mesa
Last Edit: 10 Ago 2017 05:54 by samtux. Reason: Usuario nombre
The following user(s) said Thank You: nramire1

Re: Diferencias geométricas entre líneas 10 Ago 2017 12:39 #1242

  • nramire1
  • OFFLINE
  • Visitante casual
  • Posts: 6
  • Karma: 1
¡Muy buena! Eso era lo que estaba buscando.
  • Page:
  • 1
Time to create page: 0.65 seconds
 

¿Dónde nos leen?

Usuario aleatorio

Últimos comentarios