Feeds

Back to basics for SQL Server 2008

Hand feeding

The smart choice: opportunity from uncertainty

However there is an important distinction between data collection and storage. Tools like MapPoint return values with N,S,E,W notation, so it was easier for our human convertors if we collected the data in that format. The subsequent conversion to the signed format that SQL Server requires is, of course, trivial:

 


SELECT 
foo,
        (CASE WHEN LatNS = 'S' THEN -1 ELSE 1 END) * Lat AS Latitude,
        (CASE WHEN LongEW = 'W' THEN -1 ELSE 1 END) * Long AS Longitude 
FROM penguin;

Many people felt that we should write our own spatial data types, for example good old Anonymous Coward felt moved to say:

"I am quite frankly incredulous that you have to wait for a new OS and new database design to do this, why not just define a special data type: latitude, longitude, date. Any competent final year computing science graduate could do as much."

We can clearly store spatial data as a pairs of signed values -1.222, 53.327, so why not roll our own data types? One answer is that spatial data isn't just about storing points in isolation. We store it so that we can answer questions like: "Are these points inside this polygon?" We'd need to store the polygon and the obvious place to put it is inside the data type that inevitably makes the internal structure of the data type more complex. This internal structure, combined with a complex model of the Earth's shape, makes indexing essential but non-trivial.

Another answer lies in the functions/methods associated with the data type. Most engines that support spatial data types provide a wide range of these - something in the order of 70. They provide the facility to, for example, translate from Well Known Text (WKT), return the distance between two points, return true if a point is inside a polygon and so on.

So, can you write your own, spatial data types complete with non-spherical modeling, indexing and an appropriate set of methods? Of course. You can write your own RDBMS as well... it's just a matter of how much time you have and what you charge per hour for your time. For most people, us included, it is not a realistic option unless the spatial requirements are relatively simple.

Which brings us very neatly to the next question: how do we import data that is stored as signed values into the spatial data type?

In SQL Server 2008 the answer is that we use one of those built-in methods.


UPDATE
        tblSpatialData
SET
        SpatialLocation = 
                geography::STGeomFromText('POINT (' + 
                        CONVERT(nvarchar, Latitude) +
                        ' ' +
                        CONVERT(nvarchar, Longitude) +
                        ')', 4326)
WHERE
        Latitude IS NOT NULL
AND Longitude IS NOT NULL

So the next (rhetorical) question is, how does it work? What is that 4326 doing?®

Follow Register Developer regular Mark Whitehorn next time on Project Watch: Microsoft 2008 as he continues to roll out a spanking-new 1TB database for several thousand users on Microsoft’s SQL Server 2008, Visual Studio 2008 and Windows Server 2008.

Securing Web Applications Made Simple and Scalable

More from The Register

next story
NO MORE ALL CAPS and other pleasures of Visual Studio 14
Unpicking a packed preview that breaks down ASP.NET
Cheer up, Nokia fans. It can start making mobes again in 18 months
The real winner of the Nokia sale is *drumroll* ... Nokia
Mozilla fixes CRITICAL security holes in Firefox, urges v31 upgrade
Misc memory hazards 'could be exploited' - and guess what, one's a Javascript vuln
Put down that Oracle database patch: It could cost $23,000 per CPU
On-by-default INMEMORY tech a boon for developers ... as long as they can afford it
Google shows off new Chrome OS look
Athena springs full-grown from Chromium project's head
Apple: We'll unleash OS X Yosemite beta on the MASSES on 24 July
Starting today, regular fanbois will be guinea pigs, it tells Reg
HIDDEN packet sniffer spy tech in MILLIONS of iPhones, iPads – expert
Don't panic though – Apple's backdoor is not wide open to all, guru tells us
prev story

Whitepapers

Designing a Defense for Mobile Applications
Learn about the various considerations for defending mobile applications - from the application architecture itself to the myriad testing technologies.
Implementing global e-invoicing with guaranteed legal certainty
Explaining the role local tax compliance plays in successful supply chain management and e-business and how leading global brands are addressing this.
Top 8 considerations to enable and simplify mobility
In this whitepaper learn how to successfully add mobile capabilities simply and cost effectively.
Seven Steps to Software Security
Seven practical steps you can begin to take today to secure your applications and prevent the damages a successful cyber-attack can cause.
Boost IT visibility and business value
How building a great service catalog relieves pressure points and demonstrates the value of IT service management.