4

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.

Improve this question
6
  • postgis geog column does not exist.
    – Mapperz ♦
    Jul 6, 2011 at 16:46
  • Should I be using a different Column Geometry to geog? I'm not familiar with the different types.
    – djq
    Jul 6, 2011 at 18:41
  • See postgis.refractions.net/docs/ch04.html#PostGIS_Geography for the geography vs geometry options
    – Mapperz ♦
    Jul 6, 2011 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, 2011 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, 2011 at 20:50

7 Answers 7

Reset to default
6

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

Improve this answer
2
  • 1
    Also- click on the Options button:- Is the checkbox [Load into Geography column] checked? Coz the default is 'geometry'
    – mapoholic
    Jul 6, 2011 at 16:28
  • 1
    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, 2011 at 18:44
6

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
Improve this answer
5
  • 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, 2011 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, 2011 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, 2011 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, 2011 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, 2011 at 10:56
4

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

Improve this answer
2
  • 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, 2014 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, 2014 at 10:16
1

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?

Improve this answer
1

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

Improve this answer
1

It could be fixed after creating postgis extension in the database. The http://gis-techniques.blogspot.com/2015/09/fixed-shape-file-import-error-in.html illustrates the steps to create the extension.

Improve this answer
0

Enabling the "Load into GEOGRAPHY column" checkbox resolved my problem.

enter image description here

Improve this answer

Your Answer

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

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