Best way to combine spatial data

Question

I have a question regarding spatial data and u-sql. I have a input with polygons and input with points and i want to combine these so that I know in what polygon the point belongs to.

In T sql I would do a left outer join with something like polygon.STintersect(point)= 1

But I suppose that won’t work in u sql. So how can I combine these two inputs?

score 1 · Answer 1 · answered Dec 15 '17 at 14:34

1

You can use the SQL Server type assembly (Microsoft.SqlServer.Types.dll) which provides the implementation of geometry type and its methods.

See details on how to use this assembly within blog: https://blogs.msdn.microsoft.com/azuredatalake/2016/08/26/how-to-register-u-sql-assemblies-in-your-u-sql-catalog/#s4

answered Dec 15 '17 at 14:34

maya-msft

422
2
7

Thanks. I’m aware of that. The problem I’m facing is that you cannot have a function and == 1 in a join clause in u-sql. – Tysklind-MSFT Dec 15 '17 at 15:26
I was thinking of doing a cross join with a where clause but that would give me horrible performance. Another option I have been thinking of is to write my own combine statement... any thoughts? – Tysklind-MSFT Dec 15 '17 at 15:35
1

Custom combine statement should be more performant than cross join, yes. Other than the options you mentioned, I don't have other ideas... – maya-msft Dec 20 '17 at 09:48

score 1 · Accepted Answer · answered Jan 14 '18 at 08:30

1

To summarize the discussion on maya's answer: You currently need to do the join in a combiner or with a CROSS JOIN/WHERE.

You may also want to file a feature request for native Spatial support at http://aka.ms/adlfeedback.

answered Jan 14 '18 at 08:30

Michael Rys

6,684
15
23

Best way to combine spatial data

2 Answers2