The World Around Us

I’m a Christian, and sometimes a teacher/preacher.Louisville

This lesson looks outside the church, to America and the world as a whole, then brings it back home.

People Around Us

Depending on which statistics you look at, and how they were counted, the population of the US is between 250M and 500M. Let’s go with 250M for now.

Here’s how my state and city compare to the country at large.

Place Population Relative
US 250,000,000
Kentucky 4,000,000 (1½% US)
Louisville (city limits) 250,000 (6½% KY, 0.1% US)
Louisville (metro area) 1,250,000 (30% KY, ½% US)

My city’s metropolitan area amounts to half a percent of the entire country. Not too bad, for a “big small town.”

Louisville! Nazareth! Can anything good come from there?” (John 1:46)

Half the US Population

Half of the people in America live in the 39 most populous city areas.

The other half are spread out over the entire rest of the country.

People We Know

This varies a lot by personality, job, age, region, and such, but here are some broad averages of the people we know.

Relation People
See 80,000
Meet 10,000
Acquaint 1,000
Friends 150
Close 10

And most Americans these days move around a bit, too, which helps to expand the pool of people we meet.

Jobs 10
Homes 12
Churches ?

I know more people than that (and have worked more jobs than that). Part of that scope comes from me working at a lot of places, but most comes from my wide variety of interests and activities. I’m fairly involved in the circles of technology, business, arts/theater, and church, and belong to many groups inside each of those areas.

Church Sizes in America

Churches around the county vary in size, with most of them serving fewer than 100 people on a weekly basis.

Size Churches Total Weekly Attendance
<100 150,000 9,000,000
<500 100,000 25,000,000
<1,000 12,000 9,000,000
<2,000 6,000 8,000,000
<10,000 1,000 4,000,000
>10,000 50 1,000,000
Total 300,000 56,000,000

Pew Research300,000 churches account for 56 million people each week. That’s a lot of church attendance.

* These figures account for Christian Protestant (non-Catholic) churches

100 Americans

These numbers are still fairly large, and not easy to grasp.

Let’s boil it down, and pretend that the entire US is only 100 people. If you’re in a room with 100 people, you can imagine each person filling one of these categories.

Numbers taken from Pew Research.

Religion People
Christian 69
Mormon 2
Jewish 2
Muslim 1
Hindu 1
Buddhist 1
Other 2
None 23

The Christians are comprised of these people:

Religion People
Catholic 21
Evangelical 25
Protestant 15
Black Protestant 6
Other Christian 2

As you can see, there are a lot of Christian in America.

From what we often hear on the news, it might not always sound like it.

I was a bit surprised to find out that out of 100 people, only two would be Jewish, and only one Muslim.

World Around Us

Let’s look at that first set of numbers again, but add in the rest of the world.

Place Population Relative
World 7,400,000,000 (30x)
US 250,000,000 (3%)
Kentucky 4,000,000
Louisville (city limits) 250,000
Louisville (metro area) 1,250,000

The rest of the world is 30 times larger than the US; the US is just 3% of the world population.

This time, let’s pretend that the whole world is 100 people, not just the US.

So, where is everybody?

Numbers taken from 100 People.

Continent People
Asian 60
African 16
American (North and South) 14
European 10
Australia 0

What do they speak?

Language People
Chinese 12
Spanish 6
English 5
Hindi 4
Arabic 3
Bengali 3
Portuguese 3
Russian 2
Japanese 2
Other 60

(The languages don’t add up to 100, since many people speak more than one language.)

The Bible has been translated into 2,500 languages. That’s quite a lot, but there are 7,000 languages spoken in the world today.

Other ways of looking at us :

Factor People
Read/Write 86
College 7
Internet 45
Safe Water 91
Shelter 78
Overweight 22
Underfed 11
Starving 1

Out of 100 people in the world, 9 have no water, 1 has no food, and 22 have no shelter from the elements.

We’ve already looked at the religious counts in the US. Here’s how it looks worldwide:

Religion People
Christians 31
Muslims 23
Hindus 15
Buddhists 7
Other 8
None 16

The international landscape is a lot different than what we’re used to here in the US.

People Around Us

That’s all a fine intellectual exercise, but let’s bring it home and make it personal.

Taking the national averages of 69 out of 100 Americans being Christian, and applying it locally, we get:

Place Population Christian Lost
US 250,000,000 170,000,000 80,000,000
Kentucky 4,000,000 2,500,000 1,500,000
Louisville (city limits) 250,000 170,000 80,000
Louisville (metro area) 1,250,000 850,000 400,000

FriendsThere are roughly one and a half million non-Christians in Kentucky. Close to half a million in the metro area.

People who need the Lord aren’t just “out there” — they’re here in our own neighborhoods.

People We Know

To bring it one more step closer to home, if we do the same thing to the number of people that we know:

Relation People Christian Lost
See 80,000 55,000 25,000
Meet 10,000 7,000 3,000
Acquaint 1,000 700 300
Friends 150 100 50
Lost 10 7 3

It’s those final two numbers that particularly draw my attention.

On average, each of us has 50 friends and 3 close friends that don’t know the Lord.

We can’t individually bring the gospel to the world, but even if we could, the world wouldn’t listen to us. But we each have dozens of people that will listen to us, that might not listen to anyone else.

In my case, the stakes are even higher. I know more people than average, and I intentionally have a higher percentage of non-Christians, by putting myself into new situations and stretching my comfort zone as far as I can.


  • How many people do you know fairly well?
  • How many are non-Christians?
  • How much time do you spend with them?
  • How can you reach your 50+3?
  • If you don’t have any 50+3, should you? how?


You can download the PowerPoint slides here.


Text Analytics

I’ve worked professionally with databases for a living for around 20 years, to varying degrees.

I’ve worked with words professionally for roughly the same amount of time (as an author, editor, and publisher).

It’s only natural that I’d be interested in ways of mashing them together.

Text AnalyticsSix Plots

If you’d like to learn more about text analytics, text mining, unstructured data mining, and several other synonymous terms for turning a big pile of words into meaningful data, here are some good resources.

Sentiment Analysis

If you want to sort piles of words into good/bad, happy/sad, calm/mad, and such, that’s where sentiment analysis comes into play.Sentiment Analysis

