2

In the photo there are two lines that are parallel to each other. I want to bring them at same coordinates and carry out the difference between them, which will be the small part of red line from the bottom. To bring them closer I am using ST_Snap and ST_SnapTogrid, but they are not coming at the same place. Whenever I see their WKT values, there is always a difference between them. Is there any other way of doing it? This is just an example, amd in my case I have large number of lines. The method I am using is:

    Create Table Mytable As select Difference(
    ST_snaptogrid(st_snap(area2.geometry,area1.geometry,10),1)
    ,ST_snaptogrid(st_snap(area1.geometry,area2.geometry,10),1))
As Geometry from Area1 area1,Area2 area2 

My problem is that there is always a difference between the line no matter how much I increase the value of ST_Snap

enter image description here

The area 1 WKT is:

LINESTRING(328073.546864 6807587.379884, 328083.270982 6807590.696921, 328091.225316 6807593.259645)
LINESTRING(327797.341809 6809087.203224, 327817.646694 6809124.933343, 327834.793422 6809156.142693)
LINESTRING(327858.73345 6809194.273138, 327846.069419 6809176.233451, 327834.793422 6809156.142693)
LINESTRING(327797.341809 6809087.203224, 327817.646694 6809124.933343, 327834.793422 6809156.142693)
LINESTRING(327858.73345 6809194.273138, 327846.069419 6809176.233451, 327834.793422 6809156.142693)
LINESTRING(327981.300935 6808365.031003, 327985.016518 6808372.66537, 327989.310438 6808385.47058, 327993.133348 6808398.916238, 327996.174084 6808410.698549, 327997.657363 6808418.929391, 327998.059613 6808425.623853, 327997.609842 6808433.239406, 327994.421005 6808440.092431, 327991.176344 6808443.450785, 327987.15875 6808445.332367, 327981.975209 6808445.775286, 327977.130078 6808444.892307, 327971.232208 6808442.864326, 327962.314799 6808440.573971, 327955.585748 6808437.708019, 327949.390128 6808437.284516, 327943.311077 6808440.85768, 327940.628234 6808445.778085, 327941.422165 6808452.041745, 327941.927263 6808456.636409, 327941.133349 6808463.27772, 327935.344827 6808472.914984)
LINESTRING(327933.042454 6808901.492707, 327949.590932 6808892.427465, 327982.597201 6808872.637156, 328004.02018 6808856.149622, 328024.80521 6808828.733857, 328044.07552 6808799.16736, 328059.914356 6808775.343777, 328069.481633 6808762.452965, 328076.685954 6808755.869418)
LINESTRING(328076.685954 6808755.869418, 328087.981025 6808752.712386, 328107.793822 6808752.857009, 328133.149461 6808753.121076)
LINESTRING(327933.042454 6808901.492707, 327949.590932 6808892.427465, 327982.597201 6808872.637156, 328004.02018 6808856.149622, 328024.80521 6808828.733857, 328044.07552 6808799.16736, 328059.914356 6808775.343777, 328069.481633 6808762.452965, 328076.685954 6808755.869418)
LINESTRING(328076.685954 6808755.869418, 328087.981025 6808752.712386, 328107.793822 6808752.857009, 328133.149461 6808753.121076)
LINESTRING(328132.250771 6808620.469034, 328137.46438 6808632.649227, 328141.963598 6808644.902069, 328149.764701 6808663.874684, 328153.257259 6808673.914148)
LINESTRING(327840.846786 6808834.303038, 327842.301786 6808804.3626, 327843.600265 6808785.782563, 327847.983413 6808771.875459, 327858.662089 6808755.338149, 327867.331653 6808742.453688, 327871.202073 6808730.517056, 327871.980273 6808717.386586)
LINESTRING(327836.305251 6808936.888488, 327836.651557 6808919.926536, 327837.637871 6808889.766109, 327840.134355 6808848.2158, 327840.846786 6808834.303038)
LINESTRING(327840.846786 6808834.303038, 327811.866351 6808832.593119, 327779.316555 6808832.200754, 327755.113508 6808832.555611, 327747.690049 6808833.240576)
LINESTRING(327741.101971 6808968.246202, 327782.687231 6808955.211629, 327836.305251 6808936.888488)
LINESTRING(327836.305251 6808936.888488, 327836.651557 6808919.926536, 327837.637871 6808889.766109, 327840.134355 6808848.2158, 327840.846786 6808834.303038)
LINESTRING(327836.305251 6808936.888488, 327838.246057 6808936.212788, 327898.756704 6808915.717157, 327906.53266 6808913.088692)
LINESTRING(327840.846786 6808834.303038, 327842.301786 6808804.3626, 327843.600265 6808785.782563, 327847.983413 6808771.875459, 327858.662089 6808755.338149, 327867.331653 6808742.453688, 327871.202073 6808730.517056, 327871.980273 6808717.386586)
LINESTRING(327741.101971 6808968.246202, 327782.687231 6808955.211629, 327836.305251 6808936.888488)
LINESTRING(327836.305251 6808936.888488, 327836.651557 6808919.926536, 327837.637871 6808889.766109, 327840.134355 6808848.2158, 327840.846786 6808834.303038)
LINESTRING(327836.305251 6808936.888488, 327838.246057 6808936.212788, 327898.756704 6808915.717157, 327906.53266 6808913.088692)
LINESTRING(327840.846786 6808834.303038, 327811.866351 6808832.593119, 327779.316555 6808832.200754, 327755.113508 6808832.555611, 327747.690049 6808833.240576)
LINESTRING(327741.101971 6808968.246202, 327782.687231 6808955.211629, 327836.305251 6808936.888488)
LINESTRING(327836.305251 6808936.888488, 327836.651557 6808919.926536, 327837.637871 6808889.766109, 327840.134355 6808848.2158, 327840.846786 6808834.303038)
LINESTRING(327836.305251 6808936.888488, 327838.246057 6808936.212788, 327898.756704 6808915.717157, 327906.53266 6808913.088692)
LINESTRING(327840.846786 6808834.303038, 327842.301786 6808804.3626, 327843.600265 6808785.782563, 327847.983413 6808771.875459, 327858.662089 6808755.338149, 327867.331653 6808742.453688, 327871.202073 6808730.517056, 327871.980273 6808717.386586)
LINESTRING(327836.305251 6808936.888488, 327836.651557 6808919.926536, 327837.637871 6808889.766109, 327840.134355 6808848.2158, 327840.846786 6808834.303038)
LINESTRING(327840.846786 6808834.303038, 327811.866351 6808832.593119, 327779.316555 6808832.200754, 327755.113508 6808832.555611, 327747.690049 6808833.240576)
LINESTRING(327741.101971 6808968.246202, 327782.687231 6808955.211629, 327836.305251 6808936.888488)
LINESTRING(327836.305251 6808936.888488, 327836.651557 6808919.926536, 327837.637871 6808889.766109, 327840.134355 6808848.2158, 327840.846786 6808834.303038)
LINESTRING(327836.305251 6808936.888488, 327838.246057 6808936.212788, 327898.756704 6808915.717157, 327906.53266 6808913.088692)
LINESTRING(327906.53266 6808913.088692, 327907.246699 6808912.828987, 327923.50676 6808906.284046, 327933.042454 6808901.492707)
LINESTRING(327836.305251 6808936.888488, 327838.246057 6808936.212788, 327898.756704 6808915.717157, 327906.53266 6808913.088692)
LINESTRING(327741.101971 6808968.246202, 327782.687231 6808955.211629, 327836.305251 6808936.888488)
LINESTRING(327836.305251 6808936.888488, 327836.651557 6808919.926536, 327837.637871 6808889.766109, 327840.134355 6808848.2158, 327840.846786 6808834.303038)
LINESTRING(327836.305251 6808936.888488, 327838.246057 6808936.212788, 327898.756704 6808915.717157, 327906.53266 6808913.088692)
LINESTRING(327741.101971 6808968.246202, 327782.687231 6808955.211629, 327836.305251 6808936.888488)
LINESTRING(327906.53266 6808913.088692, 327907.246699 6808912.828987, 327923.50676 6808906.284046, 327933.042454 6808901.492707)
LINESTRING(327836.305251 6808936.888488, 327836.651557 6808919.926536, 327837.637871 6808889.766109, 327840.134355 6808848.2158, 327840.846786 6808834.303038)
LINESTRING(327836.305251 6808936.888488, 327838.246057 6808936.212788, 327898.756704 6808915.717157, 327906.53266 6808913.088692)
LINESTRING(327933.042454 6808901.492707, 327949.590932 6808892.427465, 327982.597201 6808872.637156, 328004.02018 6808856.149622, 328024.80521 6808828.733857, 328044.07552 6808799.16736, 328059.914356 6808775.343777, 328069.481633 6808762.452965, 328076.685954 6808755.869418)

