Spatial SQL for the Geographer now on GitHub

A long time ago, in an apartment up the street, I put together a bunch of blog posts with simple examples of using Microsoft SQL for spatial data. Those blogs, aptly titled, Spatial SQL for the Geographer, still get a good amount of hits five years after they were originally posted.

Knowing that these old blogs still get traffic, I wanted to modernize them a little bit.  As with all my 2016 projects, I wanted to do something I hadn’t done before, or improve a skill.  So for this project, I moved all the scripts from the blog posts into my GitHub account:

The code is now much more accessible and not buried in the text of a blog post, which is really why I wanted to get them into GitHub.

The ReadMe has links to all the original blog posts. Those posts give an in-depth look into how the scripts work, references to MSDN documentation, and links to the original sample data download. I wanted to make sure this is still relevant, so I have tested the data and scripts though Microsoft SQL 2014 using their Express version. Everything works.

Check it out, make some improvements, or add some new script examples.  Spatial analysis in Microsoft SQL Server isn’t that popular with geographers and geo-techies alike, but it does have it’s place. Hopefully these scripts help get someone started on the right path.

The next step is to translate these into PostGIS and put together a Spatial PostGIS for the Geographer post!


Posted in Open Source GIS, Spatial Analysis | Comments Off on Spatial SQL for the Geographer now on GitHub

OpenStreetMap and Me: Mapping Everyday of January (almost)

For 2016 I have a few goals.  One of those goals was to contribute to OpenStreetMap more often.  To get the year started off right I decided to contribute to OpenStreetMap (almost) everyday for the month of January. I haven’t taken on a big project in OpenStreetMap project in a couple years. In fact, I hadn’t done anything in two years!  For someone who praises OpenStreetMap, I wasn’t contributing at all!  My last map-out was the University of Connecticut’s main campus and I had only made one minor edit between then and my January map-a-thon.

I needed a goal. I couldn’t just map all willy-nilly.  I first thought of mapping where I currently live in metro Boston, but most of the area has been fully mapped for a long time. So, I shifted my focus north, to map out my hometown, Littleton, New Hampshire. It’s a small town in the White Mountains of northern New Hampshire.  Littleton has recently become “cool” and has a burgeoning art scene. It’s a nice small town and it needed to be mapped.

The Plan

I started with pretty much a clean slate.  Beyond what I would call the base layers (some cleaned TIGER roads, GNIS features, national forests, etc), there were only a few buildings and place markers in the town, some of which I added a while back.  I figured if I mapped 15-30 minutes a night and a little longer on the weekends, I could get a big chunk of the mapping done by the end of the month.

I decided I was going to digitize buildings and improve as many other features as I could. My plan was to first focus on the features along the main roads in the densest parts of town, and then work into the neighborhoods. I also wanted to be consistent in my digitizing and attributing. That meant I used the same imagery, building type categories, and addressing schemes throughout the project. Consistency was key.

Data Sources

I used the Bing imagery available in the iD editor for digitizing features.  For addressing and feature naming I used the Town of Littleton’s tax maps powered by Littleton based geo-consultants, CAI Technologies.  Most probably wouldn’t professionally digitize using older leaf-on Bing imagery, but I used what was available.  If was editing OpenStreetMap in QGIS or ArcMap I could have pulled in better imagery, but I wanted to use iD. To improve my accuracy, I would frequently cross reference with the tax data and Google images. I also used the Mapbox imagery to spot check locations as necessary.

Time Commitment

I planned for 15-30 minutes a night, and on the weekends I was able to commit to some longer time frames. On average my working sessions would last a little longer than 30 minutes.

The Results!

I mapped from January 11 until February 3- technically not a full month, nor all in January.  I got a late start because I was too busy not starting other New Year’s resolutions.  I also missed one Friday night and kept mapping into February because I didn’t realize it wasn’t January. My Changesets are visualized below.  A couple times I did venture outside the borders of Littleton, mainly to add school building footprints.

Bspauld Changsets - 1/22/2016 to 2/3/2016

Bspauld Changsets – 1/22/2016 to 2/3/2016

I didn’t get nearly as much done as I wanted to.   I greatly over estimated how much I could get done each night.  I thought I would be able to get a majority of the buildings digitized and addressed in the downtown area and along the main roadways.  I was able to get the main buildings completed along the core roadways, but I didn’t even touch many of the side streets and smaller neighborhoods.   I was able to clean-up and update a number of existing features, including many of the points of interest that were already there.


View of Littleton, NH – I mapped maybe 20% of my goal

