Using Elastic Query to Support SQL Spatial in Azure SQL DW

[read this post on Mr. Fox SQL blog]

Recently we had a requirement to perform SQL Spatial functions on data that was stored in Azure SQL DW. Seems simple enough as spatial has been in SQL for many years, but unfortunately, SQL Spatial functions are not natively supported in Azure SQL DW (yet)!

If interested – this is the link to the Azure Feedback feature request to make this available in Azure SQL DW – https://feedback.azure.com/forums/307516-sql-data-warehouse/suggestions/10508991-support-for-spatial-data-type

AND SO — to use spatial data in Azure SQL DW we need to look at alternative methods. Luckily a recent new feature in Azure SQL DB in the form of Elastic Query to Azure SQL DW now gives us the ability to perform these SQL Spatial functions on data within Azure SQL DW via a very simple method!

So the purpose of this blog is to show how to perform native SQL Spatial functions on data within Azure SQL DW.

How does the Solution Work?

These are the steps needed to get this solution moving…

SQL Spatial data types (geometry, geography) are not supported in Azure SQL DW tables, so you must use varbinary(max) data type as the spatial column. SQL spatial data can very happily be stored in varbinary columns!
Create a small standalone Azure SQL DB next to your primary Azure SQL DW – on the same virtual Azure SQL Server.
Create a SQL Login / User on the primary Azure SQL DW which will be used by Azure SQL DB to connect. Grant the database user rights to select from the table containing spatial data.
Create a SQL Credential in Azure SQL DB which uses the above SQL Login to connect to Azure SQL DW
Create an External Data Source in Azure SQL DB that points to the Azure SQL DW and uses the SQL Credential to authenticate
Create an External Table in Azure SQL DB that “points” to the primary table in your existing Azure SQL DW (the one containing spatial data) using the External Data Source
NOW – the Azure SQL DB now becomes the new query entry point for any/all SQL queries that require spatial functionality. Any SELECT on the External Table in the Azure SQL DB will source their data from the existing Azure SQL DW via a Remote Query – and bring selected data back into Azure SQL DB where SQL Spatial functionality is available!

So the setup looks something like this…

Connecting Azure SQL DB and Azure SQL DW

There’s already a great tutorial on MS DOCS on how to connect Azure SQL DB to Azure SQL DW via Elastic Query, so I’m going going to repeat it here.

To see the SQL code for the above steps see the tutorial here – https://docs.microsoft.com/en-us/azure/sql-data-warehouse/tutorial-elastic-query-with-sql-datababase-and-sql-data-warehouse

Setting up SQL Spatial Tables and Data

The following SQL will setup a table in Azure SQL DW which contains 2 rows of sample SQL Spatial data. Note that the data type used for all commands in Azure SQL DW is varbinary(max) and not the standard SQL spatial types.