and WKT of Area2 is

LINESTRING(328098.741578 6807595.992692, 328091.228 6807593.261)
LINESTRING(327834.793 6809156.148, 327838.634078 6809163.160107)
LINESTRING(327834.793 6809156.148, 327838.634078 6809163.160107)
LINESTRING(327834.793 6809156.148, 327843.409 6809171.877)
LINESTRING(327834.793 6809156.148, 327843.409 6809171.877)
LINESTRING(327933.086892 6808443.109881, 327940.284 6808445.234, 327949.041176 6808448.53097)
LINESTRING(328077.154479 6808747.885264, 328076.684 6808755.871)
LINESTRING(328077.154479 6808747.885264, 328076.684 6808755.871)
LINESTRING(328077.18164 6808747.424254, 328076.684 6808755.871)
LINESTRING(328077.18164 6808747.424254, 328076.684 6808755.871)
LINESTRING(328153.261 6808673.914, 328155.571 6808678.871, 328156.421398 6808681.259019)
LINESTRING(327840.843 6808834.31, 327840.436553 6808842.291967)
LINESTRING(327840.843 6808834.31, 327840.436553 6808842.291967)
LINESTRING(327840.843 6808834.31, 327840.436553 6808842.291967)
LINESTRING(327836.468448 6808928.892544, 327836.303 6808936.884)
LINESTRING(327836.468448 6808928.892544, 327836.303 6808936.884)
LINESTRING(327836.468448 6808928.892544, 327836.303 6808936.884)
LINESTRING(327840.843 6808834.31, 327840.135 6808848.214, 327837.64 6808889.774, 327836.654 6808919.93, 327836.303 6808936.884)
LINESTRING(327840.843 6808834.31, 327840.135 6808848.214, 327837.64 6808889.774, 327836.654 6808919.93, 327836.303 6808936.884)
LINESTRING(327840.843 6808834.31, 327840.135 6808848.214, 327837.64 6808889.774, 327836.654 6808919.93, 327836.303 6808936.884)
LINESTRING(327840.843 6808834.31, 327840.135 6808848.214, 327837.64 6808889.774, 327836.654 6808919.93, 327836.303 6808936.884)
LINESTRING(327840.843 6808834.31, 327840.135 6808848.214, 327837.64 6808889.774, 327836.654 6808919.93, 327836.303 6808936.884)
LINESTRING(327836.479375 6808928.364739, 327836.303 6808936.884)
LINESTRING(327836.479375 6808928.364739, 327836.303 6808936.884)
LINESTRING(327836.479375 6808928.364739, 327836.303 6808936.884)
LINESTRING(327840.843 6808834.31, 327840.436595 6808842.291149)
LINESTRING(327840.843 6808834.31, 327840.436595 6808842.291149)
LINESTRING(327840.843 6808834.31, 327840.436595 6808842.291149)
LINESTRING(327843.878678 6808934.311913, 327838.245 6808936.22, 327836.303 6808936.884)
LINESTRING(327843.878678 6808934.311913, 327838.245 6808936.22, 327836.303 6808936.884)
LINESTRING(327843.878678 6808934.311913, 327838.245 6808936.22, 327836.303 6808936.884)
LINESTRING(327906.529 6808913.09, 327898.958813 6808915.656595)
LINESTRING(327906.529 6808913.09, 327898.958813 6808915.656595)
LINESTRING(327844.362871 6808934.147921, 327838.245 6808936.22, 327836.303 6808936.884)
LINESTRING(327844.362871 6808934.147921, 327838.245 6808936.22, 327836.303 6808936.884)
LINESTRING(327844.362871 6808934.147921, 327838.245 6808936.22, 327836.303 6808936.884)
LINESTRING(327906.529 6808913.09, 327898.76 6808915.724, 327838.245 6808936.22, 327836.303 6808936.884)
LINESTRING(327906.529 6808913.09, 327898.76 6808915.724, 327838.245 6808936.22, 327836.303 6808936.884)
LINESTRING(327906.529 6808913.09, 327898.76 6808915.724, 327838.245 6808936.22, 327836.303 6808936.884)
LINESTRING(327906.529 6808913.09, 327898.76 6808915.724, 327838.245 6808936.22, 327836.303 6808936.884)
LINESTRING(327933.194508 6808892.287715, 327933.047 6808901.5)

