1

I am trying to do an intersect in SQL using the programme pgAdmin 4. I have managed to do an intersect which only returns the resulting geom column.

I would like to have all the Attribute Information from all the other columns, too though. Just like when you do an intersect in QGIS or ArcGIS.

Here is what I have used that got me only the geom column (I am using colours to make it less complicated and easier to read):

CREATE TABLE public.green AS
SELECT ST_INTERSECTION (b.geom, y.geom_2)
FROM public.blue b, public.yellow y
WHERE ST_INTERSECTS(b.geom, y.geom_2) = 't';

I know the public isn't necessary here, but I have more than one Schema that I use. So I thought it would be better to add the Schema here so all parts are accounted for.

2

you just need to change the select statement, for example this will grab everything out of both tables.

CREATE TABLE public.green AS
SELECT b.*, y.*,ST_INTERSECTION (b.geom, y.geom_2)
FROM public.blue b, public.yellow y
WHERE ST_INTERSECTS(b.geom, y.geom_2);
  • I tried it using Create View (create table processed too long and I am not too sure why, could be due to size of the dataset, or something else). With your Statement I got all the Attribute Information from both tables but I didn't get an intersected geom column. What I am looking to achieve is an Output just like what you get when you do an intersect in QGIS/ArcGIS, but in SQL. Is it possible to get both the 'new' geom column and the Attribute Information that belongs to it? – Blue Sapphire Apr 5 '18 at 13:35
  • 1
    you can put anything you like in the select statement - probably best to go for just the intersection and attributes you need – Ian Turton Apr 5 '18 at 13:38
  • Ahh I see, I tried to incorporate the st_intersection part, too but didn't do it correctly. That's why it didn't work. Thanks for updating your answer and your help! Much appreciated :) – Blue Sapphire Apr 5 '18 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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