<div dir="ltr"><div class="gmail_default" style="font-size:small">Thank you, </div><div class="gmail_default" style="font-size:small">it was great help.</div><div class="gmail_default" style="font-size:small"><br></div><div class="gmail_default" style="font-size:small">particularly your  section worked well and helpful ( since I am going to create geojson outcome from this sql for web app):</div><div class="gmail_default" style="font-size:small">---</div><div class="gmail_default" style="font-size:small"><p class="MsoNormal" style="font-size:12.8px"><span style="font-size:11pt;font-family:calibri,sans-serif;color:rgb(31,73,125)">And I would think you would want one record per road so the union should be done after, so the below would make more sense.<u></u><u></u></span></p><p class="MsoNormal" style="font-size:12.8px"><span style="font-size:11pt;font-family:calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal" style="margin-left:0.5in;font-size:12.8px">SELECT road.gid,  ST_Union(ST_Intersection(road.<wbr>the_geom, ST_Buffer(fault.the_geom, 100)  ) ) as road_fayBuffer<u></u><u></u></p><p class="MsoNormal" style="margin-left:0.5in;font-size:12.8px">FROM road4analyse as road<u></u><u></u></p><p class="MsoNormal" style="margin-left:1in;font-size:12.8px;text-indent:0.5in">INNER JOIN fault4analyse AS fault<u></u><u></u></p><p class="MsoNormal" style="margin-left:0.5in;font-size:12.8px">  ON ST_DWithin(road.the_geom, fault.geom, 100 ) <u></u><u></u></p><p class="MsoNormal" style="margin-left:0.5in;font-size:12.8px">GROUP BY road.gid</p></div><div class="gmail_default" style="font-size:small"><br></div><div class="gmail_default" style="font-size:small"><br></div><div class="gmail_default" style="font-size:small">---</div><div class="gmail_default" style="font-size:small">  I used ST_Union because  fault lines are so scattered (roads are as well) and buffers look weird. I preferred more conservative approach by creating complete buffer zone. I thought at least one of the geometry should be simple.</div><div class="gmail_default" style="font-size:small"><br></div><div class="gmail_default" style="font-size:small">on the subject of correctness of distance calculation, how about using epsg:3857. You see</div><div class="gmail_default" style="font-size:small">I directly use geometries in 3857. Do you recommend me transform to geography for correct distance calculation?</div><div class="gmail_default" style="font-size:small"><br></div><div class="gmail_default" style="font-size:small">thank you again</div><div class="gmail_default" style="font-size:small"><br></div><div class="gmail_default" style="font-size:small">regards</div><div class="gmail_default" style="font-size:small"><br></div><div class="gmail_default" style="font-size:small">thanks again. </div></div><div class="gmail_extra"><br><div class="gmail_quote">On Mon, Oct 31, 2016 at 11:39 PM, Regina Obe <span dir="ltr"><<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div lang="EN-US" link="blue" vlink="purple"><div class="m_648607514117635773WordSection1"><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Temiz,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Good question.  They will yield similar results, but not exactly the same<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="m_648607514117635773MsoListParagraph"><u></u><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><span>1)<span style="font:7.0pt "Times New Roman"">      </span></span></span><u></u><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">When you do a buffer, it's an approximation since it uses by default 8 segments to approximate a quarter circle.  That said, it's possible you will get a different count of lines than you would with ST_DWithin and the ST_DWithin answer would be more correct.<u></u><u></u></span></p><p class="m_648607514117635773MsoListParagraph"><u></u><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><span>2)<span style="font:7.0pt "Times New Roman"">      </span></span></span><u></u><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Unfortunately if you do need to return that portion of your line that intersects your buffer, you would need the ST_Buffer you have and deal with the less the accurateness of it if it's an issue.<u></u><u></u></span></p><p class="m_648607514117635773MsoListParagraph"><u></u><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><span>3)<span style="font:7.0pt "Times New Roman"">      </span></span></span><u></u><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Have you compared the performance of both?  Your faultbuffer would not be able to utilize an index on the fault4analyse table, where as the ST_DWithin approach could.<u></u><u></u></span></p><p class="m_648607514117635773MsoListParagraph"><u></u><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><span>4)<span style="font:7.0pt "Times New Roman"">      </span></span></span><u></u><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">It would also be interesting to compare the performance differences.  In some cases using ST_Intersects with buffer is faster than using ST_Dwithin since GEOS has some special optimizations that I think ST_Dwithin lacks, but ST_DWithin would need to do less work up front than buffer (which could be huge for large geometries) and a buffered geom can't use index of underlying table. <u></u><u></u></span></p><p class="m_648607514117635773MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">I think when you buffer depending on the size of your buffer and the jagged edges you have in fault, you may end up with a simpler geometry that would require less work for ST_Intersects / ST_Dwihin.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="m_648607514117635773MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">It's also not clear to me why you are doing a ST_Union (do you have multiple fault lines with same gid?  ) if not then that union is not necessary.<u></u><u></u></span></p><p class="m_648607514117635773MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> And I would think you would want one record per road so the union should be done after, so the below would make more sense.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal" style="margin-left:.5in">SELECT road.gid,  ST_Union(ST_Intersection(road.<wbr>the_geom, ST_Buffer(fault.the_geom, 100)  ) ) as road_fayBuffer <u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in">FROM road4analyse as road<u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in;text-indent:.5in">INNER JOIN fault4analyse AS fault<u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in">  ON ST_DWithin(road.the_geom, fault.geom, 100 ) <u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in">GROUP BY road.gid<u></u><u></u></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif"> postgis-users [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@<wbr>lists.osgeo.org</a>] <b>On Behalf Of </b>Ahmet Temiz<br><b>Sent:</b> Monday, October 31, 2016 3:59 AM<span class=""><br><b>To:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><wbr>><br></span><b>Subject:</b> Re: [postgis-users] Line within range of another line?<u></u><u></u></span></p><div><div class="h5"><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p><div><div><p class="MsoNormal" style="margin-left:.5in">Thank you,<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">It was great help.<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">Later, I had built this:<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">---<u></u><u></u></p></div><div><div><p class="MsoNormal" style="margin-left:.5in">WITH faultbuffer as (<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">   SELECT gid,ST_Union(St_Buffer(the_<wbr>geom,100)) as geom from fault4analyse group by gid<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">)<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"> <u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">SELECT road.gid,ST_Intersection(road.<wbr>the_geom,faultbuffer.geom ) as road_fayBuffer from road4analyse as road , faultbuffer <u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">  WHERE ST_Intersects(road.the_geom, faultbuffer.geom ) <u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">---<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">and it is reasonably fast.<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">Does it do same thing you have offered ?<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">kind regards<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div></div></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p><div><p class="MsoNormal" style="margin-left:.5in">On Sun, Oct 30, 2016 at 10:55 AM, Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> wrote:<u></u><u></u></p><blockquote style="border:none;border-right:solid #cccccc 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in"><div><div><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Use ST_DWithin  (works for both geography and geometry) and any kind of geometry, not just lines .  </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">My example is for geography since units are always in meters.  For geometry units you have to specify based on your spatial_ref_sys and geometries have to have same spatial ref sys</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">something like below will return all roads that are within 100 meters of a fault line</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">SELECT l.gid, l.geog</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">FROM roads As l</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">WHERE EXISTS (SELECT 1 FROM fault_lines As fl WHERE ST_DWithin(fl.geog, l.geog, 100) );</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">If you need to know the exact fault lines, do a JOIN instead – keep in mind if a road is close enough to more than one fault line, it will be duplicated</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">SELECT l.gid, l.geog, <a href="http://fl.name" target="_blank">fl.name</a>, ST_Distance(l.geog, fl.geog) AS dist</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">FROM roads AS l INNER JOIN fault_lines AS fl ON (ST_DWithin(fl.geog, l.geog, 100) );</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">If you want jus the closes fault line within 100 meters use DISTINCT ON</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">SELECT DISTINCT ON(l.gid)  l.gid, l.geog, <a href="http://fl.name" target="_blank">fl.name</a>, ST_Distance(l.geog, fl.geog) AS dist</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">FROM roads AS l INNER JOIN fault_lines AS fl ON (ST_DWithin(fl.geog, l.geog, 100) )</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">ORDER BY l.gid, dist;</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Hope that helps,</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Regina</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><a href="http://www.postgis.us" target="_blank">http://www.postgis.us</a></span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><a href="http://www.paragoncorporation.com" target="_blank">http://www.paragoncorporation.<wbr>com</a></span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><a href="http://postgis.net" target="_blank">http://postgis.net</a></span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif"> postgis-users [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@<wbr>lists.osgeo.org</a>] <b>On Behalf Of </b>Ahmet Temiz<br><b>Sent:</b> Sunday, October 30, 2016 2:59 AM<br><b>To:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><wbr>>; PostGIS Users Discussion <<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.<wbr>refractions.net</a>><br><b>Subject:</b> [postgis-users] Line within range of another line?</span><u></u><u></u></p><div><div><p class="MsoNormal" style="margin-left:1.0in"> <u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in">Hi,<u></u><u></u></p><div><p class="MsoNormal" style="margin-left:1.0in">How can I find Line within range of another line?<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:1.0in">I mean I try to find line section within certain range of another line.<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:1.0in">For example, road lines close to fault lines.<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:1.0in"> <u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:1.0in">Ps : I know that using st_buffer causes performance issue.<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:1.0in">Can you give any advice?<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:1.0in"> <u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:1.0in">Regards<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:1.0in"> <u></u><u></u></p></div><p class="MsoNormal" style="margin-left:1.0in"><br><br>-- <u></u><u></u></p><div><div><div><div><p class="MsoNormal" style="margin-left:1.0in">Ahmet Temiz<br>Jeoloji Müh.<br>Afet ve Acil Durum Yönetimi Başkanlığı<br>Bilgi İşlem  Dairesi Başkanlığı-CBS Grubu<br><br><br>________________________<br><br>Ahmet Temiz<br>Geological Eng.<br>Information Systems - GIS Group<br>Disaster and Emergency Management<br>of Presidency<u></u><u></u></p></div></div></div></div><p class="MsoNormal" style="margin-left:1.0in"> <u></u><u></u></p></div></div></div></div><p class="MsoNormal" style="margin-left:.5in"><br>______________________________<wbr>_________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a><u></u><u></u></p></blockquote></div><p class="MsoNormal" style="margin-left:.5in"><br><br clear="all"><u></u><u></u></p><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><p class="MsoNormal" style="margin-left:.5in">-- <u></u><u></u></p><div><div><div><div><div><p class="MsoNormal" style="margin-left:.5in">Ahmet Temiz<br>Jeoloji Müh.<br>Afet ve Acil Durum Yönetimi Başkanlığı<br>Bilgi İşlem  Dairesi Başkanlığı-CBS Grubu<br><br><br>________________________<br><br>Ahmet Temiz<br>Geological Eng.<br>Information Systems - GIS Group<br>Disaster and Emergency Management<br>of Presidency<u></u><u></u></p></div></div></div></div></div></div></div></div></div></div><br>______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a><br></blockquote></div><br><br clear="all"><div><br></div>-- <br><div class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div>Ahmet Temiz<br>Jeoloji Müh.<br>Afet ve Acil Durum Yönetimi Başkanlığı<br>Bilgi İşlem  Dairesi Başkanlığı-CBS Grubu<br><br><br>________________________<br><br>Ahmet Temiz<br>Geological Eng.<br>Information Systems - GIS Group<br>Disaster and Emergency Management<br>of Presidency</div></div></div></div></div>
</div>