When I realized that I wasn’t going to meet my goal I switched gears. I started to add and update as many “important” features into OpenStreetMap.  That included the local hospital, schools, parks, important businesses, tourist locations, and my parents house.

map (1)

Littleton Regional Hospital

Adding Businesses, Including Jack's Lazer Car Wash

Adding Commercial Buildings, Including Jack’s Lazer Car Wash

When I got the “important” features completed, I spent most of my time focusing on the downtown area. Littleton is a central town to the larger region, so I figured focusing on the central business district would have the biggest impact on future users.  As I mentioned, some of this area was already digitized before I started my project from some work I did a few years ago, and by other contributors.  I cleaned-up existing features and added new ones. I plan on going back and adding more businesses and landmarks when time allows.

Fun fact:  Littleton is home to the world’s longest candy counter, located in Chutters General Store.

map (4)

Second Fun Fact!  Littleton was home to Eleanor H. Potter, the author of the Pollyanna books. There is a statue of Pollyanna on the front lawn of Littleton’s Public Library, which is included in OpenStreetMap.

Pollyanna Statue at Littleton Public Library

Pollyanna Statue at the Littleton Public Library

Challenges and Lessons Learned

I am a professional geographer.  There was one time in my career when I was paid to do this type of digitization, so I had an idea of what I was getting myself into when I started this project.  I knew I was going to have some issues with accurate digitization using an older low resolution Bing dataset.  If you compare my building footprints to what CAI has done there are differences.  I expect that, and most geo-professionals would.  For the most part, the features I updated and added are pretty good.  They will work wonderfully in OpenStreetMap for years to come. But, if you want official building footprints, give the Town of Littleton a call.

I used iD as my primary data editing tool. Working with iD is awesome.  The usability of the tool is incredible.  If you have used desktop GIS to mass edit geo-features and then have used iD for OpenStreetMap projects, you’ll never want to go back to those desktop programs.  It is such a fluid tool, from drawing to attributing. From tool options appearing when hovering over features in the map to type-ahead aiding the Search option, iD gets a lot of things write when it comes to geospatial data editing.  I love it.  Mapbox has done a lot of amazing stuff over the past few years and iD is way up on my list of Mapbox awesome.

There is a lot left to do in this project.   Even though the blog is done, the mapping is not.  I will continue contributing on a regular basis until I am done. I took on this project because I wanted to challenge myself and to get back into contributing to OpenStreetMap more regularly.  I think I met both of those objectives. I hope the results benefit the OpenStreetMap community and the community of Littleton!

I encourage you to check out your hometown in OpenStreetMap.  If looks a little empty, maybe you should start to map it.

Posted in GIS, Open Source GIS | Tagged | Comments Off on OpenStreetMap and Me: Mapping Everyday of January (almost)

It’s 2016 and I Want to be Better at What I Do

Welcome to the future, 2016. It’s nice to meet you.

2015 was a great year.   I did a lot of cool stuff, and almost all of it wasn’t geo-related.   Seriously, like 95% of the things I did this year had nothing to do with geo or GIS.  That’s all fine and good, but I really love geo. I went to school for a long time to just kinda give up on it in the past year.

During the last few days of 2015 I made a list of things I want to do in 2016.  The list is fairly short. I want to start to learn Mandarin Chinese, become a better runner, read more, remember my passwords to stuff, and get better at what I do.  Geo is “what I do” and I really want to get better at it in 2016.

I know the path to meeting a couple of my 2016 goals, but how do I get better at geo? I’ve been working in geo for almost 10 years.  I have a job that allows me to challenge myself on a regular basis, and do a lot of geo work.

In this case, I think the path to getting better is by doing more. Practice makes perfect. In 2016 I will strive to create more maps, tools, algorithms, datasets or anything else.  This, I hope, will force me to learn new skills, hone my existing skills, and inspire me to try new things and test new ideas. I won’t just leave ideas written down on a post-it note on my desk.

I will make some maps from those ideas I have written down, post tools and code I create to my deserted github account, write more technical and research/analysis posts (even if they aren’t any good), contribute to my local geo-community, and make a real effort to add to OSM more than I have in the past.

By doing more, I think I can keep challenging myself to expand but also refine and tighten my geo-skills. More importantly, I will keep myself motivated and interested in geo! I’ve definitely already put the first 10,000 hours in.  Maybe the next 10,000 will really define who I am as a professional geographer and member of the geo-community.

Here’s to what is hopefully the most productive and inspiring geo-year of my life!




In 2017 I will stop over using the prefix Geo.

Posted in GIS, GIS Analysis, GIS Software | Comments Off on It’s 2016 and I Want to be Better at What I Do