The session voting for Developer Day Scotland has opened.
Please go and select the sessions that you want to see.
Category Archives: Misc
Different ways to add point data in SQL Server 2008
The spatial data can be added to a table by specifying the column type of geometry or geography. The exact detail of what is in the column can be varied as a spatial column can represent a point, line string, and polygon and so on. For example, to create a table that represents the venues of developer events that I’ve been to might look like this:
CREATE TABLE Venue
(
Id INT IDENTITY(1,1) NOT NULL,
Name NVARCHAR(256),
Location geography
)
There are a number of different ways to insert data. Points, have the most varied set of options.
First of all there is the standard STGeomFromText:
INSERT INTO Venue
VALUES(
'HBOS',
geography::STGeomFromText(
'POINT(55.9271035250276 -3.29431266523898)',4326));
The function takes two parameters; the first is the Well Known Text (WKT) representation of the geometry, in this case a point, and the second is the SRID. The example above shows the location of the SQL Server UG events held in one of the conference rooms at HBOS’s offices in Sighthill, Edinburgh.
Next is the extended method Parse. I should mention that there are two types of methods with regards to standards. There are a group of methods that comply with the OGC standards (these are all prefixed with ST). Then there are “extended methods”. These are not standards compliant and have added, I’m guessing, in order to improve the capabilities to some extent over the standards.
An example of the Parse method:
INSERT INTO Venue
VALUES(
'Glasgow Caledonian University',
geography::Parse(
'POINT(55.8659449685365 -4.25072511658072)'));
The function takes only one parameter, which is the WKT. There is no SRID, but it is set to 4326 (WGS84). The example above shows the location of the Scottish Developers events held in the Continuing Professional Development Centre in Glasgow Caledonian University.
The next method is to use Well Known Binary (WKB). I won’t, however, be detailing the format of the binary. At present I would simply like to demonstrate that it can be done.
An example of WKB:
INSERT INTO Venue
VALUES(
'Dundee University',
geography::STGeomFromWKB(0x01010000000700ECFAD03A4C4001008000B5DF07C0, 4326));
The function, like its WKT counterpart, takes two parameters. The first is the binary representation of the spatial data, while the second is the SRID. The example above is the location of the North East Scotland .NET User Group who meet at Dundee University.
Next is another extended method, Point. For example:
INSERT INTO Venue
VALUES(
'Microsoft Campus, TVP',
geography::Point(51.4618933852762, -0.926690306514502, 4326));
The function takes three parameters, the latitude, the longitude and the SRID. The above example is the location of the Microsoft Campus at Thames Valley Park in Reading where events like DDD are held.
Finally, the standard function, STPointFromText, is used. For example:
INSERT INTO Venue
VALUES(
'Microsoft Edinburgh Office',
geography::STPointFromText('POINT(55.9523783996701 -3.2051030639559)', 4326));
The function takes WKT as did Parse and STGeomFromText, however, it is constrained to only WKT that represent points. If the WKT represents something else the method will fail. If, say, a linestring was supplied then an error message would be generated such as “Expected POINT at position 1. The input has LINES.” The example above shows the location of Microsoft’s Edinburgh office.
The result of adding all this information will produce a table with the following data:
| Id | Name | Location |
| 1 | HBOS | POINT (55.9271035250276 -3.29431266523898) |
| 2 | Glasgow Caledonian University | POINT (55.8659449685365 -4.25072511658072) |
| 3 | Dundee University | POINT (56.4595025684685 -2.98423195257783) |
| 4 | Microsoft Campus, TVP | POINT (51.4618933852762 -0.926690306514502) |
| 5 | Microsoft Edinburgh Office | POINT (55.9523783996701 -3.2051030639559) |
Spatial References in SQL Server 2008
In SQL Server 2008, each piece of spatial data must be tagged with an SRID (Spatial Reference Identifier). Geometry types can have a SRID of 0 (which means undefined) but geographies must have a defined SRID. By default geography types use an SRID of 4326 which equates to WGS84. Spatial operations can only occur between spatial types with the same SRID. The result of spatial operations between two pieces of data with different SRIDs is null.
The geography needs an SRID applied to it because, in order to perform calculations, it needs to know the details of the ellipsoid in use. That information is not required to perform calculations on a geometry type.
Although SRIDs are not required on geometry objects it may be useful to apply them if data using different projections is to coexist in the same database. It will provide the safety net of returning null if spatial operations are attempted between two geometries in different projections.
It is possible to find out the available SRIDs in the database by querying the system view sys.spatial_reference_systems. The view will detail the SRID (spatial_reference_id) and its attributes.
Currently, all SRIDs in the system are defined by the European Petroleum Survey Group, hence the value of the Authority (authority_name) column is “EPSG”. The WKT (well_known_text) describes the datum, ellipsoid and units of the geographic coordinate system. The Units (unit_of_measure) column describes in English the units of the projected coordinate system. Finally, the Factor (unit_conversion_factor) is the conversion factor between the units in the projected coordinate system to SI units.
For example:
| SRID | Authority | WKT | Units | Factor |
| 4157 | EPSG | GEOGCS[“Mount Dillon”, DATUM[“Mount Dillon”, ELLIPSOID[“Clarke 1858”, 6378293.64520876, 294.260676369261]], PRIMEM[“Greenwich”, 0], UNIT[“Degree”, 0.0174532925199433]] | Clarke’s Foot | 0.304797265 |
| 4243 | EPSG | GEOGCS[“Kalianpur 1880”, DATUM[“Kalianpur 1880”, ELLIPSOID[“Everest (1830 Definition)”, 6377299.36559538, 300.8017]], PRIMEM[“Greenwich”, 0], UNIT[“Degree”, 0.0174532925199433]] | Indian Foot | 0.304799518 |
| 4268 | EPSG | GEOGCS[“NAD27 Michigan”, DATUM[“NAD Michigan”, ELLIPSOID[“Clarke 1866 Michigan”, 6378450.0475489, 294.978697164674]], PRIMEM[“Greenwich”, 0], UNIT[“Degree”, 0.0174532925199433]] | US Survey Foot | 0.30480061 |
| 4277 | EPSG | GEOGCS[“OSGB 1936”, DATUM[“OSGB 1936”, ELLIPSOID[“Airy 1830”, 6377563.396, 299.3249646]], PRIMEM[“Greenwich”, 0], UNIT[“Degree”, 0.0174532925199433]] | metre | 1 |
| 4293 | EPSG | GEOGCS[“Schwarzeck”, DATUM[“Schwarzeck”, ELLIPSOID[“Bessel Namibia (GLM)”, 6377483.86528042, 299.1528128]], PRIMEM[“Greenwich”, 0], UNIT[“Degree”, 0.0174532925199433]] | German legal metre | 1.000013597 |
| 4326 | EPSG | GEOGCS[“WGS 84”, DATUM[“World Geodetic System 1984”, ELLIPSOID[“WGS 84”, 6378137, 298.257223563]], PRIMEM[“Greenwich”, 0], UNIT[“Degree”, 0.0174532925199433]] | metre | 1 |
| 4748 | EPSG | GEOGCS[“Vanua Levu 1915”, DATUM[“Vanua Levu 1915”, ELLIPSOID[“Clarke 1880 (international foot)”, 6378306.3696, 293.46630765563]], PRIMEM[“Greenwich”, 0], UNIT[“Degree”, 0.0174532925199433]] | foot | 0.3048 |
For more information about WKT, Wikipedia has a good overview and acts as a jumping off point to more information.
Technorati Tags: sql server 2008,katmai,spatial reference,wkt,coordinate system,projection system,srid,well known text
Scottish Developers Events newsletter
We’re in to February already! Time seems to get faster, and so does the pace at which technology changes. But never fear because we have some excellent events coming to help you keep up.
In just over a week Richard Fennell will be talking about continuous integration with Team Foundation Server in Glasgow. The very next day I’ll be talking about Spatial Queries in SQL Server 2008 in Edinburgh, and if you can’t make it then I’ll be repeating it in Dundee later in the month. Early in March we have Oliver Sturm taking an entire afternoon to tell you about writing business applications with WPF. April brings us back to SQL Server again, and in May we have Developer Day Scotland – our first conference.
Continuous Integration with Team Foundation Server
Glasgow: 12-Feb-2008
Speaker: Richard Fennell
URL: http://www.scottishdevelopers.com/modules/extcal/event.php?event=39
How many times have you heard the developer say ‘but it works on my PC ’? How much time have you wasted trying to get a complex solution to build on a new PC? In this session I will show how continuous integration, the automated building and testing of projects whenever files are checked in, can be used to improve the quality of any software development project. Helping to catch and resolve problems as soon as possible in the development cycle; not waiting until to delivery phase of a project to find there are integration problems. The session will include demos of continuous integration using Visual Studio Team Server, as well as discussions of integration with other system such as MSTest and Virtual Server.
Richard Fennell is a founding Director of Black Marble, a Microsoft Gold partner based in Yorkshire. He is a regular speaker at community events, speaking at SQLBits, DDD3, 4, 5 & 6 as well as variety of user groups and free events run by his company. He has a degree in Computer Science, is a Member of the BCS, a Chartered IT professional and Chartered Engineer as well as holding MCSD.NET and MCAD accreditations.
Venue: The venue is Glasgow Caledonian University’s Continuing Professional Development Centre. For directions to the university see Google Maps and the campus map (We’re in building 2 on the map, marked CPD Centre)
JetBrains have kindly given us a license for ReSharper (worth $199) to give away at this event.
Registration:
The event is FREE. If you want to attend please just turn up and feel free to bring a guest. The doors open at 18:30 for a 19:00 start.
SQL Server User Group Meeting
Edinburgh: 13-Feb-2008
Speakers: Colin Angus Mackay and Martin Bell
URL: http://www.sqlserverfaq.com/?eid=104
Colin Angus Mackay: Where’s my data? An introduction to Spatial Queries in SQL Server 2008
Colin Angus Mackay is a software developer specialising in Microsoft technologies located in Glasgow, Scotland. He is a Member of the British Computer Society, a Microsoft MVP (C#), a Microsoft Certified Solutions Developer, a Core Member of Scottish Developers, Code Project MVP for three years running and while not involved in software related pursuits is an amateur photographer, cyclist, map maker and is also the Vice President of Education for South Side Speakers, the Glasgow South Side Branch of Toastmasters International. And if all that wasn’t enough he is also organising Developer Day Scotland
It is reckoned that 80-90% of data has a spatial component to it. But what do we do with it now? At best, we constrain it to postcodes. Well, that would be great if we were delivering letters, but the majority of us aren’t. In this session we look at Spatial Queries in SQL Server to see how it works and what can be done with it.
Martin Bell: SELECT TOP(@x) Name FROM MyFavouriteDMVs ORDER BY MyRanking
Martin has been a freelance computer consultant in the UK for over 23 years. He has worked on many relational database systems and since the 1998 has specialized in SQL Server. He was first awarded Microsoft MVP (SQL Server) status in June 2003. Currently he organises the Scottish Area Meetings and is a founding organizer of the SQLBits Community Conferences.
Dynamic Management views were introduced in SQL Server 2005 and have made management and troubleshooting of SQL Server significantly easier than in previous versions. Even if you do not write your own queries against these DMVs you may be using them in Performance Dashboard Reports or other performance reporting applications. In this talk I will go through my TOP favorite DMVs and show how and when they can be used and why I like them!
This event will be held at the HBOS’s Sighthill office. For security reasons we need to submit a list of delegates in advance. If you wish to attend you MUST email Martin Bell before 12:00 on 11/12.
The event will start at 18:30 and go on till 21:00. Sandwiches and refreshments will be provided for attendees.
Where’s My Data? An introduction to Spatial Queries in SQL Server 2008
Dundee: 20-Feb-2008
Speaker: Colin Angus Mackay
URL: http://www.scottishdevelopers.com/modules/extcal/event.php?event=45
It is reckoned that 80-90% of data has a spatial component to it. But what do we do with it now? At best, we constrain it to postcodes. Well, that would be great if we were delivering letters, but the majority of us aren’t. In this session we look at Spatial Queries in SQL Server to see how it works and what can be done with it.
This event will be held in the Queen Mother building at Dundee University from 19:00.
SQLBits II
Birmingham: 1-Mar-2008
Registration is now open for this community conference dedicated to SQL Server.
20 technical Sessions broken up into 4 tracks covering all areas a SQL Pro is interested in and on top of that a recruitment panel and new product demonstrations – something for everybody – Development, DBA, Business Intelligence and of course SQL Server 2008.
Technical Sessions end at 5pm but the day continues first with a Recruitment panel and New production demos and then into an informal networking and social evening at our Group By drinks reception – a great opportunity to relax and chat about our holidays, hobbies, ok – get real – more SQL!
Register quickly; we ran out of places in 8 days at the October 2007 event.
Business Applications with WPF – the Full Monty
Edinburgh: 5-Mar-2008
Speaker: Oliver Sturm
URL: http://www.scottishdevelopers.com/modules/extcal/event.php?event=40
Lots of demos of WPF show flashy UIs with animations and videos and trickery worthy of a Hollywood production worked over by ILM. Is that what your next business app is going to look like? Probably not. Nevertheless, WPF can do a lot for you, because it’s the most powerful and most productive UI platform out there. Full stop. This double session walks you through the whole process of creating a business application with WPF, focusing on how it saves you development time.
Oliver Sturm is an experienced software architect, developer, trainer and author, with a strong background in various different fields including system and framework architecture and design, process modeling
and user interface design. He is a C# MVP and he works for Developer Express as a Technical Evangelist and Lead Program Manager for the Frameworks Division.
This event requires a modest payment of just £10, with the early bird discount, to attend.
Developer Day Scotland
Glasgow: 10-May-2008
URL: http://developerdayscotland.com/main/Default.aspx
Developer Day Scotland (DDS) is a community event, run by community for community, which is based upon the highly successful Developer! Developer! Developer! (DDD) community conference events.
[This is a copy of the Scottish Developers events newsletter that I just sent out]
Technorati Tags: scottish developers,events,team foundation server,continuous integration,sql server 2008,spatial queries,DMV,wpf,SQLBits,developer day scotland,dds,sql server ug
Stats for January 2008
Top 10 posts
| Position | Last Month | Title | Posting Date |
| 10 | New Entry | Why is it so hard to hire good software developers? | 19-Aug-2007 |
| 9 | New Entry | A simple challenge | 22-Jan-2008 |
| 8 | 4 | Follow up on hiring a software developer | 19-Dec-2007 |
| 7 | 7 | Getting started with Spatial Data in SQL Server 2008 | 01-Dec-2007 |
| 6 | 8 | Visual Studio 2005 on Vista | 15-Apr-2007 |
| 5 | 3 | What is a DAL (Part 3) | 13-Oct-2007 |
| 4 | 5 | SQL Exception because of a timeout | 17-Oct-2005 |
| 3 | 6 | What is a DAL? (Part 2) | 5-Sept-2007 |
| 2 | 1 | What is a DAL? | 28-Aug-2007 |
| 1 | 2 | Internal Error 2755 caused by folder encryption | 15-Oct-2006 |
Browser Usage
| Position | Last Month | Browser | Percentage this month | Percentage last month | Change |
| 4= | 3 | Opera | 1.01 | 4.17% | -3.16 |
| 4= | 5 | Mozilla | 1.01 | 0.58% | +0.43 |
| 3 | 4 | Safari | 1.31 | 1.01% | +0.30 |
| 2 | 2 | Firefox | 32.58% | 29.33% | +3.25 |
| 1 | 1 | Internet Explorer | 63.85% | 64.85% | -1.00 |
Location of Visitors
| Position | Last Month | Location |
| 10 | New Entry | France |
| 9 | 6 | Poland |
| 8 | New Entry | Sweden |
| 7 | 7 | Germany |
| 6 | 9 | Netherlands |
| 5 | 5 | Australia |
| 4 | 4 | Canada |
| 3 | 3 | India |
| 2 | 2 | UK |
| 1 | 1 | USA |
Solution: A simple challenge
For personal reasons I’ve not been very active in the last week or so. Therefore the solution I promised has been a bit late in coming.
Here is the “reference solution” for the simple challenge that I set last week. It is by no means the only solution, nor is it necessarily the best solution (depending on how you define “best”)
class Program
{
private static void Main(string[] args)
{
Console.Write("Width:");
int width = Convert.ToInt32(Console.ReadLine());
Console.Write("Height:");
int height = Convert.ToInt32(Console.ReadLine());
DrawBox(width, height);
Console.ReadLine();
}
private static void DrawBox(int width, int height)
{
// Work out the interior width and height (i.e. the width
// and height of the space inside the box)
int interiorWidth = width - 2;
int interiorHeight = height - 2;
// Work out what the top and bottom of the box should look like
string topAndBottom = new string('*', width);
// Work out what the interior rows should look like
string interiorRow = string.Concat(
"*", new string(' ', interiorWidth), "*", Environment.NewLine);
// Work out the entire interior using the "trick" of defining
// a string with a repeating character for as many rows as the
// interior needs to be, then replacing each of those characters
// with the pattern for a row of the interior.
string interior = new string('-', interiorHeight);
interior = interior.Replace("-", interiorRow);
// Write the box to the console.
Console.WriteLine(topAndBottom);
Console.Write(interior);
Console.WriteLine(topAndBottom);
}
}
JetBrains Sponsors Developer Day Scotland
JetBrains, the makers of ReSharper and dotTrace, have sponsored Developer Day Scotland with 6 bundles of ReSharper and dotTrace to give away as prizes. That’s $550 worth of software to each lucky winner.
Developer Day Scotland, based on the highly successful Developer! Developer! Developer! events, is being held on 10th May 2008 in Glasgow.
The call for speakers is still open, but hurry, it will be closing soon!
A simple challenge
A homework question was posted on a forum recently. As usual it was met with the cries of “We won’t do your homework for you” (We will, of course, help if you get stuck. But don’t expect any assistance without at least trying first)
The assignment was to write a console application that accepted a width and a height from the user and then created a rectangle using asterisks. e.g. This might be the result of the program
Width:8 Height:5 ******** * * * * * * ********
Now the query was asked how to complete this using loops (or conditional statements, we weren’t really sure – he said loops, but proceeded to talk about if/else statements)
One of the responses was along the lines of “I can do this without loops”, modified to add “Or conditional statements”, and modified again to add “Or recursion”
So, my challenge to you is to write a small program above without loops, conditional statements or recursion. It sounds difficult, but it isn’t really. I have a solution (from which the example output above has been taken) which I’ll post this evening.
New Year! New Events!
It is 2008 already! Time really does fly as it only seems like yesterday when I was starting to plan out how to get user group events going in Glasgow and now I’m planning a conference.
I’m still in the process of putting an event schedule together for Glasgow for the upcoming year, so if there is any thing specific you would like to hear about then please let me know and I’ll try to find some speakers to talk on that subject. In the mean time we have one excellent event coming up this week and more in the works.
Wednesday 16th January in Glasgow (Evening)
Gary Short: My Favourite Design Patterns
He’ll show you what they are, what they are used for and he’ll rustle up a coded example. If you’ve heard of design patterns and want to find out a bit more, or if you know about them already and just want to see how someone else does it, then this talk might just float your boat.
This talk is a repeat of the talk Gary gave at DDD6 where he got excellent feedback from the audience. We are in for a real treat on Wednesday evening and we’ve given Gary some extra time he didn’t have at DDD so this will be an even better extended version.
Tuesday 12th February in Glasgow (Evening)
Richard Fennell: Team Foundation Server
The talk will be on Team Foundation Server, but the exact aspect is, as yet, to be decided. The page linked to will be updated when the details are finalised.
Wednesday 13th February in Edinburgh (Evening)
Colin Mackay: Where’s my data? An introduction to Spatial Queries in SQL Server 2008
It is reckoned that 80-90% of data has a spatial component to it. But what do we do with it now? At best, we constrain it to postcodes. Well, that would be great if we were delivering letters, but the majority of us aren’t. In this session we look at Spatial Queries in SQL Server to see how it works and what can be done with it.
Martin Bell: SELECT TOP(@x) Name FROM MyFavouriteDMVs ORDER BY MyRanking
Dynamic Management views were introduced in SQL Server 2005 and have made management and troubleshooting of SQL Server significantly easier than in previous versions. Even if you do not write your own queries against these DMVs you may be using them in Performance Dashboard Reports or other performance reporting applications. In this talk I will go through my TOP favourite DMVs and show how and when they can be used and why I like them!
Saturday 1st March in Birmingham (All day)
Following from the fabulous success of the first SQL Bits conference last year in Reading they are moving to a bigger venue in Birmingham.
Wednesday 5th March in Edinburgh (Afternoon)
Oliver Sturm: Business Apps with WPF – The Full Monty
Lots of demos of WPF show flashy UIs with animations and videos and trickery worthy of a Hollywood production worked over by ILM. Is that what your next business app is going to look like? Probably not. Nevertheless, WPF can do a lot for you, because it’s the most powerful and most productive UI platform out there. Full stop. This double session walks you through the whole process of creating a business application with WPF, focusing on how it saves you development time.
NOTE: This is a paid event. Prices start at £10 for members and £25 for non-members with the early bird discount.
Saturday 10th May in Glasgow (All day)
An event that is being run by the community for the community. Currently the call for speakers is open, so if you would like to submit a talk please just email me and let me know. In early February the voting opens and you will then be able to vote for the sessions you’d like to see.
Keep an eye on the Scottish Developers website for more information about upcoming events.
[This is a copy of the Scottish Developers events newsletter I just sent out]
Design Patterns talk by Gary Short
Just a wee reminder that Gary Short will be presenting his excellent talk on Design Patterns on Wednesday 16th January.
I don’t just say it is excellent just to encourage you to go, although I do encourage you to see this presentation. I have good (some might say excellent) cause to say this and I have the numbers to back it up. Gary has recently released the scores he got when he did this presentation at DDD6 in Reading last November and they are, to say the least, impressive. You are in for a real treat with this session.
The full details are here.
The Blog of Colin Mackay 