Using spatial databases with MapFish¶

Installation¶

If you followed the installation guide, then you already have installed PostgreSQL/PostGIS and you can skip this section. If not, run the following command from a terminal to install PostgreSQL, PostGIS and the required library GEOS:

$ sudo apt-get install libgeos-3.0.0 postgresql postgis postgresql-8.3-postgis

Further information about installing PostGIS can be found in the PostGIS documentation.

Setting up a spatially-enabled database¶

Creating a spatially-enabled database is slightly different from creating an ordinary database. Run the following commands to create the database gis:

sudo su postgres
createdb -E UNICODE gis
createlang plpgsql gis
psql -d gis -f /usr/share/postgresql-8.3-postgis/lwpostgis.sql
psql -d gis -f /usr/share/postgresql-8.3-postgis/spatial_ref_sys.sql
psql -d gis -c "SELECT postgis_full_version()"
exit

The last query will inform you about the version of your PostGIS installation.

In most cases you do not want to access your database from a web application as root. The following commands create a user www-data and grant access rights to this user for the database gis:

sudo su postgres
createuser -P www-data
psql gis
grant all on database gis to "www-data";
grant select on spatial_ref_sys to "www-data";
grant all on geometry_columns to "www-data";
\q
exit

Now it is time to create tables in your database. You can either let SQLAlchemy/GeoAlchemy do that for you (see Using ‘paster setup-app’ to create your database tables) or you can manually create tables (see Creating a Spatial Table in the PostGIS documentation).

You can also use the tool shp2pgsql to create a table from a Shapefile. For example to create the table for this countries Shapefile (countries.zip), you would have to do this:

unzip countries.zip
sudo su postgres
shp2pgsql -W utf8 -s 4326 countries.shp  countries | psql -d gis
exit

And to check that everything is ok, we can query the row count. Additionally we need to grant the access to this table to user www-data in order to allow access from MapFish server in a web environment:

sudo su postgres
psql gis
\d
select count(1) from countries;
GRANT ALL ON countries TO "www-data";
\q
exit

Configuration¶

Once the database is set up, you only have to change the database connection string in the configuration file of your MapFish application. Open your configuration file, for example development.ini, and replace the line:

sqlalchemy.url = sqlite:///%(here)s/development.db

by this one:

sqlalchemy.url = postgresql://www-data:www-data@localhost/gis

Installation¶

On Debian-based systems, MySQL can be installed with:

$ sudo apt-get install mysql-server mysql-client

You will also have to install a Python driver for MySQL. Run the following command inside the virtual environment to install the library mysql-python:

(venv) $ easy_install mysql-python

Setting up a spatially-enabled database¶

For MySQL setting up a spatial database is the same as setting up an ordinary database:

mysql -u root -p
create database gis;
quit

To create a database user that can be used for accessing the database from a web application, execute the following statements:

mysql -u root -p
create user 'www-data' identified by 'www-data';
grant all on gis.* to 'www-data';
quit

Tables with geometry columns can be created like any other table using the geometry type as column data type (see Creating Spatial Columns in the MySQL manual), for example:

mysql -u root -p
use gis;
CREATE TABLE points (id INTEGER AUTO_INCREMENT,
        name VARCHAR(40),
        geom POINT NOT NULL,
        SPATIAL INDEX(geom),
        PRIMARY KEY(id));
quit

Configuration¶

Set the database connection string in the configuration file of your MapFish application (for example development.ini) by replacing the line:

sqlalchemy.url = sqlite:///%(here)s/development.db

by this one:

sqlalchemy.url = mysql://www-data:www-data@localhost/gis

Limitations¶

• MySQL does not support coordinate system transformations for geometries. All your spatial data must be in the same spatial reference system.
• Not all methods of the OpenGIS® Simple Features Specifications For SQL are supported by MySQL, for example distance() or buffer() are not part of the stable release. A list of these functions can be found here. Other functions only operate on the minimum bounding rectangle (MBR) of the geometries, a list of these functions can be found in the MySQL manual. Because of that, features queried through the MapFish Protocol are also selected using the MBR.

