I'm running into a problem trying to figure out how I should insert a GIS coverage polygon into an existing table. I'm primarily using QGIS and the PostGIS Manager plugin to handle it. After some trial and error, I believe I have my table set up correctly to accept the polygon object that I want to link to my existing table. Below is the schema, pruned down a bit -- the actual table has more traditional SQL columns that are not applicable to this.
However, effectively, I want to enable a spatial query against each of the current rows by using a polygon -- i.e., query against the rows by determining if a point is contained within the polygon.
CREATE TABLE "distribution" ( location character varying(5) NOT NULL, unit smallint NOT NULL, "unitName" character varying(15) NOT NULL, coverage geometry, CONSTRAINT "distribution_pkey" PRIMARY KEY (location, unit), CONSTRAINT enforce_dims_coverage CHECK (st_ndims(coverage) = 2), CONSTRAINT enforce_geotype_coverage CHECK (geometrytype(coverage) = 'POLYGON'::text OR coverage IS NULL), CONSTRAINT enforce_srid_coverage CHECK (st_srid(coverage) = 4326) )
Given the above table and a ESRI shapefile, how do I actually go about inserting the shapefile into the appropriate row's coverage field?