ETL: Fundamental to Spatial Analysis and Sharing of Data

Extract, Transform, and Load

ETL or Extract, Transform, and Load is a process in Data Warehousing. Data Warehouses are all around us - whether we are querying the Yellow Pages on-line to geo-coding our datasets, there is most often a database or data warehouse behind the scenes.

But how we get data into the warehouse, is called ETL.

This is a short introduction to ETL as it is an important part of data warehousing. I also cover a little about Spatial ETL.

Extract

Extract is where we begin this story. Essentially, in this step, we extract the data from source systems. A source system is where the data originates. It may be a well database, an address database, a MSExcel CSV file or almost anything you can think of.

A data warehouse ends up consolidating the data from different source systems. Each separate source system (in many cases) use a different data organization or format. Common data source formats are relational databases and flat files.

Transform

As our story continues, we run into the transform stage. In this chapter, we want to apply a series of rules or functions to the extracted data from the source to derive the data to be loaded to the end target.

If we are fortunate in having clean data (this rarely happens!) the data source will require very little or even no manipulation of the data. The most common scenario is that one or more of the following transformations need to be done to meet the business and technical needs of the end users.

For example, some of the transformations that may occur are (taken from Wikipedia - a good listing of transformations):

• Selecting only certain columns to load
• Translating coded values (e.g., if the source system stores 1 for male and 2 for female, but the warehouse stores M for male and F for female), this is called automated data cleansing; no manual cleansing occurs during ETL
• Encoding free-form values (e.g., mapping "Male" and "1" and "Mr" into M)
• Deriving a new calculated value (e.g., sale_amount = qty * unit_price)
• Joining together data from multiple sources (e.g., lookup, merge, etc.)
• Summarizing multiple rows of data (e.g., total sales for each store, and for each region)
• Generating surrogate key values
• Transposing or pivoting (turning multiple columns into multiple rows or vice versa)
• Splitting a column into multiple columns (e.g., putting a comma-separated list specified as a string in one column as individual values in different columns)
• Applying any form of simple or complex data validation; if failed, a full, partial or no rejection of the data, and thus no, partial or all the data is handed over to the next step, depending on the rule design and exception handling.

Load

After the data has been transformed, cleansed, it is now loaded into the data warehouse.

Spatial ETL

When we apply the above principles to spatial data, we call it Spatial ETL.

As we know, spatial data can suffer tremendously in accuracy, data formats, projections, datum's, etc.

With the advent of WFS and WMS and other Web Services and definitions - knowing how accurate and clean our data is is important - especially since in Web Services we are sharing data between systems.

A common method in the Well-known text (WKT). WKT is a text markup language for representing vector geometry objects on a map. It also relates to spatial reference systems of spatial objects and of the transformations between spatial reference systems. A binary equivalent, known as well-known binary (WKB) is used to transfer and store the same information on databases, such as PostGIS. The formats are regulated by the Open Geospatial Consortium (OGC) and described in their Simple Feature Access and Coordinate Transformation Service specifications.

Frank Warmerdam, the creator and maintainer of the PRO.4 and GDAL libraries of which I've talked about before covers quite well, WKT implementations and things to be aware of in several GIS products (some of the information is out of date - most users are now using Arc 9 and above).

Quoting below (from here):

• Oracle Spatial (WKT is used internall in MDSYS.WKT, loosely SFSQL based)
• ESRI - The Arc8 system's projection engine uses a roughly simple features compatible description for projections. I believe ESRI provided the WKT definition for the simple features spec.
• Cadcorp - Has the ability to read and write CT 1.0 style WKT. Cadcorp wrote the CT spec.
• OGR/GDAL - reads/writes WKT as it's internal coordinate system description format. Attempts to support old and new forms as well as forms from ESRI.
• FME - Includes WKT read/write capabilities built on OGR.
• MapGuide - Uses WKT in the SDP data access API. Roughly SF compliant.
• PostGIS - Keeps WKT in the spatial_ref_sys table, but it is up to clients to translate to
• PROJ.4 format for actual use. I believe the spatial_ref_sys table is populated using OGR generated translations.

ETL and Spatial ETL - I'll cover more of how data warehousing and GIS and internet mapping can be tied together at a later date.

If you are really interested, take a look at the MapBender project mentioned in earlier posts. It ties together many of the concepts of ETL and data sharing quite well.

If you have ideas for Blog posts that relate to GIS, Datum's, Map Projections, Oracle, Data Warehousing, let me know. Some ideas for future blogs are:

• The African Geoid Project
• Map Projections of the Middle East
• Determining Mean Sea Level
• NTv2 files and how to create them

Let me know. Contact me via e-mail.

Molodensky: A Short History of Man's Contribution to Geodesy

As internet mapping becomes more and more prevelant and GPS is being used more we keep on hearing about the Molodensky Transformation, but who was Molodensky?

A Short History

Mikhail Sergeevich Molodenksy (1909 - 1991) was a promiennt geodesist and geophysicists who many consider a reformer in the theory of the figure of the Earth and the study of the Earth's rotation and oscillations.

He was born on June 15, 1909, in Epiphan, a small town in the Russian province of Tula. He did his initial studies at the Astronomic Department of the Mechanics and Mathematics Faculty of Moscow State University.

