[postgis-users] 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:postgis-users-bounces at lists.osgeo.org] 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 <jibran at envs.au.dk<mailto:jibran at envs.au.dk>> 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
postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
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



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170602/17482d6c/attachment.html>