Magellan is a distributed execution engine for geospatial analytics on big data. It is implemented on top of Apache Spark and deeply leverages modern database techniques like efficient data layout, code generation and query optimization in order to optimize geospatial queries (further details here).
Although people mentioned in their GitHub page that the 1.0.5 Magellan library is available for Apache Spark 2.3+ clusters, I learned through a very difficult process that the only way to make it work in Azure Databricks is if you have an Apache Spark 2.2.1 cluster with Scala 2.11. The cluster I used for this experience consisted of a Standard_DS3_v2 driver type with 14GB Memory, 4 Cores and auto scaling enabled.
In terms of datasets, I used the NYC Taxicab dataset to create the geometry points and the Magellan NYC Neighbourhoods GeoJSON dataset to extract the polygons. Both datasets were stored in a blob storage and added to Azure Databricks as a mount point.
It sounds like this is much faster than using U-SQL to perform the same task.
I was recently working on a .NET 4.6 based project that was using EF 6 and nUnit for unit testing. While setting up some integration tests against a local SQL database I was receiving this error:
Spatial types and functions are not available for this provider because the assembly ‘Microsoft.SqlServer.Types’ version 10 or higher could not be found.
We had recently been using SQL Server spatial types for tracking geograpic locations and the tests which performed updates and inserts against these fields were failing.
Read on for the setup instructions.
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!
Check out that Azure Feedback item if you’d like to see native spatial support rather than using elastic query. In the meantime, click through to see Rolf’s workaround.
Discerning eyes might notice that SQL Server didn’t shade in the area inside of the polygon — it instead shaded in everything in the world EXCEPT for the interior of our polygon.
If this is the first time you’ve encountered this behavior then you’re probably confused by this behavior — I know I was.
Read on to learn more about left-hand and right-hand polygon specifications and how to translate from one to the other.
Couple of years ago I came up with an algorithm of drawing an ellipse using SQL Server spatial geometry: http://slavasql.blogspot.com/2015/02/drawing-ellipse-in-ssms.html
I’ve used that algorithm to make a sphere and as in my previous blog of drawing 3D Cube I use external procedure to simplify the process.
This time instead of temporary stored procedure I’m using a function to generate Geometrical content.
This has been an enjoyable series so far, showing how to build different shapes using spatial queries.
Hey, there is a time to go level up and instead of drawing Spirals, Fractals and other cool stuff I decided to go 3D!
So, the first my try will be drawing 3D cubes.
As you know, SQL is not an Object Orienting Programming language, and I can’t just simply create an Object “Cube” with certain properties. To create a Cube I need a Stored Procedure:
Click through for a touch of Picasso in your database.
In this script you can play with total number of iterations (@i), with increment value of @R or with width of a line (STBuffer), but generally, you will have always the same “Archimedean” type of a spiral.
Slava shows us how to build a half-dozen different types of spirals, providing sample code for each.
After proceeding through the New Layer Wizard three times to add three layers to the map, we have all of our data present. We now just need to do a little housekeeping to make the map more presentable. We’ll go through each layer and make slight tweaks to each.
Before adjusting the layers, first notice that we essentially have two legends. The Legend box and the Map Scale box. They both give us the same information. Since the Legend is using more real estate, delete it.
There are a lot of steps involved, but the end result is a nice report.
But what about when you need more flexibility in your geographic display? Some examples of this may be wanting to display something that you can’t find a shape file for (maybe all the states and provinces in North America) or maybe you want to dynamically draw the geography based on some property of the dataset. Geospatial data queries to the rescue! Using SQL Server’s native geospatial support, a geospatial query can be created to return something as simple as a point or rectangle, or complex as the geography of an entire country and all of its rivers.
Getting all of the latitude and longitude coordinates to create a useful geospatial query could potentially be an enormous amount of work. Fortunately, that work has already been done in a freely available resource, thanks to Natural Earth and Laurent Dupuis. SQL Server 2012 or greater is recommended for this process.
Click through for a walkthrough as well as some introductory queries to get you started with using the data set.
If all you have is a hammer, everything will eventually start looking like a nail. This is generally known as Maslow’s hammer and refers to the fact that you use the tools you know to solve any problem, regardless if that’s what the problem actually needs. With that said, I frequently need a way to visualize the load distribution of scheduled jobs over a day or week, but I could never be bothered to set up a web server, learn a procedural programming language or build custom visualizations in PowerBI.
So here’s how to do that without leaving Management Studio.
Click through for discussion and link to the code.