Issue with St_OffsetCurve()

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Issue with St_OffsetCurve()

Jibran Khan

Hi everyone,

 

I have two street files in my PostgreSQL 9.5 database i.e., street1 and street2. Street1 contains 85 rows while street2 contains 2,065 rows. With the following query: for every street, I am trying to get offset curves at the intersection of polygons and 50 meters buffer around street layer. The code works fine provided street  and polygons are  straight (parallel).

 

With     

                -- get street buffer

                street_buffer AS (

                                Select

                                                Street1.gid as street_id,

                                                Street1.geom as street_geom,

                                                ST_Buffer(street1.geom, 50, 'endcap=square join=round') as geom1,

                                                building.geom  as geom2             

                                from street1

                                left join building on st_dwithin(building.geom, street1.geom, 50)

                                order by street_id),

                -- get selected polygons at the intersection

                selected_buildings AS (

                                Select

                                                street_id,

                                                street_geom,

                                                ST_Intersection(geom1, geom2) geom

                                from street_buffer),

                -- compute distance from street to polygons

                distance as (

                                Select

                                                street_id,

                                                street_geom,

                                                ST_Distance(street_geom, geom) as dist

                                from selected_buildings ),

                -- get offset curves

                curves as (

                                Select

                                                street_id,

                                                street_geom,

                                                ST_OffsetCurve(ST_LineMerge(street_geom), dist) as curve1,

                                                ST_OffsetCurve(ST_LineMerge(street_geom), -dist) as curve2

                                from distance

                                order by street_id),

                -- merge both curves

                com_curves as (

                                Select

                                                street_id,

                                                street_geom,

                                                ST_Union(curve1, curve2)::geometry as com_geom from curves

                                order by street_id)

 

-- finally get nearest curves within a distance of 50 meters

Select distinct on (a.street_id)

                                a.street_id,

                                a.street_geom,

                                b.com_geom as offset,

                                st_distance(a.street_geom, b.com_geom) as dist

from curves a

left join com_curves b on st_dwithin(a.street_geom, b.com_geom, 50)

order by a.street_id, dist

 

I have two issues:

 

1)      Is there a better way to do above job as for large datasets query execution time expands in minutes??

 

2)      When I use street2 layer, then I am getting following ERROR:

 

ERROR:  ST_OffsetCurve only works with LineStrings

********** Error **********

 

ERROR: ST_OffsetCurve only works with LineStrings

SQL state: XX000

 

I googled this error and came to know about two possible fixes. (1) In QGIS, I can use multiparts to singleparts and  (2) I can use ST_Dump() like this:

 

ST_OffsetCurve(ST_LineMerge((ST_Dump(street_geom)).geom), dist) as curve1,

ST_OffsetCurve(ST_LineMerge((ST_Dump(street_geom)).geom), - dist) as curve2

 

OR

 

ST_OffsetCurve((ST_Dump(street_geom)).geom, dist) as curve1

 

I tested both ways. The code worked (at least I didn’t get the error) but code output is not the desired one. Any suggestions how to fix this error?

 

I had the feeling probably my postgis or GEOS versions are problematic but I think thigs look fine here:

 

Select PostGIS_full_version();

"POSTGIS="2.2.1 r14555" GEOS="3.5.0-CAPI-1.9.0 r4090" PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 2.0.1, released 2015/09/15" LIBXML="2.7.8" LIBJSON="0.12" RASTER"

 

Any suggestions for above issues?

 

Thanks a lot,

 

Best,

 

Jibran Khan,

Department of Environmental Science,

Aarhus University,

Denmark.


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Issue with St_OffsetCurve()

Basques, Bob (CI-StPaul)
JK,

I don’t know what you end goal is with this (completely) but I’ve been working on something similar.  And I founf that you needed to isolate the circular strings into separate features, in other words, “explode” the parcel edges into separate features, and work the process against both feature types, lineStrings, and CircularStrings.

I’m running into the same sort of slowdown in the processing as well.  I have a much bigger dataset too.   So I’m also trying to partiion up by a spatial grid.  I’m currently out of my mainstream knowledge level though, so it’s still a learning experience right now.  There’s not much out there for setting up a spatial grid partition, if that’s even what the correct search term is. . .

bobb


On Jun 2, 2017, at 12:06 PM, Jibran Khan <[hidden email]> wrote:

Hi everyone,
 
