The PostGIS ActiveRecord Adapter is an ActiveRecord connection adapter based on the standard postgresql adapter. It extends the standard adapter to provide support for the spatial extensions provided by PostGIS, using the RGeo library to represent spatial data in Ruby. Like the standard postgresql adapter, this adapter requires the pg gem.
First, this adapter extends the migration syntax to support creating spatial columns and indexes. To create a spatial column, use the :geometry type, or any of the OGC spatial types such as :point or :line_string to set a geometry type constraint. You may also provide any of the following options:
-
:geographic– If set to true, create a PostGIS geography column; otherwise create a geometry column. Default is false. -
:srid– Set a SRID constraint for the column. Default is 4326 for a geography column, or -1 for a geometry column. Note that PostGIS currently (as of version 1.5.2) requires geography columns to have SRID 4326, so this constraint is of limited use for geography columns. -
:has_z– Specify that objects in this column include a Z coordinate. Default is false. -
:has_m– Specify that objects in this column include an M coordinate. Default is false.
To create a spatial index, set the :spatial option to true when creating the index.
Examples:
create_table :my_spatial_table do |t| t.column :shape, :geometry # or t.geometry :shape t.line_string :path, :srid => 3785 t.point :lonlat, :geographic => true end change_table :my_spatial_table do |t| t.index :lonlat, :spatial => true end
When this adapter is in use, spatial attributes in your ActiveRecord objects will have RGeo geometry values. You can set spatial attributes either to RGeo geometry objects, or to strings in WKT (well-known text) format, which the adapter will automatically convert to geometry objects.
Spatial objects in RGeo are tied to a factory that specifies the coordinate system as well as other behaviors of the object. You must therefore specify a factory for each spatial column (attribute) in your ActiveRecord class. You can either set an explicit factory for a specific column, or provide a factory generator that will yield the appropriate factory for the table’s spatial columns based on their types. For the former, call the set_rgeo_factory_for_column class method on your ActiveRecord class. For the latter, set the rgeo_factory_generator class attribute. This generator should understand the usual :srid, :has_z_coordinate, and :has_m_coordinate options. It will also be passed a :geographic option indicating whether the column is a geography column. The set_rgeo_factory_for_column and rgeo_factory_generator methods are actually implemented and documented in the “rgeo-activerecord” gem.
Examples, given the spatial table defined above:
class MySpatialTable < ActiveRecord::Base # By default, use the GEOS implementation for spatial columns. self.rgeo_factory_generator = RGeo::Geos.factory_generator # But use a geographic implementation for the :lonlat column. set_rgeo_factory_for_column(:lonlat, RGeo::Geographic.spherical_factory(:srid => 4326)) end
Now you can interact with the data using the RGeo types:
rec = MySpatialTable.new rec.lonlat = 'POINT(-122 47)' # You can set by feature object or WKT. loc = rec.lonlat # Accessing always returns a feature object, in # this case, a geographic that understands latitude. loc.latitude # => 47 rec.shape = loc # the factory for the :shape column is GEOS, so the # value will be cast from geographic to GEOS. RGeo::Geos.is_geos?(rec.shape) # => true
You can create simple queries based on objective equality in the same way you would on a scalar column:
rec = MySpatialTable.where(:lonlat => RGeo::Geos.factory.point(-122, 47)).first
You can also use WKT:
rec = MySpatialTable.where(:lonlat => 'POINT(-122 47)').first
Most more complex spatial queries require the use of SQL functions. You can write such queries in raw SQL, but you may find the “squeel” gem very helpful in this regard. Using squeel, you can write queries like the following:
my_polygon = get_my_polygon() MySpatialTable.where{st_intersects(lonlat, my_polygon)}.first
Note that using squeel to create SQL functions requires Rails 3.1 or later.
One common query is to find all objects displaying in a window. This can be done using the overlap (&&) operator with a bounding box. Here’s an example that finds linestrings in the “path” column that intersect a bounding box:
sw = get_sw_corner_in_projected_coordinates() ne = get_ne_corner_in_projected_coordinates() window = RGeo::Cartesian::BoundingBox.create_from_points(sw, ne) MySpatialTable.where{path.op('&&', window)}.all
Note that bounding box queries make sense only in a projected coordinate system; you shouldn’t try to run such a query against a lat/long (geographic) column.
This adapter has the following requirements:
-
Ruby 1.8.7 or later. Ruby 1.9.2 or later preferred. JRuby 1.6.3 or later also supported.
-
PostgreSQL 9.0 or later.
-
PostGIS 1.5, PostGIS 2.0, or later.
-
ActiveRecord 3.0.3 or later. Earlier versions will not work. Should be compatible with Rails versions through 3.2.x.
-
rgeo gem 0.3.20 or later.
-
rgeo-activerecord gem 0.4.6 or later.
Please note that this version of the adapter is targeted towards Rails 3.x only. The upcoming version 0.6 will target Rails 4.0.
Install this adapter as a gem:
gem install activerecord-postgis-adapter
See the README for the “rgeo” gem, a required dependency, for further installation information.
To use this adapter, add this gem, “activerecord-postgis-adapter”, to your Gemfile, and then request the adapter name “postgis” in your database connection configuration (which, for a Rails application, is in the config/database.yml file). Most of the rest of the configuration parameters are identical to those used by the stock “postgresql” adapter, so you can create a new Rails application using:
rails new my_app --database=postgresql
…and then just change the adapter name to “postgis”.
Next, the PostGIS adapter includes a special railtie that provides support for PostGIS databases in ActiveRecord’s rake tasks. This railtie is required in order to run, e.g., rake test. To install this railtie, you should add this line to your config/application.rb:
require 'active_record/connection_adapters/postgis_adapter/railtie'
Note that this railtie must load after the ActiveRecord railtie. That is, the above require command should appear after require 'rails/all'.
Besides the adapter name “postgis”, most of the other database connection configuration parameters are the same as for the stock postgresql adapter. However, there are several important differences:
The postgis_extension parameter is specific to the PostGIS adapter, and directs the adapter to use PostgreSQL’s CREATE EXTENSION mechanism to install the PostGIS types, functions, and tables to a newly created database. Generally, the value should be set to true, although you can also set it to a comma-delimited list of extension names (which should include the base “postgis” extension) if you want to customize which extensions are installed. This is the easiest and cleanest way to create a PostGIS-enabled database, but it requires PostgreSQL 9.1 or later AND PostGIS 2.0 or later.
The script_dir parameter is specific to the PostGIS adapter, and provides an alternative mechanism for installing the PostGIS definitions into a new database if either PostgreSQL 9.1 or PostGIS 2.0 is not available. Its value should be set to the path to the directory containing the SQL scripts for PostGIS installation. This is the directory containing the files postgis.sql and spatial_ref_sys.sql. (A common setting might be /usr/local/share/contrib/postgis-1.5.)
If you do not provide postgis_extension or script_dir, you will need to add the PostGIS definitions to the database manually. Generally, therefore, one of them is required at least for the test database, which is usually automatically created by the rake tasks.
The su_username and su_password parameters are provided as optional parameters. If present, they specify an auxiliary PostgreSQL role that must have superuser privileges, and will be used for two functions:
-
Creation of the database. This is so the main database user that you specify doesn’t need superuser or createdb privileges.
-
Installation of the PostGIS definitions, if requested by the presence of script_dir. This process normally requires a PostgreSQL role with superuser privileges, so again if you don’t want your main database user to have superuser, you can perform this one-time procedure using this alternate user.
Any schemas listed in the schema_search_path parameter are automatically created by rake db:create. This is arguably what the stock PostgreSQL adapter should be doing anyway.
If the schema name “postgis” is included in the schema_search_path parameter, the PostGIS adapter omits it from a SQL structure dump. This can be useful to prevent PostGIS definitions from appearing in the dump as described below.
PostGIS adds many objects (types, functions, triggers, meta-information tables, and other elements) to a PostgreSQL database. These objects are required for PostGIS to do its magic, but they can be a hassle when you are managing a database using Rails and ActiveRecord. For example:
-
Dumping the structure as sql (rake db:structure:dump) may include all the PostGIS definitions as well, cluttering your SQL dump.
-
Rake tasks that automatically create the test database (e.g. rake test) may fail or emit a number of errors, because PostGIS definitions are either missing from or being added twice to the test database.
To deal with these issues, we recommend the following technique:
-
Set either postgis_extension or script_dir in both your development and test database configurations. This will cause the PostGIS Adapter to automatically add the PostGIS definitions and spatial references to your databases when rake db:create is run.
-
Include “postgis” in your schema_search_path for both your development and test databases. It is recommended that you include it as the last element, so that your application’s tables don’t get added to it by default. For example:
schema_search_path: public,postgis
The PostGIS Adapter responds to this special name by sandboxing the PostGIS definitions into it when rake db:create is run, and it omits this schema when running SQL structure dumps.
-
Provide a separate su_username and su_password role for your database, and make sure that separate role has the SUPERUSER privilege. This will cause the PostGIS Adapter to log in as that role when creating the database, since the PostGIS definition installation requires SUPERUSER. Alternately, you can give the normal database role SUPERUSER privilege, which may be okay for a private development database. These are used only by
rake db:createso you can remove them from your database.yml for your staging or production databases once you’ve performed the initial creation.
Finally, you generally should not set the ActiveRecord schema format to :sql. You should leave it set to :ruby. The reason is that SQL structure dumps do not currently properly emit the correct AddGeometryColumn calls to create geometry columns. As a result, the geometry_columns table will not be populated properly, among other issues. Instead, the schema.rb output by the Ruby schema format should properly replicate the schema. This is a known issue that we are investigating.
Of course, the above steps are only really necessary if you are using the ActiveRecord rake tasks that create databases, either directly such as rake db:create, or indirectly such as rake test. They should not be necessary for running migrations or normal website execution.
The PostGIS Adapter has not yet been tested in a large variety of environments, and we are still fixing bugs. Here is a partial list of current known issues.
-
Dumping as SQL (i.e. rake db:structure:dump) does not properly emit
AddGeometryColumncalls, and so does not completely create the spatial schema (e.g. it fails to add the proper row to thegeometry_columnstable.) Because of this, you should not depend on a SQL dump to be an accurate representation of the schema. (That is, you should not setconfig.active_record.schema_formatto:sql.) -
Other rake tasks may not be supported. Unfortunately, Rails makes it difficult for custom ActiveRecord adapters to support the standard rake tasks.
-
Rails 4 is not yet supported. The good news is that the base PostgreSQL adapter has been refactored in Rails 4 to make it easier to extend, so once we get it ported to Rails 4, activerecord-postgis-adapter should be cleaner and more stable.
Documentation is available at dazuma.github.com/activerecord-postgis-adapter/rdoc
Source code is hosted on Github at github.com/dazuma/activerecord-postgis-adapter
Contributions are welcome. Fork the project on Github.
Report bugs on Github issues at github.org/dazuma/activerecord-postgis-adapter/issues
Support available on the rgeo-users google group at groups.google.com/group/rgeo-users
Contact the author at dazuma at gmail dot com.
The PostGIS Adapter and its supporting libraries (including RGeo) are written by Daniel Azuma (www.daniel-azuma.com).
Development is supported by Pirq. (pirq.com).
This adapter implementation owes some debt to the spatial_adapter plugin (github.com/fragility/spatial_adapter). Although we made some different design decisions for this adapter, studying the spatial_adapter source gave us a head start on the implementation.
Copyright 2010-2012 Daniel Azuma
All rights reserved.
Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
-
Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
-
Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
-
Neither the name of the copyright holder, nor the names of any other contributors to this software, may be used to endorse or promote products derived from this software without specific prior written permission.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS “AS IS” AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.