Question: 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;

```
Answer:

```
SELECT
(SELECT sum(ST_Length_Spheroid("D1_r".the_geom,'SPHEROID["WGS 84",6378137,298.257223563]'))/1000 
       FROM "D1_r") AS km_roads1, 
(SELECT sum(ST_Length_Spheroid("D2_r".the_geom,'SPHEROID["WGS 84",6378137,298.257223563]'))/1000
       FROM "D2_r") AS km_roads2,
(SELECT sum(ST_Length_Spheroid("D1_r".the_geom,'SPHEROID["WGS 84",6378137,298.257223563]'))/1000 
       FROM "D1_r") + 
(SELECT sum(ST_Length_Spheroid("D2_r".the_geom,'SPHEROID["WGS 84",6378137,298.257223563]'))/1000
       FROM "D2_r") AS km_total
;

```

<http://gis-lab.info/forum/viewtopic.php?f=32&t=11535>

Thanks!