I was following Daniel Azuma's talk on geospatial analysis with rails but I am having difficulty whenever I run rake db:migrate in the second project.
The details of my setup are as follows: I am running Postgresql using Postgres.app which gives me version 9.1.3 of Postgres and 2.0.0 of PostGIS. I run into a few issues with the database.yml file, and running migrations. ( I have added the relevant gems and required their info in application.rb)
My database.yml file looks like this:
development:
adapter: postgis
postgis_extension: true
host: localhost
encoding: unicode
database: my_app_development
pool: 5
username: my_app
password:
If I add the following line schema_search_path: "public,postgis" I get:
rake aborted!
PG::Error: ERROR: schema "postgis" does not exist
: SET search_path TO public,postgis
If I remove that line I receive the following error when I try to migrate my database:
rake aborted!
PG::Error: ERROR: relation "geometry_columns" does not exist
LINE 1: SELECT * FROM geometry_columns WHERE f_table_name='schema_mi... ^
: SELECT * FROM geometry_columns WHERE f_table_name='schema_migrations'
Does anyone have an idea on how to fix these issues?
Drop PostGIS extenion in public schema and recreate it in postgis schema.
DROP EXTENSION PostGIS;
CREATE SCHEMA postgis;
CREATE EXTENSION PostGIS WITH SCHEMA postgis;
GRANT ALL ON postgis.geometry_columns TO PUBLIC;
GRANT ALL ON postgis.spatial_ref_sys TO PUBLIC
Here is how I solved the issue. I first created a new migration to add postgis to the database. (I have already installed both postgis and postgresql through homebrew on a mac.)
rails g migration add_postgis_to_database
In the migration file I removed the change method and used the execute method to add POSTGIS.
execute("CREATE EXTENSION postgis;")
After that you can check the database to make sure that postgis is available.
psql your_database_name
SELECT PostGIS_full_version();
What version of PostgreSQL are you using? The EXTENSION thing appeared in 9.1. Extensions are a handy way of loading several objects in one package.
If you're on less than 9.1 you'll probably be able to load PostGIS following those instructions (all the -f commands). It may also be a good idea to upgrade, but that's up to you.
Actually, the install command needs to invoke the postgis version
sudo apt-get install -y postgis postgresql-9.3-postgis-2.1
The simplest way forward is then to declare
sudo -u postgres psql -c "CREATE EXTENSION postgis" your-pg-database-name
avoiding migration hiccups.
Make sure that you have installed this
sudo apt-get install postgresql-9.3-postgis
I was facing same issue due to missing this package.
I had the same issue, except while @Raido's solution fixed the issue for db:migrate, I was still having issues with the Apartment gem when a tenant was created (e.g. during db:seed).
I discovered that Rails was automagically adding enable_extension "postgis" to schema.rb, which Apartment uses to create the tenant schema. I'm not sure exactly why Apartment doesn't use the existing postgis extension (perhaps an issue with the search_path at the time of tenant creation), but this results in the same error.
The solution (if you can call it that) was to simply remove the enable_extension "postgis" line from schema.rb. The only problem with this approach is that any subsequent migrations which trigger a schema.rb refresh result in the line being re-added.
Also, I used the Apartment approach of adding the postgis extension to the shared_extensions schema instead of its own. My lib/tasks/db_extensions.rake looks like:
namespace :db do
desc 'Also create shared_extensions Schema'
task :extensions => :environment do
# Create Schema
ActiveRecord::Base.connection.execute 'CREATE SCHEMA IF NOT EXISTS shared_extensions;'
# Enable Hstore
ActiveRecord::Base.connection.execute 'CREATE EXTENSION IF NOT EXISTS HSTORE SCHEMA shared_extensions;'
# Enable uuid-ossp for uuid_generate_v1mc()
ActiveRecord::Base.connection.execute 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp" SCHEMA shared_extensions;'
# Enable postgis extension for geographic data types
ActiveRecord::Base.connection.execute 'DROP EXTENSION IF EXISTS postgis;'
ActiveRecord::Base.connection.execute 'CREATE EXTENSION postgis WITH SCHEMA shared_extensions;'
ActiveRecord::Base.connection.execute 'GRANT USAGE ON SCHEMA shared_extensions to PUBLIC;'
puts 'Created extensions'
end
end
Rake::Task["db:create"].enhance do
Rake::Task["db:extensions"].invoke
end
Rake::Task["db:test:purge"].enhance do
Rake::Task["db:extensions"].invoke
end
And my database.yml looks like:
postgis_options: &postgis_options
adapter: postgis
postgis_extension: postgis # default is postgis
postgis_schema: shared_extensions # default is public
default: &default
schema_search_path: 'public,shared_extensions'
encoding: utf8
<<: *postgis_options
...
production:
<<: *default
url: <%= ENV['DATABASE_URL'].try(:sub, /^postgres/, 'postgis') %>
Not ideal, but it's working. Perhaps this will save someone an hour or two with PostGIS and Apartment. I'd be interested to know if anyone has a better solution than removing the enable_extension call from schema.rb :)