SQL Server and R

Most of my database career has been using Microsoft SQL Server. I’m at the beginning stages of learning R, a data science language.

Mining the Bible

As a case study, I’ve imported dozens of translations of the Bible into SQL Server, where I can look for correlations. It’s an interesting text to work with, since all these translations (a) started from the original Hebrew and Greek, (b) are written in English, and (c) have passages uniquely identified through a numbering system. That allows for some intense analysis.Bible Sentiment

Here is some great work that other people have already done in that field.


Government Innovation Summit

Last week, I spent two days at the second annual Government Innovation Summit in Louisville, Kentucky.

There were some great seminars and breakout sessions, and 500 or so leaders from all around the country.

On Wednesday, I attended the LouieStat Live workshop.

LouieStatLouieStat Live

Louisville Metro Government’s Office of Performance Improvement & Innovation uses the LouieStat methodology. LouieStat encourages reporting on a weakness orientation basis (e.g. instead of “We’re 95% on time,” say “We’re 5% late.”).

This wasn’t just a theoretical discussion of what the process is and how it works — this was a full actual meeting, so that the audience could see the entire process, and how everyone was involved. In this case, it was the first official LouieStat meeting for EMS. There were several officials present, representing technology, communication, administration, and such, plus the new director of EMS (Jody Meiman) and some EMS managers (Chad Scott and Diane Vogel).

Some of the EMS concerns that I learned were that the average ambulance check-in time has increased by about 5 minutes, but that it’s largely due to new policies and processes on the hospital side making dropoffs take more time. They’re addressing that by providing the hospitals with more radio call-ahead information to help them prepare. (Busy ERs might have to doubleup with two patients in a triage room, and knowing the gender and general complaint of the incoming patient helps them organize.) Also, there are way more low acuity (non-emergency) ambulance runs than I would have guessed.Social Muscle

I chose this session because of my love for data transparency. I’ve been familiar with LouieStat for a while, and know some of the people that created it. I even made Louisville’s mayor Greg Fischer chuckle when I answered his question about why it’s important to take incremental steps before all the information is gathered, when I answered that “You can’t steer a parked car.”

Social Muscle

There were great ideas offered by the panel of leaders, and examples from the community, but my favorite one came from William Evans, the Boston Police Commissioner. To pave the way for improved race relations in the future, the police bought an ice cream truck that they drive around parts of town where they are frequently hated and feared. By giving out ice cream, they hope to forge bonds with the younger generation, and keep them away from violence.

While the commissioner was saying this, Greg Fischer was smiling and staring off in the distance… I wonder how long until Louisville gets a city ice cream truck?

Lightning TalksLightning Talks

My only complaint is that (as expected) they were all too short. I would have preferred breakout sessions instead, so that we could split up and dive a little deeper into one topic of interest.

The talks themselves were great, and covered a broad swath of societal issues. Early in the planning stages, there was a chance that I would have been speaking on behalf of community involvement, stemming from my volunteerism at the Civic Data Alliance, hackathons, and other local events. It was good to see Daro Mott again (speaking about data-driven government), who once came from Cleveland a year or two ago to speak at a TALK (Technology Association of Louisville KY) event, and who I’ve seen once or twice when I’ve spoken at Cleveland SQL Saturdays.

Open Data

Open DataI was so overwhelmed (in a good way) by the speakers on this panel that I could barely take a note. Beth Blauer is the Director of GovEx (the Government Center for Excellence) at John Hopkins. Joy Bonaguro is the Chief Data Officer for the City of San Francisco. Jean Clark is the President of Periscope Holdings.

The panel was so dynamic and uplifting, that I wished it could have gone all day.

Third Wave

Steve Case, the founder of AOL back in the day, came to Louisville as part of his twenty-city tour. His new book, The Third Wave, applied the concepts (farming, industry, post-industry) of Alvin Toffler’s original Third Wave book to the internet. First came the planting of ISPs, then the connected backbones of industry (Google, Amazon, etc.), and now the third wave — nigh-invisible integration and proliferation in real-world devices.

As he says, “250 years ago, America was just a startup.”

I sat at a table close to the stage, and Steve sat with us for a little while before going up to speak.Becky Steele

Bonus Events!

On Wednesday evening, TALK hosted a cybersecurity event, focused on the internet of things. Greg Garcia (the White House’s first cybersecurity chief) spoke about the wonders and the dangers that await us.

On Thursday at lunch, at the Summit itself, my great friend Becky Steele was crowned the Continuous Improvement Champion for her work at Code Louisville. I believe she’ll defend her title in the gladiatorial arena at next year’s conference. Or not.

On Thursday night, EnterpriseCorp (the entrepreneurial arm of the city’s chamber of commerce, Greater Louisville Inc) held its annual Evening of Entrepreneurship. Steve Case was the keynote speaker here, too.



Guest Post: Stan Siranovich

For those who’ve wondered what a National Day of Civic Hacking is like, here’s a post from Stan Siranovich that covers his (and my) experience at last weekend’s event.

National Day of Civic Hacking

A Day at the Louisville 4th Annual Hack for Change

Stan Siranovich – Data Scientist | Data Analyst – Specializing in Chemical and Biological, Laboratory, Manufacturing and IoT Applications

Ever wondered what’s it’s like to go to one of those all-day hacking events? I did too and wanted to find out. So when the Louisville 4th Annual Hack for Change came up I registered and went. Here is what I found out when I arrived…

This particular hack was going to work on several different projects, and after an intro and brief project description by Michael Schnuerle, and a break for some coffee and donuts, we moved into the various project groups.

Some of the projects were:

  • A 311 Chatbot to allow easy submission of issues to the city of Louisville (h/t to the OPI and Innovation Office of Louisville Metro Government)
  • An Analysis of KY Voting Precincts, including senate and house boundaries and voter record data
  • A CityVoice deployment for Louisville Metro on the Dixie Highway Revitalization Project
  • An analysis and visualization of Bicycle Accident data in the Louisville and Southern Indiana metro area

(for more details please visit the 4th Annual Hack for Change link)

