test database does not seem to have a spatial_ref_sys

[patrick99e99]

When I try to do any sort of spatial tests, I get errors like:

ActiveRecord::StatementInvalid: PG::Error: ERROR: GetProj4StringSPI:
Cannot find SRID (4326) in spatial_ref_sys
: SELECT "posts".* FROM "posts" WHERE (ST_DWithin(location,
ST_GeographyFromText('SRID=4326;POINT(-118.491191 34.019454)'),
8046.7))

My database.yml configuration has the proper settings, including the
path to the postgis sql files--- Actually, it's duplicate to the
development database's settings, yet I can do spatial queries in my
development environment just fine.....

What do I need to do to make my test DB the same?

[patrick99e99]

Interesting.. As an experiment I tried to dump my development db into
the test db, and I see:
ERROR: relation "geometry_columns" does not exist
ERROR: relation "spatial_ref_sys" does not exist

... Why do these not exist?

[patrick99e99]

further exploration...

I am assuming the :geographic => true, is what actually brings those
relations into existence... So, I wiped my test db out, and tried to
migrate in the test environment.. I get:

RAILS_ENV=test rake db:migrate
rake aborted!
PG::Error: ERROR: invalid value for parameter "search_path": "public,
postgis"
DETAIL: schema "postgis" does not exist
: SET search_path TO public,postgis

... yet, doing the same thing in the development environment works
fine.. and like I said before, my dev & test config settings are
identical in database.yml

sooo.. Why does it think schema postgis does not exist?

my test config options show:
16 schema_search_path: "public,postgis"
17 script_dir: /usr/local/Cellar/postgis/2.0.1/share/postgis/

checking on my machine, indeed "/usr/local/Cellar/postgis/2.0.1/share/
postgis/postgis.sql" is a real file...

[patrick99e99]

ahh.. I figured out what is happening... I am using database_cleaner
in between specs ( https://github.com/bmabey/database_cleaner ), and
apparently that is wiping out the spatial_ref_sys columns... Does
anyone know how I can stop that from happening? Or do I need to stop
using database cleaner?

[Tony Collen]

You can specify a template database in your database.yml file, just add a "template" key and point it at template_postgis or whatever.

 - Tony

[Erik Benoist]

Putting this in your env.rb will help for normal cukes:

DatabaseCleaner.strategy = :transaction

For cukes that run some kind of web driver (ie. selenium), you may need to use this:

Cucumber::Rails::Database.javascript_strategy = :truncation, {:except=>%w[spatial_ref_sys]} 

And +1 on Daniel's notes about projections. 

 

-- 

Erik Benoist

[patrick99e99]

> Cucumber::Rails::Database.javascript_strategy = :truncation, {:except=>%w[spatial_ref_sys]}

weird.. I had tried to whitelist, by doing :only => on all my tables,
but that didn't work, so I didn't think the blacklist way would work,
but it does...

thanks!

[patrick99e99]

argh.. Ok, so now I am on a different machine than the one I was on when I originally posted, and I am having the exact same problem-- and the truncation strategy isn't making a difference..  In fact, commenting out all of the database cleaner stuff isn't making a difference either.

All of my tests fail with:

ActiveRecord::StatementInvalid: PG::Error: ERROR:  GetProj4StringSPI: Cannot find SRID (4326) in spatial_ref_sys

Yet, if I run the console with RAILS_ENV=test, then it works:

1.9.3p194 :003 > Post.nearest(:lat => 34.314, :lng => -118.01231, :distance => 5)

  Post Load (54.9ms)  SELECT "posts".* FROM "posts" WHERE (ST_DWithin(geo_data, ST_GeographyFromText('SRID=4326;POINT(-118.01231 34.314)'), 8046.7))

=> []

...  I am at a loss as for why this is happening.  How is it that when running my tests, it can't find spatial_ref_sys, when they are actually there (according to the console)???

[Erik Benoist]

Patrick-
Can you confirm that your *-test databases are being built out using the postgis template, and that they have the spatial_ref_sys table? Remember that when you're in script/console or rails c it is reading from your default rails.env, which is probably development, not test, which your tests will run in.

-Erik

--
Erik Benoist