Virtual Earth & SQL Server 2008 – Part 2: Spatial Data Management in SQL Server 2008 (2/3)

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"&gt;
           <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"&gt;
           <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.

image

In the 1st step we select the input-format

image

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).

image

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

image

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.

image

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

image

We can skip the next dialogue…

image

…and have FME create the workspace.

image

You can optionally determine which features you want to import.

image

FME will now prepare the workspace.

image

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

image

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

image

We also specify the coordinate system for our destination data.

image

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).

image

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.

image

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.

image

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.

Advertisements
This entry was posted in SQL Server Spatial. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s