2

I need to find out the lengt of each line within a surface. I have approx. 20'000 diferent lines and around 10'000 different surfaces. what kind of SQL-sentences do I need to use? I've tried things like:

select name.line, ST_length(line.thegeom, surface.thegeom)
from x.line, y.surface
where st_intersects (line.thegeom, surface.thegeom)

but my progragm (QGIS) just dies all the time or says something like "geos intersects () threw an error" I'd love to have a sql query that gives me following GID(=number of the line), GIS_NR(=number of the surface), total length of the line within the surface.

I'm quite new to GIS and postgresql.

  • Does it have to be a PostGIS solution? Do you want the sum of all line lengths in one polygon or the the length of individual lines? – underdark Mar 19 '14 at 18:03
  • I need to have the length of the single lines(e.g. a hedge) within a surface (land). I need to know which line is how long within a surface. Thank you for your help! @underdark Mit dir kann man ja auch Deutsch sprechen :-) Ich muss wissen welche Linie auf welchem Land ist und wie lang diese Linie jeweils total ist. Auf Grund der Linien länge werden Beträge an die Landbesitzer aus bezahlt. Wenn ich die Layer verschneide ändert es mir die Linien IDs was für mich eher ungünstig ist.. Danke für deine Hilfe! – Dave28238 Mar 20 '14 at 7:48
3

To better understand your problem, first I have imagined the bellow situation:

enter image description here

And these are the lines and the polygons tables:

enter image description here

enter image description here

Also, I've tried to visualise the result, based on your requirement: to find out which line is how long within a surface:

enter image description here

The above images helped me to build the necessary query:

SELECT a.id_0 gid_line, a.name line_name, 
   ST_Length((ST_Intersection(a.geom, b.geom))),
   b.name polygon_name 
     FROM my_lines a, my_polygons b
     WHERE b.id_0 > 0 AND ST_Intersects(a.geom, b.geom) = true
     ORDER BY b.name;

Looking at the results, you may notice the line length are expressed in degrees:

enter image description here

If this thing bothers you, then you may use a Spatial Reference System of which units are expressed in meters. This is what I've used in my case:

SELECT a.id_0 gid_line, a.name line_name, 
   ST_Length((ST_Intersection(ST_Transform(a.geom, 31700), ST_Transform(b.geom, 31700)))),
   b.name polygon_name 
     FROM my_lines a, my_polygons b
     WHERE b.id_0 > 0 AND ST_Intersects(a.geom, b.geom) = true
     ORDER BY b.name; 

enter image description here

Now, as everything is ok, I want you to give you an advice: because you have a lot of lines and surfaces, it will be better for you to avoid using the ST_Transform and to work from the beginning with meter units. This way, the necessary computing time will be reduced considerably.

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.