Tabla con diferencias (Table with differences)

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Tabla con diferencias (Table with differences)

Z.M.
Hello
I have two tables with line geometry the table forestal_ln with their respective data and the table mineral_ln with their data.
How do I get a third table with the lines that are in the mineral_ln table and are not in the forestal_ln table?
Below I leave the data with which I am working.

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.



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

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Tabla con diferencias (Table with differences)

Z.M.
Using this statement I get the 28 records in the table mineral_ln that if they are "duplicated" in the table Forestal_ln

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

What would you have to modify in the query to get the three records (ID: 20, 25, 27 of the MINERAL_LN table) that do not appear in those 28 records?


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?



En Tue, 04 Jul 2017 13:18:24 -0300, Néstor Ramires <[hidden email]> escribió:

> Hello
> I have two tables with line geometry the table forestal_ln with their respective data and the table mineral_ln with their data.
> How do I get a third table with the lines that are in the mineral_ln table and are not in the forestal_ln table?
> Below I leave the data with which I am working.
>
> 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.
>
>
>
> 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));
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Loading...