Yesterday’s blog post about Azure SQL Database V12 mentioned that one of the features I was particularly interested in seeing/testing were the spatial features. Interestingly, this was not even mentioned as an enhancement in the technical “What’s New” page. This may be due to the fact that the only place where a list of the […]
Bob Beauchemin
SQL Server Blog
Azure SQL Database V12 Preview – Spatial Fully Functional
Posted on: December 12, 2014 3:12 pm
Loading spatial data and cleansing-converting with SQL Server 2012
Posted on: November 29, 2012 2:27 pm
Someone asked, on Twitter, if it were possible to get a set of spatial data that included cities, counties (administrative regions) and countries in database table format for SQL Server spatial. I'd come across just these datasets (and more) as shapefiles at the CDC website and pointed them out. Then came the question about how […]
Loading ShapeFiles into SQL Server 2008 and 2012
Posted on: June 21, 2012 1:50 pm
Since I find myself "in a spatial mood", I thought I'd write one more today. About importing data from shapefiles. According to Wikipedia "The Esri shapefile or simply a shapefile is a popular geospatial vector data format for geographic information systems software". And the question always goes something like this: "Does SQL Server have anything […]
Does everybody get that? (Spatial Index Reprise)
Posted on: June 21, 2012 11:43 am
This post is an example of the "bring your own problem" approach in the classroom, inspired by upcoming SQLskills Immersion Events in August. See previous posting for the reason behind the blog post title. I believe it was John Lennon who wrote: "You say you got a real solution. Well, you know, We'd all love […]
New Wiki posting: SQL Server 2012 spatial features available in SQL Azure now!
Posted on: October 26, 2011 5:23 am
A short while ago, I wrote a blog entry about SQL Server 2012 features that are currently available in SQL Azure. In that post, I mentioned that Ed Katibah would publish an article detailing the current state of the SQL Server 2012 spatial features in SQL Azure, in detail. That article is now available at […]
Spatial index compression in SQL Server Denali
Posted on: September 30, 2011 2:11 pm
Since SQL Server Denali adds support for index compression of spatial indexes, using the same options as data and index compression in SQL Server 2008 and above. As opposed to what I expected at first (because spatial indexes consist of only a few small fixed-length columns), using compression with spatial indexes appears to be quite effective. With […]
A quick set of SQLCLR spatial decomposition functions
Posted on: September 10, 2011 10:35 am
Saw a request on the SQL Server spatial forum this morning for routines that would decompose a geography type to a set of Lat/Longs. A while ago, I was experiemnting with spatial decomposition, and happened to cruft up a couple of SQLCLR table-valued functions to accomplish this. Although the same functions could be written in […]
SQL Azure “version 11” arrives on my server
Posted on: September 6, 2011 12:44 am
I've been trying this out every day or so since I'd heard about the update (originally named the July 2011 Service Release), but I'd forgotten about it for about a week. So I don't exactly know when this happened on my SQL Azure server. But tonight, around midnight, SELECT @@version returns: Microsoft SQL Azure (RTM) […]
Interesting SQL Azure change with next SR
Posted on: June 1, 2011 1:01 pm
In looking at the what's new for SQL Azure (May 2011) page, I came across the following: "Upcoming Increased Precision of Spatial Types: For the next major service release, some intrinsic functions will change and SQL Azure will support increased precision of Spatial Types." There's a few interesting things about this announcement. Firstly, the increased […]
Visualizing the new Denali curve spatial types
Posted on: February 7, 2011 2:37 pm
I'd been fretting over the lack of a true visualizer for the new circularstring and compoundcurve spatial types in SQL Server Denali. I'd installed SSMS from SQL Server 2008 R2, and although it works just fine for Denali spatial types (even though the binary format has changed since 2008/R2), trying to use it with either […]
Using the Denali spatial aggregates on the client
Posted on: January 25, 2011 4:52 am
One of the interesting spatial features in SQL Server Denali is the inclusion of spatial aggregates, namely CollectionAggregate, ConvexHullAggregate, EnvelopeAggregate, and UnionAggregate. Here's an example of unioning two squares together to make a rectangle. create table t1 (g geometry) insert t1 values('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))') insert t1 values('POLYGON((1 0, […]
The nearest neighbor optimization in SQL Server Denali
Posted on: January 3, 2011 2:24 pm
The nearest neighbor query is one of the most common queries against spatial data. How many people haven't gone to a mapping app, typed in their current location and asked for the 10 nearest [your favorite thing goes here]? The obvious way to phrase the spatial query for this, given an STDistance method on the […]
The “other fullglobe feature” in SQL Server Denali
Posted on: December 29, 2010 2:38 pm
Now about that "other" feature that could be called fullglobe in Denali that I mentioned. I don't know which feature will retain the moniker or whether they both will…that's why I'm not in marketing. Here's the deal… the SQL Server spatial geography type is limited to a single hemisphere in size in SQL Server 2008 […]
New curve types in SQL Server Denali are now in OGC spec
Posted on: December 29, 2010 1:14 pm
Once upon a time, I wrote a blog entry about CircularString, CompoundCurve, and CurvePolygon (the new geometric types supported in Denali). And mentioned that they came from ISO-ANSI SQL/MM and weren't in the OGC spec. Well, lo and behold, while browsing the latest OGC Simple Features – Part 1 spec, the last incantation (version 1.2.1) […]
Measuring the earth with SQL Server Denali
Posted on: December 29, 2010 10:29 am
So what's the area of the earth? Well…depends on who's measurement you want to use. There an interesting SQL Server Denali spatial feature known as fullglobe. Actually there's two features with that name, more later; I was using the FULLGLOBE geometric type (geometric type is the term used by OGC, this one only works with SQL […]
SQL Server Denali – the new autogrid spatial index
Posted on: December 23, 2010 5:17 pm
I've been trying out the new Denali "AUTO_GRID" spatial index (that is …USING GEOGRAPHY_AUTO_GRID or GEOMETRY_AUTO_GRID in the CREATE SPATIAL INDEX DDL) on some of my old spatial performance use cases. It's worth repeating (from Ed's excellent whitepaper) that the AUTOGRID index uses 8 levels of tessellation instead of the 4 levels that the original […]
Loading spatial data into SQL Server – SAFE Software Webinar
Posted on: December 7, 2010 1:47 pm
Safe Software, the premier spatial data transformation and loading vendor for SQL Server, has long been known for it flagship FME Desktop and FME Server products (that include a spatial workbench for transformation and loading, support a ton of spatial formats, SSIS integration, and more). But did you know that they've just introduced an affordable, […]
New spatial query hint when using indexing
Posted on: November 9, 2010 4:01 pm
OK, last one for the evening, its getting late in Berlin. And BTW, we might have some Denali goodness here at TechEd. Search for "futures", perhaps. There a new hint for spatial queries in Denali CTP1, namely, SPATIAL_WINDOW_MAX_CELLS. This is a table hint that, as far as I can tell, this hint affects the query […]
Repost/Revision Updated serialization formats for SQL Server spatial types
Posted on: November 9, 2010 2:49 pm
SQL Server Denali CTP1 was released today. I don’t want to be the fiftieth person to post the link to the download; reference Ed's blog posting for the location. This is a repost (with some corrections now that the CTP is actually out) of a blog posting I did when someone discovered a web location […]
Geocoding and AdventureWorks 2008 data
Posted on: April 19, 2010 11:54 am
I did a few demos in the spatial visualization talk at SQLConnections using the AdventureWorks 2008 data. The Person.Address table is all geocoded and so I did a quick map in SQL Server Reporting Services 2008 R2 using individual addresses. Followed up later in the talk with addresses using ESRI's MapIt product, their Spatial Data […]
Trouble seeing “Birth of Venus” image in the SSMS spatial results tab?
Posted on: February 28, 2010 8:28 pm
Michael J Swart posted a SQL query on his blog that produced a rendition of Botticelli’s Birth of Venus, in the spatial results tab of SQL Server Management Studio when executed. But, when everyone rushed over in anxious anticipation, some folks could execute the query but couldn't see the picture on their 64-bit editions. Here's […]
Two of the coolest demos in the last month
Posted on: November 23, 2009 1:25 pm
Everyone is still recovering from the November conference weeks (reminds me of sweeps week on TV). As a "SQL guy" the conferences in the last three weeks that would interest me include: SQLPASS-US, SQL Connections, TechEd Europe, PDC, and SQLBits. Why folks insist on holding all of them in the same three week period is […]
My MSDN Column on Visualizing Spatial Data is available
Posted on: November 18, 2009 4:02 pm
My latest MSDN magazine column on Visualizing Spatial Data is out in the Novemeber issue. It covers three ways to visualize SQL Server 2008 spatial data: SQL Server 2008 R2 Reporting Services Map Control ESRI MapIt Version 1.0 MapPoint Add-In for SQL Server 2008 Interestingly because the lead times are rather long for the column, […]
Be careful with EMPTY/NULL values and spatial indexes
Posted on: September 22, 2009 12:35 pm
There are two slightly different ways of representing non-existing spatial data, the "empty geometry" concept and database NULL. You can read about the subtle differences between them here. If you're using a spatial index, either of these types have the ability to cause query perforamance problems. Let's take, as an example, a table with 10000 rows, all of […]
How’s about a map on your SSRS report?
Posted on: August 21, 2009 1:53 pm
When I downloaded SQL Server 2008 R2 CTP2 last week, I went right by (for now) the cool Gemini features and even Multi Server Management and went straight for the new SSRS Map Control. After explaining the SQL Server 2008 spatial data types, the spatial methods, and SRIDs to folks until they glazed over, and […]
Make KML documents from SQL Server’s geography data type
Posted on: August 7, 2009 11:39 am
Today I posted the code to a projcect I've been working on, a library that creates KML documents from SQL Server 2008 geography instances or queries. It's called SQLServerToKML and is available on CodePlex here. There's no releases as such yet, just the library and a rudimentary document showing how to us it. Hope it's […]
MapPoint Add-In For SQL Server Spatial shipped
Posted on: August 3, 2009 11:34 am
Last week, Microsoft released the MapPoint 2009 Add-In for SQL Server 2008 Spatial. You can get it here. This add in allows you to add layers to the map from queries against SQL Server spatial geography columns. It's more of an entry level mapping program that an IT-level (like the ESRI offerings) mapping program. You can […]
Spatial Indexes and ANSI JOIN – ON syntax
Posted on: July 20, 2009 1:51 pm
During a talk about spatial indexes and performance last week, I was surprised by a question about using ANSI-92 JOIN – ON syntax in spatial queries. I'd coded: SELECT * FROM geonames.dbo.geonames g JOIN Sample_USA.dbo.Counties c ON g.GEOG.STIntersects(c.geog) = 1 WHERE c.ID = 1569 Later I learned that the question was prompted by the […]
ESRI User Conference and MapIt
Posted on: July 19, 2009 9:14 pm
Last week I attended my first-ever ESRI conference in San Diego. Although the "star of the show" was version 9.3.1 and the upcoming version 9.4 of the ARCGIS series of products, I most enjoyed the presentations about the new ESRI MapIt product. This product is a collection of tools and toolkits to allow import, preparation, […]
At the ESRI user conference next week
Posted on: July 8, 2009 10:53 am
Just got a mail message from an old friend who asked why I'd dropped out of sight in the past month of so. I've been in "extended partial vacation mode" for the last month or so and haven't blogged in a while. But I'm "still alive and well" (Johnny Winter was at the Portland blues […]
Book review – Beginning Spatial with SQL Server 2008
Posted on: May 30, 2009 8:22 am
About a month ago, I received a copy of "Beginning Spatial with SQL Server 2008" by Alastair Aitchison. I've become acquainted with Alastair through his frequent postings on the SQL Server Spatial forum (under the login Tanoshimi), where he's always been very helpful and patient with folks starting out with spatial data concepts and SQL […]
Talking about spatial data on RunAs Radio
Posted on: May 1, 2009 11:24 am
My interview with the folks at RunAs Radio is out. I'm discussing one of my favorite topics, spatial data and spatial in SQL Server 2008, with Richard and Greg. This wandered off into some interesting possible usages for this data. Catch it here.
How to ensure your spatial index is being used
Posted on: April 25, 2009 10:02 pm
I've answered quite a few questions lately about how to make sure that your spatial index is being used. Use of a spatial index with a large or complex spatial dataset can mean the difference between a query that takes minutes to execute and subsecond execution. You also want to use an index hint as […]
Spatial Indexes in SP1, almost no hints required
Posted on: March 7, 2009 7:29 pm
On experimenting with the CTP of SQL Server 2008 SP1, I found that there were some changes made to the costing algorithm, vis-a-vis spatial indexes. In the RTM version, spatial indexes weren't uses as often as they could be, because the query costing was too high compared to the spatial filters. This made programmers resort […]
My First MSDN column is live today
Posted on: January 28, 2009 9:02 pm
I've become a columnist for MSDN magazine and my first column is available in the online edition, Feb 2009 issue today. I'm doing a column every few months called "Under The Table", about database-centric development. The first column is about one of the most exciting (IMHO) new features in SQL Server 2008, spatial data type […]
Spatial Methods: What’s with the ‘ST’?
Posted on: January 9, 2009 4:31 pm
When SQL Server's spatial data types were introduced, people asked "Why does every standard method begin with ST" (e.g. STIsEmpty). SQL Server docs always cite the OGC (Open Geospatial Consortium) specifications, but OGC's methods do not begin with ST. Where did it come from? As far as I can figure the "ST" before every standard […]
SQL Server Spatial: EMPTY vs. NULL
Posted on: January 9, 2009 4:16 pm
Yesterday a friend asked me if it was better to always use a value of GEOMETRYCOLLECTION EMPTY as an alternative to making a geometry/geography column nullable. OGC has its own concept of database NULL, that is [GEOMETRY] EMPTY, where [GEOMETRY] can be any valid geometry subtype (that is 'POINT EMPTY', 'LINESTRING EMPTY', etc). When I asked why […]
Spatial Index Diagnostic Procs – The Rest of the Story
Posted on: December 18, 2008 1:07 pm
I've devoted the last few blog entries to describing what's in that spatial index analysis proc output and how to use it and interpret the results. Just wanted to describe the rest of the information and how the information relates to what you'd find in a query plan that uses a spatial index. The procs […]
Spatial Index Diagnostic Procs – How to specify query sample
Posted on: December 17, 2008 2:54 pm
A little more about the query sample that gets fed into the spatial index procs. Query sample is a singleton geometry or geography. It's not a query. So it's not as straightforward as "here's a spatial query that could use an index, run this query and show me the figures". So this procedure is not […]
Spatial Index Diagnostic Procs – Filter Output
Posted on: December 17, 2008 12:13 pm
Last post discussed the filters. The procs report some raw numbers and some derived numbers. Here's a cheat sheet, although the info is all in the BOL. N = Number of rows in the table O = Number of rows output P = Number of rows selected by primary filter (by the index) S = […]
Spatial Index Diagnostic Procs – Filters
Posted on: December 17, 2008 11:22 am
The spatial index diagnostic procs' most enlightening pieces of information have to do with the filter counts and efficiencies reported at the end of the set of columns or XML document output. But what are these filters exactly and what do the results mean? The proc output refers to three filters: 1. Primary Filter 2. […]
Spatial Index Diagnostic Procs – Intro
Posted on: December 17, 2008 10:22 am
I've been looking at the spatial index stored procedures (sp_help_spatial_geography_index_xml and friends) a little harder recently, in an attempt to help answer two questions. 1. If I have a spatial query, why does/doesn't it use my spatial index? 2. If I have a specific spatial query in mind, what are the best values to choose […]
Using the spatial index diagnostic stored procedures
Posted on: November 5, 2008 6:49 am
I remember hearing during the SQL Server 2008 beta that there would be diagnostic stored procedures that would produce information about spatial indexes and help in troubleshooting why an index was not being used. In addition, these procedures would help in determining the best spatial index. A few weeks ago I had the good fortune […]
On hinting spatial indexes and query complexity
Posted on: September 6, 2008 11:23 am
After reading Isaac's recent blog posting about spatial queries and index hinting, I thought I might add some information based on a query I was working with this week. Sometimes if you have a query that's too syntactically complex, hinting won't work. In these cases you can cause the index to be used (or at least […]
A helper function for ring-orientation in the SQL Server 2008 geography data type
Posted on: August 21, 2008 5:25 am
Folks have always had trouble with the fact that ring orientation is required with spatial instances if you're using SQL Server 2008's geography data type, but not with the geometry data type. For an explanation of the need for this, reference Isaac Kunen's blog entry here and Ed Katibah's blog entry (link in next paragraph). In Ed's […]
Using SQL Server 2008 spatial and the Virtual Earth map control – 2
Posted on: February 13, 2008 11:53 pm
This entry is a continuation of the previous posting on how to use SQL Server 2008 spatial data and Virtual Earth. This entry discusses your web service and SQL Server code choices. The point of the web service is to translate one of the output formats of SQL Server 2008 spatial to a format this Virtual […]
Using SQL Server 2008 spatial and the Virtual Earth map control – 1
Posted on: February 13, 2008 11:52 pm
Last week at the SQL Server 2008 Jumpstart I showed an example of using SQL Server 2008 spatial data types with Virtual Earth. I showed a single coding style. There really is a plethora of coding styles to using these together, and I'd like to describe some of the most common ones. I'd like to […]
Spatial Data, a niche or a tool for the masses?
Posted on: October 25, 2007 7:54 pm
I'll have to admit it, when I first saw that SQL Server 2008 was adding spatial data support, I thought of it as a niche. The province of geographers, cartographers, and maybe a few others. Complex, involving a lot of higher mathematics, each province having their own geographic encoding, and so on… And that level […]
