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 have geometry and contain only 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.
 

Video

Step-by-Step Instructions

1. Add the Reader

Start FME Workbench and create a New Workspace. Add a Reader to the canvas, and set its format to Microsoft SQL Server Spatial. The Dataset will be the named connection, SQL Training Database, set up in Viewing and Inspecting SQL Server data.

Click on the Parameters, and set the Table List to read fmedata2016.RoadPoints.

If you do not have this named database connection, select the option to Embed Connection Parameters and after selecting Parameters, enter the parameters manually:

• Server: sql.fmetraining.ca
• Database: fmedata
• Authentication: SQL Server Authentication
• Username: fmedata
• Password: SQLtraining2016
• Table List: fmedata2016.RoadPoints

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. Open the VertexCreator and set the 'x' attribute for the X Value and the 'y' attribute for the Y Value.

3. Set the Coordinate System

Add a CoordinateSystemSetter to the canvas, connect it to the Output of the VertexCreator. Open the CoordinateSystemSetter and set the Coordinate System to TX83-CF.

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 first Attribute to 'road_id', Alpha/Num to Alphabetic, and Order to Ascending. The second attribute to sort by is 'vertex_num', numeric, ascending.

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.

Note: The LineBuilder was originally called PointConnector
 

6. Add MapInfo TAB Writer

From the Writers menu, Add Writer. Select MapInfo TAB for the format and enter a location (folder) for the dataset. Click OK.

Connect the Writer to the Line port on the PointConnector.

Open the properties of the Writer. Change the Table Name to MajorRoads. Click on the User Attributes tab and delete the 'x' and 'y' columns by selecting the attributes and clicking the minus button.

7. Run the Workspace

Run the workspace and view the output in Visual 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 here originates from data made available by the Government of Austin, Texas. It contains information available to the public domain.