Installation¶

$ sudo apt-get install libgeos-c1 proj

sudo ln /usr/lib/libgeos-3.0.0.so /usr/lib/libgeos-3.1.1.so

wget http://www.gaia-gis.it/spatialite/libspatialite-linux-x86-2.3.1.tar.gz
sudo tar -xvf libspatialite-linux-x86-2.3.1.tar.gz -C /usr/local/lib/
sudo mv /usr/local/lib/libspatialite-* /usr/local/lib/libspatialite

./configure --prefix=/home/c2c/libspatialite --with-geos-lib=/usr/lib --with-proj-lib=/usr/lib
make install

sudo ln /usr/lib/libstdc++.so.6 /usr/lib/libstdc++.so

sudo apt-get install libsqlite3-dev

[build_ext]
#define=
#include_dirs=/usr/local/include
#library_dirs=/usr/local/lib
libraries=sqlite3
#define=SQLITE_OMIT_LOAD_EXTENSION

(venv) $ python setup.py install

(venv) $ python setup.py buildstatic install

Setting up a spatially-enabled database¶

Creating a database can be done using spatialite-gui or by using the CLI client spatialite. In the following we will use spatialite, but you can also use spatialite-gui to execute the commands.

First you have to download the package spatialite-tools from Spatialite Downloads and the script init_spatialite-2.3.sql from Spatialite Ressources. The scripts creates the geometry_columns and spatial_ref_sys metadata tables and also inserts a collection of spatial reference systems.

Start the Spatialite client by calling:

spatialite gis.sqlite

This will create the file gis.sqlite, if it does not exist already. Then execute the script init_spatialite-2.3.sql:

.read init_spatialite-2.3.sql ASCII

Now you can create a table with a geometry column. This is done in two steps: First we create a plain SQLite table without the geometry column, and then we add the geometry column using the function AddGeometryColumn():

CREATE TABLE points (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(40));
SELECT AddGeometryColumn('points', 'geom', 4326, 'POINT', 2);

.loadshp countries Countries utf-8

CREATE VIRTUAL TABLE virtual_countries USING VirtualShape('/home/c2c/data/countries', utf-8, 4326);
select count(*) from virtual_countries where
        MBRWithin(Geometry, GeomFromText('POLYGON((0 0, 40 0, 40 40, 0 40, 0 0))', 4326));

Configuration¶

When using spatialite-gui and spatialite the Spatialite library is automatically loaded as extension. But when connecting to a Spatialite database using a ordinary SQLite driver, you have to load the Spatialite library manually.

In MapFish, database connections are managed by SQLAlchemy. Every time SQLAlchemy opens a new connection to a Spatalite database, the Spatialite library must be loaded. This can be done by setting up a PoolListener.

Open the file model/__init__.py and modify the method init_model(engine), so that it looks like this:

# ...

from sqlalchemy.dialects.sqlite.base import SQLiteDialect
from sqlalchemy.interfaces import PoolListener

# ...

def init_model(engine):
    sm = orm.sessionmaker(autoflush=True, autocommit=False, bind=engine)

    meta.engine = engine
    meta.Session = orm.scoped_session(sm)

    if isinstance(engine.dialect, SQLiteDialect):
        class SpatialiteConnectionListener(PoolListener):
            def connect(self, dbapi_con, con_record):
                dbapi_con.enable_load_extension(True)
                dbapi_con.execute("select load_extension('/usr/local/lib/libspatialite/lib/libspatialite.so')")
                dbapi_con.enable_load_extension(False)

        engine.pool.add_listener(SpatialiteConnectionListener())

        # ...

Now you just have to set the database connection string in the configuration file of your MapFish application (for example development.ini) by replacing the line:

sqlalchemy.url = sqlite:///%(here)s/development.db

by this one:

sqlalchemy.url = sqlite:////home/c2c/data/gis.sqlite

Installation¶

