Manage spatial data with Microsoft SQL Server

Name: Manage spatial data with Microsoft SQL Server
Rating: 4.4 (119 reviews)

Spatial SQL: a language for geographers

Created byArthur Lembo

Last updated 7/2017

English

English [Auto],

What you'll learn

Students who complete this task will be comfortable writing traditional SQL and spatial SQL within SQL Server. They will understand how to address different data types, functions, and SQL workflows for traditional and spatial data operations.

Course content

7 sections • 22 lectures • 4h 1m total length

Install SQL Server and SQL Server Management Studio5:28
For this lecture, students will download and install SQL Server and SQL Server Management Studio
Install Data3:17
This lecture will allow show you how to load the Annapolis.bak file into SQL Server. Make sure to download the file annapolis.zip, and unzip it. The video will step you through the steps to restore the database.
Course Overview6:59
Explore managing spatial data with SQL Server, including spatial constructs, vector geometry, and coordinate systems. Master querying, indexing for big data, and classic geographic models, plus an emergency response example.

Is Spatial Special?12:25
This lecture will present some of the arguments people use to discourage the use of SQL. It will also begin to answer why SQL is actually a preferable language for managing data.
Why SQL is Superior18:22
This lecture will continue to build the case for why SQL is superior to other methods of manipulating data. Further, it will present the argument that SQL is actually far superior than traditional GIS processes.

Numeric Data Types15:50
Boolean operations2:48
Apply boolean operations in SQL Server to filter spatial data by greater than, less than, and equal to, using and, or, and not on parcels and land use.
Character Operations10:52
Explore string operations in SQL Server, using substring, left, lower, upper, cast, and character index functions to search, modify, and format text within a database.
Date and Time Functions12:05
Learn date and time operations in SQL Server to convert strings to dates, compute differences, extract day, month, year, and use date from parts and date name for transaction analysis.
Spatial Functions17:58
Master spatial functions in SQL Server using geometry data types, including buffering, containment, and intersects. Work with parcels, parks, flood maps, and critical areas to compute intersections.

Select Statement12:05
Master the select statement to retrieve data from tables with star or specific columns, using where clauses and conditions, and join tables on account id, total value, and land use.
Group By Statement17:06
Explore how the group by clause performs aggregate computations on parcel data, producing sums, counts, and averages by land use, with spatial joins and union aggregates.
Case Statement5:16
SQL Potpourri17:10
Explore SQL aggregate functions and set operations—standard deviation, average, coefficient of variation, top, order by, between, in, and union all—applied to obesity data and geographic patterns.
Editing data in SQL Server11:57
Learn to edit data in SQL Server by creating, dropping, and altering tables; inserting, updating, and deleting records; and linking geometry data to owner and account information via spatial queries.

Spatial Type Functions17:47
Explore spatial type functions in SQL Server, including geometry operations like buffer, contains, intersects, distance, and adjacency, applied to parcel and state data for geospatial analysis.
Spatial Functions (mop up)10:29
Explore spatial functions in SQL Server, including area, boundary, centroid, start and end points, and coordinate system considerations, with practical examples using buildings, parcels, and states.

Nearest Neighbor Index7:02
Explore spatial analysis with SQL Server by applying distance functions to geographic data, build a nearest neighbor index, and compute average nearest neighbor distances between cities.
Huff Model for Retail Gravitation14:09
The Huff model for retail gravitation estimates a customer’s probability of visiting a store by combining store attractiveness with inverse distance weighting, enabling SQL-based market cannibalization and sales projections.
variogram of last spring frost data5:53
Explore a variogram of the last spring frost date across 76 cities to quantify spatial autocorrelation as distance grows, using 50‑mile bands and semi-variance.

flooding and emergency response11:39
Map flood zones in SQL Server using grid codes and distinct counts to identify parcels under two feet of water and total land value by land use for emergency response.
Course completion, and where to go next4:32
Explore next steps after completing managing spatial data with SQL Server Express 2012, and discover resources and courses for spatial data work including PostGIS and open source tools.

Requirements

While it is not an absolute necessity, students taking this course should be familiar with spatial data concepts and have some familiarity with database technology.

Description

Are you a database user who wants to learn about spatial data analytics? Are you a GIS professional stuck in a rut of only knowing how to use a GIS GUI? Do you want to learn how to automate tasks, but are afraid of computer programming. If so, SQL is the most powerful tool you can learn to help you perform complex GIS tasks. This course is designed to teach you how to write SQL to replicate many database and GIS tasks using Microsoft's SQL Server. We will start at a very basic overview, and then proceed to more advanced topics related to GIS.

Through a series of lectures, I will work side-by-side with you to develop SQL queries in SQL Server that perform advanced GIS tasks, some of which might be impossible to perform in the GUI. Once my students learn GIS, they very rarely want to return back to using the GUI.

By writing SQL code alongside me, you will see how solving GIS tasks in SQL is like a second nature. This is a very comprehensive course that should take multiple days to complete - but don't worry about that - you will be having a lot of fun learning how powerful SQL can be, and continually ask yourself "why didn't I start using SQL sooner!"

Who this course is for:

This course is targeted toward database professionals who want to learn how to leverage spatial data constructs within SQL Server to solve spatial problems. While there are many databases that support spatial constructs, this course will focus on the use of SQL Server to accomplish these tasks.

Manage spatial data with Microsoft SQL Server

What you'll learn

Explore related topics

Course content

Starting the Course, Loading Software and Data3 lectures • 16min

Overview of SQL2 lectures • 31min

SQL Data Types5 lectures • 1hr

Traditional SQL Statements5 lectures • 1hr 4min

Spatial Functions for Vector Data2 lectures • 28min

Geographic Models3 lectures • 27min

Bonus: Real World Example2 lectures • 16min

Requirements

Description

Who this course is for: