How do I fix missing PostGIS SRIDs in PostgreSQL?

Question

I received the following error while running my Rspec test suite:

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

I know that I enabled the PostGIS extension. How do I fix this?

Answer 1

The problem is that something removed 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.

To prevent that behavior, change the configuration. For me, that 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'll need to regenerate the rows in that table. Use the script named spatial_ref_sys.sql to do that.

I use Postgres.app, so the command to run that script was:

/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 command may be slightly different.