PostGIS Shapefile Importer Projection SRID

Question

I have a shapefile that I have imported into PostgreSQL using the PostGIS Shapefile Importer and it is currently projected in a State Plane Coordinate System. When I imported it, I selected an import SRID of 4326 (WGS84). Afterwards when I was trying to manipulate, I was getting some odd results because of the fact that my information was still seemingly in a projected coordinate system.

Does the Shapefile Importer not re-project when you assign the SRID while importing?

Answer 1

you can do a few things

1. set your srid to your state plane CS then use the st_transform function to convert to WGS84 on your geometry during whatever calculation you are performing -this will only reporject or transform the SRID on the query manipulation you are performing, it will not change the underlining SRID for the original table

   st_transform(ST_SetSRID(geom,'state plane CS'),4326)
   

2. change your underlining SRID before you perform the calculations

   SELECT UpdateGeometrySRID('table name','geom',4326);
   
   ALTER TABLE 'table name'
   ALTER COLUMN geom 
   TYPE Geometry(point/line..?, 4326) 
   USING ST_Transform(geom, 4326);
   
   Update 'table name' set geom= st_transform(geom,4326);
   
   select st_srid(geom) from 'table name';
   

Answer 2

A better tool you might try out is the QGIS DB Manager import tool, which gives you the option to re-project on import.

Once you've connected to your databse, you can browse to your shapefile, name the table and schema, then:

Set your "Source SRID" to the State Plane SRID and the "Target SRID" to 4326

Your data will then be re-projected on import to your PostGIS database.

Answer 3

Depends on whether you use shp2pgsql or shp2pgsql-gui.

If you use shp2pgsql from the command line, the option -s source_srid:to_srid should reproject for you, just plug in your state plane epsg code and 4326, separated by a colon. From the man shp2pgsql page on linux...

-s [<FROM_SRID>:]<SRID>
   Creates  and  populates  the  geometry tables with the specified
   SRID.  If FROM_SRID is given,  the  geometries  will  be  repro‐
   jected.  Reprojection cannot be used with -D.

I don't think the gui version lets you do that, it just lets you set the srid value which is assigned to the geometries which are imported.

Not tried this myself though, I usually use the technique in the answer by @ziggy as I use shp2pgsql-gui.