Postgis - Migrate table with geometry column to SQL Server

0
votes

I have a table in a PostGIS database with a geometry column, assume the table called roadpublic just has 2 columns as follows:

COLUMN roadid uuid; COLUMN geom geometry;

Now, I'd like to import this table into a SQL Server (2012) database using the Postgres Native OLEDB Provider. The provider works like a champ but it recognizes the geometry datatype as a nvarchar(4000) so the data lands in SQL Server with this structure:

roadid  geom
5730048E-7988-4BF2-B5E4-E7DD2711E042    0105000020E6100000010...deleted...
BB978741-BE77-456A-82FE-2D55F1417442    0105000020E6100000010...deleted...
EE404EE4-CA09-4E78-842F-7C27307EAC89    0105000020E6100000010...deleted...

The geom column is converted to NVARCHAR(4000).

Ideally, it would be beneficial to export the actual coordinates from PostGIS in text format, then use the SQL Server geometry::STGeomFromText to convert it to a native SQL Server geometry datatype.

Can anyone suggest a strategy for doing this?

Thanks!

2
sql-serverssisgeometryoledbpostgis

2 Answers

0
votes

when you say that it lands in SQL Server as varchar(400), you mean when you read from your source or when you insert into your destination? Because if you manage to read it fine you can use a "data conversion component" to make it geometry

0
votes

I have the same problem. I found a solution, I am trying to adequate to my query.

DECLARE @data TABLE (
  ID nvarchar(1024),
  ImportedGeometry nvarchar(100),
  FinalGeometry geometry
  )

  INSERT INTO @data (ID, ImportedGeometry) values ('1', '0xE6100000010C4703780B24B855C061C3D32B65093540')
  INSERT INTO @data (ID, ImportedGeometry) values ('2', '0xE6100000010C96438B6CE7D359C0BD5296218E853440')

select 
d.ID,
d.ImportedGeometry,
CONVERT(varbinary(max), d.ImportedGeometry, 1) as ConvertedGeometryBin,
(cast(CONVERT(varbinary(max), d.ImportedGeometry, 1) as geometry)) as FinalGeometry
from @data d

UPDATE @data
SET FinalGeometry = (cast(CONVERT(varbinary(max), ImportedGeometry, 1) as geometry))

select 
d.ID,
d.FinalGeometry,
d.FinalGeometry.STAsText(),
d.FinalGeometry.STSrid
from @data d