The Python driver cx_Oracle requires an Oracle client or server installation. If your MapFish application is running on the same system as your Oracle database, you can skip the section Installation of Oracle Instant Client.

sudo apt-get install alien libaio1

sudo alien -i oracle-instantclient11.2-basic-11.2.0.1.0-1.i386.rpm
sudo alien -i oracle-instantclient11.2-devel-11.2.0.1.0-1.i386.rpm

/usr/lib/oracle/11.2/client/lib/

sudo ldconfig
ldconfig -p | grep oracle

export ORACLE_HOME=/usr/lib/oracle/11.2/client/
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH

(venv) $ python setup.py install

(venv) $ python -i
>>> import cx_Oracle
>>>

Configuration¶

When using cx_Oracle inside MapFish, the environment variables ORACLE_HOME and LD_LIBRARY have to be set before the cx_Oracle module is used from SQLAlchemy. You can do this in the file config/environment.py before the SQLAlchemy engine is created from the configuration:

def load_environment(global_conf, app_conf):

   # ...

   # Set the evironment variables required for cx_Oracle
   os.environ['ORACLE_HOME'] = '/usr/lib/oracle/11.2/client/'
   os.environ['LD_LIBRARY'] = '/usr/lib/oracle/11.2/client/lib'

   # Setup the SQLAlchemy database engine
   engine = engine_from_config(config, 'sqlalchemy.')
   init_model(engine)

Then set the database connection string in your Pylons configuration file development.ini:

sqlalchemy.url = oracle://www-data:www-data@localhost:1521/gis

Notes about the Oracle dimension information array¶

Oracle requires a Dimension Information Array (DIMINFO) in its geometry metadata table for every spatial column (see Oracle® Spatial User’s Guide and Reference: Geometry Metadata Views).

If you are creating your tables with SQLAlchemy/GeoAlchemy (see Using ‘paster setup-app’ to create your database tables), you will have to specify a DIMINFO in your model files. GeoAlchemy then will make an entry in the view USER_SDO_GEOM_METADATA (see also Oracle® Spatial User’s Guide and Reference: DIMINFO).

Example definition (model/places.py):

from sqlalchemy import Column, types
from sqlalchemy.schema import Sequence

from geoalchemy import GeometryColumn, Point, GeometryDDL

from mapfish.sqlalchemygeom import GeometryTableMixIn
from mapfishsample.model.meta import engine, Base

diminfo = "MDSYS.SDO_DIM_ARRAY("\
            "MDSYS.SDO_DIM_ELEMENT('LONGITUDE', -180, 180, 0.000000005),"\
            "MDSYS.SDO_DIM_ELEMENT('LATITUDE', -90, 90, 0.000000005)"\
            ")"

class Place(Base, GeometryTableMixIn):
    __tablename__ = 'places'
    id = Column(types.Integer, Sequence('place_id_seq'), primary_key=True)
    name = Column(types.String(40))

    the_geom = GeometryColumn(Point(dimension=2, srid=4326, diminfo=diminfo))

GeometryDDL(Place.__table__)

For Oracle MapFish in general uses the operator SDO_WITHIN_DISTANCE for spatial filter queries. If the filter geometry (Lat/Lon, BBox or arbitrary geometry) uses a different SRID, the geometry column has to be reprojected to this SRID. In this case the Oracle function SDO_GEOM.WITHIN_DISTANCE has to be used which either requires dimension information arrays or a tolerance value. These parameters have to be set on custom filters for the method index() inside the controller classes.

Example (controllers/places.py):

class PlacesController(BaseController):
    # ..

    def index(self, format='json'):
        """GET /: return all features."""
        filter = create_default_filter(request, Place, additional_params={'tol': '0.005'})
        return self.protocol.read(request, filter=filter)
    # ..

The tolerance value will be passed to SDO_GEOM.WITHIN_DISTANCE. Alternatively you can set a DIMINFO for the reprojected geometry column and the filter query using the keywords dim1 and dim2.

