[postgis-users] Problem with overlay
Martin Davis
mbdavis at refractions.net
Tue Jul 3 13:20:08 PDT 2007
For the specification of relate, have a look at the OGC Simple Features
for SQL specification. You should be able to find this with a Web
search. You can also look at the JTS documentation & Javadoc.
Rhys Stewart wrote:
> 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 <mailto: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 <http://www.personal.psu.edu/jua130>
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> <mailto:postgis-users-bounces at postgis.refractions.net>
> [mailto: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 <http://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
> <mailto:postgis-users at postgis.refractions.net>
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> <mailto:postgis-users at postgis.refractions.net>
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> <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
>
--
Martin Davis
Senior Technical Architect
Refractions Research, Inc.
(250) 383-3022
More information about the postgis-users
mailing list