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 trying to import a shapefile into a PostGIS database using the graphical importer. I just installed POSTGRESQL, and PostGIS. The GUI looks like this:

enter image description here

After testing that my database worked, I chose the shapefile that I wanted to upload, and then received a message that it had failed to import. I tried tweaking a few paramaters, but I'm not sure what I'm doing.

Are there any good tutorials or instructions about how to do this? I'm using Windows 7 64 bit.


Just adding a little more of my error message.

Shapefile import failed. Connection: host=localhost port=5432 user=postgres password='**' Destination: public.Area Source File: D:\files\Area Shapefile type: Polygon Postgis type: MULTIPOLYGON[2] Failed SQL begins: "SET CLIENT_ENCODING TO UTF8; SET STANDARD_CONFORMING_STRINGS TO ON; BEGIN; CREATE TABLE "public"."Area" (gid serial PRIMARY KEY, "fid_1" int4, "area" numeric, "dtm" float8, "dsm" float8, "hgt" float8, "nat_area" numeric, "nat_peri" numeric, "nat_vol" num" Failed in pgui_exec(): ERROR: type "geography" does not exist LINE 14: "the_geom" geography(MULTIPOLYGON,4326)); ^

Shapefile import failed.


I've tried running @MerseyViking's suggestion, and then importing again. In the Geometry Column: box I chose MULTIPOLYGON and then tried to import the projected shapefile. The following errors appeared:

Failed SQL begins: "CREATE INDEX "NeighborCheck_MULTIPOLYGON_gist" ON "public"."NeighborCheck" using gist ("MULTIPOLYGON" gist_geometry_ops);
COMMIT;
"
Failed in pgui_exec(): ERROR:  current transaction is aborted, commands ignored until end of transaction block

Shapefile import failed.

Then I tried ticking the Load into GEOGRAPHY column box in Options, and received the following error:

Failed SQL begins: "CREATE INDEX "NeighborCheck_MULTIPOLYGON_gist" ON "public"."NeighborCheck" using gist ("MULTIPOLYGON" gist_geography_ops);
COMMIT;
"
Failed in pgui_exec(): ERROR:  current transaction is aborted, commands ignored until end of transaction block

Shapefile import failed.

Both error messages are identical and I don't quite understand them.

share|improve this question
    
postgis geog column does not exist. –  Mapperz Jul 6 '11 at 16:46
    
Should I be using a different Column Geometry to geog? I'm not familiar with the different types. –  djq Jul 6 '11 at 18:41
    
See postgis.refractions.net/docs/ch04.html#PostGIS_Geography for the geography vs geometry options –  Mapperz Jul 6 '11 at 18:50
    
it seems that geography() is not a term I can use in my import query. How can I check if I installed all the postgis parts correctly? –  djq Jul 6 '11 at 20:38
    
PostGIS support both Geometry and Geography - it is up to you which you setup for your data - NOTE: The Geography data type stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates. –  Mapperz Jul 6 '11 at 20:50
add comment

5 Answers 5

up vote 6 down vote accepted

The message you are getting is that 'type geography does not exist' This either means you did not install postgis correctly or you are using an older version? Geography data type was introduced in version 1.5

share|improve this answer
1  
Also- click on the Options button:- Is the checkbox [Load into Geography column] checked? Coz the default is 'geometry' –  mapoholic Jul 6 '11 at 16:28
    
I did tick this box - I'm using PostGIS 1.5.2 so I assume it is an error on my part. I was able to import a shapefile when I choose just to import the attributes. –  djq Jul 6 '11 at 18:44
add comment

The clue is in the import log. Note that it says:

Failed in pgui_exec(): ERROR: type geography does not exist
LINE 10: "geog" geography(MULTIPOLYGON,4326));
          ^

Shapefile import failed.

So you should ensure you have a geography column named "geog", and that you are using a version of PostGIS that supports geography columns. If you do have a working geography column, make sure the importer supports them - the GUI seems to imply that it only handles geometry columns.

Also, I notice you don't have a database specified. You will need to create one and spatially enable it. There's a good chapter in PostGIS in Action that has a script that creates a database template from which you can create spatially enabled databases:

CREATE DATABASE template_postgis WITH TEMPLATE = template1 ENCODING = 'UTF8';
\c template_postgis;
CREATE LANGUAGE plpgsql; --this may not be needed if running 8.4
\i /usr/share/pgsql/contrib/postgis-1.5/postgis.sql;
\i /usr/share/pgsql/contrib/postgis-1.5/spatial_ref_sys.sql;
\i /usr/share/pgsql/contrib/postgis-1.5/postgis_comments.sql;
UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template_postgis';
GRANT ALL ON geometry_columns TO PUBLIC;
GRANT ALL ON spatial_ref_sys TO PUBLIC;
\q
share|improve this answer
    
I just installed PostGIS and I think it is the most recent version, so I assumed it supports geography columns. Where do I paste the code that you wrote above? Is it in the PGAdmin SQL window? (Thanks for the comprehensive answer). –  djq Jul 6 '11 at 18:40
    
As @mapoholic says, any version >= 1.5 will work. Yes, that's right. The commands CREATE, UPDATE, and GRANT are SQL, and the ones starting with a slash are PostgreSQL-specific. You only need to run the script once per server, so you could just type it one line at a time, or alternatively copy it to a file and run it from either the command line with psql -f <script filename> or from within the PGAdmin III PSQL console window by typing \i <script filename> –  MerseyViking Jul 7 '11 at 10:08
    
When I try the line \i /usr/share/pgsql/contrib/postgis-1.5/postgis.sql; I get the error message 'No such file or directory'. Where can I check if this exists? I'm not sure if it makes a difference that I'm using windows. I was able to run the two GRANT lines. –  djq Jul 7 '11 at 19:56
    
I'm not certain what I was doing wrong previously, but I followed the steps here: workshops.opengeo.org/stack-intro/postgis.html and managed to import data succesfully. –  djq Jul 7 '11 at 22:55
    
Ah yes, my bad. I assumed you were using linux, despite your screenshot being Windows... The location of those files is wherever you installed PostGIS to; probably somewhere like C:\Program Files or similar. –  MerseyViking Jul 8 '11 at 10:56
add comment

I'm not sure about your question as I've never used that GUIS, but I have found that the SPIT tool in QGIS works quite well. You may want to try that and see if you still have a problem. If so, then it may be that particular shapefile? Have you tried multiple files?

share|improve this answer
add comment

If you haven't explicitly created a spatially-enabled database, that is probably the problem, as MerseyViking suggests.

share|improve this answer
add comment

I too had the same problem and after investigation, I found that the issue was that I simply had not created the postgis spatial extension.

If this is the issue with you, then you can use the following Query:

CREATE EXTENSION postgis

share|improve this answer
1  
@PolyGeo: I think that this user is describing their experience with the same error messages, and who they solved it. I have expanded the answer to provide more body to it. –  Devdatta Tengshe Apr 4 at 10:07
    
Thanks @DevdattaTengshe - that now makes a lot more sense so I'll delete my Comment and see if there are any flags that I can clear. –  PolyGeo Apr 4 at 10:16
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.