<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0cm;
margin-right:0cm;
margin-bottom:0cm;
margin-left:36.0pt;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:1650472620;
mso-list-type:hybrid;
mso-list-template-ids:-489155656 67698705 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@list l0:level1
{mso-level-text:"%1\)";
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
ol
{margin-bottom:0cm;}
ul
{margin-bottom:0cm;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal">Hi everyone,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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).<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">With <o:p></o:p></p>
<p class="MsoNormal"> -- get street buffer<o:p></o:p></p>
<p class="MsoNormal"> street_buffer AS (<o:p></o:p></p>
<p class="MsoNormal"> Select<o:p></o:p></p>
<p class="MsoNormal"> Street1.gid as street_id,<o:p></o:p></p>
<p class="MsoNormal"> Street1.geom as street_geom,<o:p></o:p></p>
<p class="MsoNormal"> ST_Buffer(street1.geom, 50, 'endcap=square join=round') as geom1,<o:p></o:p></p>
<p class="MsoNormal"> building.geom as geom2
<o:p></o:p></p>
<p class="MsoNormal"> from street1<o:p></o:p></p>
<p class="MsoNormal"> left join building on st_dwithin(building.geom, street1.geom, 50)<o:p></o:p></p>
<p class="MsoNormal"> order by street_id),<o:p></o:p></p>
<p class="MsoNormal"> -- get selected polygons at the intersection<o:p></o:p></p>
<p class="MsoNormal"> selected_buildings AS (<o:p></o:p></p>
<p class="MsoNormal"> Select <o:p></o:p></p>
<p class="MsoNormal"> street_id,<o:p></o:p></p>
<p class="MsoNormal"> street_geom,<o:p></o:p></p>
<p class="MsoNormal"> ST_Intersection(geom1, geom2) geom<o:p></o:p></p>
<p class="MsoNormal"> from street_buffer),<o:p></o:p></p>
<p class="MsoNormal"> -- compute distance from street to polygons <o:p>
</o:p></p>
<p class="MsoNormal"> distance as (<o:p></o:p></p>
<p class="MsoNormal"> Select <o:p></o:p></p>
<p class="MsoNormal"> street_id,<o:p></o:p></p>
<p class="MsoNormal"> street_geom,<o:p></o:p></p>
<p class="MsoNormal"> ST_Distance(street_geom, geom) as dist<o:p></o:p></p>
<p class="MsoNormal"> from selected_buildings ),<o:p></o:p></p>
<p class="MsoNormal"> -- get offset curves<o:p></o:p></p>
<p class="MsoNormal"> curves as (<o:p></o:p></p>
<p class="MsoNormal"> Select <o:p></o:p></p>
<p class="MsoNormal"> street_id,<o:p></o:p></p>
<p class="MsoNormal"> street_geom,<o:p></o:p></p>
<p class="MsoNormal"> ST_OffsetCurve(ST_LineMerge(street_geom), dist) as curve1,<o:p></o:p></p>
<p class="MsoNormal"> ST_OffsetCurve(ST_LineMerge(street_geom), -dist) as curve2<o:p></o:p></p>
<p class="MsoNormal"> from distance<o:p></o:p></p>
<p class="MsoNormal"> order by street_id),<o:p></o:p></p>
<p class="MsoNormal"> -- merge both curves<o:p></o:p></p>
<p class="MsoNormal"> com_curves as (<o:p></o:p></p>
<p class="MsoNormal"> Select <o:p></o:p></p>
<p class="MsoNormal"> street_id,<o:p></o:p></p>
<p class="MsoNormal"> street_geom,<o:p></o:p></p>
<p class="MsoNormal"> ST_Union(curve1, curve2)::geometry as com_geom from curves<o:p></o:p></p>
<p class="MsoNormal"> order by street_id)<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">-- finally get nearest curves within a distance of 50 meters<o:p></o:p></p>
<p class="MsoNormal">Select distinct on (a.street_id)<o:p></o:p></p>
<p class="MsoNormal"> a.street_id,<o:p></o:p></p>
<p class="MsoNormal"> a.street_geom,<o:p></o:p></p>
<p class="MsoNormal"> b.com_geom as offset,<o:p></o:p></p>
<p class="MsoNormal"> st_distance(a.street_geom, b.com_geom) as dist<o:p></o:p></p>
<p class="MsoNormal">from curves a<o:p></o:p></p>
<p class="MsoNormal">left join com_curves b on st_dwithin(a.street_geom, b.com_geom, 50)<o:p></o:p></p>
<p class="MsoNormal">order by a.street_id, dist<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I have two issues:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoListParagraph" style="text-indent:-18.0pt;mso-list:l0 level1 lfo1"><![if !supportLists]><span style="mso-list:Ignore">1)<span style="font:7.0pt "Times New Roman"">
</span></span><![endif]>Is there a better way to do above job as for large datasets query execution time expands in minutes??<o:p></o:p></p>
<p class="MsoListParagraph"><o:p> </o:p></p>
<p class="MsoListParagraph" style="text-indent:-18.0pt;mso-list:l0 level1 lfo1"><![if !supportLists]><span style="mso-list:Ignore">2)<span style="font:7.0pt "Times New Roman"">
</span></span><![endif]>When I use street2 layer, then I am getting following ERROR:<o:p></o:p></p>
<p class="MsoListParagraph"><o:p> </o:p></p>
<p class="MsoNormal"><span style="color:red">ERROR: ST_OffsetCurve only works with LineStrings<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:red">********** Error **********<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:red"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:red">ERROR: ST_OffsetCurve only works with LineStrings<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:red">SQL state: XX000<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span style="color:#00B050">ST_OffsetCurve(ST_LineMerge((ST_Dump(street_geom)).geom), dist) as curve1,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#00B050">ST_OffsetCurve(ST_LineMerge((ST_Dump(street_geom)).geom), - dist) as curve2<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#00B050"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#00B050">OR <o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#00B050"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#00B050">ST_OffsetCurve((ST_Dump(street_geom)).geom, dist) as curve1<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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?<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I had the feeling probably my postgis or GEOS versions are problematic but I think thigs look fine here:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Select PostGIS_full_version();<o:p></o:p></p>
<p class="MsoNormal">"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"<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Any suggestions for above issues?<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Thanks a lot,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Best,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal" style="background:white"><span style="color:#222222;mso-fareast-language:DA">Jibran Khan,<o:p></o:p></span></p>
<p class="MsoNormal" style="background:white"><span style="color:#222222;mso-fareast-language:DA">Department of Environmental Science,<o:p></o:p></span></p>
<p class="MsoNormal" style="background:white"><span style="color:#222222;mso-fareast-language:DA">Aarhus University,<o:p></o:p></span></p>
<p class="MsoNormal" style="background:white"><span lang="DA" style="color:#222222;mso-fareast-language:DA">Denmark.<o:p></o:p></span></p>
</div>
</body>
</html>