Tutorial: Getting Started with Microsoft SQL Server

Introduction

This tutorial is an introduction to using Microsoft SQL Server with FME. Spatial databases are useful for long-term data storage, rather than short-term data transfers, so the key operations are getting data into and out of that store.

The three key operations that occur with a spatial database, including SQL Server are:

• Data Imports
• Data Updates
• Data Distribution

The activities above involve not only transforming the data into the correct format, but also transforming the data into the correct schema or data model.

From the Microsoft SQL Server family, FME provides support for spatial data through the Microsoft SQL Server Spatial reader/writer and non-spatial data support through the Microsoft SQL Server Non-Spatial reader/writer. The non-spatial reader and writer have an optional JDBC format as well.

This tutorial will cover connecting to a SQL Server database, reading from SQL Server, and writing to SQL Server.
 

Database Terminology

When working with FME and databases, it is important to clarify some of the basic database terms used by FME, as they may differ from what is used in a particular database package.

Schema: Also known as the Data Model. In the SQL Server table list, the table name is prefaced by the schema (schema.table)

Authentication: Confirming the user trying to access the database has the right to do so

Geometry: Spatial Type, used for planar data such as UTM coordinates

Geography: Spatial Type, used for ellipsoidal data such as latitude/longitude

Coordinate System: Also known as Spatial Reference System or Spatial Reference Identifier (SRID)
 

Articles

Viewing and Inspecting SQL Server Data

This example details how to connect to the database and inspect a table.

Converting from SQL Server

How to read data from SQL Server, then write out to File Geodatabase.

Converting to SQL Server: Create, Drop or Truncate a Table

This article describes how to write data in CSV format to a new Microsoft SQL Server table.

Converting to SQL Server: Write to an Existing Table

Details how to write to a database that already exists through importing table schemas.

Converting from SQL Server to MapInfo (Non-Spatial to Spatial)

This example uses coordinate information from a non-spatial table, converts it into points, and then joins the points into lines to form a road network in MITAB format.