January 2012 – GeoNotes

SQLite doesn’t have a nice ALTER TABLE DROP COLUMN command and neither does spatialite. Instead, you get to run a long sequence of commands like this. Here I wanted to drop all the extra columns from my huc12 layer for the country.

So, starting with a table that looks like this

CREATE TABLE “huc12” (
PK_UID INTEGER PRIMARY KEY AUTOINCREMENT,
“OBJECTID” INTEGER,
“HUC_8” TEXT,
“HUC_10” TEXT,
“HUC_12” TEXT,
“ACRES” DOUBLE,
“NCONTRB_A” DOUBLE,
“HU_10_GNIS” TEXT,
“HU_12_GNIS” TEXT,
“HU_10_DS” TEXT,
“HU_10_NAME” TEXT,
“HU_10_MOD” TEXT,
“HU_10_TYPE” TEXT,
“HU_12_DS” TEXT,
“HU_12_NAME” TEXT,
“HU_12_MOD” TEXT,
“HU_12_TYPE” TEXT,
“META_ID” TEXT,
“STATES” TEXT,
“GlobalID” TEXT,
“SHAPE_Leng” DOUBLE,
“SHAPE_Area” DOUBLE, “Geometry” MULTIPOLYGON)

</pre>

and ending with


CREATE TABLE "huc12" (
 PK_UID INTEGER PRIMARY KEY AUTOINCREMENT,
 "HUC_12" TEXT,
 "ACRES" DOUBLE,
 "HU_12_GNIS" TEXT,
 "HU_12_NAME" TEXT,
 "HU_12_MOD" TEXT,
 "HU_12_TYPE" TEXT,
 "GlobalID" TEXT,
 "Geometry" MULTIPOLYGON)

I ran this stuff in the middle.


BEGIN TRANSACTION;
 CREATE temporary TABLE "huc12sm" (
 PK_UID INTEGER PRIMARY KEY AUTOINCREMENT,
 "HUC_12" TEXT,
 "ACRES" DOUBLE,
 "HU_12_GNIS" TEXT,
 "HU_12_NAME" TEXT,
 "HU_12_MOD" TEXT,
 "HU_12_TYPE" TEXT,
 "GlobalID" TEXT,
 "Geometry" MULTIPOLYGON);
 INSERT INTO huc12sm SELECT PK_UID, HUC_12, ACRES, HU_12_GNIS, HU_12_NAME, HU_12_MOD, HU_12_TYPE, GlobalID, Geometry FROM huc12;
 DROP TABLE huc12;
 CREATE TABLE "huc12" (
 PK_UID INTEGER PRIMARY KEY AUTOINCREMENT,
 "HUC_12" TEXT,
 "ACRES" DOUBLE,
 "HU_12_GNIS" TEXT,
 "HU_12_NAME" TEXT,
 "HU_12_MOD" TEXT,
 "HU_12_TYPE" TEXT,
 "GlobalID" TEXT,
 "Geometry" MULTIPOLYGON);
 INSERT INTO huc12 SELECT PK_UID, HUC_12, ACRES, HU_12_GNIS, HU_12_NAME, HU_12_MOD, HU_12_TYPE, GlobalID, Geometry FROM huc12sm;
 DROP TABLE huc12sm;
 COMMIT;
 VACUUM;
 HUC_12, ACRES, HU_12_GNIS, HU_12
 SELECT Count(*), GeometryType("Geometry"), Srid("Geometry"), CoordDimension("Geometry")
 FROM "huc12"
 GROUP BY 2, 3, 4;

SELECT RebuildGeometryTriggers('huc12', 'Geometry')

I wish someone would package this stuff into a nice function… hint hint.

Month: January 2012

Dropping columns in sqlite/spatialite