GEOGRAPHY Data Type - SQL Server to PostgreSQL Migration

SQL Server and PostgreSQL provide the GEOGRAPHY data type to store spatial data. Note that you have to enable the PostGIS extension to use the GEOGRAPHY data type in PostgreSQL.

PostgreSQL:

  -- Enable PostGIS
  CREATE EXTENSION IF NOT EXISTS postgis;

SQL Server and PostgreSQL:

  -- Sample table 
  CREATE TABLE geo 
  (
     geodata GEOGRAPHY
  );
 
  -- Inserting sample value
  INSERT INTO geo VALUES ('POLYGON ((-72 -21, -70 -18, -69 -17, -72 -21))');

By default, SQLines Data migrates SQL Server GEOGRAPHY data type to TEXT data type in PostgreSQL, so you will see the text representation of spatial data in PostgreSQL.

Once you have installed and enabled PostGIS extension you can map SQL Server GEOGRAPHY columns to GEOGRAPHY columns in PostgreSQL using -cmapf column mapping option.

For example:

  -- -- schema.table, column, target_column [, target_datatype]
  dbo.geo, geodata, geodata, GEOGRAPHY

or if you have more geodata columns you can use a wildcard as follows:

  -- schema.table, column, target_column [, target_datatype]
  *.*, geodata, geodata, GEOGRAPHY

For more information, see SQL Server to PostgreSQL Migration.