SQL
July 28, 2011 Note: This is an outdated post. Recently, the ADO.NET team has released a CTP with Spatial support as a first class citizen of the Entity Framework!!! See the following posts that I wrote as I explored the new API:
http://www.jasonfollas.com/blog/archive/2011/07/20/entity-framework-spatial-first-look.aspx
http://www.jasonfollas.com/blog/archive/2011/07/21/entity-framework-spatial-dbgeography-members.aspx
http://www.jasonfollas.com/blog/archive/2011/07/27/entity-framework-spatial-a-real-world-example.aspx
The Entity Framework does not support using User Defined Types (at least in the SQLCLR sense of the term) as properties of an entity. Yesterday, Julie Lerman contacted me to see if we could find a workaround to this current limitation, particularly for the SQL Server Spatial Types (Geometry and Geography).
Whenever I hear of someone wanting to use Spatial...
On June 5th, I'll be in Columbus (Ohio) speaking to the Microsoft SQL Server User Group (Result Data) about using the XML data type in SQL Server.
XML Capabilities of SQL Server 2008
Jason Follas, Microsoft MVP for SQL Server, will lead a discussion on the XML capabilities of SQL Server 2008. XML-based data is very prevalent in today's data world to the point that every database developer should know how to work with XML. Middle-tier translation of XML into relational data may be inflexible, and often leads to a loss of fidelity over time. One solution is to move the XML...
A quick sample of usage as requested by my friend Jeff McWherter:
Querying for all data in a table where the "zip code polygon" is within 20 miles of a geocode point (must convert 20 miles to meters in the predicate)::
select *
from dbo.fe_2007_us_zcta500
where Boundary.STDistance('POINT(-79.8884595930576 43.2609696686268)') < (20 * 1609.344)
SQL Server Reporting Services is great in the fact that it comes with a handful of different rendering (export) formats out of the box. If a particular report is intended to be paginated and printed, then from my web applications, I will often link to the report with a parameter instructing the render format to be PDF - this way, the user is never taken to the report viewer (they just get an Open/Save As dialog for the PDF itself).
The URL for such a link may resemble the following:
http://server/ReportServer?/path/reportname&rs:Command=Render&rc:LinkTarget=_blank&rs:Format=PDF&SomeParameterValue=1
There are other interesting things that you can do with the rs:Format...
I recently developed a spatially-aware .NET application that did not use SQL Server 2008 as the backend (this enterprise was still on SS2005, but we needed the spatial support in the application today). While the application worked properly on my laptop, it was a huge failboat when deployed to the server environment.
I had previously posted that you can get the Microsoft.SqlServer.Types library from MS Downloads, but it turns out that this alone is not sufficient to allow your application to run. You also need to ensure that the SQL Server 2008 Native Client is also installed (regardless of whether you're...
When you write a query that SELECTs a SQL Server 2008 Spatial type, the returned result is a binary value. Without closely examining the bytes, I just assumed that this was WKB (Well-Known Binary).
Well, as it turns out, the bytes that are returned are simply a serialized version of the .NET object (the UDT has a Write() method that it calls internally to serialize the object in binary). This is NOT directly compatible with WKB.
My discovery source: http://social.msdn.microsoft.com/Forums/en-US/sqlspatial/thread/40ee9466-d7bf-4340-b295-53217ae5128e
I've previously mentioned that the SQL Server 2008 Spatial data types are freely available for use in your .NET applications, regardless of whether you have SQL Server 2008 or not. This allows you to incorporate some powerful spatial capabilities right into your application.
(Look for "Microsoft SQL Server System CLR Types" on this page: http://www.microsoft.com/downloads/details.aspx?FamilyID=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en )
However, in most usage scenarios, there will come a time when you have an instance of a SQL Server spatial object in your .NET application, and need to commit it to your SQL Server 2008 database. How would you do this, without losing fidelity or...
As a matter of principal, not to mention terms of service, I don't often click on ads that appear on my own blog. But, when I see an ad on my site that interests me, I follow it (I believe Google knows who I am, so I don't get "credit" for that click).
Such was the case the other day when I saw a flashy plasma-lamp-like animated advertisement in my site's sidebar with the words "They're not just new forms of data... It's a new form of energy. SQL Server 2008".
I won't ask you to click on the ad from my...
I have mentioned before how the Ring Orientation for the exterior ring of a Polygon is significant when instantiating a SqlGeography object. In this case, a Counter-Clockwise orientation is required so that as an observer walks along the path, the interior of the Polygon is always to their left.
But, what I have never really seen documented (or paid attention to, at least) is the fact that the interior rings, or holes, of a Polygon also have specific Ring Orientation requirements.
In keeping with the "Left-handed" rule, interior rings must be defined in a Clockwise manner - the opposite orientation of the...
A Ring is a list of points such that the starting point and ending point are the same (forming a closed shape). The order the you define the points that make up a Ring - known as Ring Orientation - is significant, for various data formats (including SQL Server's Geography type) imply special meaning for rings that are defined in a clockwise manner as opposed to a counter-clockwise manner.
Given a list of points with no additional context, it can be difficult to determine the Ring Orientation being used.
For example, suppose that you have a generic list of points that...
I found out about 15 minutes ago that SQL Server 2008 RTM version is available for download from MSDN! Congratulations to the SQL Server team!
As I watch the 3 GB download trickle slowly onto my hard drive, I'm left with a few questions at this point, like what version of NETFX will be installed when I run setup? RC0 so nicely installed the .NET 3.5 SP1 Beta Framework onto my machine, and since .NET 3.5 SP1 itself has not RTM'd, what are they going to do? Distribute the beta with the SQL Server RTM bits? Release .NET 3.5 SP1 to manufacturing? ...
In this, the eighth part in a series on the new Spatial Data types in SQL Server 2008, I'll step away from the database and do a little spatial coding using .NET.
Redistributable .NET Library
Up to this point in the series, I have demonstrated a lot of interesting (?) things that you can do with the new Spatial data types (Geometry and Geography) in SQL Server 2008. You might be thinking, "That's swell, and all, but I wish I could do some of that stuff without needing to be tethered to a database." Well, you know what? You can!
I mentioned in...
Tuesday at the NWNUG meeting, Steven Smith spoke on various ways to squeeze performance out of your ASP.NET applications. This was a fantastic talk, and gave me plenty to think about (since ASP.NET is not my forte, I only consider myself to have an intermediate skillset on this topic).
One suggestion that he made involved caching database writes. That is, instead of immediately writing logging-type information to the database for every request, which is a relatively expensive operation considering the small payload size, that you could accumulate them in a short-term cache, and then perform the write operation periodically. Fewer database...
This morning's goal was to quickly install SQL Server 2008 RC0, and then move on with some project work. Let's just say that my project work should resume by this afternoon...
In the interest of disk space, I removed an existing installation of SQL Server 2005 Developer Edition. And then the installation of 2008 RC0 began by installing the "Microsoft.NET Framework 3.5 SP1 (Beta)"... which is probably "install-smell" for me needing to pave my machine when the product finally RTM's. But, I digress...
The installation went pretty smoothly until it came time for the "System Configuration Check" that takes place after you...
The Open Geospatial Consortium's Simple Features specification, which SQL Server 2008's Geometry data type is based upon, defines standards for working with spatial data using a flat-earth (projected planar) model. Ironically, these standards don't exactly cover the intricacies of using an ellipsoidal model, which is needed to "accurately" represent the world that we live in. In other words, the OGC standards define how to work with paper maps of the world, but not globes.
Fortunately, the SQL Server team recognized that that the Geometry type is inadequate for a lot of scenarios, and implemented a second data type just for representing...
In the Part 4 and Part 5 of the series, I demonstrated some instance methods of the Geometry type that returned a new Geometry based on existing instances. In this part, I will concentrate on instance methods and properties of the Geometry type that return scalar values and Points.
STArea, STLength
Typically, your spatial data will represent something from the real world. A LineString may be the collection of points gathered from a GPS device, and together they may represent the path that you took from your home to the office. A Polygon may be the collection of points around the...
A friend of mine (name withheld, I didn't actually ask if I could blog this... ;-) asked for advice to what appears to be a simple problem until you try to implement it. Consider the following somewhat normalized table:
AccountNum
...
In the previous part of this series, I demonstrated instance methods that transformed a single Geometry type into another useful Geometry. In this post, we'll go a step further and show methods that allow two or more instances to interact with one another in order to produce a new Geometry.
For my baseline, I'll use two Polygons that overlap each other:
DECLARE @g geometry
= 'POLYGON((10 10, 40 10, 40 40, 10 40, 10 10))'
DECLARE @h geometry
= 'POLYGON((30 30, 50 30, 50 50, 30 50,...
In this, the 4th post in a series (Part 1, Part 2, Part 3) on the new spatial data types in SQL Server 2008, I'll explain some of the methods that are used to transform a single Geometry instance into another useful Geometry instance. Note that I'm using Geometry for simplicity, but these techniques also work with Geography. Edit: Ok, after starting to take a hard look at Geography, I realized that A LOT of the methods that Geometry offers are not implemented in Geography. :-/ Sorry to mislead you.
Useful TipTo help me to visualize geometries as I explore the...
In the previous parts of this series (Part 1, Part 2), I introduced the Geometry and Geography data types, the various subclasses (Point, LineString, Polygon, etc), and demonstrated a little bit of the Well-Known Text (WKT) syntax. These two posts were primarily informational in nature, and didn't touch SQL Server at all. Let's change that!
Instantiating the UDT
The Geometry and Geography data types are implemented as User Defined Types (UDT) written in .NET. They are automatically installed with the server, and are available for use by any SQL Server 2008 database. For this post, I will use the Geometry type to...
In the first part of this series, I provided a somewhat verbose description of the two different types of Spatial data supported by SQL Server 2008: Geometry (flat Earth model) and Geography (ellipsoidal model). Now, let's dive in a bit deeper, and take a look at some of the specifics.
Standards
From their website, the Open Geospatial Consortium, Inc. (OGC) is described as "a non-profit, international, voluntary consensus standards organization that is leading the development of standards for geospatial and location based services." Unlike me, these folks are the real experts in the field who understand both the problems and the solutions...
Around the 1995 timeframe, two very different systems opened my eyes to the power of geospatial data, particularly how it can be used to enable an analyst to make better business decisions. At the time, I was working in IT at the hub sorting facility for an overnight freight company that specialized in heavyweight cargo. This company not only had a fleet of aircraft flying freight around the country, but also operated a large fleet of trucks (both linehaul and LTL).
Shortly after leaving that company, I was tasked with working on an e-commerce system that had some unique sales territory...
Beyond Relational
The introduction of SQLCLR in SQL Server 2005 allowed for very rich User Defined Types to be utilized. This meant that a developer could create a single object that contained multiple data points (properties) and could also perform calculations internally (methods), yet store that instance in a single field of a single row in a database table. Suddenly, any type of data could be stored and queried in the database, instead of just strings and numbers.
Fast forward to the present time, as Microsoft is celebrating the launch of SQL Server 2008 (notice that in the brilliance that is known...
The problem: A user tries to open a report from SQL Server Reporting Services using Internet Explorer. They are prompted for their username and password, despite the fact that their machine is part of an Active Directory domain. The expectation is that since the SSRS server is on the local intranet, that the logon would happen automatically. The findings: The server was incorrectly being assigned to the [external] Internet zone by Internet Explorer. By default, IE will not automatically log you into a website located in this zone as it would if the website was...
I received a support email yesterday for a system that I had written a few years ago. It seems that they were running year-end reporting, and total combined profit numbers were coming out in the quadrillions. It would have been a nice profit to report, but alas, not an accurate one.
When a number is magnitudes larger than what it should be, and there is division involved in a calculation somewhere, then that smells of floating point math issues. In this case, there was a SUM() being performed on a series of numbers, and instead of the result being a true zero,...
Nothing makes me want to pull my hair out more than writing SQL Queries that need to join two or more tables, with one of those tables containing an "Effective Date" column. Of course, the requirement is inevitably to filter out all records from this join such that the match condition is on some date value from one table and the latest "Effective Date" from the second table that pre-dates the first table's date value. /headdesk
For example, suppose that we have a table that maintains a "capacity" number that changes over time. To maintain historical accuracy, an "Effective Date" column...
While trying to install the SQL Server 2008 November CTP onto a fresh Windows XP VPC image, I came across this error:
---------------------------
Setup Failure
---------------------------
Setup has encountered the following error: Unable to find an entry point named 'MsiSetExternalUIRecord' in DLL 'msi.dll'..
Per Allen White's workaround posted on MS Connect, all that you need to do is manually install Windows Installer 3.1 before running the SQL Server setup.
You can find the Windows Installer 3.1 setup on the CTP DVD (ISO image) in the following directory:
D:\Servers\redist\Windows Installer
(Assume that drive D is your DVD-ROM)
SQL Server Integration Services (SSIS) is a huge step forward from its predecessor, Data Transformation Services (DTS). One thing that they have in common, though, is that there is no one simple method to allow a database user to execute a SSIS package on demand.
SSIS is THE centerpiece of Microsoft's ETL (Extract-Transform-Load) offering. In lay terms, SSIS is the tool that you use to import data into your SQL Server database from any number of sources, and it's a fantastic tool for doing so... so long as your environment doesn't change often... and you execute the package from Visual Studio or the...