Being an avid wheelman with a life-long interest in the clear presentation of data, I chose the Bicycle Accident project. Dave Mattingly coordinated the project and there were six of us in the group. In experience level and position along the “data pipeline,” we ranged all the way from Dave the Data Commando to a recent Code Louisville graduate. After a brief round of introductions we got to work.

I started out by pulling the data into RStudio and doing some exploratory analysis which included using the ggplot2 and base R visualization packages. As it turned out, there were no deep insights to be had and our goals would be well served by some simple visualizations that would allow Louisville Metro Government to identify problem streets and locations.

After spending some time developing a map in CartoDB, I decided to switch to Tableau Public. (I was much more familiar with Tableau.) After a few glitches pulling in the map outline and selecting the visual properties, I was able to post a useful map showing the location of bicycle accidents by location, number, type, weather condition, etc. After that, it was a simple matter to post the map on the Tableau Public site.

Meanwhile, Dave was hard at work with Qlik Sense and was able to develop a working prototype.

After a working lunch, provided by the sponsors, Louisville Mayor Greg Fisher paid us a visit. Next came some hand shaking and consultations with the individual groups; he then provided appropriate contacts in city government to further the development work for several projects.

We finished with the individual groups giving a summary and in some cases, a demo on what they accomplished. Overall, it was a productive and satisfying day’s work. I’m definitely coming back for the 5th Annual Hack for Change and even some other Hacks as they arise.

Finally, a big thank you to:Civic Data Alliance

for making the event possible.

Event Photo Gallery

Bike AccidentsBike Accidents by YearBike Accidents by DayBike Accidents by HourBike Accidents by AngleBike Accidents by WeatherBike Accidents ZoomBike Accidents SQL DataCivic Hacking Team FOIACivic Hacking Team ChatbotCivic Hacking Team CityVoiceCivic Hacking Glass Capitol
Civic Hacking Teams

Venture Sharks

Last week, my company CompassioNote competed in the semifinal round of Venture Sharks.Venture Sharks

We went up against eight other teams, and the top four of those teams (including us, yay!) were chosen to move on to the final round.

Venture Sharks

Venture Capital sponsors the annual Venture Sharks contest in which teams of entrepreneurs pitch their ideas to a panel of judges.

Teams get four minutes to pitch their idea/service/product, and another four minutes to answer questions from the judges.

Each year, there are ten semifinalists chosen from among the fifty or so applicants.

The winner of Startup Weekend (one month beforehand) is automatically accepted as the eleventh entry. That’s how we got involved.

Two of the other ten selected entries had to drop out, so there were only nine teams instead of eleven.

May 4 is also Star Wars day. ‘May the Fourth’ be with you.

The Finalists

These four teams advance to the finals, on May 4th:

  • CompassioNote: That’s us! Aaron Priced pitched our service that compares our customers’ contacts to life events like new homes, new jobs, marriages, and deaths.
  • Farm Specific Technology: Austin Scott invented a flex-roller crimper to make it easier and faster for farmers to remove cover crops and replant cash crops.
  • Switcher Studio: Nick Mattingly (no relation) and his team developed an app/service to make video and sound editing easier and faster. Works on Apple products, and costs roughly $50 a month.
  • Uncrash: Trenton Johnson wrote an app to help body shops and garages with their workflow and customer communication.

Our judges were Alli Truttman (Wicked Sheets), Tendai Charasika (SuperFanU), and Ross Jordan (Yearling Fund).


So who the heck are these guys, and what do they do, anyway?

You can watch this video of our final pitch at Startup Weekend, but even though our company was only one month old by that time, our idea had already matured. (The same can’t necessarily be said of our personal maturity level.)

The one-liner:

  • CompassioNote helps companies have better relationships with their customers by using personalized data.

What that means:

  • CompassioNote compares your list of contacts against several sources for life events so you’re notified when something of interest happens to the people you know.

What that does:

  • It gives you a reason (and a time) to reach out to people that you mostly know, but not all that well, to establish or strengthen that personal bond.

Who this helps:

  • People who raise funds (museum supports, alumni, political campaigns, etc.) can keep up with their important donors.
  • People who sell supplies or services can keep up with their contacts at major clients.
  • People who have a lot of friends and acquaintances don’t have to let those casual relationships fade.

“Being human is good business.”

In essence:

  • We add the human touch back into the processes that automated systems have rendered obsolete, or at least invisible.

The New Stuff

At the semifinals, we announced our integration with Salesforce and Microsoft Dynamics, and our partnership with KiZan Technologies on both the development and sales channel fronts.

Your Next Move

If you’d like to find out more about what we do, or become a customer, or refer a customer, sign up for our mailing list and let us know what specifically interests you.

If you’d like to cheer us on at the Venture Sharks finals, register for the luncheon.

If you’d like to help us spread the word, tell your friends about us on LinkedIn and Twitter.



“It’s sort of a cross between MacGyver and Shark Tank.”

This weekend, I built a new company with people I didn’t know.

It’s all part of the amazing event that is Startup Weekend. This happens in cities around the world every weekend.

This is my third time participating in a Startup Weekend, and my team won first place!

Startup Weekend

Startup Weekend Louisville #8

If you’ve never been to a Startup weekend, I recommend it.

A hundred or so people get together and pitch ideas to each other on Friday night, then we gather around the ones we like the best. We spend Saturday researching, building, and talking to potential customers to see if the idea is one that people want. Then we spend Sunday polishing it up to we can present our final 5-minute pitches to a panel of judges, who make their decision based on factors such as team-to-customer interviews, a working prototype, ongoing viability, and potential market size.

It’s sort of a cross between MacGyver and Shark Tank.

We all start with nothing but ideas, and after 54 hours of feverish activity, one team walks away on top.

I love doing it because it’s a great way to meet to people, hear new ideas, work on a short-term project that excites you, learn a new methodology or industry or technology, and have a final product of some sort to show when you’re done.


(L to R): John Davenport, Kartik Kamat, Dave Mattingly, Aaron Price, Mihir Kotwal

Keep up with #SWLou and #StartupWeekend to stay involved.


Our team initially started off with a sad story that we didn’t want to see repeated.

Kartik Kamat told us that his retired friend and mentor had passed away, but that he didn’t hear about it until well after. Kartik’s pitch was that we’d build a tool to notify us as soon as there’s an obituary for our friends and loved ones that we don’t have daily contact with.

