[postgis-users] Intersection between lines and polygons with lengths by nature of polygons

[MAURER Roger]

The following statement of intersection between lines and polygons gives the length of the lines in function of nature of the polygons (jardin, route, etc.)

WITH mg_interection AS
  (SELECT ga.gid, ga.fid, cs.genre_txt || ': ' || Round(SUM(ST_Length(ST_Intersection(ga.the_geom,cs.the_geom)))::numeric,1) || 'm' AS longueur_par_cs
  FROM mg_tmp.mf_ga_strang ga
  LEFT JOIN vd_cad.vd_cad_tpr_cstoutes_s cs ON ST_Intersects(ga.the_geom,cs.the_geom)
  WHERE ga.fid = 385
  GROUP BY ga.gid, ga.fid, cs.genre_txt
  ORDER BY ga.gid, ga.fid, cs.genre_txt)
SELECT gid, fid, array_to_string(array_agg(longueur_par_cs),'; ') FROM mg_interection GROUP BY gid, fid ORDER BY gid ASC;

1524;385;"jardin: 10.1m; route, chemin: 28.4m; trottoir: 12.6m; îlot: 6.4m"

This lengths and natures are all in one column (value = "jardin: 10.1m; route, chemin: 28.4m; trottoir: 12.6m; îlot: 6.4m").
To do some statistics works, I'd like to have one column for each nature of the polygons (jardin, route, etc.) and the length in the row even if the value is null.

Somebody knows a solution (perhaps with crosstabN() function)?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150821/fc47e902/attachment.html>