The area2 has extra lines which I want in difference. These are not complete geometries because here I cannot include all because of word restriction

  • 1
    a) Do only one snap (area2 to area1). Because if you snap both to the other one, the black line will be right and the red line will be left. – Redoute Sep 18 '17 at 15:12
  • b) More important, ST_Snap will only snap to vertices (the endpoints of a line), not to parts of a line. – Redoute Sep 18 '17 at 15:14
  • I loaded your linestrings in QGIS, but I can't guess what you try to calculate. Are you trying a kind of map matching? This would be a new question. Do you want to match row 1 to row 1, row 2 to row 2 etc.? In the first cases they nearly connect (end point of area1 row1 is very close to end point of area2 row1, start points are in opposite direction), what would you want to calculate? – Redoute Sep 19 '17 at 11:09
  • I think I got it now. You want to find rows 6, 7 (and 8, 9, 10), 11 and 41 of area2, that don't visually match on the network in area1. This needs some "map matching", and I can't help on this. – Redoute Sep 19 '17 at 11:24
  • @Redoute no you have not got it right if you draw both areas 1 and 2 they both contain the line no 41. Not All the lines in Area1 and Area2 matches I want all these lines as there are 3 extra lines in Area2 which does not match to Area1 I want those lines. Wait I can tell you the exact row – mimetype Sep 19 '17 at 11:45
