[GIS] Merging multiple tables using SQL in CartoDB

cartosql

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

Best Answer

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

Related Solutions

[GIS] When we create a CartoDB table using SQL, is there a way to register it with the tables dashboard

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:

create table test (whatever int);

at this point you will not able to see table test in the editor

select cdb_cartodbfytable('test');

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:

select cdb_cartodbfytable('myuser', 'test');

CartoDB – How to Create Links Between Points

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

Related Solutions

[GIS] When we create a CartoDB table using SQL, is there a way to register it with the tables dashboard

CartoDB – How to Create Links Between Points

Related Question