I have two street files in my PostgreSQL 9.5 database i.e., street1 and street2. Street1 contains 85 rows while street2 contains 2,065 rows. With the following query: for every street, I am trying to get offset curves at the intersection of polygons and 50 meters buffer around street layer. The code works fine provided street  and polygons are  straight (parallel).
 
With      
                -- get street buffer
                street_buffer AS (
                                Select
                                                Street1.gid as street_id,
                                                Street1.geom as street_geom,
                                                ST_Buffer(street1.geom, 50, 'endcap=square join=round') as geom1,
                                                building.geom  as geom2             
                                from street1
                                left join building on st_dwithin(building.geom, street1.geom, 50)
                                order by street_id),
                -- get selected polygons at the intersection
                selected_buildings AS (
                                Select 
                                                street_id,
                                                street_geom,
                                                ST_Intersection(geom1, geom2) geom
                                from street_buffer),
                -- compute distance from street to polygons 
                distance as (
                                Select 
                                                street_id,
                                                street_geom,
                                                ST_Distance(street_geom, geom) as dist
                                from selected_buildings ),
                -- get offset curves
                curves as (
                                Select 
                                                street_id,
                                                street_geom,
                                                ST_OffsetCurve(ST_LineMerge(street_geom), dist) as curve1,
                                                ST_OffsetCurve(ST_LineMerge(street_geom), -dist) as curve2
                                from distance
                                order by street_id),
                -- merge both curves
                com_curves as (
                                Select 
                                                street_id,
                                                street_geom,
                                                ST_Union(curve1, curve2)::geometry as com_geom from curves
                                order by street_id)
 
-- finally get nearest curves within a distance of 50 meters
Select distinct on (a.street_id)
                                a.street_id,
                                a.street_geom,
                                b.com_geom as offset,
                                st_distance(a.street_geom, b.com_geom) as dist
from curves a
left join com_curves b on st_dwithin(a.street_geom, b.com_geom, 50)
order by a.street_id, dist
 
I have two issues:
 
1)      Is there a better way to do above job as for large datasets query execution time expands in minutes??
 
2)      When I use street2 layer, then I am getting following ERROR:
 
ERROR:  ST_OffsetCurve only works with LineStrings
********** Error **********
 
ERROR: ST_OffsetCurve only works with LineStrings
SQL state: XX000
 
I googled this error and came to know about two possible fixes. (1) In QGIS, I can use multiparts to singleparts and  (2) I can use ST_Dump() like this:
 
ST_OffsetCurve(ST_LineMerge((ST_Dump(street_geom)).geom), dist) as curve1,
ST_OffsetCurve(ST_LineMerge((ST_Dump(street_geom)).geom), - dist) as curve2
 
OR 
 
ST_OffsetCurve((ST_Dump(street_geom)).geom, dist) as curve1
 
I tested both ways. The code worked (at least I didn’t get the error) but code output is not the desired one. Any suggestions how to fix this error?
 
I had the feeling probably my postgis or GEOS versions are problematic but I think thigs look fine here:
 
Select PostGIS_full_version();
"POSTGIS="2.2.1 r14555" GEOS="3.5.0-CAPI-1.9.0 r4090" PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 2.0.1, released 2015/09/15" LIBXML="2.7.8" LIBJSON="0.12" RASTER"
 
Any suggestions for above issues?
 
Thanks a lot,
 
Best,
 
Jibran Khan,
Department of Environmental Science,
Aarhus University,
Denmark.
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users



"The power of accurate observation is frequently called cynicism by those who don't have it." 
- George Bernard Shaw




_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Issue with St_OffsetCurve()

Jibran Khan

Bobb,

 

My ultimate goal is to compute the street width. By:

 

1)      getting offset curves at the intersection of polygons and buffer geometry on both sides of street

2)      computing the distance between nearest offset curves from street center line and sum them up

 

I wonder there could be other approaches to do this as well but for me, this was my initial attempt. Thanks for your suggestion anyways to break the multiline string into line strings and circular strings. I may give it a try to see if this work in my case.

Well, I have datasets containing billions of features and since I am running into query execution time/performance issues so I don’t think that this query would help much with large datasets.

 

Best,

JK

 

From: postgis-users [mailto:[hidden email]] On Behalf Of Basques, Bob (CI-StPaul)
Sent: 02 June 2017 20:49
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Issue with St_OffsetCurve()

 

