Creating a Ruby on Rails with GeoRuby and spatial_adapter

I spent some time investigating on how to query spatial objects in a postgis environment using Rails.

I accomplish this using GeoRuby and spatial adapter gems, mashed a simple startup project using Google Maps API v2 and I got my hello world kind of project. Created some interaction between objects on the Web map while updating the database.

Links

Repository and instructions here: https://www.assembla.com/wiki/show/rorgeotesting

GeoRuby extentions: http://georuby.rubyforge.org/

Some PostGIS notes

These are some notes regarding the creation and some manipulation of a georeferenced table.

First let’s create a table in our database. Note that in this step we do not add the geometry column yet:

CREATE TABLE item (
   id INTEGER,
   type VARCHAR
);

Creating the geometry column (a point in my case). After this a new column will be added to the item table named item_location:

SELECT AddGeometryColumn('item','item_location',27492,'POINT',2);

Inserting a point in the database table (in the previous step a restriction was created forcing the verification of the coordinates, so be sure that those are correct):

INSERT INTO item
  VALUES(1,'item_type',
  ST_GeomFromText('POINT(-43833.469 166045.598)',27492));

While inserting a new point with a diferent SRID one must use the transform PostGIS function:

INSERT INTO item
  VALUES(...,
  transform(
    ST_GeomFromText('POINT (lat long)', <point_srid>), <destination_srid>));

Convert Shapefiles to SQL

The PostgreSQL /bin folder contains an executable capable to perform the conversion:

shp2pgsql shape_file_name myschema.dbtable_name > output_file.sql

psql -d database_name -f output_file.sql

One command conversion and upload to the PostgreSQL can be done all in one step using UNIX pipes:

shp2pgsql shape_file_name myschema.dbtable_name | psql -d database_name

Import OpenStreetMap data into GeoServer/PostGIS

It is possible to download map data from the OpenStreetMap (OSM) dataset in a number of ways. The full dataset is available from the OSM website download area. This dataset (Planet.osm) is a large file, currently about 4GB when compressed and about 100GB when uncompressed. It is also possible to select smaller areas to download. Data normally comes in the form of XML formatted .osm files (an OpenStreetMap markup language).

I have downloaded today a part of the city of Porto using the OSM API,  converted it into shape files, added a new store to geoserver (using those shape files) and finaly visualized the new layers in GoogleEarth and uDig.

• Obtaining the contents from OpenStreetMap using the OSM API

First you will have to define your region specified by a bounding box which consists of a minimum and maximum latitude and longitude. An easy way to accomplish that task is to use Google Earth. Hovering the mouse in the desired corner of the rectangle to export, look at the coordinates and take notes (you only need two coordinates – opposite corners of the rectangle).

Then use those values to create the HTTP request using the folowing URL:

http://api.openstreetmap.org/api/0.6/map?bbox=left,bottom,right,top

for example:

http://api.openstreetmap.org/api/0.6/map?bbox=11.54,48.14,11.543,48.145

Where left is the maximum longitude, right is the value for the minimum longitude, bottom the minimum latitude and top the maximum latitude.

However there is a constraint, the API is limited to bounding boxes of about 0.5 degree by 0.5 degree. For larger areas you could try Osmxapi like so (this will also return a .osm file):

http://www.informationfreeway.org/api/0.6/map?bbox=11.54,48.14,11.543,48.145

The .osm file is not currently not supported by geoserver, so we got 2 ways to get around this. Convert it to shape files, or insert directly in PostGIS database. However I discovered a Web page that offers subsets of the original OSM dataset. They do this by dividing the datasets by country offering the information both in .osm or .shp files, very handy.

• Convert .osm files to shape

Not easy to find, cause I did it in Windows OS, but there is an application out there called osm2shp, which comes with a GUI that does this conversion in a very easy way. If having trouble finding it, just ask me. If you are working in linux, you’re be glad to know that Ubuntu (and Debian-based OSes) repositories have this package.

• Creating a datastore in geoserver uing the new shape files

The output of the previous convertion can be copied to the data folder in the geoserver path. Just create a folder (for instance osmdata) containing the shape files in:

path\to\geoserver\data_dir\data\osmdata

then create a new datastore using the GeoServer Web interface. (check this tutorial)

Just pay attention so that the projection of the source data be configured as 900913, the projection used in Google Maps, and the default for OpenStreetMap.

