Shapefile shp can be converted in pgsql file using shp2pgsql command line tool. Sometimes you have a shapefile and want to upload the same in postgresql database, you can directly use shp2pgsql command and upload shapefile to postgis. While there might be possibilites that you would not be having access to your production server to install shp2pgsql command line tool, so you can convert shapefile into a sql file and then upload the same to Database. Lets check how to convert shp to sql file.
Conversion using shp2pgsql – shp to sql
Try Our Tool MapOG Converter for SHP to SQL conversion
For executing this command you need to have shp2pgsql utility in your system. You can check the presence of utility by typing shp2pgsql in terminal. If this utility is present then you will see the following result.
Shp2pgsql has some option, some for them are used here. Option -I creates a spatial index on the geocolumn. Option -s specifies the SRS (Spatial Reference system) in EPSG (European Petroleum Survey Group) number.
–> shp2pgsql -I -s 4326 shpfilepath.shp output_table_name > output.sql
After executing this command, you can run the sql file in postgres to insert data in table.
Code – Convert- Shapefile Shp to SQL postgersql filE
If you know to read binary files by knowing its format, you can easily read and convert shapefile to sql, as shapefile has an open standard file format. For instance, you can look over tutorial I had created in how to read shapefile bounding box using c++ or reading shapefile header.
In codeigniter framework of php, we can use shell_exec() by providing command as input. In command we need to export PGPASSWORD, where password is supplied.
While writing command in PHP, we need to take SRS (Spatial Reference system) of shape file, input shapefile and output file name from the user. The passwrThen query is written in string format and supplied to shell_exec() method. This method returns output as string and generates file in folder.
PGPASSWORD behaves the same as the password connection parameter. Use of this environment variable is not recommended for security reasons, as some operating systems allow non-root users to see process environment variables via ps; instead consider using the ~/.pgpass file. For more detail you can visit https://www.postgresql.org/docs/9.1/static/libpq-envars.html link.
public function shptosql($shpfilepath,$output){
$srs=4326;
$query=”export PGPASSWORD=’download1′; shp2pgsql -I -s $srs $shpfilepath $output > $output.sql”;
shell_exec($query);
}
You can check for SQL file in the same folder where shapefile is kept. The generated result will look like-
Change coordinate system – shp to sql
While converting shapefile into sql, you can not change the coordinate system. But you can execute this process in two steps as-
- Change the coordinate system of shapefile
–> ogr2ogr -f ‘ESRI shapefile’ -s_srs EPSG:4326 (old EPSG number) -t_srs EPSG:32643 (new EPSG number) new_shapefile.shp India_raods.shp
2. Then convert shapefile into sql
–> shp2pgsql -I -s 32643 new_shapefile.shp output_table_name > output.sql
ogr2ogr utility has some options as-
-a_srs srs_def:
Assign an output SRS
-t_srs srs_def:
Reproject/transform to this SRS on output
-s_srs srs_def:
Override source SRS
You may also Check Shapefile to GeoJSON, Shapefile to KML, Shapefile toconverting TopoJSON, etc conversion.
Hope you enjoyed the conversion and executed it successfully. Let us know you face any difficulty during execution of this tutorial via commenting in provided box.