[GIS] Data Migration: PostgreSQL (PostGIS) to SQL Server 12

postgispostgresqlsql server

I'm migrating data from a PostgreSQL database (with PostGIS), which contains geospatial data to SQL Server '12 and having some import errors. I'm assuming these errors are due to the geospatial data types.

I originally ran pg_dump dbname > outfile and then loaded the output file which is a .bak into SQL Server '12 using the GUI import wizard.

Import Errors:

  • Error 0xc02020a1
  • Error 0xc020902a
  • Error 0xc0202092
  • Error 0xc0047038

Question: What is the best way to migrate PostgreSQL geospatial data into SQL Server '12?

Best Answer

A python method of doing this (for us a daily scripted process) involves reading the PostGIS table using psycopg2 and casting the spatial data as text.

I then use an insert and dump the data into MSSQL using pypyodbc, while using a 'temp_geom' table to hold the 'geometry text', then run a quick function to turn that text back into geometry, then remove the temp_geom column.

Of course, the PostGIS table exists, and the MSSQL table needs to be created in the same schema you're going to be loading into (using a create table (columns) statement)

So the python script looks like this:

import psycopg2
import pypyodbc

#connection to POSTGIS Dev
connSource = psycopg2.connect("host=hostname dbname=pgisdev user=username password=***** ")
curSource = connSource.cursor()

#connection to MSSQL Dev
connDest = pypyodbc.connect("DRIVER={SQL Server};SERVER=hostname;DATABASE=sqldev;UID=user;PWD=****")
curDest = connDest.cursor()

curSource.execute('''
SELECT
elem, mid, high , school_name, abbreviation, schnum
, classification, school_level, current_config, final_config, ST_AsText(geom)
  FROM "Schools_Current";
''')

#if repeating, add line to delete * from destination_table

#add temporary text column to hold geometry cast to text
curDest.execute('''
alter table dpsdata.Schools_Current add geom_temp varchar(max);
''')
connDest.commit()

#build first part of insert statement with parameters
sqlDest = '''
INSERT INTO [dpsdata].[Schools_Current]
           ([ELEM]
           ,[MID]
           ,[HIGH]
           ,[SCHOOL_NAME]
           ,[ABBREVIATION]
           ,[SCHNUM]
           ,[CLASSIFICATION]
           ,[SCHOOL_LEVEL]
           ,[CURRENT_CONFIG]
           ,[FINAL_CONFIG]
           ,[geom_temp])
           values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
'''


data = []


rows = curSource.fetchall()

for row in rows:
    data = [row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10]]
    curDest.execute(sqlDest, data)
    connDest.commit()

#re-build geometry into geometry column
curDest.execute('''
update dpsdata.Schools_Current
set shape = geometry::STGeomFromText(geom_temp, 2877)
''')
connDest.commit()

#remove temporary text column
curDest.execute('''
alter table dpsdata.Schools_Current_dev drop column geom_temp;
''')
connDest.commit()

connSource.close()
connDest.close()

It may look like a lot, but we have a few dozen of these deployed and scripted on Windows and Ubuntu servers and they hum along every morning!

Another HUGE benefit is when you're selecting the geom from PostGIS you can use any of the spatial functions to reproject the data, buffer, etc. etc. etc., so the select would look like this:

curSource.execute('''
SELECT
elem, mid, high , school_name, abbreviation, schnum
, classification, school_level, current_config, final_config, ST_AsText(ST_Transform(geom, 2877))
  FROM "Schools_Current";
''')

So that means we can store our PostGIS data in WGS84, then load it into MSSQL as State Plane CO Central (2877) for our MSSQL clients...

Related Question