Spatial Data, Part 2: Local Data

This is part two in a series:

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.