sql - How to Calculate length of polyline geometry for several tables in PostGIS?

PostGIS database contains several geometry (polyline) tables (with names "D1_r", "D2_r", "D3_r"). I calculate length for one "postgis layer" use request:

SELECT 
sum(ST_Length_Spheroid(the_geom,'SPHEROID["WGS 84",6378137,298.257223563]'))/1000 AS km_roads 
FROM 
"D1_r";

How to make a request to get a result for each table ("D1_r", "D2_r", "D3_r") and the total result for all tables ("D1_r"+"D2_r"+"D3_r")? Thanks!

Answer

Unioning the tables together is one way:

WITH alltables AS (
  SELECT the_geom FROM D1_r 
  UNION ALL
  SELECT the_geom FROM D2_r 
  UNION ALL
  SELECT the_geom FROM D3_r 
)

SELECT sum(ST_Length_Spheroid(the_geom,'SPHEROID["WGS 84",6378137,298.257223563]'))/1000 AS km_roads
FROM alltables;

Incidentally if you want PostGIS 1.5+ you can use the Geography type and get a simpler looking query:

WITH alltables AS (
  SELECT the_geom::geography FROM D1_r 
  UNION ALL
  SELECT the_geom::geography FROM D2_r 
  UNION ALL
  SELECT the_geom::geography FROM D3_r 

)
SELECT sum(ST_Length(the_geom))/1000 AS km_roads
FROM alltables;