Feeds

Back to basics for SQL Server 2008

Hand feeding

Choosing a cloud hosting partner with confidence

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.

Intelligent flash storage arrays

More from The Register

next story
Be real, Apple: In-app goodie grab games AREN'T FREE – EU
Cupertino stands down after Euro legal threats
Download alert: Nearly ALL top 100 Android, iOS paid apps hacked
Attack of the Clones? Yeah, but much, much scarier – report
You stupid BRICK! PCs running Avast AV can't handle Windows fixes
Fix issued, fingers pointed, forums in flames
Microsoft: Your Linux Docker containers are now OURS to command
New tool lets admins wrangle Linux apps from Windows
Bada-Bing! Mozilla flips Firefox to YAHOO! for search
Microsoft system will be the default for browser in US until 2020
Facebook, working on Facebook at Work, works on Facebook. At Work
You don't want your cat or drunk pics at the office
Soz, web devs: Google snatches its Wallet off the table
Killing off web service in 3 months... but app-happy bonkers are fine
prev story

Whitepapers

Why cloud backup?
Combining the latest advancements in disk-based backup with secure, integrated, cloud technologies offer organizations fast and assured recovery of their critical enterprise data.
A strategic approach to identity relationship management
ForgeRock commissioned Forrester to evaluate companies’ IAM practices and requirements when it comes to customer-facing scenarios versus employee-facing ones.
5 critical considerations for enterprise cloud backup
Key considerations when evaluating cloud backup solutions to ensure adequate protection security and availability of enterprise data.
Reg Reader Research: SaaS based Email and Office Productivity Tools
Read this Reg reader report which provides advice and guidance for SMBs towards the use of SaaS based email and Office productivity tools.
Choosing a cloud hosting partner with confidence
Download Choosing a Cloud Hosting Provider with Confidence to learn more about cloud computing - the new opportunities and new security challenges.