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

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:

aziende_pre.sql

and a series of polygons that covers the area of the polygon AZIENDE_PRE.

They are defined by:

valori.sql

Such polygons identifies the value, for every squared meter, of the lands occupied by the polygon AZIENDE_PRE.
Through a simple query (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 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;

---------------------------------------------