Tag: convert postgresql database to shapefile

Convert Shapefile to database, Vice Versa

Shapefile to Database

Today, I tried to convert shapefile to sql file. Ultimately to import it into the postgis database.

For it you should have shapefile downloaded. If not having you can download it from below.

$wget http://wsgw.mass.gov/data/gispub/shape/state/boundaries.zip
$unzip boundaries.zip

Tool used to convert shapefile to sql file is shp2pgsql.

$shp2pgsql -G BOUNDARY_POLY.shp > BOUNDARY_POLY.sql

Make sure you should be in the directory where shapefile located. Here, sql file has insert commands to the relation country. So there must be table created if your sql file don’t have created table.

new=# CREATE TABLE country(id integer NOT NULL, name text, boundary geography(MultiPolygon,4326),name_alias text, CONSTRAINT “countryId” PRIMARY KEY (id ));

Command to dump sql file into the database.

postgres@amisha:~/9.5/main$ psql new < BOUNDARY_POLY.sql

Database to shapefile

To convert database back to shapefile, I have used pgsql2shp.

$ pgsql2shp -f am3 -h localhost -u postgres gis “SELECT * from planet_osm_line where admin_level=’4′ and name=’Punjab'”

Output

Initializing…
Done (postgis major version: 2).
Output shape: PolyLine
Dumping: X [19 rows].

Thank you.

Advertisements