5

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?

Improve this question
1
  • I don't understand where the points you're trying to use for this spatial join are coming from
    – raphael
    Commented Oct 13, 2016 at 21:19

3 Answers 3

Reset to default
7

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.

Improve this answer
2
  • I think @Oesor is looking for an update operation rather than an append operation. Or does shp2pgsql do updates?
    – underdark
    Commented Mar 7, 2012 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
    Commented Mar 7, 2012 at 13:04
1

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)

Improve this answer
2
  • 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
    Commented Mar 7, 2012 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
    Commented Mar 7, 2012 at 16:53
0

Since this is the first Google result to qgis postgis insert into existing table, I'm going to answer the question in the title of OP's question: How to insert geometries from shapefile into existing table?

  1. Add a PostGIS layer to your project in QGIS
  2. Enable editing on your PostGIS layer
  3. Select a feature on a different layer (this could be through a spatial join
  4. Copy that feature
  5. Paste it into your PostGIS layer

Caveat: I haven't tried this with layers that have different columns.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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