Following the modern trend of switching from propriate software to free open-source equivalents, many companies migrate their database from SQL Server to PostgreSQL as one of the most powerful DBMS.

Migration of schemas and data between those two systems is quite straight forward except spatial data types that describes physical location and shape of geometric objects. SQL Server supports two spatial types:

• geometry type represents data in flat (Euclidean) coordinates
• geography type represents spatial data in terms of round-earth coordinates

Although PostgreSQL has the same spatial types as SQL Server, internal representation of the related data is completely different. So, it is possible to extract data “as is” and load it into the target database. Common approach to migration of spatial data is use special text markup language for representing vector geometry objects. This text representation applied to spatial data is called well-known text (WKT).

SQL Server provides system function STAsTextto get the WKT representation for geometry or geography columns. It can be called in a query as follows:

SELECT [geometry column].STAsText() FROM [table name]

Then WKT data can be imported into PostgreSQL spatial columns using system functionST_GeomFromText:

INSERT INTO table_name(geometry_column)VALUES (

ST_GeomFromText(WKT_string))

During database migration from SQL Server to PostgreSQL all spatial columns must be processed in that way, so it may take a lot of efforts on corporate scale data warehouse when doing manually. For large databases it is reasonable to automate migration using dedicated tools like SQL Server to PostgreSQL converter developed by Intelligent Converters, software company specializing in database conversion and synchronization since 2001.

Besides accurate migration of spatial data types and a CRM upgrade project plan, SQL Server to PostgreSQL tool offers the following capabilities:

• all versions of SQL Server and PostgreSQL are supported including SAAS variations of the database management systems
• the program migrates schemas, data, indexes, constraints and views
• optional support of SSL connection for PostgreSQL
• MS SQL data can be migrated to existing PostgreSQL using merge and synchronize scenarios
• option to preprocess the source data via SELECT-queries before migration
• each table can be customized by modifying name, type and other attributes of any column as well as excluding particular columns from migration
• all settings can be stored into profile to simplify next migrations

After listing all advantages, it is also important to explore limitations of SQL Server and PostgreSQL converter. The program does not migrate stored procedures, functions and triggers. It is distributed under commercial license, however there is free demo version to evaluate capabilities of the tool. Demo version migrates not more than fifty rows per table and does not convert foreign keys to preserve data integrity. More information about that SQL Server and PostgreSQL migration tool can be found at the official site of Intelligent Converters company.