How to fix missing PostGIS SRIDs in PostgreSQL?

Question

When I started my Rspec test suite, I received the following error:

PG::InternalError: ERROR: GetProj4StringSPI: Cannot find SRID (4326) in spatial_ref_sys 

I know that I have included the PostGIS extension. How to fix it?

Answer 1

The problem is that something deleted the rows from the spatial_ref_sys table.

In my case, the problem was in my DatabaseCleaner configuration in my spec_helper.rb . It was configured to delete / truncate the table.

Modify the configuration to prevent this behavior. For me it was:

 config.before(:suite) do DatabaseCleaner.strategy = :deletion, {:except => %w[spatial_ref_sys]} DatabaseCleaner.clean_with :truncation, {:except => %w[spatial_ref_sys]} end 

Now you will need to restore the rows in this table. Use a script called spatial_ref_sys.sql to do this.

I am using Postgres.app, so the command to run the script is:

 /Applications/Postgres.app/Contents/MacOS/bin/psql -d database_name -f /Applications/Postgres.app/Contents/MacOS/share/contrib/postgis-2.0/spatial_ref_sys.sql 

Your team may be slightly different.

Answer 2

Running rake db:test:prepare should restore the values ​​in spatial_ref_sys .

UPDATE: this is fixed in version 1.4.1: https://github.com/DatabaseCleaner/database_cleaner/pull/328

There is an error in database_cleaner version 1.4.0, so you need to specify a table exception scheme:

 DatabaseCleaner.strategy = :truncation, { except: ["public.spatial_ref_sys"] } DatabaseCleaner.clean_with :truncation, { except: ["public.spatial_ref_sys"] } 

In version 1.4.0, the schema is returned as part of the table name. See https://github.com/DatabaseCleaner/database_cleaner/pull/282

Answer 3

This is due to the lack of a spatial_ref_sys table. Insert this table using the following:

 psql -d country -f /usr/share/postgresql/9.3/contrib/postgis-2.1/spatial_ref_sys. 

country is the name of the database, and /usr/share/.../spatial_ref_sys.sql is the path where my file is stored. This works for me.