He was later invited by F. N. Krasovsky (there is an ellipsoid that bears this geodesist's name - more on a later blog about Russian mapping) to join the staff at the Central Research Institute of Geodesy, Aerophotogrammetry and Cartography (TsNIIGAik). It was here that he worked for over 25 years.

Molodensky's early steps in geodetic research and geodetic surveys date back to 1929 when he did some inital work for the Institute of General Geodetic Surveys (IOGR) and prior to this survey, he was given a proposal from the director of Astronomy - Geodetic Research Institute (AGNII) at State University in Moscow to do geodetic research.

There came a famous decree called the "Soviet of Labor and Defense" (May 6, 1927) which set about the establishment of a general gravimetric survey over the whole country. Lenin was laying out his vision for the country and defining a "Soviet". With this decree, there became an increase in the development of gravimetric surveying throughout the whole country.

Molodensky, who was avidly interested in gravimetry, participated in these surveys with his work at TsNIIGAik. While here and conducting these surveys, a young Molodensky, in 1933, headed up an expedition to the Crimea to perform gravimetric surveys (again under the above decree).

A Rigourous Solution

A year later, in 1934, Molodensky was beginning to make a name for himself by presenting a report at the 7th Baltic Geodetic Commission Conference in Moscow. His topic, which geodesists considered urgent at the time, is the co-swinging influence on double pendulums. Before his presentation, all solutions were seen as impossible because the accuracy of the solutions could not be determined. Molodensky provided a rigourous solution. In turn, this presentation was heard by scientists from Denmark, Finland, Germany, Poland, Sweden, the USSR and the members of the International Association of Geodesy. He essentially turned the world of geodesy upside down concerning astronomic-gravimetric leveling. His final report was published in Helsinki at the 1937 meeting of the Baltic Geodetic Commission.

Molodensky made significant developments to Soviet geodesy and gravimetry, in theory and in practice, especially when developing and applying survey methods combined with the design of gravimetric instruments. In the 1930's the only gravity measuring devices that could be found in the former Soviet Union were those of foreign manufacture. Therefore, the state saw immediately that there was an immediate task facing geodesy in the Soviet Union - the manufacture of gravity measuring instruments. A small batch were initially made, based on the German "Bamberg" instruments and several designs of original instruments had failed. It was not until 1938 that the Soviet Union had developed their own gravity measuring devices.

April, 1943, lead to the appointment of Molodensky as chief of the gravimetric laboratory at TsNIIGAik. He held this post until July 1956 when, against his will, he was appointed director of the Geophysical Institute of the Academy of Sciences (GEOFIAN). At this point he became responsible for the realization of the technical policies related to geodesy in the Soviet Union.

During this time he continued to make significant contributions to geodesy and the state wanted to recognize him for his efforts. In 1946, he was awarded the USSR State Prize, then he recieved the high degree of a Doctor of Technical Sciences and was then elected a member of the USSR Academy of Sciences.

The Geoid and Plumb-Lines

As you may recall in my previous blog, about the geoid, I discussed the "deflection of the vertical", well Molodensky's work played into this realm as well. Molodensky put forward the possibility of using gravimetric survey data for interpolation of plumb-line deflection between astronomic points of astronomic-geodetic networks. The result of this work permitted the integration of isolated sections of astronomic-geodetic networks into the main systems of co-ordinates. This made it therefore possible to map vast areas which had never been surveyed before.

Nowadays a similar process is in progress in Africa and South America and Canada with respects to gravity models which will help in determining Orthometric heights. Through Molodensky, lead to a determination of geoid heights.

In 1957, TsNIIGAik began to change direction in what it saw as important, and focused on solving more complex problems; such as the figure of the Earth, space exploration, defence problems, and the development of triangulation methods for large territories.

Famous Equations

Molodensky, though is more famous for a set of equations, that relate to datum transformation.

As we all know spatial data can have co-ordinates with different underlying ellipsoids or the underlying ellipsoids have different datums. The latter means that, apart from different ellipsoids, the centres or the rotation axes of the ellipsoids do not coincide. To relate these data one may need a so-called datum transformation.

In the early days of satellite surveying, when relationships between datums were not well defined and the data itself was not very precise, it was usual to apply a three parameter dX, dY, dZ shift to the X,Y,Z coordinate set in one datum to derive those in the second datum.

This assumed, generally erroneously, that the axial directions of the two ellipsoids involved were parallel. For localized work in a particular country or territory, the consequent errors introduced by this assumption were small and generally less than the observation accuracy of the data. As we collected more and more information about the shape and form of the Earth, and based on what Molodensky presented, among others, our knowledge and the amount of data that has been built up and as our surveying methods became more and more accurate, it became evident that a three parameter transformation is neither appropriate for world wide use, nor for widespread national use if one is seeking the maximum possible accuracy from the satellite surveying and a single set of transformation parameters.

The simplest transformation to implement involves applying shifts to the three geocentric coordinates. Molodensky developed a transformation which applies the geocentric shifts directly to geographical coordinates. This method assumes that the axes of the source and target systems are parallel to each other.

From a mathematical point of view a datum transformation is possible via 3 dimensional geocentric co-ordinates, thus implying a 3D similarity transformation defined by 7 parameters: 3 shifts, 3 rotations and a scale difference. This transformation is combined with transformations between the geocentric co-ordinates and ellipsoidal latitude and longitude co-ordinates in both datum systems.

The transformation from the latitude and longitude co-ordinates into the geocentric co-ordinates is rather straightforward and turns ellipsoidal latitude , longitude and height into X,Y and Z, using 3 direct equations that contain the ellipsoidal parameters a and e.

The inverse equations are more complicated and require either an iterative calculation of the latitude and ellipsoidal height.

A very good approximation of this datum transformations makes use of the Molodensky and the regression equations, relating directly the ellipsoidal latitude and longitude, and in case of Molodensky also the height, of both datum systems.

Various software uses the formulation put forward by Molodensky, whether used in cs2cs and ESRI software, or even in Oracle, the foundations where laid out by this man who has made significant contribution to our understanding of the shape and form of the Earth.