Many of us had similar experiences, and we formed a team to help.

Here’s a video that our teammate Aaron recorded, to explain his own experience, to help us get the concept across to our potential customers and to garner their feedback.

Aaron Price for CompassioNote

A 45-second video explaining the problem, and our solution.

We found that the idea resonated with a lot of people, and although we originally envisioned our target customer as HR departments that would want to keep track of former employees (after all, if someone has been there a long time and made a lot of friends, they probably want to hear about it after the employee had retired). But we found out that HR tended to care more about current employees than former ones, and suggested that sales teams would be more interested.

That was our lightbulb moment. Imagine being a salesperson with a list of a few hundred clients and a few thousand prospects. If one of them passes away, you’d at least like the option to send a card or flowers. Providing personal service like that, when it’s most needed, can cement a lifelong customer relationship.

We pivoted our efforts, and found that we can purchase obituary information for the entire country, and integrate it with professional sales tools like Salesforce, Microsoft Dynamics, Goldmine, and others.

We built a personal-use web version (that you can try here), to make sure we had a functional process, but we expected that the real money would come in from professionals whose careers are built upon relationships with very large numbers of people.

CompassioNote Pitch

The winning pitch!

Eleven Startups Enter… One Startup Leaves!

You can watch every pitch here (we all had five minutes to speak, and three minutes to answer questions from the judges).

Or you can skip to just our winning pitch here.

Coming Next

One of the prizes was automatic entry into a much bigger contest, so we’re gearing up for that.

While we do, we’re juggling dozens of other tasks to which I won’t get into just now, but I’m sure I’ll cover later.

Please sign up on our website, like our page on Facebook, follow us on Twitter, and connect to us at LinkedIn.

#startupweekend, #swlou

Tech Community Events

“In the spring, a young man’s fancy turns to thoughts of tech…”

That’s not quite how Tennyson wrote it, but he would have, if he’d lived in Louisville at a time when coders, hackers, designers, and other geeks and nerds offer such a remarkable smorgasbord of creative techie activities. That’s my story, anyway.

Here are a few specific events that you can become a part of, plus some other cool ones further down the screen.

Code Across

Code Across America

The fifth annual Code Across is a volunteer event for civic hacking.

What is civic hacking, you ask? It’s “hacking” in the good sense, to improve something, not to break down. Code for America

Over the course of one day, coders and designers pitch ideas and settle into teams. They create an app, a site, or something else. At the end of the afternoon, they present what they’ve created and give it away to the world. There are even snacks and prizes.

It’s a great way to make friends, learn a new approach, work together, create or solve something, gain experience, and help the community.

Some cool projects we’ve done in the past include:Code Louisville

  • a pet adopter that helps you find the animal you want at a shelter close to you
  • a restaurant recommender which takes into account health scores, nearby crime, and reviews
  • a park amenities locator, to find the nearest potty while you’re on that hike

This year, the event will be held at Code Louisville, 252 E Market St, Louisville, KY 40202.

Keep June 4, 2016 open on your calendar for the National Day of Civic Hacking, also a Code for America event.Civic Data Alliance

Our local “Code for America Brigade” is the Civic Data Alliance. We advocate to make government data open and transparent, so that everyone can learn from it. We also host hackathons and educational sessions.

Startup Weekend

Startup Weekend

Louisville’s Startup Weekend is a twice annual volunteer event for business idea creation.

Similar to Code Across, people gather, pitch ideas, form into teams, work, then show their results at the end.

The difference here is that the projects tend to be entrepreneurial, with an ongoing sustainable profit plan. This one is 54-hours long (Friday afternoon through Sunday afternoon), with judges and valuable prizes. On Sunday, teams show off their creations to compete for valuable prizes.

Our local Startup Weekend has gotten some national attention. Lat year, more women than men participated. And the winning team was led by a high school girl!

Projects we’ve had in the past include:

  • Moov — Uber for pickup trucks
  • MixxThis — an easier way to process your drink orders
  • Foodinary — an app to explain the ingredients in your meal

This year, the event will take place at the UofL School of Business.

A lot of the movers and shakers of the local business and tech scenes help out with and attend these.

There is a modest fee to attend this one, but I believe it’s well worth it. I’ve attended four startup weekends so far.

Here’s my post about last year’s event.

Other Events

Here are even more ways that you, too, can join in on the fun.


  • 3/11 – Nonprofit Leadership Network
    • American Printing House for the Blind, 1839 Frankfort Ave
    • If you work with any volunteer or charity group, the KNN can keep you abreast of resources, policies, and happenings.



  • 2/26 – Lean Coffee
    • Press, 252 E Market St
    • Lean Coffee is a cool way of meeting in which the agenda is created during the meeting itself.



  • 3/4-3/6 – Louisville Arcade Expo
    • Ramada Plaza, 9700 Bluegrass Parkway
    • Play Pac-Man, pinball, and hundreds of other games all weekend! Proceeds go to the children’s hospital.


  • 3/26 – Random Fandom Con
    • Louisville Public Library, 9725 Dixie Highway
    • Meet other fans of comics, anime, sci-fi, fantasy, horror, and other fun nerdy pursuits.
  • 4/8 – ConGlomeration
    • Ramada Plaza, 9700 Bluegrass Parkway
    • ConGlomeration is Louisville’s oldest and friendliest science fiction con. Highly recommended. The weekend includes expert panels, costumes, gaming, music, and food.


Keep in Touch

Want to stay in the loop about events like this? There are several ways that you can do that.


Spatial Data, Part 6: Cleveland

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

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

Spatial Results

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 (or a variety of other geodata sites), and drill down to the state or other area you’d like.

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



Spatial Data, Part 5: Resources

This is part five in a series:

The things we’ve looked at over the last few lessons come from things that I’ve picked up from the people and places that I mention here.


1. Smart People

Much of what I’ve learned, I learned from these folks:

2. Recursion and Fractals

Simon Greener shows how to make a circle that’s made of circles, with text showing their position. The whole Spatial DB Advisor is filled with amazing tricks.

Alastair Aitchison shows have to draw recursive triangles.

