This is followup to my part five series on spatial data:

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

At SQL Saturday Cleveland 473, I showed some Ohio- and Cleveland-specific examples.

Downloading the Cleveland Data

You can download the scripts and data input files here at the event page, or individually below.

• cle_tables.sql
• cle_buildings.csv
• cle_landuse.csv
• cle_natural.csv
• cle_places.csv
• cle_points.csv
• cle_railways.csv
• cle_roads.csv
• cle_waterways.csv
• cle_query.sql

Importing the Cleveland Data

• Run the cle_tables script to create the tables.
• Use SQL’s import data wizard to load the .csv files.

Querying the Cleveland Data

This query joins those tables together into a single spatial result.

select '' name, geometry::Point(-81.935191, 41.470970, 0).STBuffer(.08).STBoundary() geom union all 
select isnull(nullif(name,''),type), geom from cle_roads union all 
select isnull(nullif(name,''),type), geom.STBoundary() from cle_landuse union all 
select isnull(nullif(name,''),type), geom.STBoundary() from cle_natural union all 
select isnull(nullif(name,''),type), geom from cle_railways union all 
select isnull(nullif(name,''),type), geom.STBoundary() from cle_waterways union all 
select isnull(nullif(name,''),type), geom.STBuffer(.00002) from cle_points union all 
select isnull(nullif(name,''),type), geom from cle_buildings

Let’s break the script down:

• the first line in the script draws the big circle around the area.
• the other lines bring in the geom field from each table (either the whole object or just its outline via STBoundary).
• the nullif lets us convert any blank into nulls, which lets us use ifnull to insert another value, essentially giving us the name column unless it is blank or null, in which case it gives us the type column

From here, we can:

• zoom in for more detail
• hover over a building, road, or other feature to see its name or other column
• display a label on the results
• apply filters to only show parts of the data
• change the widths of the features by changing the STBuffer
• do lots of other cool stuff

Digging Deeper

To download the data for your entire state, go to download.geofabrik.de (or a variety of other geodata sites), and drill down to the state or other area you’d like.

Download the .shp.zip file, then unzip the contents and import them using Shape2Sql.