# 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
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!

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
)
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
)
FROM alltables;
``````

``````SELECT
(SELECT sum(ST_Length_Spheroid("D1_r".the_geom,'SPHEROID["WGS 84",6378137,298.257223563]'))/1000
(SELECT sum(ST_Length_Spheroid("D2_r".the_geom,'SPHEROID["WGS 84",6378137,298.257223563]'))/1000
(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!

``````SELECT sum(length(d1.the_geom)) as d1sum, sum(length(d2.the_geom)) as d2sum, sum(length(d3.the_geom)) as d3sum, (sum(length(d1.the_geom))+(sum(length(d1.the_geom))) as total
FROM d1 , d2,d3
``````

Is one way of doing it: EDIT : indeed , this is wrong way to do it

• @ simplexio , If I run request for first and second tables in SQL window/DB Manager, I received result 2267 km (10453 rows in table) and 220 km (834 rows in table) after 0.1 seconds. If I run request for two tables used request: SELECT sum(ST_Length_Spheroid("D1_r".the_geom,'SPHEROID["WGS 84",6378137,298.257223563]'))/1000 AS km_roads1, sum(ST_Length_Spheroid("D2_r".the_geom,'SPHEROID["WGS 84",6378137,298.257223563]'))/1000 AS km_roads2 FROM "D1_r","D2_r"; I resived result 1891450 km and 2305105 km after 183 seconds. – HasT Sep 6 '12 at 10:05
• @ simplexio , Or if I run request: SELECT sum(ST_Length_Spheroid("D1_r".the_geom,'SPHEROID["WGS 84",6378137,298.257223563]'))/1000 AS km_roads1 FROM "D1_r","D2_r"; I resived result 1891450 km after 100 seconds. In what could be the problem? Thanks! – HasT Sep 6 '12 at 10:05
• @ simplexio , if I run request: SELECT (sum(ST_Length_Spheroid("D1_r".the_geom,'SPHEROID["WGS 84",6378137,298.257223563]'))/1000)+( sum(ST_Length_Spheroid("D2_r".the_geom,'SPHEROID["WGS 84",6378137,298.257223563]'))/1000) AS total FROM "D1_r", "D2_r"; I resived result 4196555 km (1891450 km + 2305105 km) after 190 seconds. – HasT Sep 6 '12 at 10:06
• This is not what you want, you're going to get a big cartesian join out of this. – Paul Ramsey Sep 6 '12 at 17:16