Introduction
If you want to manage a custom schema with a web interface with multiple users, multiple languages for UI and content, you could roll your own using something like CakePHP to provide a framework. Doing it that way, you’ll be re-inventing many wheels for user management, authentication, etc. I evaluated half a dozen ‘higher-level’ CMS and settled on Drupal since it has all of this infrastructure plus many mature add-ons (‘modules’) that mean that you can focus on implementing the specifics of your web application rather than writing lots of plumbing. Furthermore, it’s PHP so it’s easy to deploy on inexpensive hosted services on the internet (not something that you can say for Java-based CMS, for example).
Drupal Version 7
Drupal has long had the ability to manage custom content schemas through the Content Construction Kit (CCK) module. The Drupal team thought that this was so good that they have now rolled that functionality into the latest stable release of Drupal, Version 7 released in January 2011. In Drupal 7, the standard mechanism for managing custom schema is managed through the Taxonomy, Content Type and Field features (the latter being new for Drupal 7). However that mechanism is fairly inflexible and clunky if you come from a RDBMS way of thinking and imagine a rich schema of entities with complex relations between them. In true Drupal fashion, ‘there’s a module for that’: Relations. Also, in true Drupal fashion, it’s in beta right now, but appears to work OK.
Drupal has a ton of modules for supporting geospatial and mapping content management, but the move to the Fields paradigm in Drupal 7 leaves many of those modules in the past. In this article we are focused on the present and future, so it’s Fields and Fields-compatibility all the way! Also, we’re going to be using PostgreSQL for the database back end which is a feature that is now officially supported as of the Drupal 7 release (as well as MySQL which has been the ‘gold standard’ RDBMS for Drupal since the beginning). There’s a reason I’m using PostgreSQL – PostGIS – the excellent spatial extension for PostgreSQL that makes the PostgreSQL + PostGIS combination (probably, as they say in the beer adverts) the best spatial RDBMS on the planet. Period. And that includes the commercial offerings of Oracle, IBM, etc.
For documentation, of course, there’s the Drupal web site itself, but I always feel more comfortable with a good book. I got the “Definitive Guide to Drupal 7” published by APress in e-book format and have been very happy with it.
Getting Started
Installation
Download and install Drupal 7 and drush. I shan’t go into detail on how to do that, it’s clear enough on the Drupal web site. Make sure that you have the PostgreSQL PHP driver installed to use PostGIS (and, of course, PostgreSQL) installed since your complete CMS database will need to be PostgreSQL based from the word go. Furthermore, your PostgreSQL database for use by your Drupal installation will need to have the PostGIS extensions installed. Typically, this is done by selecting the PostGIS template when creating the PostgreSQL database. For further details on PostgreSQL / PostGIS installation on Ubuntu, you can have a look at one of my other posts. For the purposes of this explanation I assume that you choose the default installation which enables a bunch of useful modules like Field UI, File, Image, List, Number and Taxonomy that come as part of the standard Drupal 7 distribution.
Modules
Following the PostGIS approach for persisting your spatial fields for this article, you will also need to install the Ctools, PostGIS and OpenLayers Drupal 7 modules. You can do that with drush which is wa-a-ay quicker than pointing and clicking around in the web admin interface:
drush dl openlayers postgis openlayers views entity entityreference
drush en -y openlayers_ui openlayers_views views_ui ctools postgis entityreference
Defining Your Schema
If you are comfortable with a relational database way of thinking you will have the concepts of tables, rows, relations (foreign keys) and column types in your head when sketching out a schema. Moving from this to Drupal requires a new vocabulary and slight change in mindset.
Let’s say that you want to manage some data that would be described in your RDMS like this:
create table countries(
country_name character varying(256),
population_in_thousands integer,
outline geometry);
create table cities(
city_name character varying(256),
population_in_thousands integer,
city_centre geometry);
There is an implicit relationship here of one-to-many of country-to-city. The geometry column type is a feature of a spatially-enabled RDBMS. In this article we shall deal with the PostgreSQL + PostGIS combination which is one of the most widely-used spatial RDBMS setups.
Content Type Definition
To model this in Drupal 7, we can use Administration->Content types ‘Add content type’ and add Country and City as new content types. Then, when you use the Drupal admin interface to list the content types, you can now select ‘manage fields’ for each of these content types and go and create the fields for the population and geometry. Worthy of special mention are the geometry fields – with the PostGIS and two OpenLayers modules now in place in Drupal 7 you can select ‘Geospatial data’ in the Field type and ‘OpenLayers Map’ as the editing widget when you define new fields (in our example the Country outline polygon and the City centre point).
Relationship Definition with Entity References
Now that you have Country and City content types defined in Drupal 7 that match your data model you sketched out from your RDBMS where there is a discrete content type for each table, you need to define the relationships between the ‘tables’ in Drupal. This is done with Entity References. You can add another field to the City content type and choose Entity Reference for the field type and a name like city_country_ref for the field name. The reason for entering such a specific name for the field is that when you have a more complex system you will probably want to be able to identify relationships between specific types rather than all types referring to one type. For example, imagine that you have another content type, Government. You want to relate each Government instance to a Country instance (this is the usual state of affairs although we can think of many governments throughout history that have had more than one country, but let’s not get greedy here). So, you would also define an Entity Reference field for the Government. Great. Now, suppose that you want to build a list of all the City instances for a given Country. You would do that in a View selecting all the Entity objects that have a city_country_ref that points to the Country in question. What you don’t want is all the Government instances coming in, too.
Viewing Your Data
To make lists / reports from your data, you will create Views from Structure->Views in your admin interface for your Drupal site. This is fairly easy to work out for making lists of standard, single content types. It gets fairly convoluted when you want to make views from ‘compound’ data types, though. By ‘compound’, what I mean is, imagine that you want a page that lists all the towns for a country, showing the country name and population as well as the town name and population in each row. I found a video explaining how to do this here – to write down how to do that would take several pages, and you still probably won’t get it!
Regarding your spatial data, you will get default presentation of your data from Drupal. You may have had a look at sample data that you entered and seen that Drupal just shows the data as Well Known Text. WKT is hardly a user-friendly presentation – what you really want is your geometry overlaid on a map. If you browse each of your Content types you can select the Manage display tab and select OpenLayers Map as the Format for your Geospatial type fields. This looks OK, but say you want a different map style or size of map? You can go into Structure->OpenLayers and select the Maps tab. From there you can define new Map (styles) by Export or Clone of existing types that you can then use to edit or display your content. For example, you could create a new Map, say ‘City Editor’, based on Export from PostGIS Widget Map. You can then add extra layers and a layer control (using the other tabs in Structure->OpenLayers) to add Google Maps, Google Satellite, etc. as switchable options as the basis for placing your points for city centre fields. (At this juncture, I should point out that you must always pay attention to the map provider’s licence agreement as to whether you are allowed to create new spatial content using their map as a background. Ahem.)
The Views module is useful for customising how the content gets presented when in lists – and this is particularly useful for PostGIS content (fields of ‘Geospatial data’ type).