In older versions (<= 2012), SSMS provides error location information relative to the batch starting point of the erroneous query. This means if you have a window full of T-SQL batch statements, your error message will be relative to the last “GO” in the editor before the syntax error:
In SSMS 2014, batch start locations aren’t considered:
Starting in SSMS 2016, the error messages revert back to counting from the current batch but also indicate the line number where the batch begins:
While all of these error messages point me in the right direction, I’m too lazy to try and remember how each version of SSMS refers to the error location. This becomes particularly problematic when jumping around environments and not being able to use a consistent version of SSMS.
There is one feature that works consistently though that makes navigating to the specific error lines easily.
You candouble click on the error message to be taken directly to the line number referenced!
I know this sounds simple, but it’s one of those small things I never thought of trying until I saw someone else do it. Little did I know what I was missing out on – especially in those giant hundreds/thousands of line long scripts. but now that I know about it it’s the only way I locate my query errors.
This functionality also works if you have multiple errors listed in the Messages window.
We can then take a look at SQL Server Management Studio’s Spatial Results tab and see our polygon of Colorado drawn on a map. You might notice something looks a little funny with this picture though:
WHY IS MY POLYGON AREA INVERTED?!!??!
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.
The Left-Hand/Right-Hand Rules
There is a logical explanation though for why SQL Server is seemingly shading in the wrong part of our polygon.
SQL Server’s geography datatype follows the “left-hand rule” when determining which side of the polygon should be shaded. On the contrary, the GeoJSON specification specifies objects should be formed following the “right-hand rule.”
The left hand rule works like this: imagine you are walking the path of polygon – whatever is to the left of the line you are walking is what is considered the “interior” of that polygon.
So if we draw arrows that point in the direction that the coordinates are listed in our GeoJSON, you’ll notice we are making our polygon in a clockwise direction:
If you imagine yourself walking along this line in the direction specified, you’ll quickly see why SQL Server shades the “outside” of the polygon: following the left-hand rule, everything except for the state of Colorado is considered the interior of our polygon shape.
Reversing Polygon Direction
So the problem here is that our polygon data was encoded in a different direction than the SQL Server geography datatype expects.
One way to fix this is to correct our source data by reordering the points so that the polygon is drawn in a counter-clockwise direction:
--Note:The middle three sets of points have been included inreverse order whilethe first/last point have stayed the same
This is pretty easy to do with a polygon that only has five points, but this would be a huge pain for a polygon with hundreds or thousands of points.
So how do we solve this in a more efficient manner?
A significant portion of Yellowstone National Park sits on top of a supervolcano. Although it’s not likely to erupt any time soon, the park is constantly monitored for geological events like earthquakes.
This week I want to take a look at how you can import this earthquake data, encoded in GeoJSON format, into SQL Server in order to be able to analyze it using SQL Server’s spatial functions.
The source for the data we’ll be using is the 30-day earthquake feed from the USGS. This data is encoded in the GeoJSON format, a specification that makes it easy to share spatial data via JSON. To get an idea of how it looks, here’s an extract:
The key thing we’ll be examining in this data is the “features” array: it contains one feature object for each earthquake that’s been recorded in the past 30 days. You can see the “geometry” child object contains lat/long coordinates that we’ll be importing into SQL Server.
We use OPENJSON() to parse our JSON hierarchy and then concatenate together the lat and long values into our well known text format to be able to use it with SQL Server’s spatial function STPointFromText:
What results is our earthquake data all nicely parsed out into our dbo.EarthquakeData table:
What about Yellowstone?
The above data includes earthquakes from around world. Since we only want to examine earthquakes in Yellowstone, we’ll need to filter the data out.
There’s a handy Place column in the data that we could probably add a LIKE ‘%yellowstone%’ filter to – but this is a post about spatial data in SQL, we can do better!
The Wyoming State Geological Survey website has Shapefiles for the boundary of Yellowstone National Park. Since we are practicing our GeoJSON import skills, I converted the Shapefiles to GeoJSON using an online converter and the resulting data looks like this:
Now we have two tables: dbo.EarthquakeData and dbo.ParkBoundaries. What we want to do is select only the Earthquake data points that fall within the boundaries of Yellowstone National Park.
This is easy to do using the STIntersects spatial function, which returns a “1” for any rows where one geography instance (our lat/long earthquake coordinate) intersects another geography instance (our park boundary):
CROSS JOIN dbo.ParkBoundariesb
ANDb.ParkName='Yellowstone National Park'
The rest is up to you
So all it takes to import GeoJSON data into SQL Server is knowing how to use SQL Server’s JSON functions.
Once geographical data is imported into geography data types, SQL Server’s spatial functions offer lots of flexibility for how to efficiently slice and dice the data.