Loading spatial data in a raw data vault

In this post I'm showing an example of spatial data in combination with the Data Vault methodology based on the philosophy implemented at our company. There are several philosophies how to model your Enterprise Data Warehouse using Data Vault (DV), and each philosophy has its merits and its drawbacks. This blog isn't meant to discuss these philosophies: there are far more suitable blogs for that. Instead, we're going to focus on one specific item regarding Data Vault, namely: how to incorporate spatial data in the Data Vault method. Data Vault consists of three main components, being hubs, links and satellites. Hubs contain the business entities, links contain the relationships between business entities and the satellites contain the context of hubs and links.

So, how does Data Vault handle spatial data? The geometry attributes of an object provide context about the location of the object in the real world. Most RDBMS support spatial information as a specific object type. So at this point it is safe to assume that geometry will find its place in the data vault. But, as we've seen in a previous post, geometry also provides spatial reference to other objects. In other words, it forms relationships between objects. Will this affect the way DV handles spatial data

The example

In our architecture we are using a raw data vault that serves as a technical copy of the source systems. Prior to loading the raw data vault, source systems are copied to a staging area. After loading, data is processed into several data marts that are accessed by end users through reporting, analysis (OLAP) and, in the near future, dashboard solutions.

Our Enterprise BI architecture

As I said, it is one of several philosophies. This is a fairly technical approach, with the benefit that a lot of work building the EDW can be automated. The routines needed for loading the staging areas and the raw data vault are generated with the use of the DV modeling tool Quipu (which was open source at the time we started building our EDW). The manual labor required to transform the data for end user purposes (the big T) is offloaded to the data mart phase.

#1: Import the source system

For this demo, I've created a new example source system with 4 spatial tables selected from our company geodatabase, containing incidents, maintenance areas, and the border of our service area both as line an polygon (which are in two separate tables). All 4 tables contain a GEOMETRY type column, named either LIGGING or GEOM.

Spatial source system model example

The source system model is imported into Quipu. The first thing we notice is that Quipu doesn't recognize geometry type columns. We need edit the column type and native type manually.

Source model in Quipu, with geometry type manually altered

 

 #2: Loading the staging area

The next step, we're creating the staging area which is a copy of the source with some added metadata fields. The appropriate staging area DDL and ETL is generated by Quipu. Let's take a look at the DDL-code for the maintenance areas table below.

CREATE TABLE edw.sta_spatial.maintenance_areas
( GAG_ID bigint
, LIGGING_WKT varchar(MAX)
, AREA_NAME varchar(50)
, SUPERVISOR_NAME varchar(50)
, SRID int
, ligging geometry
, stg_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
, stg_sequence INT DEFAULT 1
, voided BIT DEFAULT 0
, audit_id INT
);

As we notice,the DDL is generated using the correct geometry type field after we manually altered it in the previous step. With the DDL, we can create the proper staging area tables without any further edits.

In this process, Quipu also generated the ETL needed to fill the staging area. In our case, this ETL consists of Pentaho Data Integration (PDI) code. As explained in an earlier post, PDI can support geometry type fields. Unfortunately, PDI only offers support for geometry type in combination with Oracle and PostgreSQL. Since our  EDW is built on the MS SQL Server platform, we needed to create a workaround for this. We solved this by inserting the geometry into the staging area in well-known text format (stored in the field LIGGING_WKT) and converting it to spatial with an 'INSTEAD OF'-trigger we are able to fill the staging properly.

CREATE TRIGGER [dbo].[maintenance_areas_trg] ON [dbo].[maintenance_areas]
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO maintenance_areas (
       [GAG_ID]
      ,[LIGGING_WKT]
      ,[AREA_NAME]
      ,[SUPERVISOR_NAME]
      ,[SRID]
      ,[ligging]
    ) SELECT
       [GAG_ID]
      ,[LIGGING_WKT]
      ,[AREA_NAME]
      ,[SUPERVISOR_NAME]
      ,[SRID]
      ,geometry::STGeomFromText ( LIGGING_WKT , 28992 )
     FROM
        INSERTED
END

The insertion of the field 'LIGGING' is bypassed by the trigger. In the code above,we can see the field is filled by converting text to actual geometry with use of the MS SQL command STGeomFromText.

This is not the only possible solution. There are several other solutions for this problem, ranging from using another ETL-tool to fill the staging area to changing EDW database platform. For now, we can suffice with the notion that filling the staging area with spatial data is possible.

#3: Designing the raw data vault

The next step in this example consists of modeling the raw data vault. Our modeling software Quipu will automatically generate a data vault style model based on the technical properties of the staging area. It also allows us to manually identify the business key in the staging tables that lack a primary key in the source model.

Generating the spatial raw data vault example

