Importing GeoJSON Earthquake Data Into SQL Server

Published on: 2018-01-16

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.

GeoJSON

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:

{
    "type": "FeatureCollection",
    "metadata": {
        "generated": 1515603955000,
        "url": "https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/1.0_month.geojson",
        "title": "USGS Magnitude 1.0+ Earthquakes, Past Month",
        ...
    },
    "features": [{
            "type": "Feature",
            "properties": {
                "mag": 1.25,
                "place": "7km NW of The Geysers, CA",
                "time": 1515603166510,
                ...
            },
            "geometry": {
                "type": "Point",
                "coordinates": [-122.8221664, 38.8175011, 2.02]
            },
            "id": "nc72952110"
        },
        ...
        ],
    "bbox": [-179.921, -62.519, -3.35, 179.9551, 85.3951, 608.58]
}

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:

CREATE TABLE dbo.EarthquakeData
(
    Id int IDENTITY PRIMARY KEY,
    EventDate DATETIME2,
    Magnitude FLOAT,
    Place VARCHAR(300),
    Coordinates GEOGRAPHY,
    Long varchar(100),
    Lat varchar(100)
)

CREATE SPATIAL INDEX IX_Coordinates ON dbo.EarthquakeData (Coordinates)

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

DECLARE @GeoJSON nvarchar(max) = N'{...}' -- Put the GeoJSON here

INSERT INTO dbo.EarthquakeData (EventDate,Magnitude,Place,Coordinates,Long,Lat)
SELECT 
    -- Convert unix epoch time to datetime
    -- We also know the source is in UTC, so we specify that using AT TIME ZONE for easier conversions down the road
    DATEADD(second,cast(UnixMillisecondsSinceEpoch/1000 as int),'1970-01-01 00:00:00') AT TIME ZONE 'UTC' AS EventDate,
    Magnitude,
    Place,
    -- Build our geography datatype
    geography::STPointFromText('POINT ('+Long + ' ' + Lat + ')', 4326) AS Geography,
    Long,
    Lat
FROM 
    OPENJSON(@GeoJSON, '$.features')
        WITH (
            UnixMillisecondsSinceEpoch bigint '$.properties.time',
            Magnitude float '$.properties.mag',
            Place varchar(300) '$.properties.place',
            Long varchar(100) '$.geometry.coordinates[0]',
            Lat varchar(100) '$.geometry.coordinates[1]'
        )

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:

geography::STPointFromText('POINT ('+Long + ' ' + Lat + ')', 4326) AS Geography

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:

{
    "type": "FeatureCollection",
    "bbox": [-111.15354785438899, 44.13238494057162, -109.816111731858, 45.102865336505396],
    "features": [{
        "type": "Feature",
        "geometry": {
            "type": "Polygon",
            "coordinates": [
                [
                    [-111.0970801722779, 44.487322588834374],
                    [-111.09707931336956, 44.48929779632266],
                    [-111.09707877845554, 44.49109904485493],
                    [-111.09707888353985, 44.49472122457225],.
                    ...
                ]
            ]
        },
        ...
    }]
}

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:

DROP TABLE IF EXISTS dbo.ParkBoundaries
CREATE TABLE dbo.ParkBoundaries
(
    Id int IDENTITY PRIMARY KEY,
    ParkName varchar(100),
    ParkBoundary GEOGRAPHY
)
CREATE SPATIAL INDEX IX_Boundary ON dbo.ParkBoundaries (ParkBoundary)

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

INSERT INTO dbo.ParkBoundaries (ParkName, ParkBoundary)
SELECT
    'Yellowstone National Park' AS ParkName,
    geography::STPolyFromText('POLYGON ((' + STRING_AGG(CAST(Long + ' ' + Lat as varchar(max)), ',') + '))',4326).ReorientObject() AS ParkBoundary
FROM
    (
    SELECT 
        Long,
        Lat
    FROM
        OPENJSON(@Yellowstone, '$.features[0].geometry.coordinates[0]')
        WITH
            (
                Long varchar(100) '$[0]',
                Lat varchar(100) '$[1]'
            )
)d

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):

SELECT 
    d.Id,
    d.Magnitude,
    d.EventDate,
    d.Place,
    b.ParkName
FROM 
    dbo.EarthQuakeData d
    CROSS JOIN dbo.ParkBoundaries b
WHERE
    Coordinates.STIntersects(ParkBoundary) =1
    AND b.ParkName = 'Yellowstone National Park'
ORDER BY
    Magnitude DESC

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!

