1

I have 5 separate tables in CartoDB each with a varying number of columns, but all containing these 4 columns (the_geom, name, type, unit_code). I want to merge them all into 1 new table and get rid of most of the columns. I figured out that UNION ALL will get rid of the columns I don't need in the end, but I can't see how to merge the tables using SQL. Whenever I try UNION ALL it doesn't want to work. I've also seen that sometimes I need to write UNION_ALL with the underscore to make it work? Here's my sample code:

SELECT the_geom, name, type, unit_code FROM points_of_interest
UNION ALL
SELECT the_geom, altname, descriptio, unitcode FROM arch_visitor_centers
UNION ALL
SELECT the_geom, name FROM arch_trailheads
UNION ALL
SELECT the_geom, bldg_name FROM arch_restrooms
UNION ALL
SELECT the_geom, name FROM arch_parking
UNION ALL
SELECT the_geom, name, FROM arch_campgrounds
UNION ALL
Improve this question
1
  • Welcome to GIS.SE. I've made some minor code formatting changes, so its easier to see - you can do that yourself using the {} button on future questions. When you say "doesn't want to work", it can help a lot to describe what happens instead, especially any error messages. Commented Jan 25, 2015 at 3:51

1 Answer 1

Reset to default
1

So the quickest answer is that SQL expects the same dimension of columns in each statement you try to do a UNION on. So all the first column of your SQL statements should match what you want to be in the first column of your result. Generally, they will also need to match in data type.

The column names themselves don't matter.

So to make yours work you'd do something like

SELECT the_geom, name, type, '' AS description, unit_code FROM points_of_interest
UNION ALL
SELECT the_geom, altname, '' AS type, descriptio, unitcode FROM arch_visitor_centers
UNION ALL
SELECT the_geom, name, '' AS type, '' as description, '' as unitcode FROM arch_trailheads
UNION ALL
SELECT the_geom, bldg_name, '' AS type, '' as description, '' as unitcode FROM arch_restrooms
UNION ALL
SELECT the_geom, name,'' AS type, '' as description, '' as unitcode FROM arch_parking
UNION ALL
SELECT the_geom, name,'' AS type, '' as description, '' as unitcode FROM arch_campgrounds

The result would contain

the_geom, name, type, description, unit_code

I'm assuming that type, name, description, and unit_code are all Text type variables. You can keep track of what table your rows came from by manually including a final column that just includes the table name aliased to a column. E.g. 

SELECT the_geom, name, type, '' AS description, unit_code, 'points_of_interest' as source_table FROM points_of_interest
UNION ALL
SELECT the_geom, altname, '' AS type, descriptio, unitcode, 'arch_visitor_centers' as source_table FROM arch_visitor_centers
UNION ALL
SELECT the_geom, name, '' AS type, '' as description, '' as unitcode, 'arch_trailheads' as source_table FROM arch_trailheads
UNION ALL
SELECT the_geom, bldg_name, '' AS type, '' as description, '' as unitcode, 'arch_restrooms' as source_table FROM arch_restrooms
UNION ALL
SELECT the_geom, name,'' AS type, '' as description, '' as unitcode, 'arch_parking' as source_table FROM arch_parking
UNION ALL
SELECT the_geom, name,'' AS type, '' as description, '' as unitcode, 'arch_campgrounds' as source_table FROM arch_campgrounds
Improve this answer
2
  • Thanks Andrew, this code worked perfectly. So I guess in a situation such as this, the first SELECT query sets the column names for the following statements, is that the case? Commented Jan 26, 2015 at 14:43
  • the first statement sets what the column names will be in the response. the rest of the statements must select columns in the same order and type. so if the first column in the first statement is a string, the first column in the second statement must also be a string. that's all Commented Jan 28, 2015 at 23:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.