Thread: failed insert queries to one table-postgis enabled db
I have two type of INSERT queries that keep getting errors no matter how many times I run it in pgadminIII 1.8.4 PostgresQL 8.3.4+PostGIS1.3.5 on centos5 Linux machine.
1. execution of 18MB INSERT queries to one empty table failed in postgis enabled database.sample one of the thousands queries :
INSERT INTO lutons (gid, area, perimeter, map1a_, map1a_id, no_id, no_id2, main, use_bgn, unit, lvls, hght, type_bgn, no_, name_biz, addrss, biz2, code, bgn, code_bgn, builds, note, the_geom) VALUES (1, 1980.087000000000000, 179.134500000000000, 63, 0, '1001001', '1001001', 'Trade', 'Office', 1.000000000000000, NULL, '13', 'one', NULL, 'Wayne Co.', 'Elm Street', 'Office', '836', 'Good', 1, 'm', NULL, '01060000000100000001030000000100000007000000CECAEFA1EEA34BC0F618A264232F69C0BF39315DC29748C01E53771C995369C0B0B8C50181173AC080DEED01F6D16BC0D28922835C2450C08EB4F082865E70C048D67B9E1CDA56C09B5ECFBE2B756DC0AF909ABFC40A4DC04CF2D285506169C0CECAEFA1EEA34BC0F618A264232F69C0');
in the Messages tab console in pgadminIII 1.8.4 prompts this error after 30-60 seconds of executing those queries
ERROR: geometry contains non-closed rings
********** Error **********
ERROR: geometry contains non-closed rings
SQL state: XX000
==========================================================================
2. 16mb insert failed . the problem query ;
INSERT INTO lutonszone (gid, area, perimeter, lot_no, unitadmin, zoning, notes, the_geom, projectid) VALUES (23071, 3.071000, -9999.000000, '0036\0178', 46, 'ru4', NULL, '0106000020E61000000100000001030000000100000004000000C61858C7F112594099D87C5C1BDA154072A43330F2125940CC7D721420DA154049F7730AF21259',NULL);
in the Messages tab console :
WARNING: nonstandard use of escape in a string literal
LINE 23237: ...projectid) VALUES (23071, 3.071000, -9999.000000, '0036\0178...
^
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
ERROR: geometry requires more points
********** Error **********
ERROR: geometry requires more points
SQL state: XX000
1. execution of 18MB INSERT queries to one empty table failed in postgis enabled database.sample one of the thousands queries :
INSERT INTO lutons (gid, area, perimeter, map1a_, map1a_id, no_id, no_id2, main, use_bgn, unit, lvls, hght, type_bgn, no_, name_biz, addrss, biz2, code, bgn, code_bgn, builds, note, the_geom) VALUES (1, 1980.087000000000000, 179.134500000000000, 63, 0, '1001001', '1001001', 'Trade', 'Office', 1.000000000000000, NULL, '13', 'one', NULL, 'Wayne Co.', 'Elm Street', 'Office', '836', 'Good', 1, 'm', NULL, '01060000000100000001030000000100000007000000CECAEFA1EEA34BC0F618A264232F69C0BF39315DC29748C01E53771C995369C0B0B8C50181173AC080DEED01F6D16BC0D28922835C2450C08EB4F082865E70C048D67B9E1CDA56C09B5ECFBE2B756DC0AF909ABFC40A4DC04CF2D285506169C0CECAEFA1EEA34BC0F618A264232F69C0');
in the Messages tab console in pgadminIII 1.8.4 prompts this error after 30-60 seconds of executing those queries
ERROR: geometry contains non-closed rings
********** Error **********
ERROR: geometry contains non-closed rings
SQL state: XX000
==========================================================================
2. 16mb insert failed . the problem query ;
INSERT INTO lutonszone (gid, area, perimeter, lot_no, unitadmin, zoning, notes, the_geom, projectid) VALUES (23071, 3.071000, -9999.000000, '0036\0178', 46, 'ru4', NULL, '0106000020E61000000100000001030000000100000004000000C61858C7F112594099D87C5C1BDA154072A43330F2125940CC7D721420DA154049F7730AF21259',NULL);
in the Messages tab console :
WARNING: nonstandard use of escape in a string literal
LINE 23237: ...projectid) VALUES (23071, 3.071000, -9999.000000, '0036\0178...
^
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
ERROR: geometry requires more points
********** Error **********
ERROR: geometry requires more points
SQL state: XX000
On Jan 24, 2009, at 9:13 AM, m zyzy wrote: > I have two type of INSERT queries that keep getting errors no matter > how > many times I run it in pgadminIII 1.8.4 PostgresQL > 8.3.4+PostGIS1.3.5 on > centos5 Linux machine. > 2. 16mb insert failed . the problem query ; > > INSERT INTO lutonszone (gid, area, perimeter, lot_no, unitadmin, > zoning, > notes, the_geom, projectid) VALUES (23071, 3.071000, -9999.000000, > '0036\0178', 46, 'ru4', NULL, > '0106000020E61000000100000001030000000100000004000000C61858C7F112594099D87C5C1BDA154072A43330F2125940CC7D721420DA154049F7730AF21259 > ',NULL); > in the Messages tab console : > > WARNING: nonstandard use of escape in a string literal > LINE 23237: ...projectid) VALUES (23071, 3.071000, -9999.000000, > '0036\0178... > ^ > HINT: Use the escape string syntax for escapes, e.g., E'\r\n'. > ERROR: geometry requires more points > > ********** Error ********** Looks like the backslash is interpreted as an escape character. Switch on 'standard_conforming_strings' and probably turn off 'escape_string_warning'. Don't know about the other one, I'm guessing you're supplying a polygon or some other shape that is missing a closing segment (and thus being a polyline effectively I guess). I've never worked with shapes in postGIS though. It could be caused by the above issue if parts of the shape definition contain backslashes as well. And finally, I know there is a limit on query size somewhere... You might be hitting that with 16MB queries. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,497af707747031347810546!