Importing GeoJSON Earthquake Data Into SQL Server

A significant portion of Yellowstone National Park sits on top of a supervolcano.  Although it’s not likely to erupt any time soon, the park is constantly monitored for geological events like earthquakes.

This week I want to take a look at how you can import this earthquake data, encoded in GeoJSON format, into SQL Server in order to be able to analyze it using SQL Server’s spatial functions.

Watch this week’s post on YouTube! I really enjoyed making all of the overlays for this episode.


The source for the data we’ll be using is the 30-day earthquake feed from the USGS.  This data is encoded in the GeoJSON format, a specification that makes it easy to share spatial data via JSON.  To get an idea of how it looks, here’s an extract:

The key thing we’ll be examining in this data is the “features” array: it contains one feature object for each earthquake that’s been recorded in the past 30 days.  You can see the “geometry” child object contains lat/long coordinates that we’ll be importing into SQL Server.

If you want the same 30-day GeoJSON extract we’ll be using in all of the following demo code, you can download it here.

Importing GeoJSON into SQL Server

There’s no out of the box way to import GeoJSON data into SQL Server.

However, using SQL Server’s JSON functions we can build our own solution pretty easily.

First, let’s create a table where we can store all of earthquake data:

Then, let’s use the OPENJSON() function to parse our JSON and insert it into our table:

We use OPENJSON() to parse our JSON hierarchy and then concatenate together the lat and long values into our well known text format to be able to use it with SQL Server’s spatial function STPointFromText:

What results is our earthquake data all nicely parsed out into our dbo.EarthquakeData table:

What about Yellowstone?

The above data includes earthquakes from around world.  Since we only want to examine earthquakes in Yellowstone, we’ll need to filter the data out.

There’s a handy Place column in the data that we could probably add a LIKE ‘%yellowstone%’ filter to – but this is a post about spatial data in SQL, we can do better!

The Wyoming State Geological Survey website has Shapefiles for the boundary of Yellowstone National Park.  Since we are practicing our GeoJSON import skills, I converted the Shapefiles to GeoJSON using an online converter and the resulting data looks like this:

You can download the full park boundary GeoJSON file here.

Just like before, we’ll use SQL Server’s OPENJSON() function to parse our GeoJSON data into a well-known text POLYGON.

First we create our table:

And then populate it, this time using the STPolyFromText spatial function:

Filtering our data

Now we have two tables: dbo.EarthquakeData and dbo.ParkBoundaries.  What we want to do is select only the Earthquake data points that fall within the boundaries of Yellowstone National Park.

This is easy to do using the STIntersects spatial function, which returns a “1” for any rows where one geography instance (our lat/long earthquake coordinate) intersects another geography instance (our park boundary):

The rest is up to you

So all it takes to import GeoJSON data into SQL Server is knowing how to use SQL Server’s JSON functions.

Once geographical data is imported into geography data types, SQL Server’s spatial functions offer lots of flexibility for how to efficiently slice and dice the data.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

A Better Way To Script Database Objects

Photo by Andy Beales on Unsplash

Happy New Year! My New Year’s resolution for 2018 is to help you become a better SQL developer.

I want to start off with that today by showing you a much better way to generate database object change scripts.

Prefer video?  Watch this week’s post on YouTube instead!

If you are like I used to be for YEARS, anytime you want to copy a table, index, etc… you probably right click on that object in SQL Server Management Studio and click “Script <Table|Index|View|etc…> as” > “CREATE To”:

An inefficient way of generating change scripts

This is a pretty easy way to quickly script database objects, however it’s incomplete.

For starters, I’m forced to generate the scripts for tables one at a time.  Not fun.

Additionally, if I want to script associated objects for that table, like indexes, I have to go to each index and then right-click and select “Script Index as”.  Ugh.

There Is A Better Way

Instead of using “Script Table as”, you can right click on your database and choose “Tasks” > “Generate Scripts…”:

This option brings up a GUI that will allow you to script multiple objects at the same time:

Look ma, multiple objects at once!

Additionally if you click the “Advanced” button in the final page of the dialog, you will receive many more options for how your objects will get scripted, including the ability to script out the indexes!

Ooooooo, ahhhh – everything scripted in one fell swoop!

Using the Generate Scripts Task it’s easy to generate table, index, stored procedure, etc… change scripts all in one step – nice!

NOTE: If your SQL User receives an error when trying to generate the scripts, make sure they have the following access:

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!