Re: [postgis-users] Length of a line above polygon
Hi, You can use something like SELECT areas.id, SUM( ST_Length( lines.geometry) ) FROM lines,areas WHERE ST_Intersects(lines.geometry,areas.geometry) GROUP BY areas.id ; Greetings, Denis On 07/03/2012 01:36 PM, Matej Mailing wrote: Hi all, We have a layer that contains polygons and another that contains lines that lie on them. Parts of the lines are laying "inside" the polygons and I would like to get a sum of all the lengths of lines that lie on the polygons. What is the easiest way to achieve this? TIA, Matej ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Length of a line above polygon
Hi, Thank you for the answer. However, I don't get the right result. For example, when there is a line that is long 500 meters and it crosses 5 polygons (on each of them maybe just a meter or few meters, except one where lies a majority of the line), I get the full length of the lines for corresponding polygons - i.e. as every polygon that is crossed is crossed by the total line length. What I want to get is the actual length of the part of the line that crosses every specific polygon. TIA, Matej 2012/7/3 Denis Rouzaud : > Hi, > > You can use something like > > SELECT areas.id, SUM( ST_Length( lines.geometry) ) FROM lines,areas WHERE > ST_Intersects(lines.geometry,areas.geometry) GROUP BY areas.id ; > > Greetings, > > Denis > > > On 07/03/2012 01:36 PM, Matej Mailing wrote: >> >> Hi all, >> >> We have a layer that contains polygons and another that contains lines >> that lie on them. Parts of the lines are laying "inside" the polygons >> and I would like to get a sum of all the lengths of lines that lie on >> the polygons. What is the easiest way to achieve this? >> >> TIA, >> Matej >> ___ >> postgis-users mailing list >> postgis-users@postgis.refractions.net >> http://postgis.refractions.net/mailman/listinfo/postgis-users > > > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Length of a line above polygon
I think I have just found it out. I just add ST_Intersection to have a query like: SELECT areas.id, SUM( ST_Length( ST_Intersection(lines.geometry, areas.geometry) ) ) FROM lines,areas WHERE ST_Intersects(lines.geometry,areas.geometry) GROUP BY areas.id ; Now the numbers seem to be correct :-) Hopefully this could be of some use to anyone else when having such a problem. Thanks, Matej 2012/7/4 Matej Mailing : > Hi, > > Thank you for the answer. However, I don't get the right result. For > example, when there is a line that is long 500 meters and it crosses 5 > polygons (on each of them maybe just a meter or few meters, except one > where lies a majority of the line), I get the full length of the lines > for corresponding polygons - i.e. as every polygon that is crossed is > crossed by the total line length. What I want to get is the actual > length of the part of the line that crosses every specific polygon. > > TIA, > Matej > > > 2012/7/3 Denis Rouzaud : >> Hi, >> >> You can use something like >> >> SELECT areas.id, SUM( ST_Length( lines.geometry) ) FROM lines,areas WHERE >> ST_Intersects(lines.geometry,areas.geometry) GROUP BY areas.id ; >> >> Greetings, >> >> Denis >> >> >> On 07/03/2012 01:36 PM, Matej Mailing wrote: >>> >>> Hi all, >>> >>> We have a layer that contains polygons and another that contains lines >>> that lie on them. Parts of the lines are laying "inside" the polygons >>> and I would like to get a sum of all the lengths of lines that lie on >>> the polygons. What is the easiest way to achieve this? >>> >>> TIA, >>> Matej >>> ___ >>> postgis-users mailing list >>> postgis-users@postgis.refractions.net >>> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> >> ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Length of a line above polygon
Yes, you're right. This should solve this problem: SELECT areas.id, SUM( ST_Length( ST_Intersection( lines.geometry , areas.geometry ) ) ) FROM lines,areas GROUP BY areas.id ; ST_intersects returns true if there is an intersection and ST_Intersection returns the share geometry. Greetings, Denis On 07/04/2012 08:47 AM, Matej Mailing wrote: Hi, Thank you for the answer. However, I don't get the right result. For example, when there is a line that is long 500 meters and it crosses 5 polygons (on each of them maybe just a meter or few meters, except one where lies a majority of the line), I get the full length of the lines for corresponding polygons - i.e. as every polygon that is crossed is crossed by the total line length. What I want to get is the actual length of the part of the line that crosses every specific polygon. TIA, Matej 2012/7/3 Denis Rouzaud : Hi, You can use something like SELECT areas.id, SUM( ST_Length( lines.geometry) ) FROM lines,areas WHERE ST_Intersects(lines.geometry,areas.geometry) GROUP BY areas.id ; Greetings, Denis On 07/03/2012 01:36 PM, Matej Mailing wrote: Hi all, We have a layer that contains polygons and another that contains lines that lie on them. Parts of the lines are laying "inside" the polygons and I would like to get a sum of all the lengths of lines that lie on the polygons. What is the easiest way to achieve this? TIA, Matej ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Length of a line above polygon
Messages crossed, but seems we agree ;) On 07/04/2012 08:51 AM, Matej Mailing wrote: I think I have just found it out. I just add ST_Intersection to have a query like: SELECT areas.id, SUM( ST_Length( ST_Intersection(lines.geometry, areas.geometry) ) ) FROM lines,areas WHERE ST_Intersects(lines.geometry,areas.geometry) GROUP BY areas.id ; Now the numbers seem to be correct :-) Hopefully this could be of some use to anyone else when having such a problem. Thanks, Matej 2012/7/4 Matej Mailing : Hi, Thank you for the answer. However, I don't get the right result. For example, when there is a line that is long 500 meters and it crosses 5 polygons (on each of them maybe just a meter or few meters, except one where lies a majority of the line), I get the full length of the lines for corresponding polygons - i.e. as every polygon that is crossed is crossed by the total line length. What I want to get is the actual length of the part of the line that crosses every specific polygon. TIA, Matej 2012/7/3 Denis Rouzaud : Hi, You can use something like SELECT areas.id, SUM( ST_Length( lines.geometry) ) FROM lines,areas WHERE ST_Intersects(lines.geometry,areas.geometry) GROUP BY areas.id ; Greetings, Denis On 07/03/2012 01:36 PM, Matej Mailing wrote: Hi all, We have a layer that contains polygons and another that contains lines that lie on them. Parts of the lines are laying "inside" the polygons and I would like to get a sum of all the lengths of lines that lie on the polygons. What is the easiest way to achieve this? TIA, Matej ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
