This is part three in a series:

• Part 1, Introduction
• Part 2, Local Data
• Part 3, Getting Around
• Part 4, Data Sets
• Part 5, Resources

Now that we have some local buildings represented in our database, let’s move around.

Getting Around

0. Get the Buildings

In the last step of Part 2, we saved three buildings into temp tables — #orig, #bloc, and #dest.

1. Shortest Path

select geom from #orig union all select geom from #dest union all select geom from #bloc union all
select (select geom from #orig).ShortestLineTo((select geom from #dest))

Let’s Find the Shortest Path to that Condemned Building.

The .ShortestLineTo method will find the shortest path, even taking into account the shapes of the buildings.

But it doesn’t care about any obstacles in its path, which is why we also stored that blocking building to continue our experiment.

2. Put an Envelope on the Obstacle

select (select geom from #bloc).STEnvelope().STBoundary()
union all select geom from #orig union all select geom from #dest union all select geom from #bloc

An envelope will look at the highest and lowest values for the height and the width of an object, and draw a box using those values. We can use this to help find a path around the obstacle.

3. Draw the Corners of the Envelope

select geom from #orig union all select geom from #dest union all select geom from #bloc union all
select geometry::Point((select geom from #bloc).STEnvelope().STPointN((1)).STX, (select geom from #bloc).STEnvelope().STPointN((3)).STY, 0).STBuffer(.0001) union all
select geometry::Point((select geom from #bloc).STEnvelope().STPointN((3)).STX, (select geom from #bloc).STEnvelope().STPointN((3)).STY, 0).STBuffer(.0001) union all
select geometry::Point((select geom from #bloc).STEnvelope().STPointN((3)).STX, (select geom from #bloc).STEnvelope().STPointN((1)).STY, 0).STBuffer(.0001) union all
select geometry::Point((select geom from #bloc).STEnvelope().STPointN((1)).STX, (select geom from #bloc).STEnvelope().STPointN((1)).STY, 0).STBuffer(.0001)

This command illustrates where the corners of that envelope are. After all, we don’t really want the entire envelope to find a way around the obstacle, just the endpoints.

4. Save Those Corners

if object_id('tempdb.dbo.#corn', 'u') is not null drop table #corn create table #corn (geom geometry)
insert into #corn
select geometry::Point(geom.STEnvelope().STPointN((1)).STX, geom.STEnvelope().STPointN((3)).STY, 0) from #bloc
union all
select geometry::Point(geom.STEnvelope().STPointN((3)).STX, geom.STEnvelope().STPointN((3)).STY, 0) from #bloc
union all
select geometry::Point(geom.STEnvelope().STPointN((3)).STX, geom.STEnvelope().STPointN((1)).STY, 0) from #bloc
union all
select geometry::Point(geom.STEnvelope().STPointN((1)).STX, geom.STEnvelope().STPointN((1)).STY, 0) from #bloc

Let’s save those into some temp tables, too, to make the following statements a bit easier to read.

5. Paths to the Corners

select geom from #orig union all select geom from #dest union all select geom from #bloc union all
select o.geom.ShortestLineTo(c.geom).STBuffer(.00001) from #orig o left outer join #corn c on 1=1

Being a method, the .ShortestLineTo can be applied to as many shapes (or points) that we want to give it, all in a single command.

By putting the corners into a temp table, we can draw all four lines at once.

6. Eliminate Overlaps

select geom from #orig union all select geom from #dest union all select geom from #bloc union all
select o.geom.ShortestLineTo(c.geom).STBuffer(.00001)
from #orig o left outer join #corn c on 1=1 left outer join #bloc b on 1=1
where o.geom.ShortestLineTo(c.geom).STIntersects(b.geom)=0

One of those paths goes through the building, which isn’t any help in getting around it. So by added a where clause so that .STIntersects is false, we only see the lines that don’t collide with the building.

Cool! We’re halfway there.

7. Add the Destination

select geom from #orig union all select geom from #dest union all select geom from #bloc union all
select o.geom.ShortestLineTo(c.geom).STUnion(c.geom.ShortestLineTo(d.geom)).STBuffer(.00001)
from #orig o left outer join #corn c on 1=1 left outer join #bloc b on 1=1 left outer join #dest d on 1=1
where o.geom.ShortestLineTo(c.geom).STIntersects(b.geom)=0
and c.geom.ShortestLineTo(d.geom).STIntersects(b.geom)=0

Since those three valid paths are also just geometric data objects, we can use them as the new “origin” for the next leg of our journey.

So, by finding the shortest lines from those lines to the destination building, joining the two line segments into a single object with the .STUnion method, we get:

If we were to apply the .STDistance() method to those paths, we’d see that the purplish path is 4.732 meters, and the blueish path is 5.055 meters.

Shortest Path

Typically, of course, the shortest path problem is focused on roads, not taking shortcuts through the grass.

That’s already been solved in a variety of ways. The most common approach is Dijkstra’s Algorithm.

Here are a couple of SQL implementations: one from Peter Larsson, and one from Hans Olav.

The key to making it quick is generally to pre-calculate all the distances. To a point. Computers these days can’t store the distance from every point on Earth to every other point on Earth, any more than every possible chess move on every board.

This is part two in a series:

• Part 1, Introduction
• Part 2, Local Data
• Part 3, Getting Around
• Part 4, Data Sets
• Part 5, Resources

Besides just looking around at states, roads, rivers, and other large-scale features, we have the ability to bring in the structures that we all know and love because they’re right there with us.

Local Data

1. Buildings

Here are the buildings on MSTU Murfreesboro’s campus (which is where I first gave this presentation).

select id, geom, name, [type]
from mstubuildings

This brings back all the buildings on campus.

By picking Name as the label, and zooming in enough to where the text is readable, we get this:

2. Building Types

select geometry::CollectionAggregate(geom), [type]
from mstubuildings
group by type

We can group the buildings so that they’re color-coded by type. There are lots of university buildings as expected, and a few other types.

Wait, what’s that brownish building in the bottom right? Condemned? Well, I never!

Don’t worry — the campus isn’t in any danger. It’s just the old dorm.

3. How Many Roads Must a Man Walk Down?

select id, geom, name, [type]
from msturoads

Besides the buildings, we can display the roads.

4. Road Types

select geometry::CollectionAggregate(geom), [type]
from msturoads
group by type

There are a lot of “roads” on the map above, but not all of them are what we’d often consider to be a road. So let’s group them by type and see what they are.

The labels come back really tiny, but the blueish top left is pedestrian, the blueish top center is footway, the purplish on the right is service, the pinkish is residential, with primary on the bottom center, and secondary in the bottom right.

5. Road Sizes

if object_id('tempdb.dbo.#roads', 'u') is not null drop table #roads
create table #roads (typ varchar(20), siz float)
insert #roads select 'primary',    .00004
insert #roads select 'secondary',  .00002
insert #roads select 'tertiary',   .00001
insert #roads select 'residential',.000005
insert #roads select 'service',    .0000005
insert #roads select 'footway',    .0000001
insert #roads select 'pedestrian', .0000001
insert #roads select 'steps',      .00000001
select geometry::CollectionAggregate(geom).STBuffer(siz), [type]
from msturoads
inner join #roads on type = typ
group by [type], siz

Let’s change the size of the roads to match a little more closely with what we’d expect.

My unscientific guess is that primary > secondary > tertiary > residential > service > footway > pedestrian > steps. So the temp table above will apply those sizes to the roads via STBuffer, and aggregating them will make them a single color.

It looks more like a human-readable roadmap, anyway.

6. Aggregated Buildings and Roads

select geometry::CollectionAggregate(geom), name, ''
from mstubuildings
group by name
union all
select geometry::CollectionAggregate(geom).STBuffer(siz), '', [type]
from msturoads
inner join #roads on type = typ
group by [type], siz

Now, let’s put the buildings and (sized) roads together, and choose Name as the label for the buildings.

7. Cleaning Up

if object_id('tempdb.dbo.#camp','u') is not null drop table #camp
create table #camp (geom geometry)
insert into #camp (geom)
select geometry::STPolyFromText('POLYGON ((-86.371 35.855, -86.36 35.853, -86.355 35.848, -86.357 35.843, -86.373 35.845, -86.371 35.855))', 0)
select geometry::CollectionAggregate(geom), name, ''
from mstubuildings
group by name
union all
select geometry::CollectionAggregate(geom).STBuffer(siz), '', [type]
from msturoads
inner join #roads on type = typ and siz >= .0000005
group by [type], siz
union all
select geom.STBoundary().STBuffer(.0002), null, null
from #camp

But remember, we’re not just painting a picture — this is queryable data.

So dropping the not-really-roads (footway, pedestrian, steps), and drawing a homemade polygon around the campus, we’re getting closer to a more usable map.

8. Trim the Map

select 0, geometry::CollectionAggregate(geom).STIntersection((select geom from #camp)).STBoundary().STBuffer(.00002), '', ''
from mstubuildings
union all
select id, geom, name, [type]
from mstubuildings
where id = 22
union all
select 0, geometry::CollectionAggregate(geom).STIntersection((select geom from #camp)).STBuffer(siz), '', [type]
from msturoads
inner join #roads on type = typ
group by [type], siz

That polygon doesn’t have to be just for show — we can use it as a filter to only show the geometry that is within it, which “chops away” the parts of the map that we don’t care about.

To highlight a particular building (the Business and Aerospace Building which hosted SQL Saturday 480), just change the buildins into outlines via STBoundary, and union in that one building as-is.

9. Pick Three Buildings

select id, geom, name, [type]
from mstubuildings
where id in (22, 38, 40)
union all
select 0, geometry::CollectionAggregate(geom).STIntersection((select geom from #camp)).STBoundary(), '', ''
from mstubuildings

Okay, now let’s pick just a couple of buildings to focus on. The BAB, the condemned building, and that building in between.

10. Save the Buildings for Later

if object_id('tempdb.dbo.#orig','u') is not null drop table #orig create table #orig (geom geometry)
if object_id('tempdb.dbo.#dest','u') is not null drop table #dest create table #dest (geom geometry)
if object_id('tempdb.dbo.#bloc','u') is not null drop table #bloc create table #bloc (geom geometry)
insert into #orig select geom from mstubuildings where id = 22
insert into #dest select geom from mstubuildings where id = 38
insert into #bloc select geom from mstubuildings where id = 40

select geom from #orig union all select geom from #dest union all select geom from #bloc

Let’s save those three buildings for later, and select just them.

In the next post, we’ll use these buildings as we look at ways to get from place to place.

Downloads

Here’s the Excel file with the scripts to create and load the road and building tables.

One of my favorite things about SQL is spatial data.

By using the datatypes geography and geometry, we can learn things about the facts and figures that we’re looking though that we might not have noticed without mapping.

Here are some of the basics of how to look at spatial data. Once we’ve gone through these building blocks, we’ll look at some of the things we can do with it.

This is part one in a series:

• Part 1, Introduction
• Part 2, Local Data
• Part 3, Getting Around
• Part 4, Data Sets
• Part 5, Resources

Introduction

Now, let’s kick the tires and see what spatial data is about.

1. Geospatial Data

select state, geom, geom.ToString() geomtext from us_stateshapes

This command selects the field called geom, which is of data type geometry. It also selects the geom field in string format.

The string results are shown as polygons and multipolygons.

Next to the Results tab is a Spatial Results tab.

Right there in SQL Server Management Studio (SSMS), we see a picture of the US!

Clicking the Label Column adds the state abbreviation to the map as well, and the Grid Lines checkbox toggles whether or not the latitude and longitude lines are visible.

2. Where Clause

select state, geom from us_stateshapes where state not in (‘AK’,‘HI’)

Since this is just a T-SQL statement, you can exclude some states or show only specific ones.

select state, geom from us_stateshapes where state = 'KY'

3. Combine Shapes

select geometry::UnionAggregate(geom) from us_stateshapes where state not in ('AK','HI')

All of the states can be combined into a single shape by using the UnionAggregate function.

Now it doesn’t quite look like a rainbow vomited onto the map.

4. Combine Shapes with Outlines

select geometry::CollectionAggregate(geom) from us_stateshapes where state not in ('AK','HI')

The outlines of each state can be retained by using the CollectionAggregate instead.

5. Highlight a State

select geometry::UnionAggregate(geom)
from us_stateshapes
where state not in ('AK','HI','KY')
union all
select geom from us_stateshapes where state = 'KY'

By filtering out a state and aggregating the rest of the country into a single color, then unioning that missing state back in, we can essentially change that state’s color.

6. County Detail

select geometry::CollectionAggregate(geom)
from ky_counties

“Zooming in” a bit, and reading from the counties table, we can see all the counties in Kentucky.

It’s just as easy to highlight a county, with or without the other counties being outlined.

select geom
from us_stateshapes
where state = 'KY'
union all
select geom
from ky_counties
where id = 24

9. Life is a Highway

select s.geom from us_stateshapes s where s.state = 'KY'
union all
select geometry::CollectionAggregate(i.geom) from us_stateshapes s
left outer join ky_interstates i on 1=1
where s.state = 'KY' and i.geom.STIntersects(s.geom) = 1

Need to find your way around? Add the interstates to the map.

8. Zip Codes

select geom
from ky_counties
where id = 24
union all
select geometry::CollectionAggregate(geom)
from ky_zip

Zooming in a bit more, here are the zip codes within Jefferson County.

They’re a lot more… disjointed than I expected them to be.

9. Bubbleland

select geom.STIntersection(geometry::Point(-89.5,36.53,0).STBuffer(.3))
from us_stateshapes
where state in ('ky','tn','mo') 
union all 
select geom.STBoundary().STIntersection(geometry::Point(-89.5,36.53,0).STBuffer(.3)).STBuffer(.005)
from us_stateshapes
where state in ('ky','mo')

Here’s a strange place in Kentucky…

Bubbleland, as it’s been nicknamed, is a place in Kentucky that can only be accessed (by land) from Tennessee.

You can read more about Bubbleland here.

In the next post, we’ll look at local roads and buildings.

Downloads

Here’s the Excel file with the scripts to create and load the road and building tables.