PostGIS Column not found in geometry_columns table

Question

I've just loaded a bunch of shapefiles into postgis using psql2shp (from the command line). All of the shapefiles appeared to have been inserted correctly and the table I created is populated. But the points are projected in the wrong location. I specified the SRID when I imported them using the -s switch in psql:

for i in $(find . -type f -name "*.shp"); do shp2pgsql -I -s 3857 -a $i \ supermarkets_all  | psql --cluster 12/main osm;  done;

If I now check the SRID using Find_SRID() I get an error: "ERROR: find_srid() - could not find the corresponding SRID - is the geometry registered in the GEOMETRY_COLUMNS table? Is there an uppercase/lowercase mismatch? CONTEXT: PL/pgSQL function find_srid(character varying,character varying,character varying) line 17 at RAISE SQL state: P0001"

I've also checked that the geometry_columns view contains my geometry column - which it does. However, the table that it relates to is preceded by square brackets - I'm not sure if this is relevant?

Is there anyway to fix the geometry - or do I just need to re-import all of the data and try again?

Answer 1

1. May be there could also be an issue with your table names based on escaping the space before \ supermarket and escaped after $i, command shp2pgsql -I -s 3857 -a $i \ supermarkets_all in your script. IMO it is directly translated into the SQL code using double quotes around the table name. Your provided table shows, all entries starting with "[...]supermaket*" have a SRID 0. May be this is not coded/ escaped within the Find_SRID() stored procedure. I prefer to use proper SQL92 conform identifiers for table and column names.

2. You could check the column type directly in the table with \d+ " superkarket (psql command line tool) to find out, if there is a misfit between the table geometry_columns entries and the table definition it self. There should appear something like column_name geometry(TYPE, EPSG) corresponding to the geometry columns entry.

3. If not I think (but I'm not sure) you can touch the geometry columns directly with

   ALTER NAME column TYPE GEOMETRY('POINT', EPSG)