Feeds

Back to basics for SQL Server 2008

Hand feeding

Top 5 reasons to deploy VMware with Tegile

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.

Secure remote control for conventional and virtual desktops

More from The Register

next story
Microsoft to bake Skype into IE, without plugins
Redmond thinks the Object Real-Time Communications API for WebRTC is ready to roll
Microsoft promises Windows 10 will mean two-factor auth for all
Sneak peek at security features Redmond's baking into new OS
Mozilla: Spidermonkey ATE Apple's JavaScriptCore, THRASHED Google V8
Moz man claims the win on rivals' own benchmarks
FTDI yanks chip-bricking driver from Windows Update, vows to fight on
Next driver to battle fake chips with 'non-invasive' methods
PEAK APPLE: iOS 8 is least popular Cupertino mobile OS in all of HUMAN HISTORY
'Nerd release' finally staggers past 50 per cent adoption
DEATH by PowerPoint: Microsoft warns of 0-day attack hidden in slides
Might put out patch in update, might chuck it out sooner
Ubuntu 14.10 tries pulling a Steve Ballmer on cloudy offerings
Oi, Windows, centOS and openSUSE – behave, we're all friends here
Was ist das? Eine neue Suse Linux Enterprise? Ausgezeichnet!
Version 12 first major-number Suse release since 2009
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.
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.
Top 5 reasons to deploy VMware with Tegile
Data demand and the rise of virtualization is challenging IT teams to deliver storage performance, scalability and capacity that can keep up, while maximizing efficiency.
Mitigating web security risk with SSL certificates
Web-based systems are essential tools for running business processes and delivering services to customers.