Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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!

share|improve this question

3 Answers 3

up vote 4 down vote accepted

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;
share|improve this answer

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!

share|improve this answer
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

share|improve this answer
    
@ 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
1  
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.