• Direct input into PostGIS database

There is a converter that allows you to load the OpenStreetMap data into PostGIS called osm2pgsql.  This program is a package available on Debian-based distributions (such as Ubuntu), and is also available as a binary on Windows.

Running the command:

osm2pgsql -E 900913 -d myDataBase TheOsmFile.osm

This will process the XML information and load the data into a PostGIS database called “myDataBase”. The -E defines the projection of the source data, which in this case is 900913, the projection used in Google Maps, and the default for OpenStreetMap.

If successful, the database will contain the following tables:

planet_osm_line
planet_osm_point
planet_osm_polygon
planet_osm_roads

There are two different tables that contain line data, planet_osm_line and planet_osm_roads. The former includes railroads, subways, and other linear information.  The latter is made up exclusively of roads.  The planet_osm_point table has a range of data: subway stations, shopping centers, universities, and even brothels. Lastly the planet_osm_polygon table has, but is not limited to, parks, bodies of water, and even buildings in certain urban areas.

After these steps map info can be easily viewed in GIS apps like uDig/ArcView. The GeoServer Web interface also has directs links for KML for previewing the layer in GoogleEarth if installed.

Also today, I had a small rendez-vous with openLayers and I was able to visualize the new OSM data in a Web page. It is quite easy to create and customize, but have a peek in the sources that I used as a guide. I will have to explore this some day later.

Information sources:

http://blog.geoserver.org/2009/01/30/geoserver-and-openstreetmap/

http://wiki.openstreetmap.org/wiki/Getting_Data

Convert between projection stantards

How to transform the geometry of a postgis table from one projection to another:

UPDATE table_name SET the_geom = TRANSFORM(the_geom, 4326);

Where the_geom is the name of the field holding the geometry, and 4326 is the EPSG code you wish to transform to.

Converting DXF files to Shape files (.SHP)

This is a tool for converting AutoCad DXF (Data eXchange Format) files into ESRI Shapefiles (SHP). May be useful while inserting DXF shapes to a PostGIS database which only supports SHP.

Find it here.

After  the conversion is made, one will have to apply a command in order to insert it into the database.

I will post this tutorial soon.

Create a spatial database with PostGIS

Ensure your PostgreSQL database is correctly installed and running. Do do it just check the connection using PgAdmin III client. If an error arrises, probabily your server isn’t running. nu Start it using the “Start server” entry in the start menu of your operating system or else on Ubuntu this will work (if the server was installed as in this previous post)

sudo /opt/PostgreSQL/8.4/installer/server/startserver.sh

1) Open the Databases tree item and have a look at the available databases. The postgres database is the user database for the default postgres user and is not too interesting to us. The template_postgis database is what we are going to use to create spatial databases. Right-click on the Databases item and select New Database.

Note: If you receive an error indicating that the source database (template_postgis) is being accessed by other users, this is likely because you still have it selected. Right-click on the PostgreSQL 8.4 item and select Disconnect. You can then double-click the same item to reconnect and try again.

2) Fill in the New Database form with the info below and click OK.

Name: your_database_name

Owner: postgres

Encoding: UTF8

Template: template_postgis

.

3) Select the new database and click on the SQL query button (or go to Tools > Query Tool).

4) Enter the following query into the query text field:

SELECT postgis_full_version();

5) Click the play button in the toolbar (F5 will work also) to execute the query. The query will return a string that confirms that PostGIS is properly enabled in the database.

Information source/

Installing PostgeSQL and PostGIS

This is a simple method I found to install postgres and postgis in ubuntu/windows. Browse to this url http://www.postgresql.org/download/ and in “Binary Packages” you may choose the operating system where you will be using the server. Choosing windows or linux you will download an executable, but the problem in linux you will have to chmod it in order to use it as one. This will do the trick using the terminal:

  chmod 700 filename.bin

and that’s it. The binary file can now be run:

  sudo ./filename.bin

This will show a GUI installer interface where you will be able to define the root password. The final process will be the selection of addons like database connectors, web components to work with, wikis and the spatial extension we are looking for – postgis. First select the server already running in your OS platform:

In the next screen be sure to check the PostGIS extension.

The installer will then download and install the selected extensions. Open PgAdmin III and be sure that a database called template_postgis exists.

That was a peace of cake… the next post will be about creating a georeferenced enabled database.