UPDATE 2: As @GetSpatial suggested, I've tried using subqueries to sum and group the results of the first query. Same result. Here, the 3 things I've tried. All of them get a distance 2x the expected one (link to real data at the end):
First try, with one query:
SELECT a.province, sum(ST_LENGTH(ST_Intersection(a.geom, b.the_geom))/1000) as length_per_province
FROM provinces a, line_tracks b
WHERE ST_Intersects(a.geom, b.the_geom) group by a.province order by a.province;
Second try, with CTE:
with line_segments as (
SELECT a.province, ST_LENGTH(ST_Intersection(a.geom, b.the_geom))/1000 as length
FROM provinces a, line_tracks b
WHERE ST_Intersects(a.geom, b.the_geom) order by a.province
) select province, sum(length) as length_per_province from line_segments group by province;
Third one, with subquery, as suggested:
select province, sum(length) as length_per_province from(
SELECT a.province, ST_LENGTH(ST_Intersection(a.geom, b.the_geom))/1000 as length
FROM provinces a, line_tracks b
WHERE ST_Intersects(a.geom, b.the_geom)) as foo group by province;
I get this, in all cases:
"province";"total_length"
"p1";62.8687677452213
"p2";40.254207910339
The total length of the line:
select sum(st_length(the_geom))/1000 as total_length from line_tracks
The result
"total_length"
51.5614878277802
And the 2 tables here (SET client_encoding = 'LATIN1'): https://dl.dropboxusercontent.com/u/6599273/gis_data/data.sql.tar.bz2
If anyone could try, that would be great. Anyway, many thanks.
