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!

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

-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

  • @ 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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