-- RUN ON AZURE SQL DW:  Create Spatial Table
CREATE TABLE [dbo].[PolyTable]
(
 [PolyName] [varchar](255) NULL,
 [PolyVarBin] [varbinary](max) NOT NULL
)
WITH
(
 DISTRIBUTION = ROUND_ROBIN,
 HEAP
)
-- Create 2 spatial objects in the table
INSERT INTO [dbo].[PolyTable] SELECT 'Point1', CONVERT(VARBINARY(MAX), '0x000000000104810000000000000000004A400100000000804A40EC86F8B28F0C4A409534E4F04E804A40B429BCA617194A40D876E1923B814A40C21CDD2090254A40D8BA0754C5824A404CD37870F1314A401C458041EB844A40BE42F6F2333E4A407A6E2308AC874A407C56BB18504A4A4092CE49F5058B4A407FACD6693E564A40CA4BD8F7F68E4A40A6BA9A8AF7614A40976B86A17C934A4073852740746D4A40C5195E2894984A40D91BE074AD784A4014FA73683A9E4A40FF17C93C9C834A408F34D7E56BA44A404673CDD9398E4A40F48AB7CE24AB4A40820BE7BF7F984A40C763C0FD60B24A40EF48289967A24A404757A7FC1BBA4A406567A449EBAB4A4084AAEC0651C24A40DEF933F304B54A402306CC0CFBCA4A407D5513F9AEBD4A409C985BB614D44A40BAA85803E4C54A4012B7D76698DD4A403A9C3F029FCD4A407FF4184080E74A400D754831DBD44A40BB8C3226C6F14A4072CB281A94DB4A4002E836C363FC4A40EE058C97C5E14A4028E41F8B52074B403CE6A1D76BE74A408E7AD8BF8B124B406A94795E83EC4A405B456575081E4B4037B4270809F14A4082532996C1294B406F31B60AFAF44A4085A944E7AF354B408791DCF753F84A4043BD090DCC414B40E6BA7FBE14FB4A40B52C878F0E4E4B402945F8AB3AFD4A403FE322DF6F5A4B4029891E6DC4FE4A404DD64359E8664B406CCB1B0FB1FF4A401579074D70734B400000000000004B400100000000804B406CCB1B0FB1FF4A40ED86F8B28F8C4B4029891E6DC4FE4A40B529BCA617994B402945F8AB3AFD4A40C31CDD2090A54B40E6BA7FBE14FB4A404DD37870F1B14B408791DCF753F84A40BF42F6F233BE4B406F31B60AFAF44A407D56BB1850CA4B4037B4270809F14A4080ACD6693ED64B406A94795E83EC4A40A7BA9A8AF7E14B403CE6A1D76BE74A407485274074ED4B40EE058C97C5E14A40DA1BE074ADF84B4072CB281A94DB4A400018C93C9C034C400D754831DBD44A404773CDD9390E4C403A9C3F029FCD4A40830BE7BF7F184C40BAA85803E4C54A40F048289967224C407D5513F9AEBD4A406667A449EB2B4C40DEF933F304B54A40DFF933F304354C406567A449EBAB4A407E5513F9AE3D4C40EF48289967A24A40BBA85803E4454C40820BE7BF7F984A403B9C3F029F4D4C404673CDD9398E4A400E754831DB544C40FF17C93C9C834A4073CB281A945B4C40D91BE074AD784A40EE058C97C5614C4073852740746D4A403DE6A1D76B674C40A6BA9A8AF7614A406B94795E836C4C407FACD6693E564A4038B4270809714C407C56BB18504A4A407031B60AFA744C40BE42F6F2333E4A408891DCF753784C404CD37870F1314A40E6BA7FBE147B4C40C21CDD2090254A402A45F8AB3A7D4C40B429BCA617194A402A891E6DC47E4C40EC86F8B28F0C4A406DCB1B0FB17F4C400000000000004A400100000000804C401479074D70F349406DCB1B0FB17F4C404CD64359E8E649402A891E6DC47E4C403EE322DF6FDA49402945F8AB3A7D4C40B42C878F0ECE4940E6BA7FBE147B4C4042BD090DCCC149408791DCF753784C4084A944E7AFB549406F31B60AFA744C4081532996C1A9494038B4270809714C405B456575089E49406B94795E836C4C408E7AD8BF8B9249403CE6A1D76B674C4028E41F8B52874940EE058C97C5614C4001E836C3637C494072CB281A945B4C40BB8C3226C67149400D754831DB544C407FF41840806749403A9C3F029F4D4C4012B7D766985D4940BAA85803E4454C409C985BB6145449407E5513F9AE3D4C402306CC0CFB4A4940DFF933F304354C4084AAEC06514249406567A449EB2B4C404757A7FC1B3A4940EF48289967224C40C763C0FD60324940820BE7BF7F184C40F48AB7CE242B49404773CDD9390E4C409034D7E56B2449400018C93C9C034C4014FA73683A1E4940D91BE074ADF84B40C6195E28941849407385274074ED4B40986B86A17C134940A6BA9A8AF7E14B40CB4BD8F7F60E494080ACD6693ED64B4093CE49F5050B49407D56BB1850CA4B407B6E2308AC074940BF42F6F233BE4B401C458041EB0449404DD37870F1B14B40D9BA0754C5024940C31CDD2090A54B40D976E1923B014940B529BCA617994B409634E4F04E004940ED86F8B28F8C4B4002000000000049400100000000804B409634E4F04E0049401579074D70734B40D976E1923B0149404DD64359E8664B40D9BA0754C50249403FE322DF6F5A4B401C458041EB044940B52C878F0E4E4B407B6E2308AC07494043BD090DCC414B4093CE49F5050B494085A944E7AF354B40CB4BD8F7F60E494082532996C1294B40986B86A17C1349405C456575081E4B40C6195E28941849408F7AD8BF8B124B4014FA73683A1E494029E41F8B52074B409034D7E56B24494002E836C363FC4A40F48AB7CE242B4940BB8C3226C6F14A40C763C0FD6032494080F4184080E74A404757A7FC1B3A494013B7D76698DD4A4084AAEC06514249409D985BB614D44A402306CC0CFB4A49402306CC0CFBCA4A409C985BB61454494084AAEC0651C24A4012B7D766985D49404857A7FC1BBA4A407FF4184080674940C863C0FD60B24A40BB8C3226C6714940F58AB7CE24AB4A4001E836C3637C49409034D7E56BA44A4028E41F8B5287494014FA73683A9E4A408E7AD8BF8B924940C6195E2894984A405B456575089E4940976B86A17C934A4081532996C1A94940CA4BD8F7F68E4A4084A944E7AFB5494093CE49F5058B4A4042BD090DCCC149407B6E2308AC874A40B42C878F0ECE49401C458041EB844A403EE322DF6FDA4940D9BA0754C5824A404CD64359E8E64940D876E1923B814A401479074D70F349409534E4F04E804A400000000000004A400100000000804A4001000000020000000001000000FFFFFFFF0000000003', 1);
INSERT INTO [dbo].[PolyTable] SELECT 'Point2', CONVERT(VARBINARY(MAX), '0x000000000104810000000100000000805340030000000000404077437CD9478653409734E4F04E004040DB145ED38B8C5340DA76E1923B014040628E6E10C8925340DABA0754C5024040A7693CB8F89853401D458041EB04404060217BF9199F53407C6E2308AC0740403FAB5D0C28A5534094CE49F5050B40404056EB341FAB5340CC4BD8F7F60E4040535D4DC5FBB05340996B86A17C134040BAC21320BAB65340C7195E2894184040ED0D70BA56BC534015FA73683A1E4040008C641ECEC153409134D7E56B244040A4B9E6EC1CC75340F68AB7CE242B4040C185F3DF3FCC5340C963C0FD60324040782494CC33D153404957A7FC1B3A4040B333D2A4F5D5534085AAEC0651424040EFFC997982DA53402406CC0CFB4A4040BFAA897CD7DE53409D985BB6145440405D54AC01F2E2534014B7D766985D40401DCE1F81CFE6534081F4184080674040873AA4986DEA5340BC8C3226C6714040B965140DCAED534003E836C3637C4040F702C6CBE2F0534029E41F8B528740401EF3D0EBB5F353408F7AD8BF8B92404035CA3CAF41F653405C456575089E40401CDA138484F8534082532996C1A94040B7185B057DFA534086A944E7AFB54040C348EEFB29FC534043BD090DCCC1404073DD3F5F8AFD5340B52C878F0ECE40409422FC559DFE53403FE322DF6FDA404095448F3662FF53404DD64359E8E64040B6E58D87D8FF53401579074D70F3404000000000000054400100000000004140B6E58D87D8FF5340ED86F8B28F0C414095448F3662FF5340B529BCA6171941409422FC559DFE5340C31CDD209025414073DD3F5F8AFD53404DD37870F1314140C348EEFB29FC5340BF42F6F2333E4140B7185B057DFA53407C56BB18504A41401CDA138484F8534080ACD6693E56414035CA3CAF41F65340A6BA9A8AF76141401EF3D0EBB5F3534073852740746D4140F702C6CBE2F05340D91BE074AD784140B965140DCAED5340FF17C93C9C834140873AA4986DEA53404673CDD9398E41401DCE1F81CFE65340810BE7BF7F9841405D54AC01F2E25340EE48289967A24140BFAA897CD7DE53406567A449EBAB4140EFFC997982DA5340DEF933F304B54140B333D2A4F5D553407D5513F9AEBD4140782494CC33D15340B9A85803E4C54140C185F3DF3FCC5340399C3F029FCD4140A4B9E6EC1CC753400C754831DBD44140008C641ECEC1534071CB281A94DB4140ED0D70BA56BC5340ED058C97C5E14140BAC21320BAB653403BE6A1D76BE74140535D4DC5FBB053406994795E83EC41404056EB341FAB534036B4270809F141403FAB5D0C28A553406E31B60AFAF4414060217BF9199F53408691DCF753F84140A7693CB8F8985340E5BA7FBE14FB4140628E6E10C89253402845F8AB3AFD4140DB145ED38B8C534028891E6DC4FE414077437CD9478653406BCB1B0FB1FF41400100000000805340FFFFFFFFFFFF41408BBC8326B87953406BCB1B0FB1FF414027EBA12C7473534028891E6DC4FE4140A07191EF376D53402845F8AB3AFD41405B96C34707675340E5BA7FBE14FB4140A2DE8406E66053408691DCF753F84140C354A2F3D75A53406E31B60AFAF44140C1A914CBE054534037B4270809F14140AEA2B23A044F53406A94795E83EC4140483DECDF454953403BE6A1D76BE7414015F28F45A9435340ED058C97C5E1414001749BE1313E534072CB281A94DB41405E461913E33853400D754831DBD44140407A0C20C03353403A9C3F029FCD41408ADB6B33CC2E5340BAA85803E4C541404FCC2D5B0A2A53407E5513F9AEBD4140120366867D255340DFF933F304B5414042557683282153406567A449EBAB4140A4AB53FE0D1D5340EF48289967A24140E431E07E30195340820BE7BF7F9841407AC55B67921553404773CDD9398E4140489AEBF2351253400018C93C9C8341400AFD39341D0F5340DA1BE074AD784140E30C2F144A0C534074852740746D4140CC35C350BE095340A6BA9A8AF7614140E525EC7B7B07534080ACD6693E5641404AE7A4FA820553407D56BB18504A41403EB71104D6035340BF42F6F2333E41408E22C0A0750253404DD37870F13141406DDD03AA62015340C31CDD20902541406CBB70C99D005340B529BCA6171941404B1A727827005340ED86F8B28F0C4140010000000000534001000000000041404B1A7278270053401579074D70F340406CBB70C99D0053404DD64359E8E640406DDD03AA620153403FE322DF6FDA40408E22C0A075025340B52C878F0ECE40403EB71104D603534043BD090DCCC140404AE7A4FA8205534085A944E7AFB54040E525EC7B7B07534082532996C1A94040CC35C350BE0953405C456575089E4040E30C2F144A0C53408E7AD8BF8B9240400AFD39341D0F534028E41F8B52874040489AEBF23512534002E836C3637C40407AC55B6792155340BB8C3226C6714040E431E07E3019534080F4184080674040A4AB53FE0D1D534013B7D766985D404042557683282153409D985BB614544040120366867D2553402306CC0CFB4A40404FCC2D5B0A2A534084AAEC06514240408ADB6B33CC2E53404857A7FC1B3A4040407A0C20C0335340C863C0FD603240405E461913E3385340F58AB7CE242B404001749BE1313E53409034D7E56B24404015F28F45A943534015FA73683A1E4040483DECDF45495340C7195E2894184040AEA2B23A044F5340986B86A17C134040C1A914CBE0545340CB4BD8F7F60E4040C354A2F3D75A534094CE49F5050B4040A2DE8406E66053407C6E2308AC0740405B96C347076753401D458041EB044040A07191EF376D5340DABA0754C502404027EBA12C74735340DA76E1923B0140408BBC8326B87953409734E4F04E0040400100000000805340030000000000404001000000020000000001000000FFFFFFFF0000000003', 1)