Slava Murygin shows how to make recursive snowflakes.

Recursive Circles Fractal TriangleSnowflake

3. Artwork

Alex Whittles shows how to convert a vector image into geospatial data.

Click these images to see how to make Grinch, a Christmas tree with proper coloring, one with random ornaments, Venus, or a hotel floorplan.

Grinch Colored Tree Random Tree Venus Floorplan

4. Utilities

SQL Magazine offers a quite of utilities to handle a variety of graphic and charting situations.

Download here.

5. Color Palette

The default color scheme that SQL uses doesn’t always fit the bill. But by specifying the order of the items you display, you can control which one gets which color.

;with n(x) as (select 0 union all select x+1 from n where x < 99)
select cast(x as varchar) label,
from n order by x

The palette ends up looking like this.


6. Finding Geospatial Data

There are many collections of geospatial data available for free (and many more for pay). Being a cheapskate myself, I tend to stick with free as much as possible.

Search for terms like shapefile, geospatial, data, and the region (Kentucky, etc.) and the type of data (geography, roads, elevation, demographics, roads, etc.) that you’re interested in.

I generally prefer to work with .SHP shapefiles, but there are a number of filetypes out there. KML (Keyhole Markup Language) is another popular one.

When I get specific local data including buildings (like I did for MTSU in Part 2), I prefer to use the service at, but MapSys gives an overview of the various ways to find and import geospatial data.

7. Loading Geospatial Data

The tool I prefer for load the data is Shape2SQL from SharpGIS. It’s lightweight and easy to use, and the instructions should handle whatever troubles you might run into.

The one thing to keep in mind is that if you’re loading geographical data, you’ll need to switch the datatype and pick and SRID (usually the default of 4326).


8. A Parting Gift

For a little fun, run this select:

