Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 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, 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 :

enter image description here

share|improve this question
st_intersects returns true/false (vs st_intersection) and your image is confusing, since there is none between A and B. Are their gids really not integers? What have you tried so far? – lynxlynxlynx Sep 30 '13 at 20:44
My mistake! I wanted to write st_intersection. st_intersection returns a geometrycoletion. I've updated the question. – nickves Sep 30 '13 at 20:59
Woah, where did the squares came from? What does the circle represent? – Jakub Kania Oct 1 '13 at 6:04
Hmm it seems that the question is not clear enough! i'll try to explain it a bit a more. – nickves Oct 1 '13 at 7:29
up vote 2 down vote accepted

I think you're looking for this:

create tableC as 
  (select st_intersection(a.geom, b.geom) as geom, as name 
   from tableA as a, tableB as b
   where st_intersects(a.geom, b.geom));

By looking at each intersection separately, you can access all the included data.

share|improve this answer
It should be b.geom instead of g.geom in your query – afalciano Oct 1 '13 at 8:47
right, thanks - c/p error. – lynxlynxlynx Oct 1 '13 at 8:49
duh yes, I need to refresh my SQLs. – nickves Oct 1 '13 at 10:51

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.