Welcome to MapForums!

Register, sign in, or use Facebook Connect above to join in and participate in the forum.

When you are signed in, this message, the ads in this row, and the red-underlined link ads all go away.

Subscribe to receive our newsletter.
Subscribe Unsubscribe
Results 1 to 1 of 1

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

This is a discussion on Virtual Earth & SQL Server 2008 - Part 2: Spatial Data Management in SQL Server 2008 (2/3) within the Bing Maps Blogs & Tweets forums, part of the Blogs category; Loading and Extracting Data in SQL There are a number of methods which allow us to load data into SQL ...

  1. #1
    Johannes Kebeck's Blog is offline Senior Member Green Belt
    Join Date
    Sep 2007
    Posts
    154

    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
      ***** (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 1st 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.



    Click here to view the article.
    Last edited by Eric Frost; 03-03-2008 at 08:34 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Virtual Earth & SQL Server 2008 - Part 3: Getting Started with Virtual Earth
    By Johannes Kebeck's Blog in forum Bing Maps Blogs & Tweets
    Replies: 0
    Last Post: 03-02-2008, 01:40 PM
  2. Virtual Earth & SQL Server 2008 - Part 2: Spatial Data Management in SQL Server 2008 (3/3)
    By Johannes Kebeck's Blog in forum Bing Maps Blogs & Tweets
    Replies: 0
    Last Post: 03-02-2008, 01:40 PM
  3. Virtual Earth & SQL Server 2008 - Part 1: Introduction (2/2)
    By Johannes Kebeck's Blog in forum Bing Maps Blogs & Tweets
    Replies: 0
    Last Post: 03-02-2008, 07:30 AM
  4. Virtual Earth & SQL Server 2008 - Part 1: Introduction (1/2)
    By Johannes Kebeck's Blog in forum Bing Maps Blogs & Tweets
    Replies: 0
    Last Post: 03-02-2008, 07:30 AM
  5. MapDotNet Server 6.5 Supports SQL Server 2008 and New Virtual Earth Features
    By VE For Government in forum Bing Maps Blogs & Tweets
    Replies: 0
    Last Post: 11-12-2007, 05:42 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36