07 January 2008

SQL Puzzle 9

/*
Happy New Year everybody! To help with the January blues, I thought it was time for another puzzle. The inspiration for this one comes from a SQL question posed by my girlfriend. She is a geologist and, on a recent field trip to Iceland, was wandering around with a GPS strapped to her head to make 3D representations of the side of hills. At the same time she was taking pictures of rocks on her digital camera (we have the best holiday snaps!).  Her question was this: how could she match the GPS points to the photos so that she had a rough idea of where all the photos were taken.

So, on to the puzzle. We have a whole lot of points from our GPS and a whole lot of photos. Both the photos and the points have a time and date when they were taken. The puzzle is simple. I want to know which is the nearest point to each photo. Since we only have the time to go on, I want the point that is closest in terms time.

So, for example, if we have a photo taken at 13:01 and points taken at 12:55, 13.00 and 13:06 then I want the photo to be at the point taken at 13:00. Any way of doing this in SQL is fine but the simpler your solution the better. If anyone wants to show off and do this in LINQ as well then I would be interested in your answer (as somebody who likes to write stored procedures I am a bit wary of LINQ but I am very interested in learning about it). I had to make up some imaginary data and my results are:

PhotoName x           y           z
——— ———– ———– ———–
Photo01   1           1           1
Photo02   1           1           1
Photo03   2           1           1
Photo04   4           1           1
Photo05   4           1           1
Photo06   5           1           1
Photo07   8           1           1
Photo08   15          1           1
Photo09   19          1           1
Photo10   20          1           1
Photo11   20          1           1
Photo12   20          1           1
Photo13   20          1           1
Photo14   21          1           1
Photo15   21          1           1

On a final note, I would like to ask a huge favour of anyone reading this. I have been writing a tool to fill a SQL Server database with test data and have recently released a beta version. I think it is a really cool tool and is something I wanted back when I was a developer DBA. If you need a quick and easy way of filling a table with some example data you will almost certainly find this handy! We could really do with some feedback so if you have a spare moment please check it out! You can download it at:

http://www.red-gate.com/messageboard/viewtopic.php?t=6140

Most importantly, if you have any comments then please post them in the forum:

http://www.red-gate.com/messageboard/viewforum.php?f=76

Thanks and have fun,

Lionel

SET NOCOUNT ON

DECLARE @Photos TABLE
(
    PhotoName NVARCHAR(7) PRIMARY KEY,
    TimeTaken DATETIME
)

INSERT INTO @Photos(PhotoName, TimeTaken) VALUES (‘Photo01’, ‘2008-01-02 15:01:10.12’)
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES (‘Photo02’, ‘2008-01-07 10:21:06.13’)
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES (‘Photo03’, ‘2008-01-07 11:15:11.42’)
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES (‘Photo04’, ‘2008-01-07 12:38:53.653’)
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES (‘Photo05’, ‘2008-01-07 12:38:53.657’)
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES (‘Photo06’, ‘2008-01-07 13:25:51.12’)
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES (‘Photo07’, ‘2008-01-07 14:35:00.00’)
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES (‘Photo08’, ‘2008-01-07 15:04:19.55’)
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES (‘Photo09’, ‘2008-01-07 16:01:01.17’)
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES (‘Photo10’, ‘2008-01-07 16:10:32.92’)
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES (‘Photo11’, ‘2008-01-07 16:10:32.92’)
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES (‘Photo12’, ‘2008-01-07 16:10:32.92’)
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES (‘Photo13’, ‘2008-01-07 16:33:18.35’)
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES (‘Photo14’, ‘2008-10-15 16:15:52.45’)
INSERT INTO @Photos(PhotoName, TimeTaken) VALUES (‘Photo15’, ‘2010-10-07 17:53:25.26’)

DECLARE @Locations TABLE
(
    LocationID INT IDENTITY(1,1) PRIMARY KEY,
    x INT,
    y INT,
    z INT,
    TimeTaken DATETIME
)

INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (1,1,1,  ‘2008-01-07 10:00:00.00’)
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (2,1,1,  ‘2008-01-07 11:05:00.00’)
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (3,1,1,  ‘2008-01-07 11:30:00.00’)
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (4,1,1,  ‘2008-01-07 12:30:00.00’)
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (5,1,1,  ‘2008-01-07 13:10:00.00’)
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (6,1,1,  ‘2008-01-07 14:10:00.00’)
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (7,1,1,  ‘2008-01-07 14:20:00.00’)
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (8,1,1,  ‘2008-01-07 14:30:00.00’)
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (9,1,1,  ‘2008-01-07 14:40:00.00’)
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (10,1,1, ‘2008-01-07 14:50:00.00’)
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (12,1,1, ‘2008-01-07 15:01:00.00’)
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (13,1,1, ‘2008-01-07 15:02:00.00’)
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (14,1,1, ‘2008-01-07 15:03:00.00’)
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (15,1,1, ‘2008-01-07 15:04:00.00’)
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (16,1,1, ‘2008-01-07 15:05:00.00’)
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (17,1,1, ‘2008-01-07 15:06:00.00’)
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (18,1,1, ‘2008-01-07 15:07:00.00’)
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (19,1,1, ‘2008-01-07 16:08:00.00’)
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (20,1,1, ‘2008-01-07 16:09:00.00’)
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (21,1,1, ‘2008-01-07 17:00:00.00’)
INSERT INTO @Locations(x,y,z, TimeTaken) VALUES (22,1,1, ‘2008-01-07 17:00:00.00’)

SET NOCOUNT OFF

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 2206 times – thanks for reading.

  • Rate
    [Total: 0    Average: 0/5]
  • Share

Related articles

Also in Blogs

Azure SQL Data Warehouse Lives!

Frequently when a new piece of tech that I’m excited about is launched, total nerd that I am, I’ll start quoting Colin Clive in, still the best, James Wale’s Frankenstein. It’s ALIVE! ALIVE! Well, time to get excited. On Monday, July 11, Azure SQL Data Warehouse moves from being in preview on Azure to a … Read more

Join Simple Talk

Join over 200,000 Microsoft professionals, and get full, free access to technical articles, our twice-monthly Simple Talk newsletter, and free SQL tools.

Sign up

See what's happening behind the scenes

Take a peek at the bowels of the ship – the lower decks – the actual servers of SQL Server Central itself.

See what's happening