3

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!

underdark
  • 84,148
  • 21
  • 231
  • 413
spatialhast
  • 3,631
  • 2
  • 27
  • 52

3 Answers3

5

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;
Paul Ramsey
  • 19,865
  • 1
  • 47
  • 57
2

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!

spatialhast
  • 3,631
  • 2
  • 27
  • 52
-1
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

simpleuser001
  • 3,864
  • 18
  • 21
  • @ 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. – spatialhast Sep 06 '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! – spatialhast Sep 06 '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. – spatialhast Sep 06 '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 06 '12 at 17:16