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

Introduction

In this demo, a table in the SQL Server database contains non-spatial data representing surveyed roads. The 'x' and 'y' attributes in the table, will turn the non-spatial data into points. Using the 'road_id' attribute, which is unique to each road, the points will be connected into lines based on this value. Finally, the lines can be written to a spatial format such as MapInfo.

FME provides both a non-spatial Microsoft SQL Server reader and writer. These readers/writers are used for tables that do not contain geometry and only have attributes. The Microsoft SQL Server Non-Spatial reader and writer do not require a Spatial Type (Geometry or Geography).

Although FME can read non-spatial data with the Microsoft SQL Server Spatial reader, the Non-Spatial reader should be used to prevent the translation from trying to read in a spatial type.

Starting in FME 2025.2, many formats that support both spatial and non-spatial data have been combined into a single reader or writer. For an up-to-date list of the formats this change has been applied to, please see Combined Spatial and Non-Spatial Readers and Writers.

Video

The video was taken in an older version of FME. The interface may look different, but the steps remain correct.

Step-by-Step Instructions

1. Create New Workspace and Add SQL Server Connection

Start FME Workbench. From the Start page, select the New Workspace option. If you have already created the SQL database connection in a previous tutorial, there is no need to create another connection. You can simply use the connection name in the Reader.

To create a new Database connection, click on Utilities > FME Options. Select Database connections and click on the + arrow to create a new connection. Enter the following information:

• Database Connection: Microsoft SQL Server
• Name: SQL Training
• Server:  sql.fmetraining.ca
• Username: fmedata
• Password: SQLtraining2016
• Database: fmedata

From the Readers menu, select Add Reader and set the following:

• Format: Microsoft SQL Server (MSSQL)
• Connection: SQL Training
  • This is the name of the connection created above

Click Paramaeters

In the parameters window that opens, click on the ellipsis next to the Tables field. Select the RoadPoints table from fmedata2016 and click OK.

2. Create Points

Add a VertexCreator to the canvas and connect it to the RoadPoints reader. This transformer will turn the 'x' and 'y' attribute values into points. Double-click on the transformer to open the parameters and set the following:

• Mode: Add Point
• X Value: x
• Y Value: y

Click OK.

3. Set the Coordinate System

Add a CoordinateSystemSetter to the canvas, connect it to the Output of the VertexCreator. Open the CoordinateSystemSetter and set:

• Coordinate System: TX83-CF

Click OK.

4. Sort the Data

The points will need to be sorted before they are connected together. Add a Sorter to the canvas and connect it to the CoordinateSystemSetter. 

Set the following parameters:

• Attribute: road_id
  • Method: Alphabetic
  • Order: Ascending
  • Sorting Exceptions: Output Last
• Attribute: vertex_num
  • Method: Numeric
  • Order: Ascending
  • Sorting Exceptions: Output Last

5. Connect the Points

Add a LineBuilder to the canvas and connect it to the Sorter. Select 'road_id' for the Group By Attribute field. The LineBuilder will connect the points based on this attribute to form lines.

Set the following parameters:

• Group Processing: Enabled
  • Group By: road_id

Click OK.

6. Add MapInfo TAB Writer

From the toolbar, add a Writer and set the following parameters.

• Format: MapInfo TAB (MITAB)
• Dataset: roads.tab
  • Click on the ellipsis to navigate to a location on your computer

Click OK twice to add the writer to the canvas.

Connect the Writer to the Line port on the PointConnector.

Open the properties of the Writer. Under the Parameters tab, set:

• Table Name: MajorRoads

Under the User Attributes tab, select Manual for Attribute Definition and remove the following attributes:

• Name: x
• Name: y

To remove the attributes, click the minus sign.

Click OK.

7. Run the Workspace

Your workspace should resemble the image below. 

Run the workspace and view the results in the data preview.

Results

The lines are written to the spatial format: MapInfo MITAB. The spatial output (road lines) can be viewed in the Data Inspector and the Visual Preview.

Input

Output

Data Attribution

The data used in this article originates from open data made available by the City of Austin, Texas. It contains data licensed under the Public Domain Dedication License, as provided by the City of Austin.