select convert(geometry,'multipolygon (((0.0193705 0.678262, 0.0871671 0.678262, 0.164649 0.290852, 0.251816 0.629835, 0.329298 0.629835, 0.416465 0.281167, 0.493947 0.678262, 0.552058 0.678262, 0.455206 0.194, 0.377724 0.194, 0.290557 0.562039, 0.184019 0.194, 0.106538 0.194, 0.0193705 0.678262), (0.59037 0.678262, 0.658167 0.678262, 0.735649 0.290852, 0.822816 0.629835, 0.900298 0.629835, 0.987465 0.281167, 1.06495 0.678262, 1.12306 0.678262, 1.02621 0.194, 0.948724 0.194, 0.861557 0.562039, 0.755019 0.194, 0.677538 0.194, 0.59037 0.678262), (1.16137 0.678262, 1.22917 0.678262, 1.30665 0.290852, 1.39382 0.629835, 1.4713 0.629835, 1.55847 0.281167, 1.63595 0.678262, 1.69406 0.678262, 1.59721 0.194, 1.51972 0.194, 1.43256 0.562039, 1.32602 0.194, 1.24854 0.194, 1.16137 0.678262), (1.93576 0.329593, 2.06167 0.329593, 2.06167 0.203685, 1.93576 0.203685, 1.93576 0.329593), (2.70046 0.901022, 2.77795 0.901022, 2.77795 0.194, 2.70046 0.194, 2.70046 0.290852, 2.64235 0.223056, 2.59393 0.194, 2.56487 0.184315, 2.50676 0.184315, 2.46802 0.194, 2.42928 0.223056, 2.39054 0.271482, 2.36148 0.368334, 2.36148 0.465186, 2.38085 0.552354, 2.40991 0.610465, 2.45833 0.658891, 2.49707 0.678262, 2.52613 0.687947, 2.59393 0.687947, 2.64235 0.668576, 2.70046 0.62015, 2.70046 0.901022), (2.70046 0.562039, 2.70046 0.348964, 2.62298 0.271482, 2.58424 0.252111, 2.52613 0.252111, 2.48739 0.281167, 2.46802 0.310223, 2.44865 0.368334, 2.44865 0.484557, 2.46802 0.542668, 2.48739 0.581409, 2.51645 0.610465, 2.5455 0.629835, 2.60361 0.629835, 2.65204 0.60078, 2.70046 0.562039), (2.95185 0.658891, 3.01965 0.678262, 3.06807 0.687947, 3.17461 0.687947, 3.21335 0.678262, 3.25209 0.658891, 3.27146 0.629835, 3.29083 0.571724, 3.29083 0.271482, 3.30052 0.252111, 3.31989 0.242426, 3.35863 0.242426, 3.36832 0.203685, 3.32958 0.184315, 3.29083 0.184315, 3.25209 0.203685, 3.22304 0.252111, 3.1843 0.223056, 3.12619 0.194, 3.08745 0.184315, 3.02933 0.184315, 2.99059 0.194, 2.96154 0.21337, 2.93248 0.242426, 2.91311 0.290852, 2.91311 0.348964, 2.93248 0.39739, 2.98091 0.445816, 3.01965 0.465186, 3.08745 0.484557, 3.21335 0.484557, 3.21335 0.562039, 3.19398 0.60078, 3.14556 0.629835, 3.06807 0.629835, 3.00996 0.610465, 2.95185 0.581409, 2.95185 0.658891), (3.21335 0.436131, 3.12619 0.436131, 3.06807 0.41676, 3.02933 0.39739, 3.00028 0.348964, 3.00028 0.300538, 3.01965 0.271482, 3.05839 0.242426, 3.10682 0.242426, 3.16493 0.261797, 3.21335 0.300538, 3.21335 0.436131), (3.46474 0.678262, 3.55191 0.678262, 3.71656 0.281167, 3.88121 0.678262, 3.95869 0.678262, 3.74561 0.194, 3.66813 0.194, 3.46474 0.678262), (4.49579 0.41676, 4.15681 0.41676, 4.15681 0.368334, 4.17618 0.329593, 4.19555 0.300538, 4.24397 0.261797, 4.30208 0.242426, 4.37957 0.242426, 4.42799 0.252111, 4.49579 0.271482, 4.49579 0.21337, 4.42799 0.194, 4.36988 0.184315, 4.28271 0.184315, 4.23429 0.194, 4.16649 0.223056, 4.11806 0.261797, 4.08901 0.310223, 4.06964 0.378019, 4.06964 0.474872, 4.07932 0.523298, 4.09869 0.571724, 4.14712 0.629835, 4.19555 0.668576, 4.26334 0.687947, 4.34083 0.687947, 4.39894 0.668576, 4.43768 0.639521, 4.46673 0.60078, 4.4861 0.552354, 4.49579 0.494242, 4.49579 0.41676), (4.40862 0.474872, 4.15681 0.474872, 4.16649 0.523298, 4.18586 0.562039, 4.2246 0.610465, 4.26334 0.629835, 4.32145 0.629835, 4.3602 0.610465, 4.38925 0.581409, 4.40862 0.532983, 4.40862 0.474872), (4.62127 0.678262, 4.68906 0.678262, 4.68906 0.581409, 4.72781 0.639521, 4.76655 0.678262, 4.78592 0.687947, 4.83434 0.687947, 4.8634 0.658891, 4.88277 0.62015, 4.89245 0.581409, 4.92151 0.629835, 4.95057 0.668576, 4.98931 0.687947, 5.02805 0.687947, 5.0571 0.668576, 5.07647 0.639521, 5.08616 0.591094, 5.08616 0.194, 5.01836 0.194, 5.01836 0.591094, 4.98931 0.62015, 4.96994 0.62015, 4.9312 0.581409, 4.89245 0.513613, 4.89245 0.194, 4.82466 0.194, 4.82466 0.591094, 4.7956 0.62015, 4.77623 0.62015, 4.71812 0.562039, 4.68906 0.513613, 4.68906 0.194, 4.62127 0.194, 4.62127 0.678262), (5.23585 0.658891, 5.30365 0.678262, 5.35207 0.687947, 5.45861 0.687947, 5.49735 0.678262, 5.53609 0.658891, 5.55546 0.629835, 5.57483 0.571724, 5.57483 0.271482, 5.58452 0.252111, 5.60389 0.242426, 5.64263 0.242426, 5.65232 0.203685, 5.61358 0.184315, 5.57483 0.184315, 5.53609 0.203685, 5.50704 0.252111, 5.4683 0.223056, 5.41019 0.194, 5.37145 0.184315, 5.31333 0.184315, 5.27459 0.194, 5.24554 0.21337, 5.21648 0.242426, 5.19711 0.290852, 5.19711 0.348964, 5.21648 0.39739, 5.26491 0.445816, 5.30365 0.465186, 5.37145 0.484557, 5.49735 0.484557, 5.49735 0.562039, 5.47798 0.60078, 5.42956 0.629835, 5.35207 0.629835, 5.29396 0.610465, 5.23585 0.581409, 5.23585 0.658891), (5.49735 0.436131, 5.41019 0.436131, 5.35207 0.41676, 5.31333 0.39739, 5.28428 0.348964, 5.28428 0.300538, 5.30365 0.271482, 5.34239 0.242426, 5.39082 0.242426, 5.44893 0.261797, 5.49735 0.300538, 5.49735 0.436131), (5.9037 0.765429, 5.98119 0.765429, 5.98119 0.668576, 6.20395 0.668576, 6.20395 0.610465, 5.98119 0.610465, 5.98119 0.300538, 6.01024 0.261797, 6.06835 0.242426, 6.16521 0.242426, 6.21363 0.252111, 6.21363 0.194, 6.14583 0.184315, 6.02961 0.184315, 5.96182 0.203685, 5.93276 0.232741, 5.91339 0.261797, 5.9037 0.310223, 5.9037 0.610465, 5.7778 0.610465, 5.7778 0.668576, 5.9037 0.668576, 5.9037 0.765429), (6.4747 0.765429, 6.55219 0.765429, 6.55219 0.668576, 6.77495 0.668576, 6.77495 0.610465, 6.55219 0.610465, 6.55219 0.300538, 6.58124 0.261797, 6.63935 0.242426, 6.73621 0.242426, 6.78463 0.252111, 6.78463 0.194, 6.71683 0.184315, 6.60061 0.184315, 6.53282 0.203685, 6.50376 0.232741, 6.48439 0.261797, 6.4747 0.310223, 6.4747 0.610465, 6.3488 0.610465, 6.3488 0.668576, 6.4747 0.668576, 6.4747 0.765429), (7.16677 0.901022, 7.27331 0.901022, 7.27331 0.794484, 7.16677 0.794484, 7.16677 0.901022), (7.00212 0.678262, 7.26362 0.678262, 7.26362 0.194, 7.18614 0.194, 7.18614 0.62015, 7.00212 0.62015, 7.00212 0.678262), (7.51501 0.678262, 7.59249 0.678262, 7.59249 0.581409, 7.63123 0.629835, 7.67966 0.668576, 7.73777 0.687947, 7.79588 0.687947, 7.83462 0.678262, 7.87336 0.649206, 7.89273 0.610465, 7.90242 0.571724, 7.90242 0.194, 7.82494 0.194, 7.82494 0.552354, 7.80557 0.591094, 7.76683 0.610465, 7.7184 0.610465, 7.66997 0.591094, 7.59249 0.513613, 7.59249 0.194, 7.51501 0.194, 7.51501 0.678262), (8.42499 0.678262, 8.49279 0.678262, 8.49279 0.203685, 8.4831 0.145574, 8.46373 0.0971477, 8.43468 0.0584068, 8.39594 0.0293511, 8.34751 0.00998063, 8.29908 0.0002954, 8.21192 0.0002954, 8.16349 0.00998063, 8.11506 0.0196659, 8.11506 0.0874625, 8.18286 0.068092, 8.23129 0.0584068, 8.30877 0.0584068, 8.3572 0.0777772, 8.38625 0.106833, 8.41531 0.155259, 8.41531 0.300538, 8.37657 0.252111, 8.32814 0.21337, 8.27971 0.194, 8.21192 0.194, 8.16349 0.21337, 8.11506 0.252111, 8.08601 0.300538, 8.06664 0.368334, 8.06664 0.474872, 8.08601 0.552354, 8.12475 0.62015, 8.18286 0.668576, 8.24097 0.687947, 8.30877 0.687947, 8.37657 0.658891, 8.42499 0.62015, 8.42499 0.678262), (8.41531 0.562039, 8.41531 0.358649, 8.37657 0.310223, 8.33783 0.281167, 8.2894 0.261797, 8.25066 0.261797, 8.20223 0.290852, 8.17318 0.329593, 8.15381 0.39739, 8.15381 0.474872, 8.16349 0.523298, 8.19255 0.581409, 8.25066 0.629835, 8.31845 0.629835, 8.36688 0.60078, 8.41531 0.562039), (8.71512 0.901022, 8.98631 0.901022, 8.98631 0.194, 8.90883 0.194, 8.90883 0.84291, 8.71512 0.84291, 8.71512 0.901022), (9.17958 0.678262, 9.26675 0.678262, 9.4314 0.310223, 9.59605 0.678262, 9.66384 0.678262, 9.40234 0.0874625, 9.3636 0.0390363, 9.32486 0.0196659, 9.27644 0.00998063, 9.20864 0.00998063, 9.20864 0.068092, 9.28612 0.068092, 9.32486 0.0971477, 9.38297 0.21337, 9.17958 0.678262), (9.92976 0.329593, 10.0557 0.329593, 10.0557 0.203685, 9.92976 0.203685, 9.92976 0.329593), (10.37 0.678262, 10.4475 0.678262, 10.4475 0.581409, 10.4862 0.629835, 10.5347 0.668576, 10.5928 0.687947, 10.6509 0.687947, 10.6896 0.678262, 10.7284 0.649206, 10.7477 0.610465, 10.7574 0.571724, 10.7574 0.194, 10.6799 0.194, 10.6799 0.552354, 10.6606 0.591094, 10.6218 0.610465, 10.5734 0.610465, 10.525 0.591094, 10.4475 0.513613, 10.4475 0.194, 10.37 0.194, 10.37 0.678262), (11.3478 0.41676, 11.0088 0.41676, 11.0088 0.368334, 11.0282 0.329593, 11.0475 0.300538, 11.096 0.261797, 11.1541 0.242426, 11.2316 0.242426, 11.28 0.252111, 11.3478 0.271482, 11.3478 0.21337, 11.28 0.194, 11.2219 0.184315, 11.1347 0.184315, 11.0863 0.194, 11.0185 0.223056, 10.9701 0.261797, 10.941 0.310223, 10.9216 0.378019, 10.9216 0.474872, 10.9313 0.523298, 10.9507 0.571724, 10.9991 0.629835, 11.0475 0.668576, 11.1153 0.687947, 11.1928 0.687947, 11.2509 0.668576, 11.2897 0.639521, 11.3187 0.60078, 11.3381 0.552354, 11.3478 0.494242, 11.3478 0.41676), (11.2606 0.474872, 11.0088 0.474872, 11.0185 0.523298, 11.0379 0.562039, 11.076599999999999 0.610465, 11.1153 0.629835, 11.1735 0.629835, 11.2122 0.610465, 11.2413 0.581409, 11.2606 0.532983, 11.2606 0.474872), (11.6137 0.765429, 11.6912 0.765429, 11.6912 0.668576, 11.9139 0.668576, 11.9139 0.610465, 11.6912 0.610465, 11.6912 0.300538, 11.7202 0.261797, 11.7784 0.242426, 11.8752 0.242426, 11.9236 0.252111, 11.9236 0.194, 11.8558 0.184315, 11.7396 0.184315, 11.6718 0.203685, 11.6428 0.232741, 11.6234 0.261797, 11.6137 0.310223, 11.6137 0.610465, 11.4878 0.610465, 11.4878 0.668576, 11.6137 0.668576, 11.6137 0.765429)))',0)


