Spatial SQL with Postgres : A language for geographers
4.5 (159 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
898 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Spatial SQL with Postgres : A language for geographers to your Wishlist.

Add to Wishlist

Spatial SQL with Postgres : A language for geographers

learning spatial SQL and GIS with Postgresql and PostGIS for performing sophistical data analytics and data mining
Best Seller
4.5 (159 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
898 students enrolled
Created by Dr. Arthur Lembo
Last updated 4/2016
English
Current price: $10 Original price: $45 Discount: 78% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
  • 4 hours on-demand video
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • use SQL to manage spatial data
  • create advanced spatial analysis tools in SQL
  • replicate many traditional GIS tasks (overlay, buffer, etc.) using SQL
  • understand and manage traditional and spatial indexes
View Curriculum
Requirements
  • Students should have PostGRES and PostGIS installed, but if you don't there is an lesson on installation
  • Students should have some familiarity with GIS, and having experience in QGIS is very helpful
Description

Are you 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. 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 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 is the target audience?
  • students who want to learn SQL
  • students who want to apply spatial techniques with SQL
  • students who are ordinarily intimidated by programming
  • students who want to perform GIS task in a fraction of the time it takes to use the GUI
  • students who want to understand the ins-and-outs of how spatial operations work
Students Who Viewed This Course Also Viewed
Curriculum For This Course
19 Lectures
04:04:21
+
Welcome Message - What you need to get started
1 Lecture 04:12

In this short 3 minute video I'm going to show you why you'll want to take this course - I think that SQL for spatial data is so convincing that you'll easily understand why every GIS, database, and analyst will want to master SQL.

Preview 04:12
+
Loading Software and Data
2 Lectures 25:32

This lecture will walk you through installing PostGRES, PostGIS, and QGIS so that you can begin learning how to use SQL.

Installation of software
19:07

In this lecture, I will step you through loading the course data, which includes a Postgres dump file for a small city in upstate New York.  

Loading our Data
06:25
+
Overview of SQL
2 Lectures 29:08

In this lecture I will review what SQL is, and demonstrate some of the powerful features of SQL and spatial SQL.  This lecture will set the stage for why you'll want to be using SQL in your career.

Overview of SQL
19:16

Before we get down to the nitty-gritty of this course, I want to quickly show you just how powerful SQL is within a GIS and spatial context.  If this doesn't convince you to start using SQL, nothing will.  

Also, all of these quick examples use the course data, so you can stop the video and begin typing the SQL code alongside of me. 

Preview 09:52
+
SQL Data Types
2 Lectures 35:59

In this lecture we will dive into looking at how SQL handles different numeric, boolean, and text data types.  Within the context of our city-wide GIS, we'll work together to type in SQL commands that demonstrate the special functions for numeric, boolean, and text data.

SQL Data Types, Part I: Numeric, Boolean, Text
19:40

In this lecture we will dive into looking at how SQL handles different Date, Time, and spatial data types.  Within the context of our city-wide GIS, we'll work together to type in SQL commands that demonstrate the special functions for data, time, and spatial data.

SQL Data Types, Part II: Date and Time, Spatial
16:19
+
Traditional SQL
7 Lectures 01:26:57

In this lecture, we'll explore the traditional SQL commands of SELECTWHERE and GROUP BY -  along with many conditional expressions.  You'll learn the basic syntax, and then we'll gradually add more functionality to show you how to issue advanced queries.  Don't be intimidated, I'll be working right-along side of you.  This stuff builds on itself so well, you won't believe what you'll start accomplishing by the end of the lecture.

Traditional SQL: SELECT, WHERE, conditional expressions, and GROUP BY
19:59

There simply isn't enough time to show you all the cool things you can do with GROUP BY.  So, we had to create another lecture just to fit more of it in.  We are going to be doing some really sophisticated stuff, but you'd never know it, since we are simply building off of what we just learned.  

More fun with GROUP BY
14:27

You can create conditional CASE statements in SQL just like any other programming language.  In this video I will show you how to create CASE statements for numeric, text, and spatial data.

CASE Statements
08:25

In this lecture you will look at all kinds of different aggregate functions in SQL, and even perform statistical analysis and geographic analysis with the functions.  Don't worry, we'll go slow, and like everything else, we'll step into it one command at a time.

Aggregate Functions (average, standard deviation, count, min, max....)
13:27

This lecture is packed with a potpourri of SQL commands, and like everything else, we'll start of slow and easy, and as we progressively add more complexity, you'll see how easy it is to create a very sophisticated query - try doing this in a traditional GIS or spreadsheet!

Other SQL Commands (ORDER, LIMIT, OFFSET, BETWEEN)
15:20

SQL is not just a query language.  It allows you to also change things: add tables, delete tables, change tables, update individual records, bulk load records, add records from one table to another table, you get the idea!  In this lecture I will walk you through the basic ways to change data in a table.  And, just for fun, we'll even change geometry within a table.

Changing data: DROP, CREATE, INSERT, ALTER, UPDATE
09:38

SQL functions work just like functions in other programming languages.  You create a function and then pass data to the function to return values.  In this section I will step you through how to create functions with SQL.

Writing SQL Functions
05:41
+
Spatial SQL for Vector Geometry
3 Lectures 42:03

Geographic analysis can work with multiple representations and coordinate system - the trick is, you have to define them correctly or the software won't have a clue as to where the data is located.  This lecture will show you how to define and change coordinate systems.

Spatial Operations: coordinate system manipulation
11:27

Get ready to start writing spatial operations with SQL.  We'll build on all those previous SQL functions and will add spatial operations like adjacency, buffering, intersection, distance calculation, and more.  You'll be amazed at how you can expand basic spatial operations into complex spatial queries using the GROUP BY, UNION, and other SQL operations.  And the best part is, it's easy, because it's just SQL!  

Spatial operations: Adjacent, Buffer, Contains, Distance, Intersect, more..
19:05

One of the real meat-and-potato functionality of GIS is the ability to perform topological overlay functions.  When you think about it, they are all based on the same kinds of functionality of finding areas that intersect or areas that don't intersect, or both.  This obviously lends itself to SQL really nicely, as we can solve one part of the puzzle at a time.  In this lecture you'll learn how to perform classic topological overlay functions of ERASE, INTERSECT, and IDENTITY.

Spatial Operations: Topological Overlay (ERASE, INTERSECT, IDENTITY)
11:31
+
Spatial SQL for geographic analysis
2 Lectures 20:30

Get ready to create a geographic model.  In this lecture we will once again start out small, and then progressively add more complexity to create distance, adjacency, and interaction models that are typically used in geographic analysis.

Spatial Interaction: Distance, Adjacency, and Interaction matrices
13:21

In this lecture I'm going to show you how to create a spatial operation that performs a statistical analysis for evaluating spatial randomness, clustering, and dispersion.  You'll see how creating an analytical tool like the nearest neighbor index (NNI) is a simple matter of adding a few SQL operations like GROUP BY and ORDER to a basic SQL distance operation. 

Geographic Analysis: Nearest Neighbor Index
07:09
About the Instructor
Dr. Arthur Lembo
4.5 Average rating
900 Reviews
4,807 Students
11 Courses
Professor

Dr. Arthur J. Lembo, Jr. is an educator with a passion for GIS and almost 30 years of GIS industry experience.

Currently, Dr. Lembo is an Associate Professor in the Department of Geography and Geosciences at Salisbury University, where he is also the Technical Director of the Eastern Shore Regional GIS Cooperative. Dr. Lembo has published numerous academic papers on GIS, authored a leading textbook on Statistical Problem Solving in Geography, and conducted sponsored research for organizations like the National Science Foundation, NASA, the United States Department of Agriculture, and the Kellogg Foundation.