PG::UndefinedObject: ERROR: type "st_point" does not exist
Closed this issue · 13 comments
I'm following the setup documentation at https://github.com/rgeo/activerecord-postgis-adapter#creating-spatial-tables and I have created this migration:
class ChangeLatLongColumnTypeToPoint < ActiveRecord::Migration
def change
change_table :stores do |t|
t.remove :latitude
t.remove :longitude
t.st_point :lonlat, geographic: true
t.index :lonlat, using: :gist
end
end
end
When I try to run it, I get:
== 20160219044707 ChangeLatLongColumnTypeToPoint: migrating ===================
-- change_table(:stores)
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:
PG::UndefinedObject: ERROR: type "st_point" does not exist
LINE 1: ALTER TABLE "stores" ADD "lonlat" st_point
Please let me know what other information I can provide that would be useful in troubleshooting. Thanks so much!
Did you enable_extension "postgis" in the migration? I suppose if your latitude and longitude columns are spatial, then it would've been already, but maybe they were stored as strings and you are trying to store them spatially.
class ChangeLatLongColumnTypeToPoint < ActiveRecord::Migration
def change
enable_extension "postgis"
change_table :stores do |t|
t.remove :latitude
t.remove :longitude
t.st_point :lonlat, geographic: true
t.index :lonlat, using: :gist
end
end
end
I ran rake db:gis:setup as per https://github.com/rgeo/activerecord-postgis-adapter#upgrading-an-existing-database. Do I need to enable the extension in the migration as well?
@allthesignals The above didn't work for me. It fails with NameError: uninitialized constant EnablePostgis.
Instead, I created an earlier migration with just the following;
class EnablePostgis < ActiveRecord::Migration
def change
enable_extension :postgis
end
end
I suspect (new to Ruby and Rails) that you should put the enable_extension :postgis inside the change definition.
However, this didn't solve the issue. I'm still getting;
PG::UndefinedObject: ERROR: type "st_point" does not exist
LINE 1: ALTER TABLE "todos" ADD "location" st_point
That's right, @SpacyRicochet -- it should be inside the change method. Thank you. Updating my post.
What version of Active Record are you using? I can only think of this: https://github.com/rgeo/activerecord-postgis-adapter#point-and-polygon-types-with-activerecord-42.
I'm using ~> 4.0.0.beta. I'm very new to Ruby and Rails, so I figured it would be better for me to start learning from the latest release of Rails (5.0.0.beta2). Of course, that has some caveats, like PostGis still being in beta as well.
I'm not expecting that st_point not being a type is an issue with the 4.0 beta though. Could still be, that's true.
Fixed it. I forgot to change the database adapter to PostGIS.
@benhorowitz Don't forget to change the adapter in config/database.yml;
adapter: postgis
Ah, yes! Very important. I did the same thing. If you're using a database url, be sure that you use "postgis://" instead of "postgres://".
@SpacyRicochet thanks for that, adapter is set correctly.
@allthesignals, the migration is now:
class ChangeLatLongColumnTypeToPoint < ActiveRecord::Migration
def change
enable_extension "postgis"
change_table :stores do |t|
t.remove :latitude
t.remove :longitude
t.st_point :lonlat, geographic: true
t.index :lonlat, using: :gist
end
end
end
But I'm still seeing the same error:
== 20160219044707 ChangeLatLongColumnTypeToPoint: migrating ===================
-- enable_extension("postgis")
-> 1.0102s
-- change_table(:stores)
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:
PG::UndefinedObject: ERROR: type "st_point" does not exist
LINE 1: ALTER TABLE "stores" ADD "lonlat" st_point
Anything else you can think of that might be incorrect?
I had this problem today, until I realized I incorrectly set the adapter to postgis (I changed the config in another app).
So everyone having this problem: Please double check you are using the postgis adapter correctly...
I ran into the same issue today when I tried to run some migrations on heroku which were working fine in my dev environment and on circleci
StandardError: An error has occurred, this and all later migrations canceled:
undefined method `st_polygon' for #<ActiveRecord::ConnectionAdapters::PostgreSQL::TableDefinition:0x007fc1a2890808>
Did you mean? polygon
After seeing @allthesignals comment and looking a little closer at the heroku postgis docs I realised I had to set the rails database connector url
production:
url: <%= ENV['DATABASE_URL'].sub(/^postgres/, "postgis") %>
I hope this helps someone else and thanks @allthesignals
Thanks everyone for the answers. This isn't a bug, so I'm closing the issue. If you have suggestions for better documentation, please open a pull request or submit another issue.
@ConorNugent you are rock!
Hi there guys, I am not sure if I should open this again, so I will comment before re-opening. I'm facing this same exception. At the beginning, I thought it was an issue with my postgres docker image, but the exception persists even after changing to my local postgres server.
(82.7ms) SELECT pg_try_advisory_lock(7205104339106223375)
(84.0ms) SELECT "schema_migrations"."version" FROM "schema_migrations" ORDER BY "schema_migrations"."version" ASC
Migrating to AddSpacialColumnToSites (20171012171732)
(82.3ms) BEGIN
== 20171012171732 AddSpacialColumnToSites: migrating ==========================
-- remove_column(:sites, :lon, :float)
(91.7ms) ALTER TABLE "sites" DROP "lon"
-> 0.0922s
-- remove_column(:sites, :lat, :float)
(82.7ms) ALTER TABLE "sites" DROP "lat"
-> 0.0830s
-- add_column(:sites, :latlon, :st_point, {:geographic=>true})
(84.3ms) ALTER TABLE "sites" ADD "latlon" st_point
(82.4ms) ROLLBACK
(83.6ms) SELECT pg_advisory_unlock(7205104339106223375)
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:
PG::UndefinedObject: ERROR: type "st_point" does not exist
LINE 1: ALTER TABLE "sites" ADD "latlon" st_point
^
: ALTER TABLE "sites" ADD "latlon" st_point
Following is my project configuration:
Gemfile
# * rgeo (0.6.0)
# * rgeo-activerecord (5.1.1)....
gem 'rgeo'
gem 'rgeo-activerecord'
gem 'activerecord-postgis-adapter'
...My database.yml:
default: &default
adapter: postgis
encoding: unicode
schema_search_path: '"$user", public, postgis'
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
development:
<<: *default
database: development
username: user
password: pass
host: localhost
port: 5432
min_messages: notice
test:
<<: *default
database: testMy migration is the first one in the project:
# I also tried with the raw SQL version
class InstallPostgisExtension < ActiveRecord::Migration[5.1]
def up
enable_extension :postgis
end
def down
disable_extension :postgis
end
endI ran rake db:gis:setup that returns me the message NOTICE: extension "postgis" already exists, skipping (actually I ran all the rake db:* tasks). Another thing I tried was logging into my database and setting up postgis manually.
psql -h localhost -p 5432 -U db_name -d development
development=# show search_path;
search_path
-----------------
"$user", public
(1 row)
development=# CREATE EXTENSION postgis;
CREATE EXTENSION
meetwork_api_development=# set search_path = "$user", public, postgis;
SET
development=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------+-------+--------
public | geography_columns | view | user
public | geometry_columns | view | user
public | raster_columns | view | user
public | raster_overviews | view | user
public | spatial_ref_sys | table | user
(5 rows)
Am I missing something? I have tried with almost anything I could imagine, so maybe I'm just tired.
Thank for your help.