Import MapInfo styles into PostgreSQL

This article outlines the process for importing a MapInfo layer along with its associated style information into PostgreSQL. The MapInfo styling is imported to a field within a PostgreSQL table and then referenced in the Mapserver map file layer definition using STYLEITEM.

There are two methods outlined below, the first method uses Studio workflow to import the MapInfo tab file directly into PostgreSQL using a Spatial Data Transformation task with 'Expert mode' and 'Additional parameters'. The second method is a two part process where the source tab file is processed using ogr2ogr to create a shape file, which is then imported into PostgreSQL using Studio Workflow. In some cases this method of importing to PostgreSQL via shape file can fix geometry issues. 

Step-by-step guide

Method 1 - Importing the MapInfo tab file directly into PostgreSQL

In this example our source file is the layer file 'D:\iShareData\TextPropTab.tab' and the destination PostgreSQL table is 'textprop'

1. Configure a Studio Spatial Data Transformation task using the screenshot below as a guide.
   
   
   
   

   Select 'Expert mode' and add the following 'Additional parameters';
   
   -SQL "select *, CAST (OGR_STYLE AS Character (255)) AS 'style' from TextPropTab" 

2. You can then inspect the new PostgreSQL table with PgAdmin and view the 'style' field. Now set up the MapServer layer, following the instructions from point 3 in Method 2 below.

Method 2 - Importing the MapInfo tab file into PostgreSQL via ShapeFile

1. Create a ShapeFile with a 'style' data field
   
   

   Studio Workflow can be used instead of the command line tool for the first part of this method if you prefer.

   Extract "OGR Style" field from source

   ogr2ogr -sql "select *, OGR_STYLE from sourcelayer" "destinationlayer" "sourcelayer"

    

   In this example our source file is the label layer file D:\iShareData\TextPropTab.tab and our destination file is 'D:\iShareData\TextPropShape.shp'. At the command line navigate to your OGR directory (e.g. cd d:\Astun\Tools\OGR). Then enter the following (using your file paths and names).
   
   

   ogr2ogr -f "ESRI Shapefile" -SQL "select *, CAST (OGR_STYLE AS Character (255)) AS 'style' from TextPropTab" D:\iShareData\TextPropShape.shp D:\iShareData\TextPropTab.tab

   
   You will now have a 'D:\iShareData\TextPropShape.shp' file

    

2. Import the shape file into PostgreSQL using a Studio Spatial Data Transformation task. You can then inspect the new PostgreSQL table with PgAdmin and view the 'style' field.
    

3. Create a map file layer definition using STYLEITEM"field_name" to apply the text styling, as per the example below:

   	LAYER
   		NAME proptext
   		GROUP groupname
   		STATUS OFF
   		TYPE ANNOTATION
   		PROJECTION
   			 "init=epsg:27700"
   		END
   		METADATA
   			 WMS_TITLE "Property text"
   		END
   		INCLUDE "datashare.inc"
   		DATA "wkb_geometry from (select * from proptext) as foo using unique ogc_fid using srid=27700"
   		STYLEITEM "style"
   		CLASS
   		END
   	END

   
   
   

4. This MapServer PostgreSQL layer can now be incorporated with base mapping or used as a Map Source Layer in Studio.