[postgis-users] 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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170602/e1c010a4/attachment.html>