From: | Néstor Ramires <nramire1(at)rosario(dot)gov(dot)ar> |
---|---|
To: | "pgsql-es-ayuda(at)postgresql(dot)org" <pgsql-es-ayuda(at)postgresql(dot)org> |
Subject: | Tabla con diferencias |
Date: | 2017-07-04 19:55:29 |
Message-ID: | op.y2vayrraocut9v@car-800 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-es-ayuda |
Hola
Tengo dos tablas con geometría de lineas 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));
Utilizando esta sentencia obtengo los 28 registros que si están contenidos
SELECT mineral_ln.id FROM mineral_ln, forestal_ln WHERE ST_Contains(mineral_ln.geometria, forestal_ln.geometria);
¿Qué tendría que modificar para obtener los tres registros (id: 20, 25, 27) que no aparecen en esos 28 si coincidentes?
Saludso
From | Date | Subject | |
---|---|---|---|
Next Message | oscar arocha | 2017-07-05 17:08:46 | Subquery en variable |
Previous Message | jvenegasperu . | 2017-07-04 15:58:04 | Re: Problema con pg_toast |