2

If you have two lines (not necessary parallel), each defined with two vertices: start point and end point. Then you can find two points on line 1: p1 the nearest/closest to the start point of line 2 and p2 the closest from the end point of line 2.

Then the part between p1 and p2 is the part of line 1 where line 2 "covers" line 1. The remaining "difference" can have up to two parts: The part between the start point of line 1 and the first of (p1, p2), and the part between the second of (p1, p2) and the end point of line 1.

For reduced complexity I assume you have one table with pairs of lines in columns redline and blackline. Then this query should return the "difference" (redline - blackline), that are the parts of redline which are not "covered" by blackline:

-- create sample data: one pair of lines corresponding to your graphic
WITH mytable(id, redline, blackline) AS (VALUES
    (1,
     geometry 'LINESTRING(73 8, 79 201)',
     geometry 'LINESTRING(67 9, 72 166)')),
-- first expression: find points on redline
-- ST_Line_Locate_Point returns a value between 0 (start point)
-- and 1 (end point)
s1 AS (SELECT
    id, redline,
    ST_Line_Locate_Point(redline, ST_StartPoint(blackline)) p1,
    ST_Line_Locate_Point(redline, ST_EndPoint(blackline)) p2
    FROM mytable),
-- second expression: calculate and return both possible parts of
-- the "difference"
s2 AS (SELECT
    id,
    ST_Line_Substring(redline, 0.0, least(p1, p2)) geom
    FROM s1
    WHERE least(p1, p2) > 0.0
UNION SELECT
    id,
    ST_Line_Substring(redline, greatest(p1, p2), 1.0) geom
    FROM s1
    WHERE greatest(p1, p2) < 1.0)
-- ST_Union: if possible, return a simple linestring, else a multilinestring
--   no result, if blackline "covers" the full length of redline
SELECT
    id,
    ST_AsEWKT(ST_Union(geom)) diff
    FROM s2
    GROUP BY id;

result:

id  diff
1   MULTILINESTRING((73 8,73.0252648518171 8.81268606678289),(77.9062089312056 165.816387287113,79 201))
  • Thanks for your answer.This works on small geometry but when I use it in my original geometries it is not working. – mimetype Sep 19 '17 at 8:16
  • Can you help me out with this?. I can show WKT format of my areas – mimetype Sep 19 '17 at 8:23
  • Edit the question to include more information. Are area1.geometry and area2.geometry polygons? Then I don't understand what you try to calculate. – Redoute Sep 19 '17 at 9:24
  • If tables area1 and area2 contain more than one row each: How do you identify pairs? – Redoute Sep 19 '17 at 9:27
  • no both are linestrings. I am including WKT format of both areasin next 2 minutes – mimetype Sep 19 '17 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.