After analyzing our source model (shown above in paragraph #1), Quipu generates a data vault model consisting of 4 hubs and corresponding satellites. The geometry type fields (highlighted in green) have become part of the satellites, which is logical as they are identified as context properties.

Spatial raw data vault model based on example source system

Notice the absence of any links in this model? As I explained in previous posts (here and here), geographical databases rely heavily on the implicit spatial relationships to tie data together. However, when generating a raw data vault using a logical rule engine, it will ignore these spatial relationships. As far as a raw data vault is concerned, this is correct behavior. As a consequence, geographical data sources with 100+ tables will inevitably be generated into raw data vaults with 100+ hubs and satellites and virtually no links.

After generating the model, we can continue with generating the DDL and ETL necessary to create the model in our EDW environment. The DDL below shows the generation of the maintenance areas hub and satellite, including the geometry field in the satellite. The generated DDL is flawless because we altered the type and native type column for the geometry field named 'LIGGING' manually in step #1.

CREATE TABLE edw.sdv_spatial.maintenance_areas_h
( maintenance_areas_hid INT NOT NULL IDENTITY(1,1)
, GAG_ID bigint
, audit_timestamp DATETIME
, audit_recordsource VARCHAR(128)
, CONSTRAINT pk_maintenance_areas_h
  PRIMARY KEY( maintenance_areas_hid
        )
);

CREATE UNIQUE INDEX bk_maintenance_areas_h ON edw.sdv_spatial.maintenance_areas_h
( GAG_ID
);

CREATE TABLE edw.sdv_spatial.maintenance_areas_h_s
( maintenance_areas_hid INT NOT NULL
, load_date DATETIME NOT NULL
, load_date_end DATETIME DEFAULT '9999-12-31'
, LIGGING_WKT varchar(MAX)
, AREA_NAME varchar(50)
, SUPERVISOR_NAME varchar(50)
, SRID int
, ligging geometry
, voided BIT
, audit_timestamp DATETIME
, audit_recordsource VARCHAR(128)
, CONSTRAINT pk_maintenance_areas_h_s
  PRIMARY KEY( maintenance_areas_hid
        , load_date
        )
);

CREATE INDEX pd_maintenance_areas_h_s ON edw.sdv_spatial.maintenance_areas_h_s
( load_date
, load_date_end
);

We also generate the ETL steps that are needed to make the data vault run. These steps compare new data loaded into the staging area with the data already present in the raw data vault, identifying alterations and storing them into the data vault.

-- insert new business keys data from 'maintenance_areas' into 'maintenance_areas_h'
INSERT INTO edw.sdv_spatial.maintenance_areas_h
( GAG_ID
, audit_timestamp
, audit_recordsource
)
SELECT DISTINCT GAG_ID
     , CURRENT_TIMESTAMP
     , 'edw.sta_spatial.maintenance_areas'
  FROM edw.sta_spatial.maintenance_areas stg
 WHERE NOT EXISTS (SELECT 1
                     FROM edw.sdv_spatial.maintenance_areas_h
                    WHERE (stg.GAG_ID = GAG_ID OR (stg.GAG_ID IS NULL AND GAG_ID IS NULL))
                  )
;

-- insert new business keys data from 'maintenance_areas' into 'maintenance_areas_h_s'
INSERT INTO edw.sdv_spatial.maintenance_areas_h_s
( maintenance_areas_hid
, load_date
, LIGGING_WKT
, AREA_NAME
, SUPERVISOR_NAME
, SRID
, ligging
, voided
, audit_timestamp
, audit_recordsource
)
SELECT maintenance_areas_hid
     , stg_timestamp
     , LIGGING_WKT
     , AREA_NAME
     , SUPERVISOR_NAME
     , SRID
     , ligging
     , voided
     , CURRENT_TIMESTAMP
     , 'edw.sta_spatial.maintenance_areas'
  FROM ( SELECT (SELECT MAX(maintenance_areas_hid)
                   FROM edw.sdv_spatial.maintenance_areas_h
                  WHERE (src.GAG_ID = GAG_ID OR (src.GAG_ID IS NULL AND GAG_ID IS NULL))
                ) as maintenance_areas_hid
              , stg_timestamp
              , LIGGING_WKT
              , AREA_NAME
              , SUPERVISOR_NAME
              , SRID
              , ligging
              , voided
           FROM edw.sta_spatial.maintenance_areas src
       ) stg
 WHERE NOT EXISTS (SELECT 1
                     FROM edw.sdv_spatial.maintenance_areas_h_s dv
                    WHERE (stg.maintenance_areas_hid = maintenance_areas_hid OR (stg.maintenance_areas_hid IS NULL AND maintenance_areas_hid IS NULL))
                      AND (stg.LIGGING_WKT = LIGGING_WKT OR (stg.LIGGING_WKT IS NULL AND LIGGING_WKT IS NULL))
                      AND (stg.AREA_NAME = AREA_NAME OR (stg.AREA_NAME IS NULL AND AREA_NAME IS NULL))
                      AND (stg.SUPERVISOR_NAME = SUPERVISOR_NAME OR (stg.SUPERVISOR_NAME IS NULL AND SUPERVISOR_NAME IS NULL))
                      AND (stg.SRID = SRID OR (stg.SRID IS NULL AND SRID IS NULL))
                      AND ((stg.ligging.STEquals(ligging) = 1) OR (stg.ligging IS NULL AND ligging IS NULL))
                      AND (stg.voided = voided OR (stg.voided IS NULL AND voided IS NULL))
                      AND load_date = (SELECT MAX(load_date)
                                         FROM edw.sdv_spatial.maintenance_areas_h_s
                                        WHERE maintenance_areas_hid = dv.maintenance_areas_hid
                                      )
                  )
;

-- insert deleted business keys data from 'maintenance_areas' into 'maintenance_areas_h_s
-- entries in the edw.dbo.edw_load with is_full_load = 1 are treated as full loads!
INSERT INTO edw.sdv_spatial.maintenance_areas_h_s
( maintenance_areas_hid
, load_date
, LIGGING_WKT
, AREA_NAME
, SUPERVISOR_NAME
, SRID
, ligging
, voided
, audit_timestamp
, audit_recordsource
)
SELECT maintenance_areas_hid
     , COALESCE(ld.load_timestamp,CURRENT_TIMESTAMP)
     , LIGGING_WKT
     , AREA_NAME
     , SUPERVISOR_NAME
     , SRID
     , ligging
     , 1
     , CURRENT_TIMESTAMP
     , 'edw.sta_spatial.maintenance_areas'
  FROM edw.sdv_spatial.maintenance_areas_h_s dv
 INNER
  JOIN edw.dbo.edw_load ld
    ON ld.table_name = 'edw.sta_spatial.maintenance_areas'
   AND ld.is_full_load = 1
 WHERE load_date = (SELECT MAX(load_date)
                      FROM edw.sdv_spatial.maintenance_areas_h_s
                     WHERE maintenance_areas_hid = dv.maintenance_areas_hid
                   )
   --AND dv.voided = 0
   AND dv.voided = 0
   AND NOT EXISTS (SELECT 1
                     FROM edw.sta_spatial.maintenance_areas src
                     LEFT OUTER HASH
                     JOIN edw.sdv_spatial.maintenance_areas_h t1821432
                       ON (t1821432.GAG_ID = src.GAG_ID OR (t1821432.GAG_ID IS NULL AND src.GAG_ID IS NULL))
                    WHERE t1821432.maintenance_areas_hid = dv.maintenance_areas_hid
                  )
;

-- Satellite end dating in 'maintenance_areas_h_s'
UPDATE edw.sdv_spatial.maintenance_areas_h_s
   SET load_date_end = (SELECT MIN(s.load_date)
                          FROM edw.sdv_spatial.maintenance_areas_h_s s
                         WHERE s.maintenance_areas_hid = edw.sdv_spatial.maintenance_areas_h_s.maintenance_areas_hid
                           AND s.load_date > edw.sdv_spatial.maintenance_areas_h_s.load_date
                       )
 WHERE load_date_end = '9999-12-31' -- in ISO 8601 format
   AND (SELECT MIN(s.load_date_end)
          FROM edw.sdv_spatial.maintenance_areas_h_s s
         WHERE s.maintenance_areas_hid = edw.sdv_spatial.maintenance_areas_h_s.maintenance_areas_hid
           AND s.load_date > edw.sdv_spatial.maintenance_areas_h_s.load_date
       ) IS NOT NULL
;

This code needs one small but crucial alteration for each satellite that is generated. The code that updates the new business keys data into the satellite compares the existing data with the new data that is loaded into the staging area, based on an '=' comparison. This operator won't work for geometry type fields. Instead, the database platform offers specific operators for handling spatial data. Let's take a closer look at the satellite insert statement.

-- insert new business keys data from 'maintenance_areas' into 'maintenance_areas_h_s'
INSERT INTO edw.sdv_spatial.maintenance_areas_h_s
( maintenance_areas_hid
, load_date
, LIGGING_WKT
, AREA_NAME
, SUPERVISOR_NAME
, SRID
, ligging
, voided
, audit_timestamp
, audit_recordsource
)
SELECT maintenance_areas_hid
     , stg_timestamp
     , LIGGING_WKT
     , AREA_NAME
     , SUPERVISOR_NAME
     , SRID
     , ligging
     , voided
     , CURRENT_TIMESTAMP
     , 'edw.sta_spatial.maintenance_areas'
  FROM ( SELECT (SELECT MAX(maintenance_areas_hid)
                   FROM edw.sdv_spatial.maintenance_areas_h
                  WHERE (src.GAG_ID = GAG_ID OR (src.GAG_ID IS NULL AND GAG_ID IS NULL))
                ) as maintenance_areas_hid
              , stg_timestamp
              , LIGGING_WKT
              , AREA_NAME
              , SUPERVISOR_NAME
              , SRID
              , ligging
              , voided
           FROM edw.sta_spatial.maintenance_areas src
       ) stg
 WHERE NOT EXISTS (SELECT 1
                     FROM edw.sdv_spatial.maintenance_areas_h_s dv
                    WHERE (stg.maintenance_areas_hid = maintenance_areas_hid OR (stg.maintenance_areas_hid IS NULL AND maintenance_areas_hid IS NULL))
                      AND (stg.LIGGING_WKT = LIGGING_WKT OR (stg.LIGGING_WKT IS NULL AND LIGGING_WKT IS NULL))
                      AND (stg.AREA_NAME = AREA_NAME OR (stg.AREA_NAME IS NULL AND AREA_NAME IS NULL))
                      AND (stg.SUPERVISOR_NAME = SUPERVISOR_NAME OR (stg.SUPERVISOR_NAME IS NULL AND SUPERVISOR_NAME IS NULL))
                      AND (stg.SRID = SRID OR (stg.SRID IS NULL AND SRID IS NULL))
                      AND ((stg.ligging.STEquals(ligging) = 1) OR (stg.ligging IS NULL AND ligging IS NULL))
                      AND (stg.voided = voided OR (stg.voided IS NULL AND voided IS NULL))
                      AND load_date = (SELECT MAX(load_date)
                                         FROM edw.sdv_spatial.maintenance_areas_h_s
                                        WHERE maintenance_areas_hid = dv.maintenance_areas_hid
                                      )
                  )
;

Highlighted in green we see the part where the newly inserted geometries are being compared to the geometry data already present in our data vault. In the Quipu generated statement, they were simply compared using the '=' operator, which won't work. So we alter the statement slightly using the MS SQL specific statement STEquals (other RDBMS support the same statement, but with different syntax). This statement compares the actual spatial data stored in the two geometry fields. This small edit needs to be done for each satellite ETL that is generated. In our example this means altering four SQL statements. After this edit, our generated ETL will work as expected, identifying all altered objects in our staging area.

Conclusion

When integrating spatial data in a raw data vault style EDW, there are two important questions to consider:

• Does my ETL tooling support spatial object types?
• What alterations do I have to make to my code?

This example shows us that, with some minor alterations to our code, we can easily adapt a raw data vault to support storing and tracking spatial attribute information. In the next post, we will take a look at spatial in the business data vault.

Using spatial relationships in ETL

The case

In this post we're going to take a look at spatial ETL. A lot of BI work traditionally exists of getting our data from A to B and transforming it in the process. So what if our source data contains spatial information?

For example, we have two tables in our source database. One is containing 9 maintenance areas that are assigned to a particular supervisor. The other is containing over 3200 incidents that have been reported in the area. Both tables are stored in an Oracle 11g database, contain a spatial column and can be visualised with GIS software to show where they're situated.

All incidents

9 maintenance areas, colored by unique area

Now imagine we want to determine the designated supervisor for each incident to report progress of incident management on a dashboard. No primary-foreign key construction exists between the two tables. There is no other way to link these two objects together than by use of the spatial relationship.

Common ETL tools usually don't know how to treat spatial data columns. But with the right combination of functionality, you can certainly do spatial ETL. In this example, I will be using Pentaho Data Integration 6.0 (PDI) and the additional AtolCD Pentaho GIS plugin. The plugin adds two specific improvements to regular Pentaho:

1. The ability to treat spatial columns as column type 'geometry' within the ETL.
2. A set of transformation steps that enhance Pentaho with geospatial operations.

The solution path

I've created a simple Pentaho-transformation to combine the incidents and maintenance area. As you will notice the transformation looks like any other, with the exception of a couple of geospatial steps that will be explained further on. All steps in the transformation shown below are referred to in the text by use of double quotes and starting with a capital (e.g. "Table incidents").

PDI transformation with added geospatial steps.

We see that both tables are loaded with two standard table input steps, "Table incidents" and "Table maintenance areas", resulting in two data streams. When we examine the output of these steps, we see the following data.

Incidents table preview

The incident table contains about 3200 incidents that each have an unique ID and an incident location. The maintenance area table contains 9 maintenance areas, that all have a name, a supervisor name and an area location.

Maintenance areas table preview

Notice how the columns INCIDENT_LOCATION and MAINTENANCE_AREA_LOCATION contain some kind of coordinate definition? We can clearly see one contains points and the other contains polygons. The added geospatial functionality enables PDI to show the contents of the geospatial columns in readable text. We can see the field types when examining the output fields for the two table input steps; the location fields are marked  as type 'Geometry'.

Output fields for the incidents stream

Output fields for the maintenance area stream

The text "SRID=28992" tells us that the spatial data is in a Dutch coordinate system. It is important that the data from both geometry columns is in the same coordinate system. Otherwise it isn't possible to relate the two columns. We verify the coordinate system with a "Geometry information" step for each datastream. This step is part of the additional steps added by the GIS plugin.

The next step is to join both data streams based on a "Cartesian join". This step doesn't yet recognize the spatial relationship. It will simply join each record from one stream with each record from the other. For this example, this means that because we started with 3000 incidents and 9 maintenance area, the resulting data stream will contain about 27000 records.

Cartesian join result

In the join result we can clearly see that incident ID no 1012 is joined to all 9 area names. But an incident can't be in all 9 maintenance areas at the same time. We wanted to identify the maintenance area for each incident, remember?

As a result, the datastream now contains two geometry columns. We need to filter the datastream based on the spatial relationship. For this, we use the step called "Determine spatial relationship", which is also part of the GIS plugin.

Define the spatial relationship between incidents and maintenance area.

In this step we can define the spatial relationship between the two geometry columns. The step supports a lot of different spatial relationships that can be described to relate two objects by use of location. The one we're looking for is simply called 'Within', as we want to identify each incident within each maintenance area. The spatial analysis settings shown above can be summarized as a select statement that selects the records where 'INCIDENT_LOCATION is within MAINTENANCE_AREA_LOCATION' evaluates to TRUE. When this step is run, only the records where this statement is true are kept in the resulting datastream.

Incidents within their maintenance area.

As the image above shows, the resulting datastream contains one record per incident ID with the corresponding maintenance area information. However, the datastream still contains two geometry columns. This is no problem for the database table we're about to create, but most common GIS software only supports one geometry type per data layer. So we make a selection of the output fields with the "Select values" step, assuring that the resulting table only contains the incidents point geometry attribute, combined with ID, area name and supervisor name.

Select the necessary fields

After this, the final step is to write the resulting data to a new database table with the "Table output" step. (Another approach could be to add the maintenance area and supervisor name to the existing incidents table; in that scenario we might consider using an "Insert/update" step.) Of course, the receiving database must support geometry type database fields.

Output the table to the database

Now, when we visualize the resulting dataset with GIS software, we can see that each incident is nicely matched with its corresponding maintenance area by color.

Incidents colored by maintenance area

Conclusion

Spatial relationships allow us to define relationships between objects that have no other relation to each other than the locational one. Extending standard ETL tools with geospatial capabilities is a very cool way to make use of the specific advantage of geospatial data.

Granted, there are some caveats in the use of spatial properties in ETL tooling.

• The AtolCD Pentaho GIS plugin used for PDI supports Oracle Spatial and PostgreSQL/PostGIS geometry (and also MySQL, but this I haven't tested), but lacks support for MS SQL Server geometry.
• Geospatial data can get quite large in size. Substantially larger datasets like cadastral parcel information (400.000+ records) that are joined by a cartesian join will result in billions of records in the datastream. This might hamper performance but I haven't tested yet.
• I have yet to try this in other ETL tools like SSIS or Talend.

Still, I'm confident these issues will eventually get resolved.

Geography in 3NF normalized source systems

We're going to look for geography in source systems in this post. When designing a BI solution, most data will be stored in one or more source systems that follow a 3NF relational model stored in a database. It is fairly uncommon for such source systems to have true geographical information in geography type database fields, but that doesn't mean these sources don't contain geographical information.

Let's take for example the Sakila database model for MySQL (the original data model by Matt Casters, Jos van Dongen & Roland Bouman can be found here). This very well normalized sample database shows us three entities that are likely to have a geographical component. The addresses, that are represented with point geometries, and the city and country that are polygon geometries with their respective city limits and country borders. Address, city and country geometries are easily obtained through a geocoding service (see my previous post about geometry types).

Geometry attributes added to address, city and country. Geographical relationships in red.

The relations between address, city and country in this data model are stored with primary-foreign key relationships. However, adding geometry to these entities allows us to perform additional analysis on the data model. In the Sakila data model we can also see that three entities, namely store, customer and staff, are related to the address entity. By adding geometry to the address we can analyse which store the customers live closest to, and compare this to the store in which they rent the most movies at.

Geographical relation between store and customer, based on address geography.

We might learn that some customers rather go to a store further away from their home and try to explain this with other data like sales data. Additionaly, plotting these customer addresses and their relation to the stores frequently visited on a map can give us a clear indication about 'blind spots' in our store coverage.

So at this point we really only have to decide how to incorporate the geographical information in our design. If we're building a datawarehouse solution, we might consider storing the geographical attribute of the addresses, cities and countries in our datawarehouse on a regular basis. 

In reality, source systems are hardly ever properly normalized like the example above. Very often we find address information is scattered over several tables in the data model. For instance, the customer, store and staff tables might all contain it's own address attributes. For geographical analysis, this doesn't make much difference. We can still perform analysis like described above.

The geographical address entity is very easy to spot in source systems, however there are more entities that can identified as having a geographical attribute. Not so much in financial systems, but latent geography can be found in source systems at organisations that have their operations taking place in the public space.

The 'geodatabase'

Organisations that handle a lot of geographical data often have a separate GIS department. This GIS department will most likely have their information stored in a dedicated geographically enabled database they call the 'geodatabase'. But this geodatabase has little of the characteristics you can expect of a BI database. GIS systems usually don't require any 3NF datamodel to be used in a database environment. Instead, GIS systems are equipped with functionality to add and remove tables with geodata to any database that supports geography.

Let's take a look at the image below. It shows the datamodel of a particular schema from our geodatabase. Notice the lack of any relationships between the tables.

One of the schemas in our 'geodatawarehouse'. No key-based relationships between tables.

In traditional 3NF systems you will find relationships between tables based on keys (PK/FK, many-to-many etc). In a geodatabase, every table is related through the spatial reference of the geographical attribute, as explained in this previous post. In the above datamodel, each table has an attribute named GEOMETRY (with type OTHER because SQL PowerArchitect doesn't support geometry types) which relates it to every other table in the database. Seen from one of the tables, these relationships would look something like this:

Table MINSET_STUW is related to all other tables (ánd selfreferencing) through the geometry attribute.

However, if we would draw relationships for all tables it might look something like this:

All tables related to all other tables

You can imagine that in a geodatabase with between 200 and 1000 separate tables this would generate an unreadable datamodel.

In essence, any geodatabase is mainly a storage platform for every table related to geography. From a BI standpoint it should be treated as a source system, albeit a strange one. As I mentioned earlier, a very specific property of the geodatabase is that GIS folk tend to add and remove tables to/from the geodatabase at any given time. Because no table is explicitly related to anything, this poses no problems from a GIS standpoint. But it will certainly hurt your BI deliverables if your carefully crafted report, that is depending on geographical relations, suddenly isn't working anymore because a table has been removed from the source system.

The hybrid database

So far we've discussed source systems that are either purely administrative or purely geographical in nature. There is a third flavour source systems that are made of a normalized 3NF data model ánd contain geographical attributes in the database tables. These hybrid databases are extremely rare. If you encounter one you can safely use it for your BI solution as it will offer you the best of both worlds:

• a tightly managed data model
• integrated geographical attributes

Feel free to discuss, and if you have any questions please let me know.

The spatial reference in BI

In the previous post I wrote about the fact geographical information can be of great business value. Currently, in Business Intelligence solutions geography is mostly used for visualisation purposes. In Business Intelligence, we're always looking for the relationships in data. With structured datasets based on RDBMS sources, the relationships between objects are managed through primary and foreign keys. In unstructured data, as Bill Inmon (http://www.b-eye-network.com/view/14120) explained, you have to depend on taxonomies.

But there's another option that might be applied to both structured and unstructured data. Geographical information contains a unique property making it possible to relate even the most seemingly unrelated datasets: the spatial one. Every object with a spatial location can be related to every other object that has one. These relationships can be queried with common SQL (though the approach is dependent on the database platform), calculated with spatially enabled ETL tools or just with use of a nice JavaScript or Python-library.

Let's have a look at some examples of spatial relationships.  

Example 1: Distance to specific location Imagine you have two data source tables in your database: a table with home addresses and a table with pubs. At first glance, just looking at the tables doesn't tell you much. But with the use of their respective locations you can determine the proximity to each pub for each home address with a simple SQL statement. A spatial relationship like this is called a proximity relationship.

The map shows 3 pubs as green diamonds. Addresses are symbolized as blue dots.

Below is an example for PostgreSQL.

SELECT a.address
,      dist.name as pubname
,      dist.distance
FROM adressen a
LEFT JOIN (SELECT a.address, p.name, ST_Distance(p.geom, a.geom) as distance 
                FROM adressen a
                LEFT JOIN pubs p ON ST_DWithin(a.geom, p.geom, 10000)
                ) as dist
                on (a.address = dist.address)
WHERE a.address = dist.address 
AND dist.name = (SELECT p.name
               from pubs p
                order by p.geom <-> a.geom LIMIT 1)
order by a.address;

The query is designed to find the nearest pub for each address within 10km of the address. With the use of the subquery in the LEFT JOIN we determine the distance to each pub for each individual address, basically a cartesian join between addresses and pubs on the locational relationship. The subquery in the WHERE clause is optional to determine the closest pub for each address, again based on the spatial relationship.

The resulting table would be as followed. Clearly, you can see the distance from each address to the nearest pub. In Oracle, this query would be somewhat simpler, but both queries would yield the same result.

Example 2: Area of influence
In rural areas the government keeps record of all nuisance caused by farms. Around each farm, the stench contour defining the area where air quality is affected is registered as a polygon in a table. Another table containing house addresses can be queried to see which addresses are within the contour.

The farm is shown in yellow, the area of influence is shown with the red contour. The adresses are the house-shaped blue-ish polygons.

Also here are sample queries for Oracle and PostgreSQL.

SELECT a.street
,      a.number
,      a.city
FROM address a
,    farm_contour f
WHERE SDO_RELATE(a.geometry, f.geometry,'mask=inside') = 'TRUE';  
SELECT a.street
,      a.number
,      a.city
FROM address a
,    farm_contour f
WHERE ST_Within(a.geometry, f.geometry) = 'TRUE';

These queries already look somewhat more similar. Again, both queries will return the same results: a list of all addresses within a stench contour. Addresses that are outside all contours are excluded by the 'TRUE' condition in the spatial where clause. This second kind of spatial relationship is called an overlap. Both full and partial overlaps can be determined depending on the spatial operator used.

Example 3: neighbors along ownership
My organization has to deal with a lot of land owners that own parcels that are neighboring our property. Very often, we need to figure out which parcels directly touch parcels owned by a specific owner, in this case our organization

Left the parcel owned, right the parcels that directly touch the left parcel.

SELECT k.parcel_code
,      k.owner
,      o.parcel_code
FROM parcels k   --
,    parcels o
WHERE SDO_RELATE(k.geometry, o.geometry,'mask=touch') = 'TRUE'
AND o.owner = '<our organization>';

SELECT k.parcel_code
,      k.owner
,      o.parcel_code
FROM parcels k   --
,    parcels o
WHERE ST_Touches(k.geometry, o.geometry) = 'TRUE'
AND o.owner = '<our organization>';

We notice the queries only select the parcels that share a common boundary. This is a third kind of spatial relationship called adjacency.

Implicit relationship
The cool thing about spatial relationships is that they are implicit. Each table can be maintained on it's own without the need to maintain a relational table or primary-foreign key constraint between tables. In the above samples, the addresses table and pubs table can be stored and edited independent of each other. New addresses can be added to the address table without any specific requirement regarding the pubs locations. The same goes for the addition of new houses or parcels. Once two tables are queried, the spatial relationship can be determined on-the-fly. This is possible because all spatial data shares a common attribute: they are all positioned somewhere on (or below) the earth surface.

Self-intersecting
In the last query set we can notice the parcels table is referenced twice in the FROM-part of our query. In example 3 we see that both the central parcel and the adjacent parcels are stored in the same parcels table. The geometry relation in the WHERE clause of the query is referencing the same column (geom) twice. This behavior is exemplary for spatial data. Each spatial relationship is self-intersecting, meaning that objects from within the same table can be referenced to each other.

Relevance for BI
These kinds of relationships are very common in BI. With geographical information in your data sources you can determine and use spatial relationships based on proximity, adjacency and overlap. The implicit relationship allows us to relate data from different sources that are seemingly unrelated from a BI perspective. An example of an implicit and self-intersecting spatial relationship is the relationship between a store and a customer. This relationship can be administratively based on the fact that a customer bought something in the store, which will undoubtedly show up when examining source systems. But on the other hand the geographical location of the addresses of both the store and the customer are implicitly related to one another based on their respective locations. This can allow for additional insight in shopping behavior. Maybe the relationships will reveal a pattern of customers shopping at particular stores inspite of the fact other store locations are closer to their home.

The spatial relationships described in this post will be present in both structured and unstructured data sources. In structured data, the spatial reference may reveal new relationships between your data sources you never knew existed as a bonus. But for unstructured data, the spatial references already is proving to be the most promising way to relate all this data together. Virtually every post, tweet, picture or video already has location associated with it. And for documents, if it has an address in it, you can very easily add location to the mix.

Any thoughts? Leave them below. Next time, we'll be taking a closer look at spatial information in 3NF source systems. Be sure to check back in!

The types of geospatial information in Business Intelligence

Location plays an inportant role in many aspects of our daily lives. In Business Intelligence, every so often you will encounter geospatial data. From a BI perspective, this geospatial data is different from traditional data stacked in source systems. It will even look different, or even unreadable when you query it. But there's a growing awareness that location is valuable and useful for business purposes. So as a BI specialist you can deliver added business value by using this data in reporting, analytics and presentation.

What is geospatial data anyway? Geospatial data is used to locate an item or event on, below or over the earth's surface. Whether it's the location of a store, the perimeter of a building or the route of a delivery truck, if you can pinpoint it anywhere on earth it is geospatial data. It doesn't even have to represent a real-world object. County borders, property boundaries and environmental zones are clearly geospatial objects.Though you can't literally point to them and say “there it is” you can print them on a map.

The geospatial location of an object is often stored as one of three geometric representations: a point, line or polygon. In essence, all geography consists of coordinates. A point is simply a coordinate pair. Lines are made of 2 or more coordinate pairs linked in a specific order, while polygons are made of 1 or more connecting lines. There are more elaborate type objects like arcs, multigeometries, raster grids and TINs (and there's also 3D).

But let's keep it simple for now. In BI, geospatial data can be encountered in roughly three different ways.

Geocoding

Any object that contains a reference to an address, town, zipcode, county, state or country can be located on the map using that reference. There are several geocoding services out there that are happy to provide you with the location of objects. You simply enter your reference into the geocoding service and it will return the location as point, line or polygon. You don't need to maintain geography. Of course your reference needs to be complete to return a valid result. For example, if the name of a state is misspelled, the geocoding service might return no or an incorrect result.

Below is an example of a geocoding request to the Google Maps Geocoding API with the output it generates. You can see the location coordinates returned by the service, as well as some additional geographical info.

https://maps.googleapis.com/maps/api/geocode/json?address=bosscheweg+56+boxtel+the+netherlands

{
   "results" : [
      {
         "address_components" : [
            {
               "long_name" : "56",
               "short_name" : "56",
               "types" : [ "street_number" ]
            },
            {
               "long_name" : "Bosscheweg",
               "short_name" : "Bosscheweg",
               "types" : [ "route" ]
            },
            {
               "long_name" : "Boxtel",
               "short_name" : "Boxtel",
               "types" : [ "locality", "political" ]
            },
            {
               "long_name" : "Boxtel",
               "short_name" : "Boxtel",
               "types" : [ "administrative_area_level_2", "political" ]
            },
            {
               "long_name" : "Noord-Brabant",
               "short_name" : "NB",
               "types" : [ "administrative_area_level_1", "political" ]
            },
            {
               "long_name" : "Netherlands",
               "short_name" : "NL",
               "types" : [ "country", "political" ]
            },
            {
               "long_name" : "5283 WB",
               "short_name" : "5283 WB",
               "types" : [ "postal_code" ]
            }
         ],
         "formatted_address" : "Bosscheweg 56, 5283 WB Boxtel, Netherlands",
         "geometry" : {
            "location" : {
               "lat" : 51.60054179999999,
               "lng" : 5.323087699999999
            },
            "location_type" : "ROOFTOP",
            "viewport" : {
               "northeast" : {
                  "lat" : 51.60189078029149,
                  "lng" : 5.324436680291502
               },
               "southwest" : {
                  "lat" : 51.59919281970849,
                  "lng" : 5.321738719708497
               }
            }
         },
         "place_id" : "ChIJWxp4d1noxkcRm32m9pvecrs",
         "types" : [ "street_address" ]
      }
   ],
   "status" : "OK"
}

Geocoding services also work the other way around. You can give them a location (e.g. a coordinate pair) and request for the return of an address, town, county, state or country. This is called reverse geocoding. Professional geocoding services provide additional functionality like returning the distance or shortest route between two objects. Both Google (Google Maps Geocoding API ) and Microsoft (Geocode Dataflow API) provide a geocode service, but there are other free and/or paid services. Free geocoding services can have a limitation in the number of feature requests you can place with them.

In addition, geocoding services are integrated in off-the-shelf BI solutions. An object can be presented on the map using on-the-fly geocoding. You simply throw your location reference onto the map component and it automatically adds location through a geocoding service.

 

Coordinate pairs

Another common form of geospatial data is the usage of coordinate pairs to store the location of an object. In a table, this is noticeable by the presence of two columns which each carry one part of the coordinate pair. One column contains an X-value or latitude, the other contains an Y-value or longitude. A big advantage of coordinate pairs is that both columns are number type columns that can easily be read or edited by anyone. The downside is that coordinate pairs can only be used for point locations. But for many uses, like store locations, addresses or incident reports, this is more than enough.

Coordinate pairs are also the lifeblood of big data. Every post, tweet, photo or video made with your smartphone can (and probably is) geographically tagged by adding the latitude and longitude recorded by your phones GPS unit. So as a business intelligence specialist you will be running into this data once in a while.

Using coordinate pairs is fairly straightforward. Most BI tools for reporting and dashboarding will support some kind of mapping component that accept two columns for location reference. Even spreadsheet software has plugins enabling the use of maps with coordinate pairs. The usage of coordinate pairs is inherently limited to plotting placemarkers on the map. These placemarkers can carry additional information through use of different sizes, colors and fonts. 

In the above image, you see a map as part of a dashboard environment. The dots on the map are positioned by two fields (latitude and longitude), as seen on the right of the image. 

Spatial attributes

We're entering the real spatial domain now. Some data sources contain spatial information stored in table attribute columns. These columns will contain actual points, lines and polygons. From the outside, the columns will probably look like to contain some unreadable BLOB type information. But in reality, these columns contain what we call spatial attributes. Below are examples of a geometry column in Oracle, MS SQL Server and PostgreSQL.

These spatial attributes can be used for complex geospatial analysis through querying or with use of specialized geospatial tooling. Nowadays, all major RDBMS vendors support spatial information through a special datatype, which allows you to define attribute columns specifically for storage of spatial information. Besides the datatype, these products will also offer functions and procedures for definining spatial relationships and spatial indexes.

When using spatial attributes, some basic knowledge of geospatial techniques is required. Spatial attributes will contain more relevant information than the point, line or polygon alone, like a defined coordinate system, map projection, rectangular bouding box, precision settings and some other stuff. Storage can be an issue too as spatial data can grow quite large.

All this makes handling of true spatial attributes more of a specialty, but it also gives you the most possibilities in terms of analysis and defining relationships between datasets. I will elaborate on the possibilities in future posts.

In conclusion

These three types of geospatial data all have their pros and cons. But they don't mutually exclude one another. In fact, they complement and even merge with one another on a regular basis. If you obtain the coordinate pair of an object with a request to a geocoding service, it is fairly simple to subsequently store the coordinate pair in your system. And coordinate pairs can be translated into spatial point attributes with the same amount of ease. Coordinate pairs can also be derived from spatial attributes. Furthermore, mapping components can also use spatial attributes for visualisation purposes. So whatever geospatial data you encounter, you always have options.

 If you have any comments or suggestions, feel free to do so below!