JK,

 

I don’t know what you end goal is with this (completely) but I’ve been working on something similar.  And I founf that you needed to isolate the circular strings into separate features, in other words, “explode” the parcel edges into separate features, and work the process against both feature types, lineStrings, and CircularStrings.

 

I’m running into the same sort of slowdown in the processing as well.  I have a much bigger dataset too.   So I’m also trying to partiion up by a spatial grid.  I’m currently out of my mainstream knowledge level though, so it’s still a learning experience right now.  There’s not much out there for setting up a spatial grid partition, if that’s even what the correct search term is. . .

 

bobb

 

 

On Jun 2, 2017, at 12:06 PM, Jibran Khan <[hidden email]> wrote:

 

Hi everyone,

 

I have two street files in my PostgreSQL 9.5 database i.e., street1 and street2. Street1 contains 85 rows while street2 contains 2,065 rows. With the following query: for every street, I am trying to get offset curves at the intersection of polygons and 50 meters buffer around street layer. The code works fine provided street  and polygons are  straight (parallel).

 

With      

                -- get street buffer

                street_buffer AS (

                                Select

                                                Street1.gid as street_id,

                                                Street1.geom as street_geom,

                                                ST_Buffer(street1.geom, 50, 'endcap=square join=round') as geom1,

                                                building.geom  as geom2             

                                from street1

                                left join building on st_dwithin(building.geom, street1.geom, 50)

                                order by street_id),

                -- get selected polygons at the intersection

                selected_buildings AS (

                                Select 

                                                street_id,

                                                street_geom,

                                                ST_Intersection(geom1, geom2) geom

                                from street_buffer),

                -- compute distance from street to polygons 

                distance as (

                                Select 

                                                street_id,

                                                street_geom,

                                                ST_Distance(street_geom, geom) as dist

                                from selected_buildings ),

                -- get offset curves

                curves as (

                                Select 

                                                street_id,

                                                street_geom,

                                                ST_OffsetCurve(ST_LineMerge(street_geom), dist) as curve1,

                                                ST_OffsetCurve(ST_LineMerge(street_geom), -dist) as curve2

                                from distance

                                order by street_id),

                -- merge both curves

                com_curves as (

                                Select 

                                                street_id,

                                                street_geom,

                                                ST_Union(curve1, curve2)::geometry as com_geom from curves

                                order by street_id)

 

-- finally get nearest curves within a distance of 50 meters

Select distinct on (a.street_id)

                                a.street_id,

                                a.street_geom,

                                b.com_geom as offset,

                                st_distance(a.street_geom, b.com_geom) as dist

from curves a

left join com_curves b on st_dwithin(a.street_geom, b.com_geom, 50)

order by a.street_id, dist

 

I have two issues:

 

1)      Is there a better way to do above job as for large datasets query execution time expands in minutes??

 

2)      When I use street2 layer, then I am getting following ERROR:

 

ERROR:  ST_OffsetCurve only works with LineStrings

********** Error **********

 

ERROR: ST_OffsetCurve only works with LineStrings

SQL state: XX000

 

I googled this error and came to know about two possible fixes. (1) In QGIS, I can use multiparts to singleparts and  (2) I can use ST_Dump() like this:

 

ST_OffsetCurve(ST_LineMerge((ST_Dump(street_geom)).geom), dist) as curve1,

ST_OffsetCurve(ST_LineMerge((ST_Dump(street_geom)).geom), - dist) as curve2

 

OR 

 

ST_OffsetCurve((ST_Dump(street_geom)).geom, dist) as curve1

 

I tested both ways. The code worked (at least I didn’t get the error) but code output is not the desired one. Any suggestions how to fix this error?

 

I had the feeling probably my postgis or GEOS versions are problematic but I think thigs look fine here:

 

Select PostGIS_full_version();

"POSTGIS="2.2.1 r14555" GEOS="3.5.0-CAPI-1.9.0 r4090" PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 2.0.1, released 2015/09/15" LIBXML="2.7.8" LIBJSON="0.12" RASTER"

 

Any suggestions for above issues?

 

Thanks a lot,

 

Best,

 

Jibran Khan,

Department of Environmental Science,

Aarhus University,

Denmark.

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

 

 

"The power of accurate observation is frequently called cynicism by those who don't have it." 

- George Bernard Shaw

 

 

 


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users