GEOGRAPHY Data Type - SQL Server to PostgreSQL Migration

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

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.