Postgrex.Error: ERROR (undefined_object): type "geometry" does not exist

[andreapavoni]

Hi there, I'm new to Elixir world :)

I'm trying to play with Phoenix framework, Ecto and Postgres. I needed geolocation and I've found this package. I've added dependencies to mix.exs:

  defp deps do
    [{:phoenix, "~> 0.13"},
     {:phoenix_ecto, "~> 0.4"},
     {:geo, "~> 0.12.0"},
     {:postgrex, ">= 0.0.0"},
     {:phoenix_html, "~> 1.0"},
     {:phoenix_live_reload, "~> 0.4", only: :dev},
     {:cowboy, "~> 1.0"}]
  end

configured database:

config :baho_api, BahoApi.Repo,
  database: "baho_api_dev",
  adapter: Ecto.Adapters.Postgres,
  extensions: [{Geo.PostGIS, library: Geo}],
  username: "USER",
  password: "SECRET",
  size: 10 # The amount of database connections in the pool

then I've created the migration migration:

  def change do
    create table(:mytable) do
      add :place, :geometry
      timestamps
    end
  end

but when I run mix ecto.migrate I get this error:

** (Postgrex.Error) ERROR (undefined_object): type "geometry" does not exist
    (ecto) lib/ecto/adapters/sql/worker.ex:26: Ecto.Adapters.SQL.Worker.query!/4
    (ecto) lib/ecto/adapters/sql.ex:187: Ecto.Adapters.SQL.use_worker/3
    (ecto) lib/ecto/adapters/postgres.ex:59: Ecto.Adapters.Postgres.execute_ddl/3
    (stdlib) timer.erl:194: :timer.tc/3
    (ecto) lib/ecto/migration/runner.ex:22: Ecto.Migration.Runner.run/6
    (ecto) lib/ecto/migrator.ex:113: Ecto.Migrator.attempt/6
    (ecto) lib/ecto/migrator.ex:63: anonymous fn/4 in Ecto.Migrator.do_up/4
    (ecto) lib/ecto/adapters/sql.ex:442: Ecto.Adapters.SQL.transaction/3

I'm almost sure I'm missing something right away, can someone help me with this problem?

thanks in advance :)

[andreapavoni]

as an update, I've also tried to use the git master (and done the new changes to configs), but I get the same error.

[bryanjos]

From the error it looks like it could be that postgis isn't enabled for the database you are using. In psql or whatever admin app you are using for postgres you should run the command 'create extension postgis;' and then try again. I think that's the command. I'm going off memory at the moment.

[andreapavoni]

Thank you for the hint! :-)

for reference, here's a solution (and the right postgres syntax), I've created a migration:

defmodule MyApp.Repo.Migrations.EnablePostgis do
  use Ecto.Migration

  def up do
    execute "CREATE EXTENSION IF NOT EXISTS postgis"
  end

  def down do
    execute "DROP EXTENSION IF EXISTS postgis"
  end
end

I'd suggest to put this in the README, it might be useful for others :P

[bryanjos]

No problem. And yes, I should add that to the docs. I should also start doing it in a migration myself. I've been doing it the old fashioned way