Spatial Data, Part 4: Data Sets

This is part four in a series:

Besides just looking around at geographical features like states, roads, and buildings, we can incorporate human-centric data like restaurants, crimes, and more.

Data Sets

1. Toxic Release Inventory

A “TRI” as reported by the EPA is a Toxic Release Inventory. This could be a chemical spill or some other accident that’s not in our best health interests. Here are the incidents that took place in Kentucky over a ten-year period.

select geometry::CollectionAggregate(geom)
from ky_counties
union all
select geom.STBuffer(.05)
from ky_tri
where geom.STWithin((select geom from us_stateshapes where state = 'KY')) = 1

The EPA offers an interactive map of TRI’s around the country here.

TRIs in KY

2. Toxic Incidents in Jefferson County

select geometry::CollectionAggregate(geom).STBuffer(.001)
from ky_interstates where geom.STWithin((select geom from ky_counties where id = 24)) = 1
union all 
select geometry::CollectionAggregate(geom) from ky_counties where id = 24
union all
select geom.STBuffer(.01) from ky_tri where geom.STWithin((select geom from ky_counties where id = 24)) = 1

Bringing it close to home, here are the incidents that have taken place in my own hometown of Louisville, KY.

TRIs in Jefferson County

3. Add in the Sirens

select geometry::CollectionAggregate(geom).STBuffer(.001)
from ky_interstates where geom.STWithin((select geom from ky_counties where id = 24)) = 1
union all 
select geom from ky_counties where id = 24
union all
select geometry::UnionAggregate(geom.STBuffer(.01)) from ky_tri where geom.STWithin((select geom from ky_counties where id = 24)) = 1
union all
select geometry::UnionAggregate(geom.STBuffer(.02)) from ky_sirens

The siren radius is an estimate, since the hearing distance can depend on a lot of factors. Still, it’s disturbing to see that there are some parts of the county that have had several incidents but have no sirens for tens of miles. The I-65 corridor on the southern half of Louisville has its share of problems, but no active alert system.

TRIs with Sirens

4. Toxic Incidents per County

