This is followup to my part five series on spatial data:
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.
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
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.