My goal is take a single table and st_union all polygons that are either touching or close to each other into single polygons
I'm a C# developer who is starting to learn about PostGIS. Using the code below, I was able to accomplish this, but it seems inefficient, and there's much to PostGIS that is new to me.
From my initial attempt (still in comments), I was able to reduce the iterations by using array_agg with ST_UNION instead of unioning just polys at a time.
I wind up with 133 polys from my orig 173.
sql = "DROP TABLE IF Exists tmpTable; create table tmpTable ( ID varchar(50), Geom geometry(Geometry,4326), Touchin varchar(50) ); create index idx_tmp on tmpTable using GIST(Geom); "; CommandText = sql; ExecuteNonQuery(); sql = ""; for (int i = 0; i < infos.Count(); i++) { sql += "INSERT INTO tmpTable SELECT '" + infos.ID + "', ST_GeomFromText('" + infos.wkt + "', 4326), '0';"; } CommandText = sql; ExecuteNonQuery(); CommandText = "update tmpTable set touchin = (select id from tmpTable as t where st_intersects(st_buffer(geom, 0.0001), (select geom from tmpTable as t2 where t2.ID = tmpTable.ID ) ) and t.ID tmpTable.ID limit 1)"; ExecuteNonQuery(); CommandText = "select count(*) from tmpTable where touchin is not null"; long touching = (long)ExecuteScalar(); string thisId = ""; // string otherId = ""; while (touching > 0) { CommandText = "select touchin, count(*) from tmpTable where touchin is not null group by touchin order by 2 desc limit 1"; //CommandText = "select id, touchin from tmpTable where touchin is not null"; using (var prdr = ExecuteReader()) { CommandText = ""; if (prdr.Read()) { thisId = prdr.GetString(0); // otherID = prdr.GetString(1); CommandText = @"update tmpTable set geom = st_union(unioned) from (select array_agg(geom) as unioned from tmpTable where touchin = '" + thisId + "' or id = '" + thisId + @"') as data where id = '" + thisId + "'"; // CommandText = "update tmpTable set geom = st_union(geom, (select geom from tmpTable where ID = '" + otherId + "')) where id = '" + thisId + "'"; } } if (!string.IsNullOrEmpty(CommandText)) { ExecuteNonQuery(); //CommandText = "update tmpTable set geom = null, touchin = null where ID = '" + otherId + "'"; CommandText = "update tmpTable set geom = null, touchin = null where touchin = '" + thisId + "'"; ExecuteNonQuery(); } CommandText = "update tmpTable set touchin = (select id from tmpTable as t where st_intersects(st_buffer(geom, 0.0001), (select geom from tmpTable as t2 where t2.ID = tmpTable.ID ) ) and t.ID tmpTable.ID limit 1)"; ExecuteNonQuery(); CommandText = "select count(*) from tmpTable where touchin is not null"; touching = (long)ExecuteScalar(); }Here's part of the dataset I am using:
INSERT INTO tmpTable SELECT '872538', ST_GeomFromText('POLYGON((-101.455035985 26.8835084441,-101.455035985 26.8924915559,-101.444964015 26.8924915559,-101.444964015 26.8835084441,-101.455035985 26.8835084441))', 4326), '0';INSERT INTO tmpTable SELECT '872550', ST_GeomFromText('POLYGON((-93.9484752173 46.0755084441,-93.9484752173 46.0844915559,-93.9355247827 46.0844915559,-93.9355247827 46.0755084441,-93.9484752173 46.0755084441))', 4326), '0';INSERT INTO tmpTable SELECT '872552', ST_GeomFromText('POLYGON((-116.060688575 47.8105084441,-116.060688575 47.8194915559,-116.047311425 47.8194915559,-116.047311425 47.8105084441,-116.060688575 47.8105084441))', 4326), '0';INSERT INTO tmpTable SELECT '872553', ST_GeomFromText('POLYGON((-116.043688832 47.8125084441,-116.043688832 47.8214915559,-116.030311168 47.8214915559,-116.030311168 47.8125084441,-116.043688832 47.8125084441))', 4326), '0';INSERT INTO tmpTable SELECT '872557', ST_GeomFromText('POLYGON((-80.6380222359 26.5725084441,-80.6380222359 26.5814915559,-80.6279777641 26.5814915559,-80.6279777641 26.5725084441,-80.6380222359 26.5725084441))', 4326), '0';INSERT INTO tmpTable SELECT '872558', ST_GeomFromText('POLYGON((-80.6520223675 26.5755084441,-80.6520223675 26.5844915559,-80.6419776325 26.5844915559,-80.6419776325 26.5755084441,-80.6520223675 26.5755084441))', 4326), '0';INSERT INTO tmpTable SELECT '872559', ST_GeomFromText('POLYGON((-80.6400224991 26.5785084441,-80.6400224991 26.5874915559,-80.6299775009 26.5874915559,-80.6299775009 26.5785084441,-80.6400224991 26.5785084441))', 4326), '0';INSERT INTO tmpTable SELECT '872560', ST_GeomFromText('POLYGON((-80.6530226307 26.5815084441,-80.6530226307 26.5904915559,-80.6429773693 26.5904915559,-80.6429773693 26.5815084441,-80.6530226307 26.5815084441))', 4326), '0';INSERT INTO tmpTable SELECT '872568', ST_GeomFromText('POLYGON((-90.7892258584 30.7365084441,-90.7892258584 30.7454915559,-90.7787741416 30.7454915559,-90.7787741416 30.7365084441,-90.7892258584 30.7365084441))', 4326), '0';INSERT INTO tmpTable SELECT '872569', ST_GeomFromText('POLYGON((-90.7832259127 30.7375084441,-90.7832259127 30.7464915559,-90.7727740873 30.7464915559,-90.7727740873 30.7375084441,-90.7832259127 30.7375084441))', 4326), '0';INSERT INTO tmpTable SELECT '872570', ST_GeomFromText('POLYGON((-90.8112259669 30.7385084441,-90.8112259669 30.7474915559,-90.8007740331 30.7474915559,-90.8007740331 30.7385084441,-90.8112259669 30.7385084441))', 4326), '0';INSERT INTO tmpTable SELECT '872571', ST_GeomFromText('POLYGON((-90.8042260212 30.7395084441,-90.8042260212 30.7484915559,-90.7937739788 30.7484915559,-90.7937739788 30.7395084441,-90.8042260212 30.7395084441))', 4326), '0';INSERT INTO tmpTable SELECT '872573', ST_GeomFromText('POLYGON((-90.7812282478 30.7805084441,-90.7812282478 30.7894915559,-90.7707717522 30.7894915559,-90.7707717522 30.7805084441,-90.7812282478 30.7805084441))', 4326), '0';INSERT INTO tmpTable SELECT '872574', ST_GeomFromText('POLYGON((-90.7992283565 30.7825084441,-90.7992283565 30.7914915559,-90.7887716435 30.7914915559,-90.7887716435 30.7825084441,-90.7992283565 30.7825084441))', 4326), '0';INSERT INTO tmpTable SELECT '872575', ST_GeomFromText('POLYGON((-95.1392329877 30.8675084441,-95.1392329877 30.8764915559,-95.1287670123 30.8764915559,-95.1287670123 30.8675084441,-95.1392329877 30.8675084441))', 4326), '0';INSERT INTO tmpTable SELECT '872576', ST_GeomFromText('POLYGON((-95.1332331515 30.8705084441,-95.1332331515 30.8794915559,-95.1227668485 30.8794915559,-95.1227668485 30.8705084441,-95.1332331515 30.8705084441))', 4326), '0';INSERT INTO tmpTable SELECT '872579', ST_GeomFromText('POLYGON((-95.1242368165 30.9375084441,-95.1242368165 30.9464915559,-95.1137631835 30.9464915559,-95.1137631835 30.9375084441,-95.1242368165 30.9375084441))', 4326), '0';INSERT INTO tmpTable SELECT '872580', ST_GeomFromText('POLYGON((-95.1222369809 30.9405084441,-95.1222369809 30.9494915559,-95.1117630191 30.9494915559,-95.1117630191 30.9405084441,-95.1222369809 30.9405084441))', 4326), '0';INSERT INTO tmpTable SELECT '872584', ST_GeomFromText('POLYGON((-93.9152506561 31.1885084441,-93.9152506561 31.1974915559,-93.9047493439 31.1974915559,-93.9047493439 31.1885084441,-93.9152506561 31.1885084441))', 4326), '0';INSERT INTO tmpTable SELECT '872586', ST_GeomFromText('POLYGON((-93.9202509891 31.1945084441,-93.9202509891 31.2034915559,-93.9097490109 31.2034915559,-93.9097490109 31.1945084441,-93.9202509891 31.1945084441))', 4326), '0';INSERT INTO tmpTable SELECT '872587', ST_GeomFromText('POLYGON((-93.9152528777 31.2285084441,-93.9152528777 31.2374915559,-93.9047471223 31.2374915559,-93.9047471223 31.2285084441,-93.9152528777 31.2285084441))', 4326), '0';INSERT INTO tmpTable SELECT '872588', ST_GeomFromText('POLYGON((-93.9162545469 31.2585084441,-93.9162545469 31.2674915559,-93.9057454531 31.2674915559,-93.9057454531 31.2585084441,-93.9162545469 31.2585084441))', 4326), '0';INSERT INTO tmpTable SELECT '872609', ST_GeomFromText('POLYGON((-91.6293603712 33.0745084441,-91.6293603712 33.0834915559,-91.6186396288 33.0834915559,-91.6186396288 33.0745084441,-91.6293603712 33.0745084441))', 4326), '0';INSERT INTO tmpTable SELECT '872621', ST_GeomFromText('POLYGON((-91.2884411565 34.3585084441,-91.2884411565 34.3674915559,-91.2775588435 34.3674915559,-91.2775588435 34.3585084441,-91.2884411565 34.3585084441))', 4326), '0';INSERT INTO tmpTable SELECT '872637', ST_GeomFromText('POLYGON((-90.0255612625 36.1285084441,-90.0255612625 36.1374915559,-90.0144387375 36.1374915559,-90.0144387375 36.1285084441,-90.0255612625 36.1285084441))', 4326), '0';INSERT INTO tmpTable SELECT '872638', ST_GeomFromText('POLYGON((-89.9605961195 36.6145084441,-89.9605961195 36.6234915559,-89.9494038805 36.6234915559,-89.9494038805 36.6145084441,-89.9605961195 36.6145084441))', 4326), '0';INSERT INTO tmpTable SELECT '872639', ST_GeomFromText('POLYGON((-89.9855962647 36.6165084441,-89.9855962647 36.6254915559,-89.9744037353 36.6254915559,-89.9744037353 36.6165084441,-89.9855962647 36.6165084441))', 4326), '0';INSERT INTO tmpTable SELECT '872643', ST_GeomFromText('POLYGON((-89.8466400999 37.2115084441,-89.8466400999 37.2204915559,-89.8353599001 37.2204915559,-89.8353599001 37.2115084441,-89.8466400999 37.2115084441))', 4326), '0';INSERT INTO tmpTable SELECT '872667', ST_GeomFromText('POLYGON((-110.040051001 27.2175084441,-110.040051001 27.2264915559,-110.029948999 27.2264915559,-110.029948999 27.2175084441,-110.040051001 27.2175084441))', 4326), '0';INSERT INTO tmpTable SELECT '872668', ST_GeomFromText('POLYGON((-110.050051046 27.2185084441,-110.050051046 27.2274915559,-110.039948954 27.2274915559,-110.039948954 27.2185084441,-110.050051046 27.2185084441))', 4326), '0';INSERT INTO tmpTable SELECT '872669', ST_GeomFromText('POLYGON((-110.038051364 27.2255084441,-110.038051364 27.2344915559,-110.027948636 27.2344915559,-110.027948636 27.2255084441,-110.038051364 27.2255084441))', 4326), '0';INSERT INTO tmpTable SELECT '872670', ST_GeomFromText('POLYGON((-110.048051454 27.2275084441,-110.048051454 27.2364915559,-110.037948546 27.2364915559,-110.037948546 27.2275084441,-110.048051454 27.2275084441))', 4326), '0';INSERT INTO tmpTable SELECT '872677', ST_GeomFromText('POLYGON((-105.797556593 36.0625084441,-105.797556593 36.0714915559,-105.786443407 36.0714915559,-105.786443407 36.0625084441,-105.797556593 36.0625084441))', 4326), '0';INSERT INTO tmpTable SELECT '872678', ST_GeomFromText('POLYGON((-105.808556734 36.0645084441,-105.808556734 36.0734915559,-105.797443266 36.0734915559,-105.797443266 36.0645084441,-105.808556734 36.0645084441))', 4326), '0';INSERT INTO tmpTable SELECT '872680', ST_GeomFromText('POLYGON((-105.80655737 36.0735084441,-105.80655737 36.0824915559,-105.79544263 36.0824915559,-105.79544263 36.0735084441,-105.80655737 36.0735084441))', 4326), '0';INSERT INTO tmpTable SELECT '872683', ST_GeomFromText('POLYGON((-97.8184084121 45.4975084441,-97.8184084121 45.5064915559,-97.8055915879 45.5064915559,-97.8055915879 45.4975084441,-97.8184084121 45.4975084441))', 4326), '0';INSERT INTO tmpTable SELECT '872684', ST_GeomFromText('POLYGON((-97.8214094368 45.5065084441,-97.8214094368 45.5154915559,-97.8085905632 45.5154915559,-97.8085905632 45.5065084441,-97.8214094368 45.5065084441))', 4326), '0';INSERT INTO tmpTable SELECT '872688', ST_GeomFromText('POLYGON((-102.02870175 47.9125084441,-102.02870175 47.9214915559,-102.01529825 47.9214915559,-102.01529825 47.9125084441,-102.02870175 47.9125084441))', 4326), '0';INSERT INTO tmpTable SELECT '872690', ST_GeomFromText('POLYGON((-95.82971578 48.0205084441,-95.82971578 48.0294915559,-95.81628422 48.0294915559,-95.81628422 48.0205084441,-95.82971578 48.0205084441))', 4326), '0';INSERT INTO tmpTable SELECT '872691', ST_GeomFromText('POLYGON((-95.83471578 48.0205084441,-95.83471578 48.0294915559,-95.82128422 48.0294915559,-95.82128422 48.0205084441,-95.83471578 48.0205084441))', 4326), '0';INSERT INTO tmpTable SELECT '872694', ST_GeomFromText('POLYGON((-101.969724787 48.0895084441,-101.969724787 48.0984915559,-101.956275213 48.0984915559,-101.956275213 48.0895084441,-101.969724787 48.0895084441))', 4326), '0';INSERT INTO tmpTable SELECT '872697', ST_GeomFromText('POLYGON((-103.687861158 49.1035084441,-103.687861158 49.1124915559,-103.674138842 49.1124915559,-103.674138842 49.1035084441,-103.687861158 49.1035084441))', 4326), '0';INSERT INTO tmpTable SELECT '872701', ST_GeomFromText('POLYGON((-101.763928568 49.5845084441,-101.763928568 49.5934915559,-101.750071432 49.5934915559,-101.750071432 49.5845084441,-101.763928568 49.5845084441))', 4326), '0';INSERT INTO tmpTable SELECT '872703', ST_GeomFromText('POLYGON((-101.760929847 49.5935084441,-101.760929847 49.6024915559,-101.747070153 49.6024915559,-101.747070153 49.5935084441,-101.760929847 49.5935084441))', 4326), '0';INSERT INTO tmpTable SELECT '872704', ST_GeomFromText('POLYGON((-101.821933829 49.6215084441,-101.821933829 49.6304915559,-101.808066171 49.6304915559,-101.808066171 49.6215084441,-101.821933829 49.6215084441))', 4326), '0';INSERT INTO tmpTable SELECT '872706', ST_GeomFromText('POLYGON((-91.5669735606 49.8985084441,-91.5669735606 49.9074915559,-91.5530264394 49.9074915559,-91.5530264394 49.8985084441,-91.5669735606 49.8985084441))', 4326), '0';INSERT INTO tmpTable SELECT '872722', ST_GeomFromText('POLYGON((-99.1659915913 25.8605084441,-99.1659915913 25.8694915559,-99.1560084087 25.8694915559,-99.1560084087 25.8605084441,-99.1659915913 25.8605084441))', 4326), '0';INSERT INTO tmpTable SELECT '872724', ST_GeomFromText('POLYGON((-92.9691769339 29.8135084441,-92.9691769339 29.8224915559,-92.9588230661 29.8224915559,-92.9588230661 29.8135084441,-92.9691769339 29.8135084441))', 4326), '0';INSERT INTO tmpTable SELECT '872726', ST_GeomFromText('POLYGON((-91.9421806171 29.8845084441,-91.9421806171 29.8934915559,-91.9318193829 29.8934915559,-91.9318193829 29.8845084441,-91.9421806171 29.8845084441))', 4326), '0';INSERT INTO tmpTable SELECT '872728', ST_GeomFromText('POLYGON((-91.2021940603 30.1415084441,-91.2021940603 30.1504915559,-91.1918059397 30.1504915559,-91.1918059397 30.1415084441,-91.2021940603 30.1415084441))', 4326), '0';INSERT INTO tmpTable SELECT '872731', ST_GeomFromText('POLYGON((-91.1921984392 30.2245084441,-91.1921984392 30.2334915559,-91.1818015608 30.2334915559,-91.1818015608 30.2245084441,-91.1921984392 30.2245084441))', 4326), '0';INSERT INTO tmpTable SELECT '872732', ST_GeomFromText('POLYGON((-90.743222825 30.6805084441,-90.743222825 30.6894915559,-90.732777175 30.6894915559,-90.732777175 30.6805084441,-90.743222825 30.6805084441))', 4326), '0';INSERT INTO tmpTable SELECT '872733', ST_GeomFromText('POLYGON((-90.7282229332 30.6825084441,-90.7282229332 30.6914915559,-90.7177770668 30.6914915559,-90.7177770668 30.6825084441,-90.7282229332 30.6825084441))', 4326), '0';INSERT INTO tmpTable SELECT '872735', ST_GeomFromText('POLYGON((-95.0322288459 30.7915084441,-95.0322288459 30.8004915559,-95.0217711541 30.8004915559,-95.0217711541 30.7915084441,-95.0322288459 30.7915084441))', 4326), '0';INSERT INTO tmpTable SELECT '872736', ST_GeomFromText('POLYGON((-95.0212289003 30.7925084441,-95.0212289003 30.8014915559,-95.0107710997 30.8014915559,-95.0107710997 30.7925084441,-95.0212289003 30.7925084441))', 4326), '0';INSERT INTO tmpTable SELECT '872737', ST_GeomFromText('POLYGON((-95.0112290091 30.7945084441,-95.0112290091 30.8034915559,-95.0007709909 30.8034915559,-95.0007709909 30.7945084441,-95.0112290091 30.7945084441))', 4326), '0';INSERT INTO tmpTable SELECT '872740', ST_GeomFromText('POLYGON((-93.2522356665 30.9165084441,-93.2522356665 30.9254915559,-93.2417643335 30.9254915559,-93.2417643335 30.9165084441,-93.2522356665 30.9165084441))', 4326), '0';INSERT INTO tmpTable SELECT '872742', ST_GeomFromText('POLYGON((-90.7742431909 31.0535084441,-90.7742431909 31.0624915559,-90.7637568091 31.0624915559,-90.7637568091 31.0535084441,-90.7742431909 31.0535084441))', 4326), '0';INSERT INTO tmpTable SELECT '872744', ST_GeomFromText('POLYGON((-93.4912446248 31.0795084441,-93.4912446248 31.0884915559,-93.4807553752 31.0884915559,-93.4807553752 31.0795084441,-93.4912446248 31.0795084441))', 4326), '0';INSERT INTO tmpTable SELECT '872746', ST_GeomFromText('POLYGON((-93.4932451767 31.0895084441,-93.4932451767 31.0984915559,-93.4827548233 31.0984915559,-93.4827548233 31.0895084441,-93.4932451767 31.0895084441))', 4326), '0';INSERT INTO tmpTable SELECT '872748', ST_GeomFromText('POLYGON((-93.8142468343 31.1195084441,-93.8142468343 31.1284915559,-93.8037531657 31.1284915559,-93.8037531657 31.1195084441,-93.8142468343 31.1195084441))', 4326), '0';INSERT INTO tmpTable SELECT '872750', ST_GeomFromText('POLYGON((-92.8712607444 31.3695084441,-92.8712607444 31.3784915559,-92.8607392556 31.3784915559,-92.8607392556 31.3695084441,-92.8712607444 31.3695084441))', 4326), '0';INSERT INTO tmpTable SELECT '872751', ST_GeomFromText('POLYGON((-92.8582608004 31.3705084441,-92.8582608004 31.3794915559,-92.8477391996 31.3794915559,-92.8477391996 31.3705084441,-92.8582608004 31.3705084441))', 4326), '0';INSERT INTO tmpTable SELECT '872752', ST_GeomFromText('POLYGON((-92.8722613044 31.3795084441,-92.8722613044 31.3884915559,-92.8617386956 31.3884915559,-92.8617386956 31.3795084441,-92.8722613044 31.3795084441))', 4326), '0';INSERT INTO tmpTable SELECT '872753', ST_GeomFromText('POLYGON((-92.8602613604 31.3805084441,-92.8602613604 31.3894915559,-92.8497386396 31.3894915559,-92.8497386396 31.3805084441,-92.8602613604 31.3805084441))', 4326), '0';INSERT INTO tmpTable SELECT '872758', ST_GeomFromText('POLYGON((-91.5923521112 32.9385084441,-91.5923521112 32.9474915559,-91.5816478888 32.9474915559,-91.5816478888 32.9385084441,-91.5923521112 32.9385084441))', 4326), '0';INSERT INTO tmpTable SELECT '872759', ST_GeomFromText('POLYGON((-91.5773522323 32.9405084441,-91.5773522323 32.9494915559,-91.5666477677 32.9494915559,-91.5666477677 32.9405084441,-91.5773522323 32.9405084441))', 4326), '0';INSERT INTO tmpTable SELECT '872760', ST_GeomFromText('POLYGON((-91.5943527773 32.9495084441,-91.5943527773 32.9584915559,-91.5836472227 32.9584915559,-91.5836472227 32.9495084441,-91.5943527773 32.9495084441))', 4326), '0';INSERT INTO tmpTable SELECT '872761', ST_GeomFromText('POLYGON((-91.5793528378 32.9505084441,-91.5793528378 32.9594915559,-91.5686471622 32.9594915559,-91.5686471622 32.9505084441,-91.5793528378 32.9505084441))', 4326), '0';INSERT INTO tmpTable SELECT '872763', ST_GeomFromText('POLYGON((-91.5563634219 33.1245084441,-91.5563634219 33.1334915559,-91.5456365781 33.1334915559,-91.5456365781 33.1245084441,-91.5563634219 33.1245084441))', 4326), '0';INSERT INTO tmpTable SELECT '872768', ST_GeomFromText('POLYGON((-91.3173768118 33.3425084441,-91.3173768118 33.3514915559,-91.3066231882 33.3514915559,-91.3066231882 33.3425084441,-91.3173768118 33.3425084441))', 4326), '0';INSERT INTO tmpTable SELECT '872770', ST_GeomFromText('POLYGON((-90.7743809562 33.4095084441,-90.7743809562 33.4184915559,-90.7636190438 33.4184915559,-90.7636190438 33.4095084441,-90.7743809562 33.4095084441))', 4326), '0';INSERT INTO tmpTable SELECT '872771', ST_GeomFromText('POLYGON((-90.8773859227 33.4895084441,-90.8773859227 33.4984915559,-90.8666140773 33.4984915559,-90.8666140773 33.4895084441,-90.8773859227 33.4895084441))', 4326), '0';INSERT INTO tmpTable SELECT '872774', ST_GeomFromText('POLYGON((-90.8793866072 33.5005084441,-90.8793866072 33.5094915559,-90.8686133928 33.5094915559,-90.8686133928 33.5005084441,-90.8793866072 33.5005084441))', 4326), '0';INSERT INTO tmpTable SELECT '872779', ST_GeomFromText('POLYGON((-90.9453898477 33.5525084441,-90.9453898477 33.5614915559,-90.9346101523 33.5614915559,-90.9346101523 33.5525084441,-90.9453898477 33.5525084441))', 4326), '0';INSERT INTO tmpTable SELECT '872781', ST_GeomFromText('POLYGON((-90.8563938476 33.6165084441,-90.8563938476 33.6254915559,-90.8456061524 33.6254915559,-90.8456061524 33.6165084441,-90.8563938476 33.6165084441))', 4326), '0';INSERT INTO tmpTable SELECT '872783', ST_GeomFromText('POLYGON((-90.8503939728 33.6185084441,-90.8503939728 33.6274915559,-90.8396060272 33.6274915559,-90.8396060272 33.6185084441,-90.8503939728 33.6185084441))', 4326), '0';INSERT INTO tmpTable SELECT '872786', ST_GeomFromText('POLYGON((-90.9113949748 33.6345084441,-90.9113949748 33.6434915559,-90.9006050252 33.6434915559,-90.9006050252 33.6345084441,-90.9113949748 33.6345084441))', 4326), '0';INSERT INTO tmpTable SELECT '872788', ST_GeomFromText('POLYGON((-91.6104148647 33.9495084441,-91.6104148647 33.9584915559,-91.5995851353 33.9584915559,-91.5995851353 33.9495084441,-91.6104148647 33.9495084441))', 4326), '0';INSERT INTO tmpTable SELECT '872789', ST_GeomFromText('POLYGON((-91.6464166478 33.9775084441,-91.6464166478 33.9864915559,-91.6355833522 33.9864915559,-91.6355833522 33.9775084441,-91.6464166478 33.9775084441))', 4326), '0';INSERT INTO tmpTable SELECT '872790', ST_GeomFromText('POLYGON((-91.6544169664 33.9825084441,-91.6544169664 33.9914915559,-91.6435830336 33.9914915559,-91.6435830336 33.9825084441,-91.6544169664 33.9825084441))', 4326), '0';INSERT INTO tmpTable SELECT '872791', ST_GeomFromText('POLYGON((-91.6384170939 33.9845084441,-91.6384170939 33.9934915559,-91.6275829061 33.9934915559,-91.6275829061 33.9845084441,-91.6384170939 33.9845084441))', 4326), '0';INSERT INTO tmpTable SELECT '872796', ST_GeomFromText('POLYGON((-91.4094227798 34.0735084441,-91.4094227798 34.0824915559,-91.3985772202 34.0824915559,-91.3985772202 34.0735084441,-91.4094227798 34.0735084441))', 4326), '0';INSERT INTO tmpTable SELECT '872803', ST_GeomFromText('POLYGON((-89.7214342914 34.2525084441,-89.7214342914 34.2614915559,-89.7105657086 34.2614915559,-89.7105657086 34.2525084441,-89.7214342914 34.2525084441))', 4326), '0';INSERT INTO tmpTable SELECT '872808', ST_GeomFromText('POLYGON((-91.4734465571 34.4415084441,-91.4734465571 34.4504915559,-91.4625534429 34.4504915559,-91.4625534429 34.4415084441,-91.4734465571 34.4415084441))', 4326), '0';INSERT INTO tmpTable SELECT '872809', ST_GeomFromText('POLYGON((-91.4574466875 34.4435084441,-91.4574466875 34.4524915559,-91.4465533125 34.4524915559,-91.4465533125 34.4435084441,-91.4574466875 34.4435084441))', 4326), '0';INSERT INTO tmpTable SELECT '872812', ST_GeomFromText('POLYGON((-90.2654528313 34.5375084441,-90.2654528313 34.5464915559,-90.2545471687 34.5464915559,-90.2545471687 34.5375084441,-90.2654528313 34.5375084441))', 4326), '0';INSERT INTO tmpTable SELECT '872813', ST_GeomFromText('POLYGON((-90.2664536177 34.5495084441,-90.2664536177 34.5584915559,-90.2555463823 34.5584915559,-90.2555463823 34.5495084441,-90.2664536177 34.5495084441))', 4326), '0';INSERT INTO tmpTable SELECT '872817', ST_GeomFromText('POLYGON((-90.3554619027 34.6755084441,-90.3554619027 34.6844915559,-90.3445380973 34.6844915559,-90.3445380973 34.6755084441,-90.3554619027 34.6755084441))', 4326), '0';INSERT INTO tmpTable SELECT '872825', ST_GeomFromText('POLYGON((-95.0104722321 34.8315084441,-95.0104722321 34.8404915559,-94.9995277679 34.8404915559,-94.9995277679 34.8315084441,-95.0104722321 34.8315084441))', 4326), '0';INSERT INTO tmpTable SELECT '872828', ST_GeomFromText('POLYGON((-94.3874749601 34.8725084441,-94.3874749601 34.8814915559,-94.3765250399 34.8814915559,-94.3765250399 34.8725084441,-94.3874749601 34.8725084441))', 4326), '0';INSERT INTO tmpTable SELECT '872829', ST_GeomFromText('POLYGON((-94.3754750267 34.8735084441,-94.3754750267 34.8824915559,-94.3645249733 34.8824915559,-94.3645249733 34.8735084441,-94.3754750267 34.8735084441))', 4326), '0';INSERT INTO tmpTable SELECT '872831', ST_GeomFromText('POLYGON((-91.0284802989 34.9525084441,-91.0284802989 34.9614915559,-91.0175197011 34.9614915559,-91.0175197011 34.9525084441,-91.0284802989 34.9525084441))', 4326), '0';INSERT INTO tmpTable SELECT '872832', ST_GeomFromText('POLYGON((-91.0354802989 34.9525084441,-91.0354802989 34.9614915559,-91.0245197011 34.9614915559,-91.0245197011 34.9525084441,-91.0354802989 34.9525084441))', 4326), '0';INSERT INTO tmpTable SELECT '872834', ST_GeomFromText('POLYGON((-90.9544846523 35.0175084441,-90.9544846523 35.0264915559,-90.9435153477 35.0264915559,-90.9435153477 35.0175084441,-90.9544846523 35.0175084441))', 4326), '0';INSERT INTO tmpTable SELECT '872840', ST_GeomFromText('POLYGON((-90.1955033617 35.2945084441,-90.1955033617 35.3034915559,-90.1844966383 35.3034915559,-90.1844966383 35.2945084441,-90.1955033617 35.2945084441))', 4326), '0';INSERT INTO tmpTable SELECT '872841', ST_GeomFromText('POLYGON((-90.1755034977 35.2965084441,-90.1755034977 35.3054915559,-90.1644965023 35.3054915559,-90.1644965023 35.2965084441,-90.1755034977 35.2965084441))', 4326), '0';INSERT INTO tmpTable SELECT '872843', ST_GeomFromText('POLYGON((-90.1815037698 35.3005084441,-90.1815037698 35.3094915559,-90.1704962302 35.3094915559,-90.1704962302 35.3005084441,-90.1815037698 35.3005084441))', 4326), '0';INSERT INTO tmpTable SELECT '872847', ST_GeomFromText('POLYGON((-90.4085127786 35.4325084441,-90.4085127786 35.4414915559,-90.3974872214 35.4414915559,-90.3974872214 35.4325084441,-90.4085127786 35.4325084441))', 4326), '0';INSERT INTO tmpTable SELECT '872848', ST_GeomFromText('POLYGON((-93.3415127786 35.4325084441,-93.3415127786 35.4414915559,-93.3304872214 35.4414915559,-93.3304872214 35.4325084441,-93.3415127786 35.4325084441))', 4326), '0';
أكثر...
I'm a C# developer who is starting to learn about PostGIS. Using the code below, I was able to accomplish this, but it seems inefficient, and there's much to PostGIS that is new to me.
From my initial attempt (still in comments), I was able to reduce the iterations by using array_agg with ST_UNION instead of unioning just polys at a time.
I wind up with 133 polys from my orig 173.
sql = "DROP TABLE IF Exists tmpTable; create table tmpTable ( ID varchar(50), Geom geometry(Geometry,4326), Touchin varchar(50) ); create index idx_tmp on tmpTable using GIST(Geom); "; CommandText = sql; ExecuteNonQuery(); sql = ""; for (int i = 0; i < infos.Count(); i++) { sql += "INSERT INTO tmpTable SELECT '" + infos.ID + "', ST_GeomFromText('" + infos.wkt + "', 4326), '0';"; } CommandText = sql; ExecuteNonQuery(); CommandText = "update tmpTable set touchin = (select id from tmpTable as t where st_intersects(st_buffer(geom, 0.0001), (select geom from tmpTable as t2 where t2.ID = tmpTable.ID ) ) and t.ID tmpTable.ID limit 1)"; ExecuteNonQuery(); CommandText = "select count(*) from tmpTable where touchin is not null"; long touching = (long)ExecuteScalar(); string thisId = ""; // string otherId = ""; while (touching > 0) { CommandText = "select touchin, count(*) from tmpTable where touchin is not null group by touchin order by 2 desc limit 1"; //CommandText = "select id, touchin from tmpTable where touchin is not null"; using (var prdr = ExecuteReader()) { CommandText = ""; if (prdr.Read()) { thisId = prdr.GetString(0); // otherID = prdr.GetString(1); CommandText = @"update tmpTable set geom = st_union(unioned) from (select array_agg(geom) as unioned from tmpTable where touchin = '" + thisId + "' or id = '" + thisId + @"') as data where id = '" + thisId + "'"; // CommandText = "update tmpTable set geom = st_union(geom, (select geom from tmpTable where ID = '" + otherId + "')) where id = '" + thisId + "'"; } } if (!string.IsNullOrEmpty(CommandText)) { ExecuteNonQuery(); //CommandText = "update tmpTable set geom = null, touchin = null where ID = '" + otherId + "'"; CommandText = "update tmpTable set geom = null, touchin = null where touchin = '" + thisId + "'"; ExecuteNonQuery(); } CommandText = "update tmpTable set touchin = (select id from tmpTable as t where st_intersects(st_buffer(geom, 0.0001), (select geom from tmpTable as t2 where t2.ID = tmpTable.ID ) ) and t.ID tmpTable.ID limit 1)"; ExecuteNonQuery(); CommandText = "select count(*) from tmpTable where touchin is not null"; touching = (long)ExecuteScalar(); }Here's part of the dataset I am using:
INSERT INTO tmpTable SELECT '872538', ST_GeomFromText('POLYGON((-101.455035985 26.8835084441,-101.455035985 26.8924915559,-101.444964015 26.8924915559,-101.444964015 26.8835084441,-101.455035985 26.8835084441))', 4326), '0';INSERT INTO tmpTable SELECT '872550', ST_GeomFromText('POLYGON((-93.9484752173 46.0755084441,-93.9484752173 46.0844915559,-93.9355247827 46.0844915559,-93.9355247827 46.0755084441,-93.9484752173 46.0755084441))', 4326), '0';INSERT INTO tmpTable SELECT '872552', ST_GeomFromText('POLYGON((-116.060688575 47.8105084441,-116.060688575 47.8194915559,-116.047311425 47.8194915559,-116.047311425 47.8105084441,-116.060688575 47.8105084441))', 4326), '0';INSERT INTO tmpTable SELECT '872553', ST_GeomFromText('POLYGON((-116.043688832 47.8125084441,-116.043688832 47.8214915559,-116.030311168 47.8214915559,-116.030311168 47.8125084441,-116.043688832 47.8125084441))', 4326), '0';INSERT INTO tmpTable SELECT '872557', ST_GeomFromText('POLYGON((-80.6380222359 26.5725084441,-80.6380222359 26.5814915559,-80.6279777641 26.5814915559,-80.6279777641 26.5725084441,-80.6380222359 26.5725084441))', 4326), '0';INSERT INTO tmpTable SELECT '872558', ST_GeomFromText('POLYGON((-80.6520223675 26.5755084441,-80.6520223675 26.5844915559,-80.6419776325 26.5844915559,-80.6419776325 26.5755084441,-80.6520223675 26.5755084441))', 4326), '0';INSERT INTO tmpTable SELECT '872559', ST_GeomFromText('POLYGON((-80.6400224991 26.5785084441,-80.6400224991 26.5874915559,-80.6299775009 26.5874915559,-80.6299775009 26.5785084441,-80.6400224991 26.5785084441))', 4326), '0';INSERT INTO tmpTable SELECT '872560', ST_GeomFromText('POLYGON((-80.6530226307 26.5815084441,-80.6530226307 26.5904915559,-80.6429773693 26.5904915559,-80.6429773693 26.5815084441,-80.6530226307 26.5815084441))', 4326), '0';INSERT INTO tmpTable SELECT '872568', ST_GeomFromText('POLYGON((-90.7892258584 30.7365084441,-90.7892258584 30.7454915559,-90.7787741416 30.7454915559,-90.7787741416 30.7365084441,-90.7892258584 30.7365084441))', 4326), '0';INSERT INTO tmpTable SELECT '872569', ST_GeomFromText('POLYGON((-90.7832259127 30.7375084441,-90.7832259127 30.7464915559,-90.7727740873 30.7464915559,-90.7727740873 30.7375084441,-90.7832259127 30.7375084441))', 4326), '0';INSERT INTO tmpTable SELECT '872570', ST_GeomFromText('POLYGON((-90.8112259669 30.7385084441,-90.8112259669 30.7474915559,-90.8007740331 30.7474915559,-90.8007740331 30.7385084441,-90.8112259669 30.7385084441))', 4326), '0';INSERT INTO tmpTable SELECT '872571', ST_GeomFromText('POLYGON((-90.8042260212 30.7395084441,-90.8042260212 30.7484915559,-90.7937739788 30.7484915559,-90.7937739788 30.7395084441,-90.8042260212 30.7395084441))', 4326), '0';INSERT INTO tmpTable SELECT '872573', ST_GeomFromText('POLYGON((-90.7812282478 30.7805084441,-90.7812282478 30.7894915559,-90.7707717522 30.7894915559,-90.7707717522 30.7805084441,-90.7812282478 30.7805084441))', 4326), '0';INSERT INTO tmpTable SELECT '872574', ST_GeomFromText('POLYGON((-90.7992283565 30.7825084441,-90.7992283565 30.7914915559,-90.7887716435 30.7914915559,-90.7887716435 30.7825084441,-90.7992283565 30.7825084441))', 4326), '0';INSERT INTO tmpTable SELECT '872575', ST_GeomFromText('POLYGON((-95.1392329877 30.8675084441,-95.1392329877 30.8764915559,-95.1287670123 30.8764915559,-95.1287670123 30.8675084441,-95.1392329877 30.8675084441))', 4326), '0';INSERT INTO tmpTable SELECT '872576', ST_GeomFromText('POLYGON((-95.1332331515 30.8705084441,-95.1332331515 30.8794915559,-95.1227668485 30.8794915559,-95.1227668485 30.8705084441,-95.1332331515 30.8705084441))', 4326), '0';INSERT INTO tmpTable SELECT '872579', ST_GeomFromText('POLYGON((-95.1242368165 30.9375084441,-95.1242368165 30.9464915559,-95.1137631835 30.9464915559,-95.1137631835 30.9375084441,-95.1242368165 30.9375084441))', 4326), '0';INSERT INTO tmpTable SELECT '872580', ST_GeomFromText('POLYGON((-95.1222369809 30.9405084441,-95.1222369809 30.9494915559,-95.1117630191 30.9494915559,-95.1117630191 30.9405084441,-95.1222369809 30.9405084441))', 4326), '0';INSERT INTO tmpTable SELECT '872584', ST_GeomFromText('POLYGON((-93.9152506561 31.1885084441,-93.9152506561 31.1974915559,-93.9047493439 31.1974915559,-93.9047493439 31.1885084441,-93.9152506561 31.1885084441))', 4326), '0';INSERT INTO tmpTable SELECT '872586', ST_GeomFromText('POLYGON((-93.9202509891 31.1945084441,-93.9202509891 31.2034915559,-93.9097490109 31.2034915559,-93.9097490109 31.1945084441,-93.9202509891 31.1945084441))', 4326), '0';INSERT INTO tmpTable SELECT '872587', ST_GeomFromText('POLYGON((-93.9152528777 31.2285084441,-93.9152528777 31.2374915559,-93.9047471223 31.2374915559,-93.9047471223 31.2285084441,-93.9152528777 31.2285084441))', 4326), '0';INSERT INTO tmpTable SELECT '872588', ST_GeomFromText('POLYGON((-93.9162545469 31.2585084441,-93.9162545469 31.2674915559,-93.9057454531 31.2674915559,-93.9057454531 31.2585084441,-93.9162545469 31.2585084441))', 4326), '0';INSERT INTO tmpTable SELECT '872609', ST_GeomFromText('POLYGON((-91.6293603712 33.0745084441,-91.6293603712 33.0834915559,-91.6186396288 33.0834915559,-91.6186396288 33.0745084441,-91.6293603712 33.0745084441))', 4326), '0';INSERT INTO tmpTable SELECT '872621', ST_GeomFromText('POLYGON((-91.2884411565 34.3585084441,-91.2884411565 34.3674915559,-91.2775588435 34.3674915559,-91.2775588435 34.3585084441,-91.2884411565 34.3585084441))', 4326), '0';INSERT INTO tmpTable SELECT '872637', ST_GeomFromText('POLYGON((-90.0255612625 36.1285084441,-90.0255612625 36.1374915559,-90.0144387375 36.1374915559,-90.0144387375 36.1285084441,-90.0255612625 36.1285084441))', 4326), '0';INSERT INTO tmpTable SELECT '872638', ST_GeomFromText('POLYGON((-89.9605961195 36.6145084441,-89.9605961195 36.6234915559,-89.9494038805 36.6234915559,-89.9494038805 36.6145084441,-89.9605961195 36.6145084441))', 4326), '0';INSERT INTO tmpTable SELECT '872639', ST_GeomFromText('POLYGON((-89.9855962647 36.6165084441,-89.9855962647 36.6254915559,-89.9744037353 36.6254915559,-89.9744037353 36.6165084441,-89.9855962647 36.6165084441))', 4326), '0';INSERT INTO tmpTable SELECT '872643', ST_GeomFromText('POLYGON((-89.8466400999 37.2115084441,-89.8466400999 37.2204915559,-89.8353599001 37.2204915559,-89.8353599001 37.2115084441,-89.8466400999 37.2115084441))', 4326), '0';INSERT INTO tmpTable SELECT '872667', ST_GeomFromText('POLYGON((-110.040051001 27.2175084441,-110.040051001 27.2264915559,-110.029948999 27.2264915559,-110.029948999 27.2175084441,-110.040051001 27.2175084441))', 4326), '0';INSERT INTO tmpTable SELECT '872668', ST_GeomFromText('POLYGON((-110.050051046 27.2185084441,-110.050051046 27.2274915559,-110.039948954 27.2274915559,-110.039948954 27.2185084441,-110.050051046 27.2185084441))', 4326), '0';INSERT INTO tmpTable SELECT '872669', ST_GeomFromText('POLYGON((-110.038051364 27.2255084441,-110.038051364 27.2344915559,-110.027948636 27.2344915559,-110.027948636 27.2255084441,-110.038051364 27.2255084441))', 4326), '0';INSERT INTO tmpTable SELECT '872670', ST_GeomFromText('POLYGON((-110.048051454 27.2275084441,-110.048051454 27.2364915559,-110.037948546 27.2364915559,-110.037948546 27.2275084441,-110.048051454 27.2275084441))', 4326), '0';INSERT INTO tmpTable SELECT '872677', ST_GeomFromText('POLYGON((-105.797556593 36.0625084441,-105.797556593 36.0714915559,-105.786443407 36.0714915559,-105.786443407 36.0625084441,-105.797556593 36.0625084441))', 4326), '0';INSERT INTO tmpTable SELECT '872678', ST_GeomFromText('POLYGON((-105.808556734 36.0645084441,-105.808556734 36.0734915559,-105.797443266 36.0734915559,-105.797443266 36.0645084441,-105.808556734 36.0645084441))', 4326), '0';INSERT INTO tmpTable SELECT '872680', ST_GeomFromText('POLYGON((-105.80655737 36.0735084441,-105.80655737 36.0824915559,-105.79544263 36.0824915559,-105.79544263 36.0735084441,-105.80655737 36.0735084441))', 4326), '0';INSERT INTO tmpTable SELECT '872683', ST_GeomFromText('POLYGON((-97.8184084121 45.4975084441,-97.8184084121 45.5064915559,-97.8055915879 45.5064915559,-97.8055915879 45.4975084441,-97.8184084121 45.4975084441))', 4326), '0';INSERT INTO tmpTable SELECT '872684', ST_GeomFromText('POLYGON((-97.8214094368 45.5065084441,-97.8214094368 45.5154915559,-97.8085905632 45.5154915559,-97.8085905632 45.5065084441,-97.8214094368 45.5065084441))', 4326), '0';INSERT INTO tmpTable SELECT '872688', ST_GeomFromText('POLYGON((-102.02870175 47.9125084441,-102.02870175 47.9214915559,-102.01529825 47.9214915559,-102.01529825 47.9125084441,-102.02870175 47.9125084441))', 4326), '0';INSERT INTO tmpTable SELECT '872690', ST_GeomFromText('POLYGON((-95.82971578 48.0205084441,-95.82971578 48.0294915559,-95.81628422 48.0294915559,-95.81628422 48.0205084441,-95.82971578 48.0205084441))', 4326), '0';INSERT INTO tmpTable SELECT '872691', ST_GeomFromText('POLYGON((-95.83471578 48.0205084441,-95.83471578 48.0294915559,-95.82128422 48.0294915559,-95.82128422 48.0205084441,-95.83471578 48.0205084441))', 4326), '0';INSERT INTO tmpTable SELECT '872694', ST_GeomFromText('POLYGON((-101.969724787 48.0895084441,-101.969724787 48.0984915559,-101.956275213 48.0984915559,-101.956275213 48.0895084441,-101.969724787 48.0895084441))', 4326), '0';INSERT INTO tmpTable SELECT '872697', ST_GeomFromText('POLYGON((-103.687861158 49.1035084441,-103.687861158 49.1124915559,-103.674138842 49.1124915559,-103.674138842 49.1035084441,-103.687861158 49.1035084441))', 4326), '0';INSERT INTO tmpTable SELECT '872701', ST_GeomFromText('POLYGON((-101.763928568 49.5845084441,-101.763928568 49.5934915559,-101.750071432 49.5934915559,-101.750071432 49.5845084441,-101.763928568 49.5845084441))', 4326), '0';INSERT INTO tmpTable SELECT '872703', ST_GeomFromText('POLYGON((-101.760929847 49.5935084441,-101.760929847 49.6024915559,-101.747070153 49.6024915559,-101.747070153 49.5935084441,-101.760929847 49.5935084441))', 4326), '0';INSERT INTO tmpTable SELECT '872704', ST_GeomFromText('POLYGON((-101.821933829 49.6215084441,-101.821933829 49.6304915559,-101.808066171 49.6304915559,-101.808066171 49.6215084441,-101.821933829 49.6215084441))', 4326), '0';INSERT INTO tmpTable SELECT '872706', ST_GeomFromText('POLYGON((-91.5669735606 49.8985084441,-91.5669735606 49.9074915559,-91.5530264394 49.9074915559,-91.5530264394 49.8985084441,-91.5669735606 49.8985084441))', 4326), '0';INSERT INTO tmpTable SELECT '872722', ST_GeomFromText('POLYGON((-99.1659915913 25.8605084441,-99.1659915913 25.8694915559,-99.1560084087 25.8694915559,-99.1560084087 25.8605084441,-99.1659915913 25.8605084441))', 4326), '0';INSERT INTO tmpTable SELECT '872724', ST_GeomFromText('POLYGON((-92.9691769339 29.8135084441,-92.9691769339 29.8224915559,-92.9588230661 29.8224915559,-92.9588230661 29.8135084441,-92.9691769339 29.8135084441))', 4326), '0';INSERT INTO tmpTable SELECT '872726', ST_GeomFromText('POLYGON((-91.9421806171 29.8845084441,-91.9421806171 29.8934915559,-91.9318193829 29.8934915559,-91.9318193829 29.8845084441,-91.9421806171 29.8845084441))', 4326), '0';INSERT INTO tmpTable SELECT '872728', ST_GeomFromText('POLYGON((-91.2021940603 30.1415084441,-91.2021940603 30.1504915559,-91.1918059397 30.1504915559,-91.1918059397 30.1415084441,-91.2021940603 30.1415084441))', 4326), '0';INSERT INTO tmpTable SELECT '872731', ST_GeomFromText('POLYGON((-91.1921984392 30.2245084441,-91.1921984392 30.2334915559,-91.1818015608 30.2334915559,-91.1818015608 30.2245084441,-91.1921984392 30.2245084441))', 4326), '0';INSERT INTO tmpTable SELECT '872732', ST_GeomFromText('POLYGON((-90.743222825 30.6805084441,-90.743222825 30.6894915559,-90.732777175 30.6894915559,-90.732777175 30.6805084441,-90.743222825 30.6805084441))', 4326), '0';INSERT INTO tmpTable SELECT '872733', ST_GeomFromText('POLYGON((-90.7282229332 30.6825084441,-90.7282229332 30.6914915559,-90.7177770668 30.6914915559,-90.7177770668 30.6825084441,-90.7282229332 30.6825084441))', 4326), '0';INSERT INTO tmpTable SELECT '872735', ST_GeomFromText('POLYGON((-95.0322288459 30.7915084441,-95.0322288459 30.8004915559,-95.0217711541 30.8004915559,-95.0217711541 30.7915084441,-95.0322288459 30.7915084441))', 4326), '0';INSERT INTO tmpTable SELECT '872736', ST_GeomFromText('POLYGON((-95.0212289003 30.7925084441,-95.0212289003 30.8014915559,-95.0107710997 30.8014915559,-95.0107710997 30.7925084441,-95.0212289003 30.7925084441))', 4326), '0';INSERT INTO tmpTable SELECT '872737', ST_GeomFromText('POLYGON((-95.0112290091 30.7945084441,-95.0112290091 30.8034915559,-95.0007709909 30.8034915559,-95.0007709909 30.7945084441,-95.0112290091 30.7945084441))', 4326), '0';INSERT INTO tmpTable SELECT '872740', ST_GeomFromText('POLYGON((-93.2522356665 30.9165084441,-93.2522356665 30.9254915559,-93.2417643335 30.9254915559,-93.2417643335 30.9165084441,-93.2522356665 30.9165084441))', 4326), '0';INSERT INTO tmpTable SELECT '872742', ST_GeomFromText('POLYGON((-90.7742431909 31.0535084441,-90.7742431909 31.0624915559,-90.7637568091 31.0624915559,-90.7637568091 31.0535084441,-90.7742431909 31.0535084441))', 4326), '0';INSERT INTO tmpTable SELECT '872744', ST_GeomFromText('POLYGON((-93.4912446248 31.0795084441,-93.4912446248 31.0884915559,-93.4807553752 31.0884915559,-93.4807553752 31.0795084441,-93.4912446248 31.0795084441))', 4326), '0';INSERT INTO tmpTable SELECT '872746', ST_GeomFromText('POLYGON((-93.4932451767 31.0895084441,-93.4932451767 31.0984915559,-93.4827548233 31.0984915559,-93.4827548233 31.0895084441,-93.4932451767 31.0895084441))', 4326), '0';INSERT INTO tmpTable SELECT '872748', ST_GeomFromText('POLYGON((-93.8142468343 31.1195084441,-93.8142468343 31.1284915559,-93.8037531657 31.1284915559,-93.8037531657 31.1195084441,-93.8142468343 31.1195084441))', 4326), '0';INSERT INTO tmpTable SELECT '872750', ST_GeomFromText('POLYGON((-92.8712607444 31.3695084441,-92.8712607444 31.3784915559,-92.8607392556 31.3784915559,-92.8607392556 31.3695084441,-92.8712607444 31.3695084441))', 4326), '0';INSERT INTO tmpTable SELECT '872751', ST_GeomFromText('POLYGON((-92.8582608004 31.3705084441,-92.8582608004 31.3794915559,-92.8477391996 31.3794915559,-92.8477391996 31.3705084441,-92.8582608004 31.3705084441))', 4326), '0';INSERT INTO tmpTable SELECT '872752', ST_GeomFromText('POLYGON((-92.8722613044 31.3795084441,-92.8722613044 31.3884915559,-92.8617386956 31.3884915559,-92.8617386956 31.3795084441,-92.8722613044 31.3795084441))', 4326), '0';INSERT INTO tmpTable SELECT '872753', ST_GeomFromText('POLYGON((-92.8602613604 31.3805084441,-92.8602613604 31.3894915559,-92.8497386396 31.3894915559,-92.8497386396 31.3805084441,-92.8602613604 31.3805084441))', 4326), '0';INSERT INTO tmpTable SELECT '872758', ST_GeomFromText('POLYGON((-91.5923521112 32.9385084441,-91.5923521112 32.9474915559,-91.5816478888 32.9474915559,-91.5816478888 32.9385084441,-91.5923521112 32.9385084441))', 4326), '0';INSERT INTO tmpTable SELECT '872759', ST_GeomFromText('POLYGON((-91.5773522323 32.9405084441,-91.5773522323 32.9494915559,-91.5666477677 32.9494915559,-91.5666477677 32.9405084441,-91.5773522323 32.9405084441))', 4326), '0';INSERT INTO tmpTable SELECT '872760', ST_GeomFromText('POLYGON((-91.5943527773 32.9495084441,-91.5943527773 32.9584915559,-91.5836472227 32.9584915559,-91.5836472227 32.9495084441,-91.5943527773 32.9495084441))', 4326), '0';INSERT INTO tmpTable SELECT '872761', ST_GeomFromText('POLYGON((-91.5793528378 32.9505084441,-91.5793528378 32.9594915559,-91.5686471622 32.9594915559,-91.5686471622 32.9505084441,-91.5793528378 32.9505084441))', 4326), '0';INSERT INTO tmpTable SELECT '872763', ST_GeomFromText('POLYGON((-91.5563634219 33.1245084441,-91.5563634219 33.1334915559,-91.5456365781 33.1334915559,-91.5456365781 33.1245084441,-91.5563634219 33.1245084441))', 4326), '0';INSERT INTO tmpTable SELECT '872768', ST_GeomFromText('POLYGON((-91.3173768118 33.3425084441,-91.3173768118 33.3514915559,-91.3066231882 33.3514915559,-91.3066231882 33.3425084441,-91.3173768118 33.3425084441))', 4326), '0';INSERT INTO tmpTable SELECT '872770', ST_GeomFromText('POLYGON((-90.7743809562 33.4095084441,-90.7743809562 33.4184915559,-90.7636190438 33.4184915559,-90.7636190438 33.4095084441,-90.7743809562 33.4095084441))', 4326), '0';INSERT INTO tmpTable SELECT '872771', ST_GeomFromText('POLYGON((-90.8773859227 33.4895084441,-90.8773859227 33.4984915559,-90.8666140773 33.4984915559,-90.8666140773 33.4895084441,-90.8773859227 33.4895084441))', 4326), '0';INSERT INTO tmpTable SELECT '872774', ST_GeomFromText('POLYGON((-90.8793866072 33.5005084441,-90.8793866072 33.5094915559,-90.8686133928 33.5094915559,-90.8686133928 33.5005084441,-90.8793866072 33.5005084441))', 4326), '0';INSERT INTO tmpTable SELECT '872779', ST_GeomFromText('POLYGON((-90.9453898477 33.5525084441,-90.9453898477 33.5614915559,-90.9346101523 33.5614915559,-90.9346101523 33.5525084441,-90.9453898477 33.5525084441))', 4326), '0';INSERT INTO tmpTable SELECT '872781', ST_GeomFromText('POLYGON((-90.8563938476 33.6165084441,-90.8563938476 33.6254915559,-90.8456061524 33.6254915559,-90.8456061524 33.6165084441,-90.8563938476 33.6165084441))', 4326), '0';INSERT INTO tmpTable SELECT '872783', ST_GeomFromText('POLYGON((-90.8503939728 33.6185084441,-90.8503939728 33.6274915559,-90.8396060272 33.6274915559,-90.8396060272 33.6185084441,-90.8503939728 33.6185084441))', 4326), '0';INSERT INTO tmpTable SELECT '872786', ST_GeomFromText('POLYGON((-90.9113949748 33.6345084441,-90.9113949748 33.6434915559,-90.9006050252 33.6434915559,-90.9006050252 33.6345084441,-90.9113949748 33.6345084441))', 4326), '0';INSERT INTO tmpTable SELECT '872788', ST_GeomFromText('POLYGON((-91.6104148647 33.9495084441,-91.6104148647 33.9584915559,-91.5995851353 33.9584915559,-91.5995851353 33.9495084441,-91.6104148647 33.9495084441))', 4326), '0';INSERT INTO tmpTable SELECT '872789', ST_GeomFromText('POLYGON((-91.6464166478 33.9775084441,-91.6464166478 33.9864915559,-91.6355833522 33.9864915559,-91.6355833522 33.9775084441,-91.6464166478 33.9775084441))', 4326), '0';INSERT INTO tmpTable SELECT '872790', ST_GeomFromText('POLYGON((-91.6544169664 33.9825084441,-91.6544169664 33.9914915559,-91.6435830336 33.9914915559,-91.6435830336 33.9825084441,-91.6544169664 33.9825084441))', 4326), '0';INSERT INTO tmpTable SELECT '872791', ST_GeomFromText('POLYGON((-91.6384170939 33.9845084441,-91.6384170939 33.9934915559,-91.6275829061 33.9934915559,-91.6275829061 33.9845084441,-91.6384170939 33.9845084441))', 4326), '0';INSERT INTO tmpTable SELECT '872796', ST_GeomFromText('POLYGON((-91.4094227798 34.0735084441,-91.4094227798 34.0824915559,-91.3985772202 34.0824915559,-91.3985772202 34.0735084441,-91.4094227798 34.0735084441))', 4326), '0';INSERT INTO tmpTable SELECT '872803', ST_GeomFromText('POLYGON((-89.7214342914 34.2525084441,-89.7214342914 34.2614915559,-89.7105657086 34.2614915559,-89.7105657086 34.2525084441,-89.7214342914 34.2525084441))', 4326), '0';INSERT INTO tmpTable SELECT '872808', ST_GeomFromText('POLYGON((-91.4734465571 34.4415084441,-91.4734465571 34.4504915559,-91.4625534429 34.4504915559,-91.4625534429 34.4415084441,-91.4734465571 34.4415084441))', 4326), '0';INSERT INTO tmpTable SELECT '872809', ST_GeomFromText('POLYGON((-91.4574466875 34.4435084441,-91.4574466875 34.4524915559,-91.4465533125 34.4524915559,-91.4465533125 34.4435084441,-91.4574466875 34.4435084441))', 4326), '0';INSERT INTO tmpTable SELECT '872812', ST_GeomFromText('POLYGON((-90.2654528313 34.5375084441,-90.2654528313 34.5464915559,-90.2545471687 34.5464915559,-90.2545471687 34.5375084441,-90.2654528313 34.5375084441))', 4326), '0';INSERT INTO tmpTable SELECT '872813', ST_GeomFromText('POLYGON((-90.2664536177 34.5495084441,-90.2664536177 34.5584915559,-90.2555463823 34.5584915559,-90.2555463823 34.5495084441,-90.2664536177 34.5495084441))', 4326), '0';INSERT INTO tmpTable SELECT '872817', ST_GeomFromText('POLYGON((-90.3554619027 34.6755084441,-90.3554619027 34.6844915559,-90.3445380973 34.6844915559,-90.3445380973 34.6755084441,-90.3554619027 34.6755084441))', 4326), '0';INSERT INTO tmpTable SELECT '872825', ST_GeomFromText('POLYGON((-95.0104722321 34.8315084441,-95.0104722321 34.8404915559,-94.9995277679 34.8404915559,-94.9995277679 34.8315084441,-95.0104722321 34.8315084441))', 4326), '0';INSERT INTO tmpTable SELECT '872828', ST_GeomFromText('POLYGON((-94.3874749601 34.8725084441,-94.3874749601 34.8814915559,-94.3765250399 34.8814915559,-94.3765250399 34.8725084441,-94.3874749601 34.8725084441))', 4326), '0';INSERT INTO tmpTable SELECT '872829', ST_GeomFromText('POLYGON((-94.3754750267 34.8735084441,-94.3754750267 34.8824915559,-94.3645249733 34.8824915559,-94.3645249733 34.8735084441,-94.3754750267 34.8735084441))', 4326), '0';INSERT INTO tmpTable SELECT '872831', ST_GeomFromText('POLYGON((-91.0284802989 34.9525084441,-91.0284802989 34.9614915559,-91.0175197011 34.9614915559,-91.0175197011 34.9525084441,-91.0284802989 34.9525084441))', 4326), '0';INSERT INTO tmpTable SELECT '872832', ST_GeomFromText('POLYGON((-91.0354802989 34.9525084441,-91.0354802989 34.9614915559,-91.0245197011 34.9614915559,-91.0245197011 34.9525084441,-91.0354802989 34.9525084441))', 4326), '0';INSERT INTO tmpTable SELECT '872834', ST_GeomFromText('POLYGON((-90.9544846523 35.0175084441,-90.9544846523 35.0264915559,-90.9435153477 35.0264915559,-90.9435153477 35.0175084441,-90.9544846523 35.0175084441))', 4326), '0';INSERT INTO tmpTable SELECT '872840', ST_GeomFromText('POLYGON((-90.1955033617 35.2945084441,-90.1955033617 35.3034915559,-90.1844966383 35.3034915559,-90.1844966383 35.2945084441,-90.1955033617 35.2945084441))', 4326), '0';INSERT INTO tmpTable SELECT '872841', ST_GeomFromText('POLYGON((-90.1755034977 35.2965084441,-90.1755034977 35.3054915559,-90.1644965023 35.3054915559,-90.1644965023 35.2965084441,-90.1755034977 35.2965084441))', 4326), '0';INSERT INTO tmpTable SELECT '872843', ST_GeomFromText('POLYGON((-90.1815037698 35.3005084441,-90.1815037698 35.3094915559,-90.1704962302 35.3094915559,-90.1704962302 35.3005084441,-90.1815037698 35.3005084441))', 4326), '0';INSERT INTO tmpTable SELECT '872847', ST_GeomFromText('POLYGON((-90.4085127786 35.4325084441,-90.4085127786 35.4414915559,-90.3974872214 35.4414915559,-90.3974872214 35.4325084441,-90.4085127786 35.4325084441))', 4326), '0';INSERT INTO tmpTable SELECT '872848', ST_GeomFromText('POLYGON((-93.3415127786 35.4325084441,-93.3415127786 35.4414915559,-93.3304872214 35.4414915559,-93.3304872214 35.4325084441,-93.3415127786 35.4325084441))', 4326), '0';
أكثر...