Feeds

Microsoft measures up

A meter is a meter is a meter. Not

Choosing a cloud hosting partner with confidence

Project Watch: Microsoft 2008 The next task in rolling out our 1TB SQL Server 2008 application using Visual Studio 2008 and Windows Server 2008 is to import signed spatial data into the spatial data type. This is the code:


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 what's it doing? We do the conversion via an intermediate - the Well-Known Text (WKT) representation of spatial data as defined by the Open Geospatial Consortium (OGC). As the Text bit suggests, this is a text string. I'm also sure that, while it is undoubtedly Well Known within the OGC, I won't be bringing it up as a topic of conversation in my local pub.

So, we have to convert the signed integers, held in a field called SpatialLocation, into a well-formed WKT string. This is what the following does:


 ('POINT (' + CONVERT(nvarchar, Latitude) + ' ' + CONVERT(nvarchar, Longitude) +')', 4326)

It turns two innocent integers into a WKT string of the form:

POINT (23.2343 34.4544)

WKT as a format is perfectly capable of dealing with more complex structures such as polygons. And the "4326" at the end of the conversion? The 4326 makes it quite clear that we are using good old WGS 84. Those with an interest in the alternatives need only run:


SELECT * FROM sys.spatial_reference_systems

From this, 388 rows of alternatives are returned.

project watch alternatives

Alternative options

To choose one at random, 4604 is the Montserrat 1958 standard. Most of them are based on the meter, but five are based on Clarke's foot (0.304797265 of a meter), one on the Indian foot (0.304799518m) and one on the German legal meter which very, very close to, but annoyingly not exactly the same as, the common or garden meter (1.000013597m).

All of this is, I assume, meat and drink to a geographer, but it is all new territory to a humble database person like myself. It is also an eye opener. Never again will I be defensive about my profession being nerdy.

Secure remote control for conventional and virtual desktops

More from The Register

next story
Netscape Navigator - the browser that started it all - turns 20
It was 20 years ago today, Marc Andreeesen taught the band to play
UNIX greybeards threaten Debian fork over systemd plan
'Veteran Unix Admins' fear desktop emphasis is betraying open source
Sign off my IT project or I’ll PHONE your MUM
Honestly, it’s a piece of piss
Return of the Jedi – Apache reclaims web server crown
.london, .hamburg and .公司 - that's .com in Chinese - storm the web server charts
Chrome 38's new HTML tag support makes fatties FIT and SKINNIER
First browser to protect networks' bandwith using official spec
Admins! Never mind POODLE, there're NEW OpenSSL bugs to splat
Four new patches for open-source crypto libraries
Torvalds CONFESSES: 'I'm pretty good at alienating devs'
Admits to 'a metric ****load' of mistakes during work with Linux collaborators
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.
Cloud and hybrid-cloud data protection for VMware
Learn how quick and easy it is to configure backups and perform restores for VMware environments.
Three 1TB solid state scorchers up for grabs
Big SSDs can be expensive but think big and think free because you could be the lucky winner of one of three 1TB Samsung SSD 840 EVO drives that we’re giving away worth over £300 apiece.
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.
Security for virtualized datacentres
Legacy security solutions are inefficient due to the architectural differences between physical and virtual environments.