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.