Feeds

Back to basics for SQL Server 2008

Hand feeding

Providing a secure and efficient Helpdesk

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.

Beginner's guide to SSL certificates

More from The Register

next story
ONE MILLION people already running Windows 10
A third of them are doing it in VMs, but early feedback focuses on frippery
Netscape Navigator - the browser that started it all - turns 20
It was 20 years ago today, Marc Andreeesen taught the band to play
Sway: Microsoft's new Office app doesn't have an Undo function
Content aggregation, meet the workplace ... oh
Sign off my IT project or I’ll PHONE your MUM
Honestly, it’s a piece of piss
Do Moan! MONSTER 6-day EMAIL OUTAGE hits Domain Monster
Customers freaked out by frightful service
Return of the Jedi – Apache reclaims web server crown
.london, .hamburg and .公司 - that's .com in Chinese - storm the web server charts
NetWare sales revive in China thanks to that man Snowden
If it ain't Microsoft, it's in fashion behind the Great Firewall
prev story

Whitepapers

Forging a new future with identity relationship management
Learn about ForgeRock's next generation IRM platform and how it is designed to empower CEOS's and enterprises to engage with consumers.
Win a year’s supply of chocolate
There is no techie angle to this competition so we're not going to pretend there is, but everyone loves chocolate so who cares.
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.
High Performance for All
While HPC is not new, it has traditionally been seen as a specialist area – is it now geared up to meet more mainstream requirements?
Saudi Petroleum chooses Tegile storage solution
A storage solution that addresses company growth and performance for business-critical applications of caseware archive and search along with other key operational systems.