Azure Data Factory Geography or Geometry Data types

1
votes

I've been trying to set up and Azure Data Factory (unsuccessfully to date). I have 2 Azure SQL databases which are both on the same server (in the same subscription)

In both databases I have a table with the following schema

CREATE TABLE [dbo].[Country_Boundaries]
(
    [Id] [nvarchar](255) NOT NULL,
    [Boundary] [geography] NULL,
    [Name] [nvarchar](255) NULL,
    [Centroid] [geography] NULL
)

I would like to use a data factory to transfer the data from one database table to the same structured table in the second DB.

Do data factories support the Geography/Geometry data type?

I have also looked at using Azure data sync to do this - unfortunately each row in the table is too big for a single data sync transaction (the table contains complex country boundaries using the Geography data type).

Example

No of Bytes
Scotland -   55,340,796
NorthernI -  2,149,616
England -    2,126,804
Wales -      705,266

The other way I have looked at doing this is using referenced tables but unfortunately referenced tables don't support the Geography/Geometry data type, either.

3
azurespatialsqlgeographycross-databaseazure-data-factory
Vote for this to get Microsoft to work on it! feedback.azure.com/forums/307516-azure-synapse-analytics/… – blobbles

3 Answers

0
votes

Do data factories support the Geography/Geometry data type?

No, Azure Data Factory does not support spatial types at this time. When selecting a table to sync via the copy wizard, if the table has any spatial columns, you will receive an error:

Error when processing request: Column: Location,The data type is not supported. activityId: [...]

Or if you select multiple tables, one of which has a spatial column, you will get the error:

Some tables contain unsupported data type or Object type: [dbo].[Table]. Please use Custom Query to exclude them.

0
votes

I would look at transforming the data into either Well known binary or well known text then pass that into adf and then use a sproc and table type to load the data in batches this gives you the ability to re transform the data ing geography dont forget to include the srid

0
votes

SQL Geography/Geometry is supported in a limited way that they can only be transferred between SQL databases. As your source and sink SQL tables have same schema,

CREATE TABLE [dbo].[Country_Boundaries]
(
    [Id] [nvarchar](255) NOT NULL,
    [Boundary] [geography] NULL,
    [Name] [nvarchar](255) NULL,
    [Centroid] [geography] NULL
)

the only thing you need to do is to create source and sink datasets and Copy activity. The Copy activity will transfer the 4 column data from source to sink.