[postgis-users] Problem with overlay

Wed Jul 4 11:01:39 PDT 2007

Can you post the actual WKT for the result linestrings?  I don't have a 
PostGIS instance handy to run the SQL.

I'm betting that it is a precision issue.  One way to handle this would 
be to use a buffer around one of the geometries you want to compare, to 
"fuzz" the comparison.

Jonathan Aguero wrote:
> Martin, 
>
> Here it is the original LINESTRINGM and the two geometries created from it
> with the locate_between_measures function. In theory, their intersection is
> a linestring but I might be experiencing the coordinate drift you mentioned.
> Now, as I see it, this is a precision problem, not a problem with my
> definitions of the geometries. What do you think? 
>
> CREATE TABLE lrs2 (gid int4, road_inv varchar(4)); 
> SELECT AddGeometryColumn('lrs2', 'the_geom', 2927, 'LINESTRINGM', 3);
> INSERT INTO LRS2 (gid,road_inv, the_geom) VALUES
> (1, '290', GeomFromText('LINESTRINGM(2406383.283 861534.851 0.07,2406359.112
> 861586.107 0.082,2406335.853 861620.089 0.091,2406310.002 861656.69
> 0.101,2406255.656 861722.092 0.12,2406212.863 861767.807 0.133,2406167.562
> 861808.508 0.147,2405858.946 862103.134 0.24,2405700.764 862300.136
> 0.26,2405597.455 862454.556 0.313,2405561.594 862504.091 0.33,2405526.644
> 862606.293 0.35,2405376.043 863080.764 0.444,2405107.283 863988.701
> 0.622,2405089.364 864074.548 0.639,2405083.673 864156.245 0.654,2405087.106
> 864240.691 0.67,2405115.272 864242.612 0.675,2405814.858 864282.694
> 0.8,2406187.349 864301.081 0.869,2407373.155 864345.473 1.09,2408850.001
> 864414.309 1.37,2409646.552 864450.813 1.522,2409724.132 864459.6
> 1.537,2409866.429 864498.596 1.565,2410149.525 864603.864 1.623,2410692.582
> 864788.24 1.733,2411535.108 865067.94 1.902,2411877.4 865184.391
> 1.971,2411997.555 865229.746 1.996,2412066.702 865258.947 2.01 ,2412122.725
> 865291.502 2.022,2412189.604 865347.555 2.039,2413099.432 866096.864
> 2.264,2413233.115 866208.772 2.297,2413337.133 866308.421 2.325,2413649.134
> 866597.512 2.406,2414104.962 867058.318 2.53,2414174.266 867117.086
> 2.547,2414329.433 867259.172 2.586,2414541.968 867377.955 2.631,2415561.425
> 867898.663 2.843,2415663.666 867950.203 2.865,2415711.381 867964.733
> 2.874,2415760.712 867974.327 2.883,2415834.682 867983.789 2.897,2415954.61
> 867986.436 2.919,2417079.882 868026.214 3.128,2417170.24 868029.877
> 3.145,2417195.169 868042.176 3.15,2417721.664 868392.441 3.27,2417796.276
> 868423.849 3.285,2418197.637 868559.305 3.366,2418832.192 868788.621
> 3.495,2419543.831 869053.946 3.641,2419770.891 869135.697 3.687,2419997.995
> 869225.542 3.733,2421265.462 869754.998 3.996,2421387.091 869796.842
> 4.021,2421476.281 869826.718 4.038,2421571.488 869846.445 4.057,2421664.639
> 869860.112 4.075,2421870.976 869869.151 4.115,2422422.273 869893.302
> 4.22,2423118.185 869912.227 4.351,2423322.658 869910.882 4.39,2423543.675
> 869887.113 4.432,2423761.002 869840.387 4.474,2423900.347 869825.931
> 4.5,2425047.751 869848.138 4.717,2425262.25 869851.04 4.757,2425349.276
> 869854.623 4.773,2425426.24 869868.376 4.788,2425559.956 869900.037
> 4.814,2425663.321 869931.861 4.834,2425807.25 869981.679 4.863 ,2427651.83
> 870598.722 5.23,2428262.085 870816.397 5.353,2430198.032 871483.911
> 5.74,2431505.587 871940.293 6,2432815.135 872390.593 6.26,2435415.059
> 873293.352 6.78,2437780.605 874089.52 7.257,2437820.359 874103.312 
> 7.265,2438045.344 874181.343 7.31,2438366.008 874269.892 7.373,2438966.15
> 874475.042 7.494,2440234.314 874916.234 7.749,2442988.007 875863.599
> 8.303,2443271.918 875961.321 8.36,2443560.018 876049.762 8.416,2443608.255 
> 876068.505 8.426,2443666.681 876097.327 8.438,2443732.747 876133.708
> 8.452,2443788.707 876175.211 8.465,2444125.925 876459.689 8.546,2444432.057
> 876711.694 8.62,2444631.05 876873.741 8.672,2444787.543 877000.844 
> 8.712,2444943.989 877119.08 8.751,2445073.637 877200.723 8.782,2445208.325
> 877277.273 8.813,2445344.218 877342.415 8.843,2445453.449 877396.299
> 8.868,2445811.841 877539.109 8.945,2445825.476 877557.714 8.95,2445938.059 
> 877587.195 8.972,2446085.112 877622.033 9,2446299.084 877660.47
> 9.04,2446608.007 877688.501 9.098,2447923.128 877847.699 9.346,2448425.232
> 877912.299 9.44,2452388.897 878388.295 10.2,2452678.1 878436.029
> 10.26 ,2452785.541 878453.306 10.28,2452839.486 878461.985 10.29,2452936.212
> 878477.791 10.309,2453147.417 878512.326 10.35,2453203.975 878520.16
> 10.361,2453529.509 878565.275 10.424,2453718.67 878587.397 10.46,2454143.639
>
> 878624.842 10.541,2454592.191 878672.24 10.628,2454813.568 878695.375
> 10.67,2455810.625 878816.796 10.861,2456783.057 878923.353
> 11.046,2457948.075 879068.144 11.269,2459000.96 879187.493 11.47,2459503.893
> 879234.27 11.566,2459785.345 879264.009 11.619,2460056.408 879299.012
> 11.671,2460426.624 879367.34 11.743,2460854.335 879471.822
> 11.826,2461219.594 879587.06 11.899,2462427.66 879986.859 12.14,2463875.818
> 880473.917 12.43 ,2464522.953 880697.024 12.558,2465634.74 881112.965
> 12.78,2466133.525 881277.739 12.86,2466464.64 881418.43 12.931,2468377.709
> 882145.196 13.333,2469223.238 882450.574 13.51,2470998.744 883136.062
> 13.871,2471904.723 883482.827 14.055,2472282.975 883623.285
> 14.132,2474415.388 884367.481 14.56,2476883.851 885294.585
> 15.057,2477619.048 885577.557 15.205,2478111.881 885802.791
> 15.307,2478588.523 886044.39 15.408,2479003.256 886276.557 
> 15.497,2479643.615 886689.823 15.641,2479954.098 886909.531
> 15.713,2482447.575 888637.839 16.284,2483535.9 889406.806 16.535,2484758.36
> 890295.507 16.82,2485433.873 890812.227 16.982,2486278.785 891394.59
> 17.177 ,2486869.777 891816.656 17.315,2487564.761 892296.149
> 17.475,2488317.986 892821.017 17.649,2489120.621 893384.276
> 17.836,2489836.725 893868.926 18,2491205.446 894830.469 18.31)', 2927
> ));
>
> CREATE TABLE road_segments (road_id int4, road_inv varchar(4),begmp real, 
> endmp real);
> SELECT AddGeometryColumn('road_segments', 'the_geom', 2927, 'LINESTRING',
> 2);
> INSERT INTO road_segments (road_id,road_inv, begmp, endmp) VALUES
> (10, '290', 1.48 , 1.79);
> UPDATE road_segments SET the_geom =
> force_2d(locate_between_measures(lrs2.the_geom,begmp,endmp))
> FROM lrs2;
>
> CREATE TABLE curves (curve_id int4, road_inv varchar(4),begmp real, endmp
> real);
> SELECT AddGeometryColumn('curves', 'the_geom', 2927, 'LINESTRING', 2); 
> INSERT INTO curves (curve_id,road_inv, begmp, endmp) VALUES
> (100, '290', 1.48, 1.79);
> UPDATE curves SET the_geom =
> force_2d(locate_between_measures(lrs2.the_geom,begmp,endmp))
> FROM lrs2;
>
> SELECT road_id FROM road_segments, curves 
> WHERE road_segments.the_geom && curves.the_geom AND
> overlaps(road_segments.the_geom, curves.the_geom);
>
>
> Jonathan Aguero Valverde
> Research Assistant and Ph.D. Candidate
> Pennsylvania State University
> The Pennsylvania Transportation Institute
> 201 Transportation Research Building
> University Park, PA 16802
> www.personal.psu.edu/jua130
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Martin
> Davis
> Sent: Tuesday, July 03, 2007 8:07 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Problem with overlay
>
> Can you post the WKT of two linestrings which you think should overlap?
>
> Overlaps has a very narrow definition. For Linestrings, two linestrings 
> overlap if and only if their intersection is itself a linestring (i.e. 
> they overlap in a point-set of dimension 1).
>
> I suspect you may be seeing the result of the inevitable coordinate 
> drift caused by computing a new coordinate along a line segment. In 
> almost all cases, doing this results in two new line segments which are 
> NOT coincident with the original.
>
> Jonathan Aguero wrote:
>   
>> Rhys,
>>
>> That's the point, they should /overlap/ since they were created from 
>> the same linear reference system and the road segment '/contains/' the 
>> curve.
>>
>> I also tried with /intersects/ but the problem is that it picks up 
>> more segments than I need. For example if a curve ends at the same 
>> point than a road segment; theoretically, the overlaps should pick up 
>> only that segment while /intersects/ pick also the following segment 
>> that starts when the previous segment ends. Think also in a situation 
>> where a road intersection is located in a curve: /intersects /will 
>> select the other segments that converge in the intersection even 
>> though they have no curves.
>>
>> Now, I might be misinterpreting what the overlap should do; if this is 
>> the case please let me know.
>>
>> Thanks.
>>
>>     
>
>   

-- 
Martin Davis
Senior Technical Architect
Refractions Research, Inc.
(250) 383-3022