GEOMETRY Data Type - SQL Server to PostgreSQL Migration

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

PostgreSQL:

  -- Enable PostGIS
  CREATE EXTENSION IF NOT EXISTS postgis;

SQL Server and PostgreSQL:

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

By default, SQLines Data migrates SQL Server the GEOMETRY 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 GEOMETRY columns to GEOMETRY columns in PostgreSQL using the global data type mapping -dtmapf or -cmapf column mapping options.

For example:

  -- Global data type mapping
  GEOMETRY, GEOMETRY

Column mapping:

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

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

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

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.