10 thoughts on “Importing GeoJSON Earthquake Data Into SQL Server”

  1. Hi, really a great article!
    One correction though, you are missing a ‘ in the 1st line of your insert GeoJSON statement.

    Replace
    DECLARE @GeoJSON nvarchar(max) = N'{…} — Put the GeoJSON here
    with
    DECLARE @GeoJSON nvarchar(max) = N'{…}’ — Put the GeoJSON here

    ———————————————————————-
    INSERT INTO dbo.EarthquakeData (EventDate,Magnitude,Place,Coordinates,Long,Lat)
    SELECT
    — Conve……
    ———————————————————————-

    Greetings from Amsterdam, the Netherlands

  2. Hello,

    I am using SQl 2014 and the STRING_AGG and With commands don’t seem to work.

    For the String_agg I converted it to:

    INSERT INTO dbo.txbuildings (Shape)
    SELECT
    geography::STPolyFromText(‘POLYGON ((‘ + CAST(Longitude as varchar(max)) + ‘,’
    + CAST(Latitude as varchar(max)) + ‘))’,4326).ReorientObject() as Shape

    But the With command part of the following code keeps giving the error (please see error afte the code snippet)

    INSERT INTO dbo.txbuildings (Shape)
    SELECT
    geography::STPolyFromText(‘POLYGON ((‘ + CAST(Longitude as varchar(max)) + ‘,’
    + CAST(Latitude as varchar(max)) + ‘))’,4326).ReorientObject() as Shape
    FROM(
    SELECT
    Longitude,
    Latitude
    FROM
    OPENJSON(@JSON, ‘$.features[0].geometry.coordinates[0]’)
    WITH(Longitude varchar(100) ‘$[0]’, Latitude varchar(100) ‘$[1]’)
    )

    ERROR:
    Msg 156, Level 15, State 1, Line 19
    Incorrect syntax near the keyword ‘WITH’.
    Msg 319, Level 15, State 1, Line 19
    Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

    I tried putting the semicolon to the previous statement but it does not seem to work.

    Can you recommend another solution?

    Thanks

    1. Hi Ujvala, the JSON functions were introduced in SQL Server 2016, so I’m guessing you are experiencing errors in trying to use the OPENJSON function.

  3. Hi Bret,

    Sorry…I misread your comment. Yes I am having an issue with the OPENJSON function.

    Is there any alternative to that under SQL Server 2014?

    Can we do something similar in Postgresql?

    Thanks!

  4. Hi Bret,

    I modified the code a little bit and tried it on SS2017 and it works but it is loading only one record. Is there a way to upload all records? (looping yes, but I am looking for a statement)

    Thanks much!

    1. Hi Ujvala,
      Glad to hear you got it working on SQL Server 2017.

      Not sure what you mean exactly by “all records” – you should be able to do it probably with OPENJSON and CROSS APPLY though. If you can create some example test data and query, post it to https://dba.stackexchange.com/ and someone might be able to help you out (feel free to link to the question in these comments and I can take a look as well). Good luck!

  5. Hi Bret,

    Here is the code snippet that I am working on. This loads only the first polygon into the new table. I have multiple polygons in the geoJson file. How do I upload all of them. I understand that CRSOSS APPLY or OUTER APPLY are similar to self joins but need to understand why I would need that.

    CREATE TABLE dbo.tbuildings
    (
    Id int IDENTITY PRIMARY KEY,
    Shape GEOGRAPHY
    )
    CREATE SPATIAL INDEX IX_Boundary ON dbo.tbuildings (Shape)

    DECLARE @JSON nvarchar(max)

    — load the geojson into the variable
    SELECT @JSON = BulkColumn
    FROM OPENROWSET
    (BULK ‘C:\test\test.geojson’, SINGLE_CLOB) as JSON

    INSERT INTO dbo.tbuildings (Shape)
    SELECT
    geography::STPolyFromText(‘POLYGON ((‘ + STRING_AGG(CAST(Longitude + ‘ ‘ + Latitude as varchar(max)), ‘,’) + ‘))’,4326).ReorientObject()
    AS Shape

    FROM(
    SELECT
    Longitude,
    Latitude
    FROM
    OPENJSON(@JSON, ‘$.features[0].geometry.coordinates[0]’)
    WITH(Longitude varchar(100) ‘$[0]’, Latitude varchar(100) ‘$[1]’)
    )d

  6. Here’s a test dataset

    {
    “type”:”FeatureCollection”,
    “features”:
    [
    {“type”:”Feature”,”geometry”:{“type”:”Polygon”,”coordinates”:[[[-155.874593,19.207723],[-155.87452,19.207684],[-155.874489,19.207735],[-155.874639,19.207816],[-155.874702,19.207712],[-155.874625,19.20767],[-155.874593,19.207723]]]},”properties”:{}},
    {“type”:”Feature”,”geometry”:{“type”:”Polygon”,”coordinates”:[[[-155.868595,19.206225],[-155.868544,19.206225],[-155.868544,19.206175],[-155.868447,19.206175],[-155.868447,19.206314],[-155.868595,19.206314],[-155.868595,19.206225]]]},”properties”:{}},
    {“type”:”Feature”,”geometry”:{“type”:”Polygon”,”coordinates”:[[[-155.861671,19.204379],[-155.861556,19.204319],[-155.861512,19.204394],[-155.861628,19.204454],[-155.861671,19.204379]]]},”properties”:{}},
    {“type”:”Feature”,”geometry”:{“type”:”Polygon”,”coordinates”:[[[-155.770331,19.163372],[-155.770258,19.16344],[-155.770304,19.163484],[-155.770377,19.163415],[-155.770331,19.163372]]]},”properties”:{}},
    {“type”:”Feature”,”geometry”:{“type”:”Polygon”,”coordinates”:[[[-155.780961,19.116794],[-155.780838,19.116869],[-155.780896,19.116955],[-155.781019,19.11688],[-155.780961,19.116794]]]},”properties”:{}},
    {“type”:”Feature”,”geometry”:{“type”:”Polygon”,”coordinates”:[[[-155.779772,19.118749],[-155.779719,19.118682],[-155.779604,19.118765],[-155.779657,19.118831],[-155.779772,19.118749]]]},”properties”:{}},
    {“type”:”Feature”,”geometry”:{“type”:”Polygon”,”coordinates”:[[[-155.781308,19.122869],[-155.781308,19.122965],[-155.781415,19.122965],[-155.781415,19.122869],[-155.781308,19.122869]]]},”properties”:{}},
    {“type”:”Feature”,”geometry”:{“type”:”Polygon”,”coordinates”:[[[-155.767671,19.118275],[-155.767701,19.118366],[-155.76786,19.118319],[-155.767829,19.118228],[-155.767671,19.118275]]]},”properties”:{}},
    {“type”:”Feature”,”geometry”:{“type”:”Polygon”,”coordinates”:[[[-155.772987,19.104606],[-155.772996,19.104676],[-155.773173,19.104656],[-155.77316,19.104556],[-155.773047,19.104569],[-155.773051,19.104599],[-155.772987,19.104606]]]},”properties”:{}},
    {“type”:”Feature”,”geometry”:{“type”:”Polygon”,”coordinates”:[[[-155.773005,19.108122],[-155.772896,19.108141],[-155.772916,19.108241],[-155.773025,19.108222],[-155.773005,19.108122]]]},”properties”:{}},
    {“type”:”Feature”,”geometry”:{“type”:”Polygon”,”coordinates”:[[[-155.760298,19.104608],[-155.760257,19.104581],[-155.760207,19.104647],[-155.760248,19.104674],[-155.760298,19.104608]]]},”properties”:{}},
    {“type”:”Feature”,”geometry”:{“type”:”Polygon”,”coordinates”:[[[-155.792801,19.079177],[-155.792782,19.079134],[-155.792877,19.079099],[-155.792829,19.078984],[-155.792636,19.079056],[-155.792702,19.079214],[-155.792801,19.079177]]]},”properties”:{}},
    {“type”:”Feature”,”geometry”:{“type”:”Polygon”,”coordinates”:[[[-155.771951,19.099011],[-155.772042,19.098984],[-155.772002,19.098861],[-155.77191,19.098888],[-155.771951,19.099011]]]},”properties”:{}},
    {“type”:”Feature”,”geometry”:{“type”:”Polygon”,”coordinates”:[[[-155.785452,19.088912],[-155.785343,19.088949],[-155.785382,19.089049],[-155.78549,19.089013],[-155.785452,19.088912]]]},”properties”:{}},
    {“type”:”Feature”,”geometry”:{“type”:”Polygon”,”coordinates”:[[[-155.782593,19.091201],[-155.782621,19.091272],[-155.782727,19.091235],[-155.782699,19.091163],[-155.782593,19.091201]]]},”properties”:{}},
    {“type”:”Feature”,”geometry”:{“type”:”Polygon”,”coordinates”:[[[-155.764504,19.08444],[-155.764588,19.084446],[-155.764596,19.084349],[-155.764512,19.084343],[-155.764504,19.08444]]]},”properties”:{}},
    {“type”:”Feature”,”geometry”:{“type”:”Polygon”,”coordinates”:[[[-155.756341,19.094739],[-155.756305,19.09465],[-155.75617,19.0947],[-155.756207,19.094789],[-155.756341,19.094739]]]},”properties”:{}},
    {“type”:”Feature”,”geometry”:{“type”:”Polygon”,”coordinates”:[[[-155.746441,19.096682],[-155.746468,19.096763],[-155.746558,19.096735],[-155.746531,19.096655],[-155.746441,19.096682]]]},”properties”:{}},
    {“type”:”Feature”,”geometry”:{“type”:”Polygon”,”coordinates”:[[[-155.751316,19.097135],[-155.75128,19.097146],[-155.751262,19.097094],[-155.751176,19.09712],[-155.75123,19.097278],[-155.751352,19.097241],[-155.751316,19.097135]]]},”properties”:{}},
    {“type”:”Feature”,”geometry”:{“type”:”Polygon”,”coordinates”:[[[-155.76171,19.093146],[-155.761584,19.093027],[-155.761517,19.09309],[-155.761643,19.09321],[-155.76171,19.093146]]]},”properties”:{}},
    {“type”:”Feature”,”geometry”:{“type”:”Polygon”,”coordinates”:[[[-155.747878,19.087693],[-155.747724,19.087769],[-155.747769,19.087852],[-155.747923,19.087776],[-155.747878,19.087693]]]},”properties”:{}},
    ]
    }

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.