Import Shapefile in Postgresql – shp2pgsql

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.

Upload or Import KML file on Google map

Upload or import KML file on Google map. KML or Keyhole Markup Language is file which models and stores geographic features for display in Google Earth or Google maps. You can upload and view the KML file you already created on Google Map, to verify Whether the overlapped feature of KML uploaded has proper valid geometry or not in respect to the Google Map Feature. Before uploading the KML on Google Map, you need to have a Google account. The file uploaded will be saved to “My Places“, you don’t need to upload it twice when require. If you are not having an already built KML file, you can look at How to create KML file on Google map or How to create KML file on Google earth.

You can also try MAPOG Map Analysis Product to upload or import the KML file Online and View on map for free. Along with that you can further process the GIS Data with different operation and finally create a brief Analysis report.

Upload or Import KML file on Google map

Upload or import KML file on Google map
Upload or import KML file on Google map

Here are the steps to upload or import KML file on Google map

1.) Open Google map, and sign in for all in one Google account.
2.) Move to My Places, a button on left upper side of Google map, and then select the Create with classic My Maps as shown below.

Upload or import KML file on Google map
Upload or import KML file on Google map

3.) Select import option, and then browse the KML file you want to upload on Google map.

Upload KML file on Google map
Upload KML file on Google map

4.) Done, edit the title and description. You can make the map public or private. Save the map.

Upload KML file on Google map
Upload KML file on Google map
  • Note: You can upload KML file of Maximum size of 3MB on Google Map, as per Google instructions. While i found some lose of data while uploading KML file of greater than 2MB. Alternative to Google map is Google earth for the same.

The map saved, can be again viewed and downloaded by going to “My Places -> click on the title of map -> then the KML” option appearing just below the title of your map. You can also find the tutorial to convert KML to Shapefile and Shapefile to KML, with the help of QGIS an open source GIS tool. Want to know more technically on the KML and shapefile, i will provide you soon, related tools and coding for the same with explanation.