[postgis-users] Query to divide a polygon in two polygons considering intersection with other polygons which define different values for any particular area

Fri Jun 1 11:09:27 PDT 2007

bonifax,

Probably the easiest way to do this is to use your dividing line to 
construct a polygon that covers one of the parts you want after it is 
divided. then you can use intersection() and difference() to get the two 
parts.

-Steve W.

bonifax wrote:
> Hello all, 
> 
> a bit of help would be really appreciated to solve a problem with seems to
> be a bit difficult, at least for me.
> 
> I have a polygon (AZIENDE_PRE) defined by:
> 
> http://www.nabble.com/file/p10915354/aziende_pre.sql aziende_pre.sql 
> 
> and a series of polygons that covers the area of the polygon AZIENDE_PRE.
> 
> They are defined by:
> 
> http://www.nabble.com/file/p10915354/valori.sql valori.sql 
> 
> Such polygons identifies the value, for every squared meter, of the lands
> occupied by the polygon AZIENDE_PRE.
> Through a simple query (
> http://www.nabble.com/file/p10915354/poligono_valore_tot.sql
> poligono_valore_tot.sql ) I'm able to find the relative total value of the
> land AZIENDE_PRE.
> My objective  is to be able to divide the polygon AZIENDE_PRE (through the
> indication of one straight line 
> http://www.nabble.com/file/p10915354/line.sql line.sql ) in two polygons,
> both having the same VALUE (approximated) and subsequently to modify one or
> the other diminishing or increasing the relative obtained VALUE.
> 
> For example I calculate that AZIENDE_PRE has a value of 100 Euro and I want
> to divide it in 2 polygons: the first with a value of 60 Euro the other with
> a value of 40 Euro.
> 
> Is this possible?
> 
> Thanks in advance,
> 
> bonifax
> 
> -------------------SQL---------------------
> 
> ==AZIENDE_PRE===
> 
> create table aziende_pre (gid int4 , COD_PRE varchar , NOME_PRE varchar ,
> NOME_PRE0 float8 );
> ALTER TABLE aziende_pre ADD COLUMN the_geom geometry;
> ALTER TABLE aziende_pre ALTER COLUMN the_geom SET STORAGE MAIN;
> ALTER TABLE aziende_pre ADD CONSTRAINT aziende_pre_pkey PRIMARY KEY(gid);
> ALTER TABLE aziende_pre ADD CONSTRAINT enforce_dims_the_geom CHECK
> (ndims(the_geom) = 2);
> ALTER TABLE aziende_pre ADD CONSTRAINT enforce_geotype_the_geom CHECK
> (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
> ALTER TABLE aziende_pre ADD CONSTRAINT enforce_srid_the_geom CHECK
> (srid(the_geom) = -1);
> 
> insert into aziende_pre values ('0','198','NOME
> COGNOME','171.0',GeometryFromText('MULTIPOLYGON (( (-16266.6710 -35891.0000
> , -16274.8810 -35898.3500 , -16275.7530 -35899.1270 , -16280.1430
> -35903.5010 , -16282.2910 -35905.6410 , -16283.7400 -35907.1820 ,
> -16300.0900 -35924.1600 , -16301.2810 -35925.8700 , -16269.0710 -35950.6500
> , -16254.7110 -35962.5300 , -16248.8110 -35966.8000 , -16245.3110
> -35971.3300 , -16236.7600 -35949.0300 , -16229.9300 -35932.7000 ,
> -16228.7300 -35930.6500 , -16228.4110 -35929.7900 , -16245.3910 -35911.7700
> , -16248.2110 -35908.9500 , -16249.0710 -35908.5200 , -16266.6710
> -35891.0000)))',-1) );
> 
> ------------------------------------------
> 
> ==VALORI==
> 
> create table valori (gid int4 , ID float8 , VALORI float8 );
> ALTER TABLE valori ADD COLUMN the_geom geometry;
> ALTER TABLE valori ALTER COLUMN the_geom SET STORAGE MAIN;
> ALTER TABLE valori ADD CONSTRAINT valori_pkey PRIMARY KEY(gid);
> ALTER TABLE valori ADD CONSTRAINT enforce_dims_the_geom CHECK
> (ndims(the_geom) = 2);
> ALTER TABLE valori ADD CONSTRAINT enforce_geotype_the_geom CHECK
> (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
> ALTER TABLE valori ADD CONSTRAINT enforce_srid_the_geom CHECK
> (srid(the_geom) = -1);
> 
> insert into valori values
> ('0','24397.0','0.7429',GeometryFromText('MULTIPOLYGON (( (-16397.4702
> -36184.7119 , -16395.3075 -36171.2696 , -16398.6641 -36159.8448 ,
> -16419.9910 -36138.1500 , -16432.2910 -36129.6900 , -16453.4010 -36116.5300
> , -16452.5510 -36114.0500 , -16448.8710 -36106.8700 , -16445.8810
> -36099.5200 , -16445.3710 -36098.5000 , -16442.8141 -36094.3381 ,
> -16440.7510 -36090.9800 , -16435.2810 -36083.4600 , -16429.9910 -36077.0500
> , -16430.0406 -36068.9942 , -16430.0710 -36064.0600 , -16429.3910
> -36058.0700 , -16429.1484 -36052.6653 , -16428.8810 -36046.7100 ,
> -16428.3423 -36045.5586 , -16426.4810 -36041.5800 , -16417.0800 -36030.1290
> , -16401.2710 -36011.3300 , -16386.8310 -36021.3300 , -16383.5810
> -36024.0600 , -16380.5110 -36025.9400 , -16368.2810 -36035.1700 ,
> -16364.2800 -36037.7252 , -16333.0810 -36057.6500 , -16341.7110 -36075.1700
> , -16343.2510 -36079.9500 , -16343.2510 -36081.2300 , -16333.3310
> -36089.6900 , -16324.7010 -36095.4200 , -16304.6210 -36104.9100 ,
> -16296.1610 -36107.1300 , -16291.7920 -36108.3290 , -16291.7460 -36108.1330
> , -16291.4729 -36107.0300 , -16287.6220 -36091.4740 , -16281.2100
> -36065.5980 , -16280.7700 -36064.4146 , -16278.7148 -36058.8877 ,
> -16275.3944 -36049.9583 , -16273.8950 -36045.9260 , -16272.1458 -36041.3342
> , -16266.2280 -36025.7990 , -16264.3710 -36018.9300 , -16258.2110
> -36005.0900 , -16250.6110 -35984.4900 , -16248.1335 -35979.2533 ,
> -16247.6210 -35978.1700 , -16245.6966 -35972.4719 , -16245.3110 -35971.3300
> , -16237.4577 -35950.8495 , -16278.3196 -35905.5236 , -16280.1430
> -35903.5010 , -16295.5610 -35882.6300 , -16299.8310 -35876.6400 ,
> -16274.8125 -35849.9536 , -16272.8310 -35847.8400 , -16267.1010 -35842.3700
> , -16260.8610 -35835.4500 , -16256.7610 -35831.5200 , -16250.1810
> -35824.0900 , -16250.3500 -35823.2300 , -16251.1600 -35821.9900 ,
> -16252.5182 -35808.3679 , -16253.9712 -35796.0854 , -16255.0380 -35783.5659
> , -16256.6687 -35772.5293 , -16257.2319 -35765.9434 , -16256.4649
> -35759.9286 , -16258.0410 -35756.9200 , -16275.3910 -35734.6100 ,
> -16278.6410 -35729.9100 , -16289.3210 -35712.9900 , -16290.1810 -35711.5400
> , -16301.3710 -35694.2700 , -16306.3310 -35688.2100 , -16310.6010
> -35684.2700 , -16327.6910 -35670.6900 , -16332.5600 -35664.4500 ,
> -16337.9120 -35657.3680 , -16343.4390 -35650.3360 , -16351.2730 -35648.2230
> , -16353.6630 -35647.4440 , -16354.8300 -35646.4440 , -16357.2030
> -35644.1270 , -16358.9710 -35642.2290 , -16359.6510 -35643.1700 ,
> -16364.6110 -35656.9300 , -16365.5510 -35657.9500 , -16368.8810 -35663.8500
> , -16372.8110 -35674.7900 , -16374.1010 -35687.7800 , -16379.4810
> -35685.3000 , -16381.7910 -35684.1000 , -16390.1960 -35680.1750 ,
> -16409.6000 -35671.1140 , -16424.6010 -35664.1100 , -16429.3000 -35660.9400
> , -16436.9100 -35670.6900 , -16442.6950 -35679.4980 , -16444.4790
> -35681.8070 , -16444.4900 -35681.8280 , -16461.4191 -35664.5628 ,
> -16470.5800 -35655.2200 , -16476.4800 -35650.2600 , -16477.0700 -35649.3200
> , -16483.8714 -35643.6611 , -16505.1100 -35625.9900 , -16520.8300
> -35614.3700 , -16530.4900 -35606.4200 , -16538.8756 -35614.3667 ,
> -16543.4800 -35618.7300 , -16549.3700 -35624.8800 , -16553.3910 -35625.0500
> , -16561.4210 -35618.3000 , -16578.7710 -35606.5900 , -16585.6110
> -35613.0900 , -16607.4910 -35635.6500 , -16620.3010 -35647.9500 ,
> -16626.2910 -35653.0000 , -16633.4710 -35660.1800 , -16653.8010 -35640.3500
> , -16659.7910 -35635.4800 , -16677.5610 -35621.5500 , -16680.5510
> -35618.7300 , -16686.5410 -35614.2000 , -16698.5910 -35604.1100 ,
> -16704.7410 -35598.4390 , -16706.2800 -35597.0200 , -16708.7100 -35594.9300
> , -16711.7500 -35592.2300 , -16717.0400 -35588.6500 , -16723.3700
> -35583.4300 , -16733.9700 -35571.9000 , -16739.7810 -35574.8000 ,
> -16753.1110 -35585.0600 , -16755.2410 -35586.3400 , -16757.3810 -35585.6500
> , -16768.1510 -35581.8100 , -16780.2010 -35575.0600 , -16799.0010
> -35561.3800 , -16802.2510 -35559.0800 , -16804.2110 -35557.9700 ,
> -16807.4610 -35556.6800 , -16808.7410 -35556.3400 , -16810.7910 -35555.5700
> , -16813.1010 -35556.6000 , -16814.1310 -35557.5400 , -16819.3410
> -35563.8600 , -16821.9910 -35567.9600 , -16823.7810 -35571.8090 ,
> -16826.6910 -35574.7200 , -16835.0390 -35584.5410 , -16837.8010 -35587.7900
> , -16848.1839 -35597.7830 , -16853.7810 -35603.1700 , -16861.1310
> -35607.5300 , -16869.4210 -35610.7800 , -16873.0900 -35611.8900 ,
> -16886.4200 -35617.1900 , -16888.6250 -35617.8980 , -16893.9850 -35619.6600
> , -16892.8930 -35622.2170 , -16891.5240 -35626.0120 , -16886.0000
> -35630.7800 , -16884.7200 -35633.6800 , -16876.5566 -35648.3134 ,
> -16870.1517 -35662.7938 , -16865.2886 -35669.2032 , -16862.9736 -35672.2544
> , -16861.6998 -35673.9332 , -16860.4256 -35675.6126 , -16848.4462
> -35693.8913 , -16842.7196 -35700.7174 , -16842.7195 -35700.7175 ,
> -16840.3807 -35703.5054 , -16832.3152 -35713.1195 , -16819.8478 -35729.2423
> , -16819.8477 -35729.2423 , -16816.9103 -35733.0411 , -16814.0492
> -35737.4514 , -16811.2025 -35741.8430 , -16803.6115 -35751.5758 ,
> -16799.8159 -35758.1038 , -16799.5860 -35758.4827 , -16799.5859 -35758.4828
> , -16791.0980 -35772.4655 , -16786.1757 -35780.2991 , -16781.6959
> -35788.4391 , -16781.6959 -35788.4391 , -16779.4286 -35792.5588 ,
> -16777.1613 -35796.6786 , -16772.1796 -35807.3609 , -16767.6724 -35816.6189
> , -16765.0630 -35822.0788 , -16762.4536 -35827.5386 , -16760.2594
> -35833.8294 , -16758.0652 -35840.1202 , -16755.9895 -35845.8175 ,
> -16753.9139 -35851.5147 , -16751.6014 -35867.0632 , -16752.0462 -35875.0154
> , -16752.4910 -35882.9675 , -16753.2552 -35887.5273 , -16754.0200
> -35892.0880 , -16753.1910 -35893.8200 , -16747.9010 -35901.5100 ,
> -16737.0400 -35905.9600 , -16728.6700 -35908.0100 , -16724.3900 -35910.3100
> , -16718.3960 -35918.2410 , -16715.3350 -35921.8220 , -16713.0910
> -35924.9610 , -16712.4530 -35925.8550 , -16706.8980 -35932.9870 ,
> -16706.3790 -35933.6190 , -16705.3080 -35934.9230 , -16700.6400 -35939.8000
> , -16698.5000 -35942.7000 , -16695.5900 -35953.2200 , -16685.7710
> -35969.0300 , -16683.7700 -35971.6240 , -16676.0210 -35981.6700 ,
> -16674.5710 -35984.6600 , -16671.9210 -35988.0800 , -16664.3210 -35999.7900
> , -16659.2150 -36005.4820 , -16654.7410 -36010.4700 , -16640.2210
> -36028.0800 , -16636.2910 -36032.1800 , -16627.5090 -36042.6220 ,
> -16626.9870 -36043.4190 , -16624.9580 -36046.5200 , -16621.5460 -36051.6040
> , -16616.8000 -36058.5000 , -16614.6630 -36062.2540 , -16613.3790
> -36063.8690 , -16612.0070 -36065.5920 , -16607.2300 -36070.1200 ,
> -16622.2710 -36073.7100 , -16626.1210 -36074.3100 , -16634.3210 -36076.4500
> , -16652.6110 -36077.4700 , -16654.3210 -36077.9000 , -16670.1310
> -36073.9700 , -16672.2610 -36073.1100 , -16674.0610 -36073.0300 ,
> -16679.8710 -36072.3500 , -16681.3210 -36075.6800 , -16680.9810 -36080.1200
> , -16681.2410 -36083.6300 , -16680.1310 -36098.9200 , -16680.0410
> -36106.9600 , -16679.5310 -36109.9500 , -16677.9010 -36115.2500 ,
> -16674.4290 -36121.1310 , -16667.9110 -36132.1700 , -16667.6510 -36133.6200
> , -16666.1410 -36136.4910 , -16665.2610 -36140.8000 , -16662.8610
> -36144.9000 , -16660.4710 -36147.5500 , -16648.4210 -36155.5000 ,
> -16643.3810 -36160.2000 , -16637.2310 -36177.7200 , -16633.8910 -36183.9600
> , -16631.1610 -36187.8900 , -16627.1410 -36194.9800 , -16625.5210
> -36196.6900 , -16619.8810 -36204.4700 , -16616.2010 -36210.9600 ,
> -16611.5010 -36215.4900 , -16611.0710 -36217.2800 , -16609.1110 -36221.5600
> , -16608.3410 -36226.2600 , -16610.7300 -36234.5500 , -16611.6700
> -36236.6800 , -16611.6330 -36238.0580 , -16611.4910 -36243.3500 ,
> -16611.2510 -36249.0800 , -16614.0710 -36257.6200 , -16619.7910 -36267.4500
> , -16626.2910 -36286.1600 , -16626.5410 -36305.9900 , -16625.4413
> -36316.5596 , -16627.4810 -36330.3500 , -16613.3010 -36351.6300 ,
> -16614.6848 -36360.5374 , -16616.5137 -36367.7025 , -16621.2581 -36377.1978
> , -16623.6303 -36380.5212 , -16625.7710 -36382.0500 , -16625.9410
> -36386.6600 , -16626.0310 -36388.4600 , -16628.2510 -36395.2100 ,
> -16630.4710 -36405.3000 , -16631.9517 -36411.1858 , -16633.3130 -36416.5968
> , -16634.0610 -36419.5700 , -16634.7510 -36426.4000 , -16630.3910
> -36431.6200 , -16621.8410 -36436.9100 , -16614.2410 -36444.3500 ,
> -16610.8210 -36448.3700 , -16603.1310 -36456.1400 , -16593.5610 -36458.0200
> , -16583.9010 -36461.4400 , -16579.6310 -36464.3500 , -16577.4010
> -36464.6900 , -16570.0550 -36466.8590 , -16572.9130 -36479.5440 ,
> -16575.7440 -36493.0080 , -16576.3980 -36496.0820 , -16577.2400 -36513.8737
> , -16576.7308 -36549.7123 , -16577.5690 -36557.2310 , -16578.0910
> -36559.3800 , -16583.8110 -36576.9800 , -16588.5566 -36591.8969 ,
> -16586.9662 -36599.5095 , -16585.5455 -36602.0493 , -16585.5454 -36602.0494
> , -16582.5181 -36607.4614 , -16580.8736 -36609.7421 , -16575.7573
> -36616.8381 , -16568.7145 -36631.7453 , -16566.4639 -36636.5091 ,
> -16562.0164 -36639.4734 , -16558.0380 -36642.0859 , -16558.0163 -36642.1002
> , -16554.0163 -36644.7269 , -16550.8280 -36648.5082 , -16548.6354
> -36650.8250 , -16546.0083 -36654.2381 , -16542.8800 -36659.3700 ,
> -16534.9005 -36665.2385 , -16526.9210 -36671.1070 , -16525.8110 -36671.8740
> , -16520.8020 -36675.3100 , -16517.6660 -36677.3160 , -16514.4810
> -36679.0420 , -16511.1710 -36681.5890 , -16510.0600 -36682.4400 ,
> -16507.4600 -36685.6540 , -16505.2980 -36688.1560 , -16500.5710 -36691.9890
> , -16490.7232 -36671.9597 , -16488.6245 -36667.6911 , -16412.3181
> -36512.4921 , -16406.8330 -36501.3360 , -16402.0819 -36491.6731 ,
> -16399.5757 -36486.5759 , -16398.9700 -36485.3440 , -16398.8760 -36485.1500
> , -16397.1430 -36481.9090 , -16380.8325 -36450.3902 , -16379.7327
> -36448.2650 , -16377.4610 -36443.8750 , -16375.3288 -36438.7246 ,
> -16373.5168 -36434.3477 , -16372.6275 -36432.1997 , -16372.1881 -36431.1383
> , -16366.4292 -36417.2275 , -16365.9625 -36416.1001 , -16364.7130
> -36413.0820 , -16364.5399 -36412.6654 , -16358.8622 -36398.9984 ,
> -16358.1352 -36397.2486 , -16358.0220 -36396.9760 , -16355.7620 -36389.1050
> , -16350.9670 -36369.6210 , -16351.9600 -36368.8000 , -16369.5700
> -36352.3100 , -16389.1410 -36337.1800 , -16395.9710 -36331.1200 ,
> -16398.0210 -36326.9300 , -16399.3110 -36325.2200 , -16401.7810 -36317.9600
> , -16406.3110 -36311.5500 , -16406.1410 -36308.3000 , -16406.4910
> -36305.9100 , -16404.5210 -36294.4500 , -16403.3210 -36289.8400 ,
> -16401.8710 -36279.1600 , -16400.2510 -36271.2100 , -16399.3610 -36262.9990
> , -16394.8320 -36263.3530 , -16382.6400 -36264.5400 , -16377.3400
> -36265.9100 , -16371.2800 -36266.5900 , -16366.1510 -36267.8800 ,
> -16354.8554 -36269.8685 , -16356.2848 -36260.6453 , -16367.0784 -36244.3845
> , -16370.6958 -36237.9748 , -16374.3132 -36231.5652 , -16383.3276
> -36217.3222 , -16389.4954 -36205.4529 , -16393.5281 -36195.3641 ,
> -16397.5609 -36185.2752 , -16397.4702 -36184.7119)))',-1) );
> insert into valori values
> ('1','15686.0','0.8172',GeometryFromText('MULTIPOLYGON (( (-16278.3196
> -35905.5236 , -16237.4577 -35950.8495 , -16236.7600 -35949.0300 ,
> -16229.9300 -35932.7000 , -16228.7300 -35930.6500 , -16228.4110 -35929.7900
> , -16227.3061 -35926.8173 , -16220.2271 -35907.7717 , -16218.8335
> -35904.0223 , -16218.6200 -35903.4480 , -16200.3710 -35854.3510 ,
> -16202.5810 -35843.6600 , -16208.7180 -35831.3740 , -16209.2870 -35830.5360
> , -16210.0940 -35829.3450 , -16213.7700 -35822.1200 , -16215.3100
> -35820.0700 , -16220.7000 -35810.5800 , -16222.3210 -35806.9900 ,
> -16224.5410 -35802.5500 , -16233.6910 -35790.4200 , -16239.2410 -35785.4600
> , -16240.9510 -35782.6400 , -16250.4410 -35770.0800 , -16253.4310
> -35765.7200 , -16256.4649 -35759.9286 , -16257.2319 -35765.9434 ,
> -16256.6687 -35772.5293 , -16255.0380 -35783.5659 , -16253.9712 -35796.0854
> , -16252.5182 -35808.3679 , -16251.1600 -35821.9900 , -16250.3500
> -35823.2300 , -16250.1810 -35824.0900 , -16256.7610 -35831.5200 ,
> -16260.8610 -35835.4500 , -16267.1010 -35842.3700 , -16272.8310 -35847.8400
> , -16274.8125 -35849.9536 , -16299.8310 -35876.6400 , -16295.5610
> -35882.6300 , -16280.1430 -35903.5010 , -16278.3196 -35905.5236)))',-1) );
> 
> ---------------------------------------------
> 
> ==LINE==
> 
> create table dividente (gid int4 , dividente varchar );
> ALTER TABLE dividente ADD COLUMN the_geom geometry;
> ALTER TABLE dividente ALTER COLUMN the_geom SET STORAGE MAIN;
> ALTER TABLE dividente ADD CONSTRAINT dividente_pkey PRIMARY KEY(gid);
> ALTER TABLE dividente ADD CONSTRAINT enforce_dims_the_geom CHECK
> (ndims(the_geom) = 2);
> ALTER TABLE dividente ADD CONSTRAINT enforce_geotype_the_geom CHECK
> (geometrytype(the_geom) = 'MULTILINESTRING'::text OR the_geom IS NULL);
> ALTER TABLE dividente ADD CONSTRAINT enforce_srid_the_geom CHECK
> (srid(the_geom) = -1);
> 
> insert into dividente values ('0','divide',GeometryFromText('MULTILINESTRING
> ((-16250.5534 -35907.0443 , -16279.2996 -35942.7809))',-1) );
> 
> ------------------------------------------------
> 
> ==POLIGONO_VALORE_TOT==
> 
> --create table poligonoval as 
> select sum(valore) as valoreTOT, the_geom , nome_pre, cod_pre
> from
> (select v.the_geom
> as the_geom, v.cod_pre, v.nome_pre0, v.nome_pre,
> area(intersection(v.the_geom, m.the_geom))*m.valori as valore
> from aziende_pre v, valori m
> where v.the_geom && m.the_geom and 
> intersects(v.the_geom, m.the_geom)) as val
> group by the_geom, nome_pre, cod_pre;
> 
> ---------------------------------------------