Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

up vote 3 down vote accepted

I am not sure about the QGIS PostGIS Manager, but the command line utility that comes with PostGIS shp2pgsql has a "-a" option that will append a shapefile to an existing table.

share|improve this answer
    
I think @Oesor is looking for an update operation rather than an append operation. Or does shp2pgsql do updates? –  underdark Mar 7 '12 at 7:40
    
shp2pgsql will not do updates. It will either create a new table, drop and recreate a table, or append to an existing table. You can use an insert trigger in PostgreSQL to turn the inserts into updates based on a primary key lookup, but that should be a separate question here. –  David Bitner Mar 7 '12 at 13:04
add comment

You can also use the plugin SPIT in Quantum GIS. This will allow you to insert your shapefile as a new table in PostGIS. Then you will have to copy the data from this new table to the one you have modelled.

This solution is probably less straight-forward than the one proposed by David Bitner, but it has the advantage of using graphical tools, not command line (this is by far not always an advantage, but maybe easier at the beginning)

share|improve this answer
    
From what I can tell (hub.qgis.org/issues/3710), postgis manager will be replacing SPIT, as it allows to append to a table. I'm not really asking about tools, really, though, but more about how the schema itself should be set up. –  Oesor Mar 7 '12 at 14:12
    
Ok, sorry then I misunderstood your question! But what do you actually want to know then? In general your table can have any structure, as long as it has a single geometry column. The import shouldn't be affected by the number or type of columns in your table. –  Stéphane Henriod Mar 7 '12 at 16:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.