1

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? enter image description here

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

1 Answer 1

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)

3
  • thanks, yep I hadn't noticed that my tables all had a space before the actual name. The entries in geometry_columns table and the table name itself do match though. I've even renamed tables to remove the space. But still no joy. I queried the table from the command line as you suggested and the geometry column name from my table (geom) matches the entry in the geometry_columns table. So not really sure what else has gone wrong here...
    – marty_c
    Oct 21, 2021 at 9:55
  • But the SRID in your provided table is ZERO, which is not a valid EPSG. I think you alter the geometry column in your teble ensemble.
    – huckfinn
    Oct 21, 2021 at 13:56
  • Hi @huckfinn - yes its true for some tables - SRID is ZERO, BUT not for the table I refer to here "supermarkets_all". SRID is 3857 (see row 11 of the table above in my screenshot)...
    – marty_c
    Oct 22, 2021 at 8:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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