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
First see if the join works...
SELECT
l.country || ' - ' || l.country2 AS link,
FROM points_table a
JOIN links_table l ON a.country = l.country
JOIN points_table b ON b.country = l.country2;
Hopefully you'll get a finite number of results (actually, exactly the same number of results as your links table has). If not, perhaps your country keys don't actually match.
To generate the connecting lines for all links you'll want something like this:
SELECT
10000 * a.cartodb_id + b.cartodb_id AS cartodb_id,
l.country || ' - ' || l.country2 AS link,
ST_Transform(
Geometry(
ST_Segmentize(
Geography(
ST_SetSRID(
ST_MakeLine(ST_MakePoint(a.long, a.lat), ST_MakePoint(b.long, b.lat)),
4326)),
100000)),
3857) AS the_geom_webmercator
FROM points_table a
JOIN links_table l ON a.country = l.country
JOIN points_table b ON b.country = l.country2;
The magic part is the bottom, where you join the points table to the links twice, using different columns, to get the end points of the links.
The other magic part is the big nest of functions, which make a simple point-to-point line, then flip into geography, segmentize to get a great circle route, flip back to geometry and reproject to mercator for mapping in CartoDB.
Best Answer
basically this is the guide: https://github.com/CartoDB/cartodb/wiki/creating-tables-though-the-SQL-API
the important information:
lets create a table using SQL API:
at this point you will not able to see table test in the editor
Now if you go to your dashboard should appear (it takes some seconds to be available, so refresh after a bit if it's not there)
For people using multiuser account you need to include your username in cdb_cartodbfytable call: