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 it 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 also have an optional JDBC format.
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 essential to clarify some of the basic database terms used by FME, as they may differ from those used in a particular database package.
Schema: Also known as a data model. In the SQL Server table list, the table name is prefaced by the schema (schema.table)
Authentication: Confirming that 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 a 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 utilizes coordinate information from a non-spatial table, converts it into points, and then joins the points to form lines, creating a road network in MITAB format.