MapForums

Loading and Extracting Data in SQL

There are a number of methods which allow us to load data into SQL Server 2008. We can load data from

• Well Known Text (WKT), e.g.
  INSERT INTO Test01 (Geometry, Geography) VALUES (
  *** geometry::STGeomFromText('POLYGON((
  ******* 51.461408935417026 -0.9266281127929573,
  ******* 51.46091427889742 -0.9263437986373904,
  ******* 51.46108473546569 -0.9255471825599749,
  ******* 51.46157939013779 -0.9258341789245446,
  ******* 51.461408935417026 -0.9266281127929573))', 4326),
  *** geography::STGeomFromText('POLYGON((
  ******* 51.461408935417026 -0.9266281127929573,
  ******* 51.46091427889742 -0.9263437986373904,
  ******* 51.46108473546569 -0.9255471825599749,
  ******* 51.46157939013779 -0.9258341789245446,
  ******* 51.461408935417026 -0.9266281127929573))', 4326))
• Well Known Binaries (WKB), e.g.
  INSERT INTO Test01 (Geometry, Geography) VALUES (
  *** geometry::STGeomFromWKB
  ***** (0x0102000000020000008716D9CEF7D34740D7A3703D0A975E C08716D9CEF7D34740CBA145B6F3955EC0, 4326),
  *** geography::STGeomFromWKB
  ***** (0x0102000000020000008716D9CEF7D34740D7A3703D0A975E C08716D9CEF7D34740CBA145B6F3955EC0, 4326))
• Geographic Markup Language (GML), e.g.
  INSERT INTO Test01 (Geometry, Geography) VALUES(
  * geometry::GeomFromGml(
  *** '
  ******
  ********
  **********
  ************ 51.461408935417026 -0.92662811279295731
  ************ 51.46091427889742 -0.92634379863739036
  ************ 51.46108473546569 -0.92554718255997492
  ************ 51.461579390137793 -0.92583417892454456
  ************ 51.461408935417026 -0.92662811279295731
  **********
  ********
  ******
  **** ', 4326),
  * geography::GeomFromGml(
  *** '
  ******
  ********
  **********
  ************ 51.461408935417026 -0.92662811279295731
  ************ 51.46091427889742 -0.92634379863739036
  ************ 51.46108473546569 -0.92554718255997492
  ************ 51.461579390137793 -0.92583417892454456
  ************ 51.461408935417026 -0.92662811279295731
  **********
  ********
  ******
  **** ', 4326))

Of course there are similar methods to retrieve the data again:

• Well Known Text (WKB)
  SELECT Geography.STAsText() FROMTest01
  ---
  LINESTRING (51.461621168158487 -0.92789947986601884, 51.461925310996484 -0.92718601226808184)
• Well Known Binary (WKB)
  SELECT Geography.STAsBinary() FROM Test01
  ---
  0x010200000002000000E630066716BB4940AFFFFF3F5AB1ED BF57F65B5E20BB49408B00000082ABEDBF
• Geographic Markup Language (GML)
  SELECT Geography.AsGml() FROM Test01
  ---
  <LineString xmlns="http://www.opengis.net/gml">
  * <posList>
  *** 51.461621168158487 -0.92789947986601884
  *** 51.461925310996484 -0.92718601226808184
  * posList>
  LineString>

Loading and Extracting Data with Safe FME

The above mentioned methods are all very good if we have just a few data to insert or retrieve but for bulk loading there are better tools – specialized spatial ETL-tools – such as Safe Software’s Feature Manipulation Engine (FME 2008 Beta). Let’s see how we can leverage the power of FME.

Loading Data into SQL Server 2008

After starting the FME Workbench we can use the workspace wizard which guides us through the complete process.

In the 1^st step we select the input-format

FME supports a huge variety of spatial data formats. In this example we are going to import spatial data from the Ordnance Survey Great Britain’s (OS GB) MasterMap. The file will be formatted in the Geographic Markup Language (GML) and the coordinate system will be the OSGB36 (Ordnance Survey Great Britain 1936) a.k.a. British National Grid (BNG).

Once we determined the input format we can select the file in our file-system.

We repeat the procedure for the destination format but this time we select ‘Microsoft SQL Server (Spatial)’. This is the writer we need for our SQL Server 2008.

Next we specify the server, the database and the type of authentication.

We can skip the next dialogue…

…and have FME create the workspace.

You can optionally determine which features you want to import.

FME will now prepare the workspace.

Now we can specify various translation parameters either on workspace-level…

...or for individual tables. In these cases we want to make sure that the data is inserted as geography data type.

We also specify the coordinate system for our destination data.

In this case we want to convert from OSGB36 into WGS84. All of the below will do for us but since we would like to use a consistent Spatial Reference System (SRS) Identifier (ID) it is a good idea to use ID 4326 as specified by the European Petroleum Survey Group (EPSG).

Once we are happy with the settings we can start the process of transformation and loading. FME will give as a summary of what it did.

Extracting Data from SQL Server 2008

A simple way to review the result of the data load is to use the FME Universal Viewer. You can easily connect to the various data sources and have a look at the results. You can pan and zoom the maps and you can also select a feature and have a closer look at its metadata.

Of course you can also convert the data from SQL Server 2008 formats into all the other formats which are supported by FME. One of these new formats is the ‘Virtual Earth Tile Layer’. And we will have a closer look at this when we come to Virtual Earth itself.