CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-4gispostgis
Greg
Greg

Reputation: 2609

Add geometry field to PostGIS with Rails

I have a database with a table called addresses. I want to add a geometry field. I could do it in pgAdmin, but not sure how it would play with Rails.

I think sql would be:

ALTER TABLE addresses ADD geom geometry(Point,4326)

Then I will want to run

UPDATE addresses SET geom = ST_SetSRID(ST_MakePoint(longitude,latitude),4326);

Can I do that in Rails or must I do it in pgAdmin (or psql)?

Thanks. Newbie getting lost in the complications of mashing several applications.

Upvotes: 1

Views: 4434

Answers (3)

Greg
Greg

Reputation: 2609

I hadn't created the postgis extension in my database (a serious error and created some of the frustration of things not working). To get to my database from psql in on the command line

\c my_database_name

then again in psql

create extension postgis

In rails I had to add the correct column (which could have been done when the database was created) which is part of what @K M Rakibul Islam suggested.

add_column :addresses, :geom, :geometry

Now we have the column geom of type geometry. This next step is beyond what I was asking, but it is where it's leading to. Having longitude and latitude isn't enough. I did this in pgAdmin since I knew the syntax, but better if I'd done it via Rails.

UPDATE addresses SET geom = ST_SetSRID(ST_MakePoint(longitude,latitude),4326);

I now need to see if the geom field is correct. But that's not part of this thread.

Upvotes: 1

Rajarshi Das
Rajarshi Das

Reputation: 12320

while using Rails 3.1, and you have tables with the PostGIS geometry datatype. These don't seem to be compatible with rake db:schema:dump or rake db:test:clone and the rake test:* tasks.

First of all, you need a PostgreSQL template with PostGIS functions support.

Create a template database:

$ psql -U postgres
> CREATE DATABASE template_postgis WITH TEMPLATE=template1 ENCODING='UTF8';
> \c template_postgis;
> CREATE LANGUAGE plpgsql;

Load necessary PostGIS functions into template (I'm using Homebrew, so find the paths to your PostGIS SQL files):

$ psql -f /usr/local/share/postgis/postgis.sql template_postgis
$ psql -f /usr/local/share/postgis/spatial_ref_sys.sql template_postgis

Set database as template and grant permissions:

$ psql -U postgres template_postgis
> UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template_postgis';
> GRANT ALL ON geometry_columns TO PUBLIC;
> GRANT ALL ON spatial_ref_sys TO PUBLIC;

Then, add gem postgis_adapter to your Gemfile and run bundle. After that add template: template_postgis to your config/database.yml like this:

development:
  adapter: postgresql
  template: template_postgis
  database: postgis_db

Upvotes: 0

K M Rakibul Islam
K M Rakibul Islam

Reputation: 34318

Are you using this gem activerecord-postgis-adapter? If not, you can use that and then you can add a geometry column in your migration file:

create_table :my_spatial_table do |t|
  t.column :shape1, :geometry
end

or:

create_table :my_spatial_table do |t|
  t.geometry :shape2
end

The activerecord-postgis-adapter extends ActiveRecord's migration syntax to support some spatial types such as geometry. So, you can use geometry type in your Rails migration seamlessly like other built-in types (string, date, integer etc.) while using this gem.

See Creating Spatial Tables section from the gem's documentation for some more information and examples.

Upvotes: 3

Related Questions