The following SQL will setup an External Table in Azure SQL DB which will connect across to the Azure SQL DW table containing the SQL Spatial data. Note that for this demo I have called my External Data Source as “ASDW“.

-- RUN ON AZURE SQL DB:  Create External Table Pointing to SQL DW
CREATE EXTERNAL TABLE [dbo].[PolyTable]
(
 [PolyName] [varchar](255) NOT NULL,
 [PolyVarBin] [varbinary](max) NOT NULL
)
WITH 
(
  DATA_SOURCE = [ASDW],
  SCHEMA_NAME = N'dbo',
  OBJECT_NAME = N'PolyTable'
)

Querying SQL Spatial Data from Azure SQL DB

So now that we have the source Azure SQL DW table containing our spatial data, and the External Table in Azure SQL DB pointing to the source table, we can now run some SQL Spatial Queries!

Connect using SQL Management Studio (SSMS) to the Azure SQL DB and run the following “classic” SQL Spatial queries.

For my demo I’m only using a couple of spatial rows, so its worth poining out that you will need to validate this architecture for your data set, specifically at scale (ie if you are pulling back millions of rows over Remote Query). This articile spells out some of the recommended best practices when setting this up at scale – https://docs.microsoft.com/en-us/azure/sql-data-warehouse/how-to-use-elastic-query-with-sql-data-warehouse

