Inserting union of two tables into another table using PostgreSQL?

Question

I am working on a research project. I have two tables, one of a polygon that represents a severe weather risk area, one that represents the US. I want to merge them into one output polygon. I am stuck with the syntax of how to do this.

spc is the table of the risk area, simple_us is the us. I want to output the union of these two table into the geometry column of a new table called spc_clip.

My current command is:

INSERT INTO spc_clip (geom) VALUES (ST_Union(a.geom, b.geom)), spc as a, simple_us as b;

As it sits now I get a syntax error at spc when I run the command.

Any ideas on how to proceed?

Next I want to export the geometry of the union to a JSON. I am not sure how to do this. I cannot test this line until I get the above union done.

SELECT row_to_json(t) FROM (SELECT name,geom FROM spc_clip) t;

NOTE: name is already in the table and works fine. I am just struggling with the join and the json export.

Answer 1

Try this instead your INSERT:

INSERT INTO spc_clip (geom)
  SELECT (ST_Union(a.geom, b.geom))
  FROM spc as a, simple_us as b;