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 the GEOGRAPHY data type to the TEXT data type in PostgreSQL, so you will see the Well-Known Text (WKT) representation of spatial data in PostgreSQL.

Once you have installed and enabled the PostGIS extension, you can map SQL Server GEOGRAPHY columns to GEOGRAPHY columns in PostgreSQL using the global data type mapping -dtmapf or -cmapf column mapping options.

For example:

  -- Global data type mapping
  GEOGRAPHY, GEOGRAPHY

Column mapping:

  -- 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

Updating SRID - Spatial Reference Identifier

When transferring data using the Well-Known Text (WKT) format, the SRID can be lost. You can update it by executing the following query after the transfer:

PostgreSQL:

  -- SRID 25832 (European regional standard)
  UPDATE geo SET geodata = ST_SetSRID(geodata, 25832);

For more information, see SQL Server to PostgreSQL Migration.