I may very well be doing something wrong here, but:

If I import some shapefile to a PostGIS database using shp2pgsql, I have to first figure out the SRID/EPSG of that shapefile. I think this is, at minimum, a two step process. First I query the shapefile like this:

>ogrinfo -al -so someshapefile.shp

which returns the well-known text (wkt) projection information, but is a bit verbose and somewhat opaque [to me]. Something like:

    SPHEROID["GRS 1980",6378137,298.257222101,

I then typically run the wkt info through a conversion tool like Prj2EPSG to find the EPSG/SRID.

At this point, I can import the shapefile using:

>shp2pgsql -I -s 4269 someshapefile.shp <schema>.<table> | psql -U <user> -d <dbname> -h <hostaddress> -p 5432

Note, I specify the SRID with the -s flag.

If, I run shp2pgsql without specifying the SRID, no projection is set and I think the geom column has to manually be updated to include a projection.

Alternatively, I can skip the lookup, and just use ogr2ogr:

>ogr2ogr -f "PostgreSQL" "PG:host=<hostaddress> user=<user> dbname=<dbname> password=<password>" "C:/shapefile.shp" -nln <schema>.<table>

which apparently sets the projection fine, presumably extracting it automatically from the source shapefile/prj.


So what's the disadvantage to using ogr2ogr? Is there in fact a flag so shp2pgsql automatically extracts and set the right projection also? If not, why not?


There's an interesting, perhaps slightly dated, comparative analysis of using ogr2ogr vs shp2pgsql available on naturalgis.pt. It demonstrates for their particular sample data, that ogr2ogr performs significantly better on small datasets, but that shp2pgsql performs slightly better on larger datasets.

I don't feel this provides a definitive answer. The codebases may have evolved, improving each one's performance. They've only tested a very small set of sample data. The representative "large" dataset isn't actually that large. Also, it is mainly discussing issues of performance, which certainly affects usability, but the original question is more interested in usability related user input requirements.

  • 1
    I don't think I have ever had a shapefile where I have doubted the SRS. I have certainly had shapefiles that have forced me to question my will to live on so many other levels, but the issue about which is the better environment to parse an outdated, 2Gb limited format, that allows all kinds of self-intersecting geometries and so many other horrors is not one of them. – John Powell Sep 25 '18 at 19:35

Without speaking with the shp2pgsql developers my answer is opinion-based, but at least shp2pgsql is much easier without automatic detection of the projection. The main part of the source code is about 1800 lines http://postgis.net/docs/doxygen/2.2/d8/da3/shp2pgsql-core_8c_source.html and it does what is essential: converts geometries and attributes from shapefiles into PostGIS.

In GDAL sources the code that only takes care of interpreting the ESRI .prj files is 2700 lines https://github.com/OSGeo/gdal/blob/master/gdal/ogr/ogr_srs_esri.cpp.

Pros and cons:

For users point of view the main advantages of shp2pgsql are:

Main disadvantage of shp2pgsql is that it supports only shapefiles.

GDAL and ogr2ogr give the same functionalities for shapefiles than shp2pgsql but GDAL can handle tens of different vector formats https://gdal.org/ogr_formats.html and it gives much more options for selecting and processing data during the conversion. However, with GDAL

  • User must install GDAL
  • User must learn to use GDAL, especially ogr2ogr and the many options of the PostGIS driver
  • ogr2ogr is command line utility without GUI
| improve this answer | |
  • You have a valid point-- the logic for parsing the .prj file is complicated and has unaddressed corner cases. My question, however, is predominately related to usability of the tools. – jac Sep 25 '18 at 6:57
  • ogr2ogr and gdal are necessary evils, but, your point is a good one -- they are non-obvious and have a learning curve. shp2pgsql does what it says on the can. – John Powell Sep 25 '18 at 19:37

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