1

I am attempting to take data from 2 tables, find intersecting geometries, and insert rows into the new table representing the intersections. I have done this in the past with CartoDB using SQL but now I'm not able to do so. I'd also be open to using the Merge Dataset functionality via the UI, but that doesn't appear to be designed for what I need.

Specifically, when I run the following sql via the UI, I get an error stating "SELECT ... INTO is not allowed here".

SELECT clipped.city_name, the_geom
INTO newtable
FROM 
    (SELECT table1.city_name, table2.namelsad, (ST_Dump(ST_Intersection(table2.the_geom, table1.the_geom))).geom As the_geom
    FROM table2
    INNER JOIN table1
    ON ST_Intersects(us_county.the_geom, us_or_city.the_geom))  As clipped

Any suggestions on how to make this work?

1

Needed to invert the insert as follows:

INSERT INTO newtable
(SELECT nextval('{sequence_name, see below}'), the_geom, null, concat(clipped.city_name, ' (', clipped.namelsad,')') AS Name
FROM 
    (SELECT table1.city_name, table2.namelsad, (ST_Dump(ST_Intersection(table2.the_geom, table1.the_geom))).geom As the_geom
    FROM us_county
    INNER JOIN us_or_city
    ON ST_Intersects(us_county.the_geom, us_or_city.the_geom))  As clipped)

And to get the sequence id for the cartodb_id column, use:

pg_get_serial_sequence({tablename}, 'cartodb_id')
1
  • A tip: If the table is CartoDBfied (any table created with the Carto Editor/Builder will be) you don't need to specify the cartodb_id value in the INSERT INTO statement, as any row you insert will automatically generate a cartodb_id value from its sequence. So you could specify the fields you want to insert in your INSERT INTO statement, omitting cartodb_id (as you omit already the_geom_webmercator, which is also auto-generated) and just retrieve those columns in the select. I hope this makes your work easier!
    – iriberri
    Sep 26 '16 at 7:47

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.