[postgis-users] Line within range of another line?

Tue Nov 1 23:58:37 PDT 2016

Thank you,
it was great help.

particularly your  section worked well and helpful ( since I am going to
create geojson outcome from this sql for web app):
---

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.

SELECT road.gid,  ST_Union(ST_Intersection(road.the_geom,
ST_Buffer(fault.the_geom, 100)  ) ) as road_fayBuffer

FROM road4analyse as road

INNER JOIN fault4analyse AS fault

  ON ST_DWithin(road.the_geom, fault.geom, 100 )

GROUP BY road.gid

---
  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.

on the subject of correctness of distance calculation, how about using
epsg:3857. You see
I directly use geometries in 3857. Do you recommend me transform to
geography for correct distance calculation?

thank you again

regards

thanks again.

On Mon, Oct 31, 2016 at 11:39 PM, Regina Obe <lr at pcorp.us> wrote:

> Temiz,
>
>
>
> Good question.  They will yield similar results, but not exactly the same
>
>
>
> 1)      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.
>
> 2)      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.
>
> 3)      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.
>
> 4)      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.
>
> 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.
>
>
>
> 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.
>
>
>
> 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.
>
>
>
> SELECT road.gid,  ST_Union(ST_Intersection(road.the_geom,
> ST_Buffer(fault.the_geom, 100)  ) ) as road_fayBuffer
>
> FROM road4analyse as road
>
> INNER JOIN fault4analyse AS fault
>
>   ON ST_DWithin(road.the_geom, fault.geom, 100 )
>
> GROUP BY road.gid
>
>
>
>
>
>
>
> *From:* postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] *On
> Behalf Of *Ahmet Temiz
> *Sent:* Monday, October 31, 2016 3:59 AM
> *To:* PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> *Subject:* Re: [postgis-users] Line within range of another line?
>
>
>
> Thank you,
>
>
>
> It was great help.
>
>
>
> Later, I had built this:
>
> ---
>
> WITH faultbuffer as (
>
>    SELECT gid,ST_Union(St_Buffer(the_geom,100)) as geom from
> fault4analyse group by gid
>
> )
>
>
>
> SELECT road.gid,ST_Intersection(road.the_geom,faultbuffer.geom ) as
> road_fayBuffer from road4analyse as road , faultbuffer
>
>   WHERE ST_Intersects(road.the_geom, faultbuffer.geom )
>
> ---
>
>
>
> and it is reasonably fast.
>
>
>
> Does it do same thing you have offered ?
>
>
>
> kind regards
>
>
>
>
>
> On Sun, Oct 30, 2016 at 10:55 AM, Regina Obe <lr at pcorp.us> wrote:
>
> Use ST_DWithin  (works for both geography and geometry) and any kind of
> geometry, not just lines .
>
> 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
>
>
>
> something like below will return all roads that are within 100 meters of a
> fault line
>
>
>
> SELECT l.gid, l.geog
>
> FROM roads As l
>
> WHERE EXISTS (SELECT 1 FROM fault_lines As fl WHERE ST_DWithin(fl.geog,
> l.geog, 100) );
>
>
>
> 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
>
>
>
> SELECT l.gid, l.geog, fl.name, ST_Distance(l.geog, fl.geog) AS dist
>
> FROM roads AS l INNER JOIN fault_lines AS fl ON (ST_DWithin(fl.geog,
> l.geog, 100) );
>
>
>
>
>
>
>
> If you want jus the closes fault line within 100 meters use DISTINCT ON
>
>
>
> SELECT DISTINCT ON(l.gid)  l.gid, l.geog, fl.name, ST_Distance(l.geog,
> fl.geog) AS dist
>
> FROM roads AS l INNER JOIN fault_lines AS fl ON (ST_DWithin(fl.geog,
> l.geog, 100) )
>
> ORDER BY l.gid, dist;
>
>
>
>
>
>
>
> Hope that helps,
>
> Regina
>
> http://www.postgis.us
>
> http://www.paragoncorporation.com
>
>
>
> http://postgis.net
>
>
>
>
>
> *From:* postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] *On
> Behalf Of *Ahmet Temiz
> *Sent:* Sunday, October 30, 2016 2:59 AM
> *To:* PostGIS Users Discussion <postgis-users at lists.osgeo.org>; PostGIS
> Users Discussion <postgis-users at postgis.refractions.net>
> *Subject:* [postgis-users] Line within range of another line?
>
>
>
> Hi,
>
> How can I find Line within range of another line?
>
> I mean I try to find line section within certain range of another line.
>
> For example, road lines close to fault lines.
>
>
>
> Ps : I know that using st_buffer causes performance issue.
>
> Can you give any advice?
>
>
>
> Regards
>
>
>
>
>
> --
>
> Ahmet Temiz
> Jeoloji Müh.
> Afet ve Acil Durum Yönetimi Başkanlığı
> Bilgi İşlem  Dairesi Başkanlığı-CBS Grubu
>
>
> ________________________
>
> Ahmet Temiz
> Geological Eng.
> Information Systems - GIS Group
> Disaster and Emergency Management
> of Presidency
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>
>
>
> --
>
> Ahmet Temiz
> Jeoloji Müh.
> Afet ve Acil Durum Yönetimi Başkanlığı
> Bilgi İşlem  Dairesi Başkanlığı-CBS Grubu
>
>
> ________________________
>
> Ahmet Temiz
> Geological Eng.
> Information Systems - GIS Group
> Disaster and Emergency Management
> of Presidency
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>

-- 
Ahmet Temiz
Jeoloji Müh.
Afet ve Acil Durum Yönetimi Başkanlığı
Bilgi İşlem  Dairesi Başkanlığı-CBS Grubu

________________________

Ahmet Temiz
Geological Eng.
Information Systems - GIS Group
Disaster and Emergency Management
of Presidency
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20161102/723f5c6a/attachment.html>