Importing a Shapefile into PostgreSQL

I tried to import a shapefile to a PostgreSQL database using SPIT in QGIS, but kept getting an error because the gid column already existed in the .dbf. So I used the command line instead:

shp2pgsql Downloads/Archive/data_dev.shp > data.sql

then ran the resulting data.sql in PGAdmin.

Trying to execute
SELECT ST_Intersects(ST_GeomFromText('MULTIPOLYGON(((-170.82 -14.40,-170.82 -14.29,-170.62 -14.29,-170.62 -14.40,-170.82 -14.40)))',4326), the_geom)
as uni_data FROM data_dev

ERROR: Operation on two geometries with different SRIDs

I had to set the SRID on the table using
ALTER TABLE data_dev DROP CONSTRAINT "enforce_srid_the_geom" RESTRICT;
UPDATE data_dev SET the_geom = SETSRID (the_geom, 4326);
ALTER TABLE data_dev ADD CONSTRAINT "enforce_srid_the_geom" CHECK (SRID(the_geom)=4326);

(As discussed here.)

About Jennifer Phillips Campbell

Software Developer and Medieval Historian
This entry was posted in PostgreSQL. Bookmark the permalink.

1 Response to Importing a Shapefile into PostgreSQL

  1. André says:

    Hehe, I had the same problem but I decided to just adapt the SRID in the query: laaaazy.

    I bumped into the constraint, and was locked because I had to edit two tables at the same time… meaning I’d have to temporarily lift the constraint.
    As I was tired of tinkering, I decided aaaah, let’s just make a switch-case when constructing the query string and ST_GeomFromText( '...', -1) instead of keep throwing time and willpower into this.

    A day in the life of.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s