;with tox (id, ct) as
(select, count(*) from ky_counties c join ky_tri t on c.geom.STContains(t.geom) = 1 group by
select c.geom, isnull(tox.ct,0) Incidents
from ky_counties c
left outer join tox on =

By counting the number of incidents per county in a CTE, and joining that to the county map, we can display that number on the SQL map.

On the good side, it’s good to see that there are several counties with zero incidents; on the bad side, 73 in my county? Ouch.

TRIs per County

5. Toxic Incident Range Bands

select geometry::CollectionAggregate(geom), '0' Incidents
from ky_counties c where not in (select id from tox) union all
select geometry::CollectionAggregate(geom), '< 5'
from ky_counties c inner join tox on = where tox.ct < 5 union all
select geometry::CollectionAggregate(geom), '5 - 10'
from ky_counties c inner join tox on = where tox.ct between 5 and 10 union all
select geometry::CollectionAggregate(geom), '11 - 25'
from ky_counties c inner join tox on = where tox.ct between 11 and 25 union all
select geometry::CollectionAggregate(geom), '> 25'
from ky_counties c inner join tox on = where tox.ct > 25

Seeing the individual numbers is informative, but divvying up the data into range bands can make it easier to find patterns and trends.

TRIs with Range Bands

Since we aggregated the counts into just five range bands, they’re considered just five objects, and therefore only five labels appear.

The southeast part of the state looks pretty good, with mostly light blue zeros and a scattering of tan lows. There’s an almost solid dark blue of tens riding up along I-75. And, if I had to present this kind of bad news to my boss, you can see that I’ve cleverly downplayed Louisville’s 73 TRIs into an “over 25” category.

6. Low Food Scores

;with s (id, dt) as
(select establishmentid, max(inspectiondate) from ky_food
where typedescription = 'food service' group by establishmentid)
select geom.STBuffer(.01) Geom, establishmentname + ': ' + convert(varchar,f.score) Name
from ky_food f 
inner join s on f.establishmentid = and f.inspectiondate = s.dt
where score < 90
union all
select geometry::CollectionAggregate(geom), ''
from ky_interstates where geom.STWithin((select geom from ky_counties where id = 24)) = 1
union all
select geom.STBoundary(), '' from ky_counties where id = 24

Instead of the bad stuff that we may inadvertently breathe and drink due to industrial accidents or chemical spills, let’s look at the bad stuff that we put into our bodies on purpose. Here are the restaurant health inspection codes (usually the “A” papers that we see posted) that are not up to the A level (score < 90).

Here, I’m plotting the low-grade restaurants (using a CTE because I only want the most recent inspection date for each restaurant) around the city, and setting the hover-text to be the name and score.

Low Food Scores

The data I’m using is from early 2015. So Phi Binh Minh (and the others) might have an “A” in the window these days. Don’t take my map as a food critic review.

7. Violent Crimes in Jefferson County

select geom.STBuffer(.001), left(Crime,1) Crime, convert(varchar,IncidentDate,111) Date
from ky_crime
where geom.STWithin((select geom from ky_counties where id = 24)) = 1
and crime in ('homicide', 'aggravated assault', 'simple assault') and year(incidentdate) = 2014
union all
select geometry::CollectionAggregate(geom), null, null
from ky_interstates where geom.STWithin((select geom from ky_counties where id = 24)) = 1
union all
select geom.STBoundary(), null, null from ky_counties where id = 24

More bad news! Sorry.

Here are the violent crimes in town (thankfully, relatively few homicides) over the course of a year, with the first letter of each of those crimes in our hover-text.

Louisville Violent Crimes

The west end, downtown, and the airport region seem to have the worst of it.

8. Violent Crimes in Jefferson County with Bubble Sizes

;with siz (lat, lon, sz) as
(select latitude, longitude, count(*) size from ky_crime where crime in ('homicide', 'aggravated assault', 'simple assault') group by latitude, longitude)
select geom.STBuffer(sz * .0003) from ky_crime c 
inner join siz on c.latitude = and c.longitude = siz.lon
where crime in ('homicide', 'aggravated assault', 'simple assault')
and geom.STWithin((select geom from ky_counties where id = 24)) = 1 and sz > 1
union all
select geometry::CollectionAggregate(geom)
from ky_interstates where geom.STWithin((select geom from ky_counties where id = 24)) = 1
union all
select geom.STBoundary() from ky_counties where id = 24

Instead of just seeing where they are, how about we highlight the dangerous parts of town by making each dot (an address where a crime happened) wider for every crime that happened there, and ignore the dots where only one crime happened. The previous maps showed us which parts of town are dangerous, but this shows us which specific places are “repeat customers” for crimes.

KY Crime Bubbles

Of course, even one violent crime is too many. And my filename for this image (KY Crime Bubbles) sounds like a street gang made up of clowns or janitors.

The underlying data shows that one of the larger circles could actually be removed. The puke-green circle around the center of the second grid square is some sort of default address for the city. The address is listed simply as “Louisville Metro.” Even so, we learn a lot by plotting our own data on a map like this.

9. Reported Crimes (Bad Geo)

select geom.STBuffer(.5), crime, convert(varchar,IncidentDate,111) Date
from ky_crime
where geom.STWithin((select geom from us_stateshapes where state = 'KY')) = 0
union all
select geometry::CollectionAggregate(geom), null, null from us_stateshapes

So far, I’ve been limited the data displayed just to what happens within Louisville/Jefferson County, but sometimes crimes get reported to the Louisville Metro Police that take place out of town.

Bad Geo

Oh, and there are a lot of crimes (32 of them, not all that many compared to the overall volume, really) that happen way out in the middle of the ocean. Actually, no. That’s location 0, 0 (one of those 0’s is the equator, and the other is the prime meridian, which is also where the GMT time zone starts). If no geodata was recorded with the incident, many systems will default it to there. For data like that, you can ignore it or correct it by geocoding it (assigning a latitude and longitude to an address). There are some services that will do that for free, for a given amount of addresses (google maps will geocode 10,000 per day, for example), and other services that will geocode for a fee.

Data Sources

Here are the data sets use for this lesson (the us_stateshapes, ky_counties, and ky_interstates have appeared previously.)

  • TRI data is here.
  • Siren, food, and crime data (and dozens of other Louisville data sets) are here.

Getting Involved

I encourage all of you to join in this year’s National Day of Civic Hacking, which is sponsored by the White House’s open data portal. It’s an annual event put on by the Code for America Brigade to build apps in a single day with other local coders / designers / data’ers, primarily based on using open data sets.

In Louisville, in the past few years, we’ve had apps that:

Your area might also have a chapter of MapTime, which uses and enhances geodata in a variety of ways.