Note that this tolerance is not the one used in the MapFish Protocol to specify a distance in which features should be queried.

Notes about using a tolerance value for MapFish Web Services in Oracle¶

Requests to a MapFish web service can contain a tolerance parameter, which specifies within which distance features should be queried. Usually the unit of this value is the unit associated with the coordinate system in use (for example degree for EPSG:4326). But for geodetic coordinate systems (like EPSG:4326) Oracle uses meter as unit. You have to keep this in mind when developing applications for Oracle.

If you want to use a different unit, you can set it as parameter in your controller files. This parameter is passed to the database function call without further checks.

Example (controllers/places.py):

# ..

def index(self, format='json'):
    """GET /: return all features."""
    filter = create_default_filter(request, Place, additional_params={'params': 'unit=KM'})
    return self.protocol.read(request, filter=filter)

# ..

Valid units are listed in the view SDO_DIST_UNITS.

Declarative and non-declarative mapping¶

With SQLAlchemy table mappings can be configured either by defining a table and a class separetly (non-declarative) or by doing this at once (declarative). MapFish supports both ways. The following two examples show the mapping for a table spots, one time as non-declarative and one time as declarative mapping.

Non-declarative mapping:

from sqlalchemy import Column, Table, Integer, Numeric
from sqlalchemy.orm import mapper

from geoalchemy import (Geometry, GeometryColumn,
GeometryDDL, GeometryExtensionColumn)

from mapfish.sqlalchemygeom import GeometryTableMixIn

from mapfishsample.model.meta import metadata

# table definition
spots_table = Table('spots', metadata,
        Column('spot_id', Integer, primary_key=True),
        Column('spot_height', Numeric(asdecimal=False)),
        GeometryExtensionColumn('spot_location', Geometry(2)))

# class definition
class Spot(GeometryTableMixIn):
    __table__ = spots_table

    def __init__(self, spot_id=None, spot_height=None, spot_location=None):
        self.spot_id = spot_id
        self.spot_height = spot_height
        self.spot_location = spot_location

# set up the mapping between table and class
mapper(Spot, spots_table, properties={
            'spot_location': GeometryColumn(spots_table.c.spot_location,
                                            comparator=PGComparator)})

# register table for DDL extension, so that it can be created from SQLAlchemy
GeometryDDL(spots_table)

Declarative mapping:

from sqlalchemy import Column, types

from geoalchemy import GeometryColumn, Geometry

from mapfish.sqlalchemygeom import GeometryTableMixIn
from mapfishsample.model.meta import engine, Base

class Spot(Base, GeometryTableMixIn):
    __tablename__ = 'spots'

    spot_id = Column(Integer, primary_key=True)
    spot_height = Column(Numeric(asdecimal=False))
    spot_location = GeometryColumn(Point(2), comparator=PGComparator)

GeometryDDL(Spot.__table__)

Geometry column properties¶

When using paster mf-layer or paster mf-model, MapFish creates a default configuration for the geometry column of your table. You may want to customize this configuration to your needs.

Example configuration for model/spots.py:

# [..]
spot_location = GeometryColumn(
        Point(dimension=2, srid=4326, spatial_index=True),
        comparator=PGComparator,
        nullable=False)
# [..]

dimension=2

The dimension of the geometry (default: 2).

srid=4326

The spatial reference system (SRS) of the geometry column as EPSG code (default: 4326).

spatial_index=True

Indicates if a spatial index is created for the geometry column (default: True).

comparator=PGComparator

You only have to set this option, when you want to use a database specific function (like AsKML in PostGIS) on a geometry column in a SQLAlchemy query (for example session.query(Spot).filter(Spot.geom.kml == '..'). Following comparators are available:

• PostGIS: geoalchemy.postgis.PGComparator
• MySQL: geoalchemy.mysql.MySQLComparator
• Spatialite: geoalchemy.spatialite.SQLiteComparator
• Oracle: geoalchemy.oracle.OracleComparator

nullable=False

Indicates if null values can be inserted into the geometry column (default: True).