Query 1 – Simple Geometry Select

SELECT
    PolyName,
    cast(PolyVarBin as GEOMETRY) as PolyVarBin
FROM [dbo].PolyTable

Query Execution Plan;

Query Result;

Query 2 – Spatial Boundary Function

DECLARE @g GEOMETRY;
SELECT @g = PolyVarBin
FROM [dbo].PolyTable
WHERE [PolyName] = 'Point1';
select @g.STBoundary()
UNION ALL
select @g.STEnvelope();

Result;

Query 3 – Spatial Area Function

DECLARE @g GEOMETRY;
SELECT @g = PolyVarBin
FROM [dbo].PolyTable
WHERE [PolyName] = 'Point1';
select @g.STArea() as PolyArea;

Result;

Query 4 – Spatial Distance Function

DECLARE @g GEOMETRY;
DECLARE @g2 GEOMETRY;
SELECT @g = PolyVarBin
FROM [dbo].PolyTable
WHERE [PolyName] = 'Point1';
SELECT @g2 = PolyVarBin
FROM [dbo].PolyTable
WHERE [PolyName] = 'Point2';
select @g.STDistance(@g2) as DistanceP1toP2;

Result;

Query 5 – The Old Classic “Nearest Neighbour” Spatial Function

DECLARE @g GEOMETRY = 'POINT(57 39)';
SELECT @g = @g.STBuffer(2);
SELECT
 --TOP 1 -- Uncomment this to only show closest polygon
 PolyName +' [' + cast(cast(PolyVarBin as GEOMETRY).STDistance(@g) as varchar(250)) + ']' as PolyName,
 cast(PolyVarBin as GEOMETRY) as PVB,
 cast(PolyVarBin as GEOMETRY).STDistance(@g) as Distance
