Import Shapefile in Postgresql directly by command line tool. Shapefile is a data vector file, which stores GeoSpatial Data. PostGreSql Database support Spatial or GIS data. As shapefile is just a representation of Geo Data, while postgresql is database which support many GIS functionalities as like finding nearest neighbour, distance calculation from one point to another etc. This makes developer to think for importing the shapefile data in postgresql and view attributes. Similarly you may also check for exporting shapefile from postgresql database.
Prior to import Shapefile in Postgresql, we first need to install postgresql and PostGIS. Also for running a command line tool to import shapefile you should check if shp2pgsql is available in your system and is installed. What does shp2pgsql command does? shp2pgsql is a command line tool, which helps in to convert or import single or multiple shapefiles into postgresql or postgis database tables.
If you are linux or ubuntu system, you can check the same by running shp2pgsql command in terminal after navigating to “/usr/lib/postgresql” path. If shp2pgsql is not installed, you should first download the same from the authentic website and then try to follow out the steps provided below.
Import Shapefile in Postgresql
For inserting shapefile to postgis, we first need to have one shapefile. If you don’t have any such shapefile, you may download the shapefile for free as suggested in the linked post. For instance to demonstrate import shapefile in postgresql, I have downloaded United state administrative boundary shape file. Now this unzip the folder. You can choose any other shape file which contain atleast this 3 files i.e .dbf , .shp, and .shx.
Creating database and table in postgresql before inserting the shapefile
Creating Database
Now to import shape file you first need to create database and table. Now lets create one database using given command as:
sudo -u postgres createdb -O DATABASE_USER DATABASE_NAME
now test created database is working properly by executing the given command
psql -h localhost -U DATABASE_USER DATABASE_NAME
for the given command output should be
psql (9.6.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Create Table
Now create the table, you need to write. The structure of table should be in this format, which should have a geom as Geometry column atleast.
CREATE TABLE TABLE_name(gid integer, code smallint, name varchar(32), shape_leng numeric, shape_area numeric, geom geometry);
Here is one such sample
CREATE TABLE nybb(gid integer,code smallint, name varchar(32),shape_leng numeric,shape_area numeric,geom geometry)
shp2pgsql command to import shapefile
Now you are ready to add you shapefile. Just navigate yourself to unzipped folder and execute the given command
shp2pgsql -I -s 2263 SHAPEFILE.shp DATATABLE | psql -U DATABASE_USER -d DATABASE_NAME
for example :
shp2pgsql -I -s 2263 nybb.shp nybb | psql -U hello -d gisdata
here 2263 is spatial reference system or coordinate system of the shape file.
Nybb.shp is the shapefile, nybb is the database table name, hello is the database user which is replace byDATABASE_USER and gisdata is the database.
Know the details of shapefile inserted in postgresql or postgis
Lets get connect with postgresql database. Command for the following as shown:
psql -h localhost -U DATABASE_USER DATABASE_NAME
This will navigate you in database you created. Now to know the attributes details of the table you can run following query:
select * from TABLE_NAME
This will show all rows and column in table format. While I have faced few problems while importing shapefile and checking the shapefile table in postgresql. Lets now look over the problem and solution for the same, if you are also facing the same.
Problems and solution encountered in implementing Import shapefile in postgresql
Problem 1 : While running query or fetching shapefile table in postgresql I got the following error:
psql: FATAL: Peer authentication failed for user “postgres”
Solution 1 : For solving this you need to open and edit pg_hba.conf, where you need to replace all peer with md5.
Md5: The password-based authentication methods are md5 and password. These methods operate similarly except for the way that the password is send across the connection, namely MD5-hashed and clear-text respectively.
If you are at all concerned about password “sniffing” attacks then md5 is preferred. Plain password should always be avoided if possible. However, md5 cannot be used with the db_user_namespace feature.
Peer: The peer authentication method works by obtaining the client’s operating system user name from the kernel and using it as the allowed database user name (with optional user name mapping). This method is only support on local connections.
Problem 2: Unable to open nybb.shp or nybb.SHP or nybb.shp: dbf file (.dbf) can not be open.
Solution : You may also face problem as state above.
To solve this, you must take care that you should add shapefile in a zip folder or in the folder where your shapefile is kept.
I hope this post might helped you in easily uploading or import shapefile in postgresql. If you still find any problem in doing so, do comment below with the problem, so that we can discuss and look together over the solution.