15

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?

Alireza
  • 5,421
  • 5
  • 34
  • 67
barelyknown
  • 5,510
  • 3
  • 34
  • 46

3 Answers3

38

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.

Jeehut
  • 20,202
  • 8
  • 59
  • 80
barelyknown
  • 5,510
  • 3
  • 34
  • 46
  • 1
    macports postgis: `/opt/local/share/postgresql93/contrib/postgis-2.1/spatial_ref_sys.sql` – Seamus Abshere Mar 31 '14 at 18:04
  • 4
    I noticed that all I had to do was rerun `rake db:test:prepare` or rake spec to fix up the spacial_ref_sys table. – Mike Jul 25 '14 at 15:35
  • Damn, still doesn't work with v 1.5.1 - `spatial_ref_sys` is deleted everytime suite is executed – lessless Apr 03 '16 at 04:55
  • This might seem obvious, but you have a separate cleanup defined that uses delete or truncation, you'll need to add the `{:except => %w[spatial_ref_sys]}` there as well. This is really common for js feature tests IE `config.before(:each, :js => true)`. Even if feature tests are not using postgis, if he feature tests are run before other tests then you'll run into the same problem when running the whole test suite – Calvin Apr 10 '17 at 20:27
  • With newer versions of the Postgres app the directory is `/Applications/Postgres.app/Contents/Versions/9.4/share/postgresql/contrib/postgis-2.1/` – CWitty Nov 09 '18 at 17:54
14

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 a bug in database_cleaner version 1.4.0, so you need to specify the schema of the table exceptions:

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

tee
  • 4,149
  • 1
  • 32
  • 44
4

This is due to not having 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 database name, and /usr/share/.../spatial_ref_sys.sql is the path where my file is stored. This works for me.

Arman H
  • 5,488
  • 10
  • 51
  • 76
mohitji
  • 129
  • 1
  • 10