FROM [dbo].PolyTable
UNION ALL
SELECT 'To Point' as PolyName,
 @g as PVB,
 0 as Distance
ORDER BY cast(PolyVarBin as GEOMETRY).STDistance(@g);

Result;

Summary

So there you have it, a pretty simple method to perform SQL Spatial function on data within Azure SQL DW – even when spatial data types aren’t even supported!

So as usual, and as I always say, please test this out yourself on your own data and validate your scalability needs as your mileage may vary!

References

Some great MS DOCS references I can call out here…

How to use Elastic Query with SQL Data Warehouse – https://docs.microsoft.com/en-us/azure/sql-data-warehouse/how-to-use-elastic-query-with-sql-data-warehouse
Configure Elastic Query with SQL Data Warehouse – https://docs.microsoft.com/en-us/azure/sql-data-warehouse/tutorial-elastic-query-with-sql-datababase-and-sql-data-warehouse
Create External Data Source – https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql
Create External Table – https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql
SQL Server Spatial Data – https://docs.microsoft.com/en-us/sql/relational-databases/spatial/spatial-data-sql-server

Disclaimer: all content on Mr. Fox SQL blog is subject to the disclaimer found here

Using Elastic Query to Support SQL Spatial in Azure SQL DW

How does the Solution Work?

Connecting Azure SQL DB and Azure SQL DW

Setting up SQL Spatial Tables and Data

Querying SQL Spatial Data from Azure SQL DB

Query 1 – Simple Geometry Select

Query 2 – Spatial Boundary Function

Query 3 – Spatial Area Function

Query 4 – Spatial Distance Function

Query 5 – The Old Classic “Nearest Neighbour” Spatial Function

Summary

References

Rate

Share

Share

Rate