Let's say that we have two tables with polygons representing areas. Table A and Table B. Geometries from table A represents both the valid and invalid areas. And the geometries in table B represents the invalid ones.
For example, lets say that the geometries from table A represent, harvesting fields, and table B represents roads, shacks and generally areas that the harvesting is impossible for w/e reason (as polygons). Table B represents some kind of mask that's has being digitized differently.
The problem arises that the mask, can pass through many fields. So I want to clip that mask (Table B), with the field polygon (Table A) to create a new set (Table C) consisting with the geometries that represents the masks 'per field' along with the name of the field it was cut from.
This can be done by
create tableC as (select st_intersection(a.geom,b.geom) geom from tableA as a, tableB as b);
But I have troubles on how to select the tableA.name as well and transferring it to tableC.
the selection part of an ideal querry would be like this:
create tableC as (select st_intersection(a.geom,g.geom) geom, tableA.name as name from .... );
A sample defintion of table C is the following:
Column | Type | gid | int | geom | geometry | <- st_intersetion(a.geom,b.geom) name |varchar(255) | <- name of the feature of table A which took part in the st_intersection
Table A is defined as following:
Column | Type | gid | int | name | varchar(255)| geom | geometry |
And Table B as follows:
Column | Type | gid | int | geom | geometry |
And for a visual representation :