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
        (0x0102000000020000008716D9CEF7D34740D7A3703D0A975EC08716D9CEF7D34740CBA145B6F3955EC0, 4326),
      geography::STGeomFromWKB
        (0x0102000000020000008716D9CEF7D34740D7A3703D0A975EC08716D9CEF7D34740CBA145B6F3955EC0, 4326))
• Geographic Markup Language (GML), e.g.
  INSERT INTO Test01 (Geometry, Geography) VALUES(
    geometry::GeomFromGml(
      '<Polygon xmlns="http://www.opengis.net/gml">
         <exterior>
           <LinearRing>
             <posList>
               51.461408935417026 -0.92662811279295731
               51.46091427889742 -0.92634379863739036
               51.46108473546569 -0.92554718255997492
               51.461579390137793 -0.92583417892454456
               51.461408935417026 -0.92662811279295731
             </posList>
           </LinearRing>
         </exterior>
       </Polygon>', 4326),
    geography::GeomFromGml(
      '<Polygon xmlns="http://www.opengis.net/gml">
         <exterior>
           <LinearRing>
             <posList>
               51.461408935417026 -0.92662811279295731
               51.46091427889742 -0.92634379863739036
               51.46108473546569 -0.92554718255997492
               51.461579390137793 -0.92583417892454456
               51.461408935417026 -0.92662811279295731
             </posList>
           </LinearRing>
         </exterior>
       </Polygon>', 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
  ---
  0x010200000002000000E630066716BB4940AFFFFF3F5AB1EDBF57F65B5E20BB49408B00000082ABEDBF
• 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.