Have you ever wondered how to import and analyze GeoJSON data in SQL Server? In this article, we will explore the process of importing earthquake data encoded in GeoJSON format into SQL Server and using SQL Server’s spatial functions to analyze it.

The data we will be using is the 30-day earthquake feed from the USGS, which is encoded in the GeoJSON format. GeoJSON is a specification that makes it easy to share spatial data via JSON. Let’s take a look at a sample extract of the GeoJSON data:

{
  "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 information we will be examining in this data is the “features” array, which contains one feature object for each earthquake recorded in the past 30 days. The “geometry” child object contains the latitude and longitude coordinates that we will be importing into SQL Server.

To import GeoJSON data into SQL Server, we will use SQL Server’s JSON functions to build our own solution. First, we need to create a table to store the 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)

Next, we will use the OPENJSON() function to parse the 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
  DATEADD(second, CAST(UnixMillisecondsSinceEpoch / 1000 as int), '1970-01-01 00:00:00') AT TIME ZONE 'UTC' AS EventDate,
  Magnitude,
  Place,
  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]'
)

By using the OPENJSON() function, we can parse the JSON hierarchy and concatenate the latitude and longitude values into a well-known text format that can be used with SQL Server’s spatial function STPointFromText.

Now that we have imported the earthquake data into our dbo.EarthquakeData table, let’s focus on earthquakes in Yellowstone National Park. To filter the data, we will use the Wyoming State Geological Survey website’s Shapefiles for the boundary of Yellowstone National Park, which we have converted to GeoJSON format:

{
  "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],
            ...
          ]
        ]
      },
      ...
    }
  ]
}

Using SQL Server’s OPENJSON() function again, we can parse the GeoJSON data into a well-known text POLYGON and store it in a new 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)

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

Now we have two tables: dbo.EarthquakeData and dbo.ParkBoundaries. To select only the earthquake data points that fall within the boundaries of Yellowstone National Park, we can use the STIntersects spatial function:

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

With SQL Server’s JSON functions and spatial functions, importing and analyzing GeoJSON data becomes a straightforward process. Once the geographical data is imported into geography data types, SQL Server’s spatial functions offer flexibility for efficiently slicing and dicing the data.

So, if you ever find yourself needing to work with GeoJSON data in SQL Server, now you know how to do it!