1

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?

  • 1
    I use python with pypyodbc and psycopg2 - what tools are you using? You can also use FME and ogr2ogr (GDAL) – DPSSpatial Oct 26 '16 at 17:47
  • @DPSSpatial yeah I was looking at the ogr2ogr and just installed it but a little confused on how to use it. Do I query SELECT * FROM geometry_columns and export using ogr2ogr? – Jordan Davis Oct 26 '16 at 17:49
  • Putting up an example of the errors, and the tools you are using, might be helpful. – John Powell Oct 26 '16 at 18:03
  • @JohnBarça I just pg_dump dbname > outfile and then used the GUI on SQL Server '12 to import. I'll update the question, good call. – Jordan Davis Oct 26 '16 at 18:05
  • I believe Postgres and SQL Server have a different binary representation of spatial data, so that is likely to be a problem. Using one of the tools mentioned above in the comments will help with this. – John Powell Oct 26 '16 at 18:09
1

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

  • Ok wow yeah thanks! I'm going to give this at try. In the python script though are you only exporting geospatial columns? or could I just list the entire table or even db to be dumped? – Jordan Davis Oct 26 '16 at 19:40
  • This is a single table solution and only 1 geom column is being exported with several other columns of varying types... – DPSSpatial Oct 26 '16 at 19:46
  • Ok perfect! yeah I was confused on if I can export the other columns not of the geospatial type in the same table into a shapefile. If that is the case I could just dump the entire db schema at once right? – Jordan Davis Oct 26 '16 at 19:47
  • I don't think there is a tool that allows you to dump an entire postgresql database to a file that mssql will import wholesale... as far as the shapefile dump tools, they should be able to take all columns of all types... – DPSSpatial Oct 26 '16 at 20:01
  • ahhh okay so I would have to tediously run the export on each table in the db generating a shapefile? the other thing I notice if I do choose the shapefile path when I run SELECT * FROM geometry_columns I noticed that SRID column is different, and I noticed the pgsql2shp has a -s param, do you know anything about that, like if I have to change it for each different query based on the SRID value? Your way is looking way easier by the minute haha, just want to understand some constructs. – Jordan Davis Oct 26 '16 at 20:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.