Getting a bounding box from your SQL Geography

I saw a question posed on twitter last week asking how to pass a SQL 2008 Geography as the bounding box to the many search engines on the web that now allow a spatial query. Flickr would be an example of this, the search api takes a bbox parameter described as:

bbox (Optional)

A comma-delimited list of 4 values defining the Bounding Box of the area that will be searched.
The 4 values represent the bottom-left corner of the box and the top-right corner, minimum_longitude, minimum_latitude, maximum_longitude, maximum_latitude.

We had to do this for some SSRS maps we made and thanks to Rob Farley from LobsterPot we created this helper in SQL:

 

CREATE function [dbo].[GetLatLongBounds](@geo geography) returns table as
return (
select
  MIN(@geo.STPointN(n.num).Lat) as MinLat,
  MAX(@geo.STPointN(n.num).Lat) as MaxLat,
  MIN(@geo.STPointN(n.num).Long) as MinLong,
  MAX(@geo.STPointN(n.num).Long) as MaxLong
from dbo.nums n
where n.num <= @geo.STNumPoints()
)
;
GO

This makes us of a table of numbers calls dbo.nums, a trick used widely when you need a list of all indexes:

 

CREATE TABLE [dbo].[nums](
    [num] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [num] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

 

Populate the table of numbers with the largest number you will need, we put a million numbers in there.

Use the bounds function like this, where you want the bounds returned for a particular GEO in MYTABLE with MYID of 42:

 

SELECT b.*

FROM [dbo].[MYTABLE]

CROSS APPLY (SELECT * FROM [dbo].[GetLatLongBounds](g.GEO)) b

WHERE MYID = 42

 

The output is perfect for your next bounding box search. If you really want to impress, when the results come back why not test their STIntersects() with the Geography and filter out those false positives from the bounding box?

You can reach me on http://twitter.com/soulsolutions

Girl Geek Dinners Brisbane-Dinner 15- Kinect Game Night

ggdbneDue to popular demand from our Girl Geek Dinner attendees, this month, Microsoft have kindly lent us their office so we’re having another night of games!

This event we’ll be showing of the Kinect for Xbox.

From the official Kinect site (http://www.xbox.com/en-au/kinect):

You are the controller. No gadgets, no gizmos, just you!

Kinect brings games and entertainment to life in extraordinary new ways without using a controller. Imagine controlling movies and music with the wave of a hand or the sound of your voice*. With Kinect, technology evaporates, letting the natural magic in all of us shine.

If anyone is interested in giving a 5-7min presentation or would like to bring along their Xbox, Kinect and games please get in touch!

Who is invited?

If you are a geek and a girl or know of one who is willing to escort you then you are welcome and encouraged to come along. There is a technical focus with the intention of having fun and connecting with other women in IT.
Who pays for dinner?
We will be providing a few nibblies so suggest grabbing something beforehand
Where + When
Date: Thursday 30th April, 2009
Time: 5:30 pm
Where: Microsoft Office, Level 9, 1 Waterfront Place, Brisbane
Further details on building access will be provided closer to the event.
 

Register for Girl Geek Dinners Brisbane - Dinner 15 - Kinect Gaming Night in Brisbane, Queensland  on Eventbrite

 

Passed 70-506-Silverlight 4, Development

MCTS(rgb)_1401Late last year I sat the beta exam for Silverlight 4. These exams are a little different as there’s no courses, practise exams or material of any kind other than the list of skills measured and the internet. They are free to sit but you have to typically wait 3 months to fin out whether you passed.  I also sat a the 2 WPF betas last year and find the fact they’re free seems to lift some of the stress about passing…or maybe it’s just that you don’t find out for so long afterwards.  Anyway, I got notification last week that I passed 70-506 TS: Silverlight 4, Development.

 

Technorati Tags: ,,