[postgis-users] Problem with overlay

Tue Jul 3 13:49:15 PDT 2007

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.

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 Rhys
Stewart
Sent: Tuesday, July 03, 2007 3:32 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Problem with overlay

Ahoy there,

Well they don't overlap, they intersect. Had a similar problem and realized
that a geometry that intersects doesn't necessarily overlap.use relate to
see how the two geometries interact.
If you can find out how to decipher the output from the relate command drop
me a line, cuz i cant find it anywhere on the net. 

SELECT road_id, relate(b.the_geom,a.the_geom) FROM road_segments a, curves b
WHERE a.the_geom && b.the_geom AND intersects(a.the_geom, b.the_geom);

 road_id |  relate 
---------+-----------
      10 | 1FFF0FFF2   <<< the original row
      10 | 1F1F00102    <<< modified the original row so this row overlaps.
(2 rows)

SELECT road_id, relate(b.the_geom ,a.the_geom) FROM road_segments a, curves
b WHERE a.the_geom && b.the_geom AND overlaps(a.the_geom, b.the_geom);
 road_id |  relate
---------+-----------
      10 | 1F1F00102  <<< modified the original row so this row overlaps. 
(1 row)

hope that helps

Rhys

On 7/3/07, Jonathan Aguero <jua130 at psu.edu> wrote: 

Mark,
Thanks for your answer, here it is the sql you request
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);

The last SELECT is returning null in my system which means GEOS is failing
to detect the overlap. 

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 Mark
Cave-Ayland
Sent: Monday, July 02, 2007 5:45 PM
To: PostGIS Users Discussion 
Subject: Re: [postgis-users] Problem with overlay

On Sat, 2007-06-30 at 14:05 -0400, Jonathan Aguero wrote:
> Listers
>
> I got a multilinestringM shape that I loaded into postgis without
> problem (LRS). Then I used the linear referencing functions to create 
> the segments and curves tables from the tables (BTW, these linear
> referencing functions rock!!!!). Now, my problem is that when I tried
> to join the information on curves into the segments table using the 
> overlay function, it failed to detect several curves. Note that both
> geometries (segments and curves) are generated from the same base
> lines (LRS), therefore all curves should overlay one or more segments. 
> I am guessing I encountered a precision problem on GEOS but I wonder
> whether my guess is correct and if there is any solution (maybe
> snaptogrid).
>
> Thanks in Advance for your help
>
> PS:
>
> OS: Windows XP
>
> PGSQL: PostgreSQL 8.2.3 on i686-pc-mingw32, compiled by GCC gcc.exe
> (GCC) 3.4.2 (mingw-special)
>
> Postgis: 1.2
>
> GEOS: 3.0.0rc4-CAPI-1.3.3 
>
>
>
>
>
> 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

Hi Jonathan,

Not sure about whether I can help you, however we need a concrete test 
case to able to determine whether this is a bug or not. Basically an SQL
file containing a couple of tables containing some sample geometries
along with the join you are using would be a great help.

Kind regards, 

Mark.

--
ILande - Open Source Consultancy
http://www.ilande.co.uk

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________ 
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070703/192d3cf3/attachment.html>