Press "Enter" to skip to content

Category: Spatial

Natural Earth In SQL Server

Published 2017-02-22 by Kevin Feasel

Jeff Pries shows how to use the Natural Earth data set in SQL Server:

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.

Comments closed

Visualizing SQL Server Agent Jobs

Published 2016-11-17 by Kevin Feasel

Daniel Hutmacher shows how to visualize SQL Server Agent job runtimes using spatial data types:

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.

Comments closed

Spatial Fragmentation Viewer

Published 2016-08-24 by Kevin Feasel

Slava Murygin writes a spatial query which shows database fragmentation:

As you can see, I definitely have a lot of free space, but my data are so spread across the file and especially up to it’s border, that there is no way to make file size smaller.

If we zoom at the very tail we can figure out the names of tables at the very end of the file, which prevent file from shrinking:

This looks quite a bit like the old Windows 95 defrag tool.  I like it.

Comments closed

Spatial Functions In Hive

Published 2016-08-04 by Kevin Feasel

Constantin Stanca has a couple of posts on using Hive to implement geospatial queries.  First, an overview:

The Esri Geometry API for Java includes geometry objects (e.g. points, lines, and polygons), spatial operations (e.g. intersects, buffer), and spatial indexing. By deploying the library (as a jar) within Hadoop, you are able to build custom MapReduce applications using Java to complete analysis on your spatial data. This can be used as a standalone library, or combined with Spatial Framework for Hadoop to create a SQL like experience.

The Spatial Framework for Hadoop includes among others, the Hive Spatial library with User-Defined Functions and SerDes for spatial analysis in Hive. By enabling this library in Hive, you are able to construct queries using Hive Query Language (HQL), which is very similar to SQL. This allows you to avoid complicated MapReduce algorithms and stick to a more familiar workflow. The API used by the Hive UDF’s could be used by developers building geometry functions for 3rd-party applications using Storm, Spark, HBase etc.

He follows that up with some pieces hive misses compared to SQL Server, Oracle, etc.:

As discussed with ESRI recently, there are no plans to open source all spatial functions currently available for traditional RDBMS like Oracle, SQL Server, or Netezza, as those are commercially licensed packages. The best option to compensate for the 5-10% missing functions is to contribute to ESRI’s open source repository: https://github.com/Esri/spatial-framework-for-hadoop. ESRI does not provide a commercial library for Hive including all spatial functions.

Be sure to check out that second link to get an understanding of exactly what’s missing.  Via Mark Herring.

Comments closed

Graphing CPU Utilization

Published 2016-03-24 by Kevin Feasel

Slava Murygin uses spatial data types to graph CPU utilization on a SQL Server instance:

That diagram provides you about 260 last minutes of Server CPU usage and measured in percents.

As you can see my SQL Server is mostly doing nothing and only during that blog-post writing in the last half and hour or so it is heavily running test scripts to over-utilize my CPU, but it still barely goes more than 60% of CPU (Blue line).

The Red line represents all other processes besides of SQL Server and you can tell if anything else from outside is impacting your performance.

Combined with Glenn Berry’s diagnostic queries, you could generate some quick analytics.  I’d still use R for anything more than slightly complicated, but this is great for those environments in which you don’t have good alternative tooling.

Comments closed

Native Spatial In SQL Server 2016

Published 2016-03-04 by Kevin Feasel

The CSS SQL Server Engineers team points out that spatial types will be a lot faster in the upcoming version of SQL Server:

The SQL Server development team was able to remove the PInvoke and PUnInvoke activities during T-SQL execution for many of the spatial methods.   A critical aspect of the change is that the change is fully compatible across the server and client scenarios. 

The same source code is used to build the managed C++ implementation and the unmanaged C++ implementation.   At the risk of understating this work the C++  managed code can be compiled with the C++ /CLI compiler, creating the managed assembly and a few, cleaver templates and macros bridge the native variations allowing C++ native compilation.  Any change to the code is made in one source file and built in two different ways.

They also have a couple of demos and point out that if you’re using a spatial index appropriately, the performance benefit from switching to 2016 is upwards of 3x.  A 3x performance improvement with no code changes is nothing to sneeze at.

Comments closed

Investigating Cleveland

Published 2016-02-10 by Kevin Feasel

Dave Mattingly goes spatial on Cleveland:

From here, we can:

  • zoom in for more detail

  • hover over a building, road, or other feature to see its name or other column

  • display a label on the results

  • apply filters to only show parts of the data

  • change the widths of the features by changing the STBuffer

  • do lots of other cool stuff

Spatial types and display in SQL Server has always been a weak point for me, so I enjoy seeing the fruits of somebody who is very good at it.

Comments closed

Spatial Data

Published 2016-01-28 by Kevin Feasel

Dave Mattingly has a multi-part series on spatial data.  This is part 5 (with links to the previous).

 

If you’re interested in spatial data, this looks like a fantastic set of blog posts which mesh well with Dave’s presentation on spatial data.

Comments closed

Using GeoJSON Data

Published 2016-01-06 by Kevin Feasel

Jovan Popovic shows how to use data in GeoJSON format.

First, building data in GeoJSON format from a spatial type:

In geometry object are placed type of the spatial data and coordinates. In “property” object can be placed various custom properties such as address line, town, postcode and other information that describe object. SQL Server stores spatial information as geometry or geography types, and also stores additional properties in standard table columns.

Since GeoJSON is JSON, it can be formatted using new FOR JSON clause in SQL Server.

In this example, we are going to format content of Person.Address table that has spatial column SpatialLocation in GeoJSON format using FOR JSON clause.

Then, converting GeoJSON to Geography types:

New OPENJSON function in SQL Server 2016 enables you to parse and load GeoJSON text into SQL Server spatial types.

In this example, I will load GeoJSON text that contains a set of bike share locations in Washington DC. GeoJSON sample is provided ESRI and it can be found in https://github.com/Esri/geojson-layer-js/blob/master/data/dc-bike-share.json

Check them out.

Comments closed