A Comprehensive Guide to PostGIS and Spatial Queries

Explore how PostgreSQL uses the PostGIS extension to manage spatial data, enable geometry and geography types, and run spatial queries and operations like buffering, intersections, and nearest-point analyses.

Create a new database, enable the PostGIS extension, and import shapefiles with the PostGIS Loader, then explore spatial tables, geometry columns, and sample queries.

Run basic select queries on the post offices table, learn sql basics and how data, information, knowledge, and wisdom relate to databases and dbms concepts.

Master sql execution and statement specification in PostGIS by learning select and from keywords, semicolon rules for multiple statements, last statement execution, string quoting, filtering, geometry viewing, and csv export.

Master SQL select statements by retrieving specific columns such as code, class, and name, ordering by one or two columns in asc or desc, and measuring name length.

Learn to write and manage SQL queries in PostgreSQL, including commenting, selecting distinct values, dropping and truncating tables, and grouping with having and count.

Master sql queries with between ranges, like patterns, not equals, and combining conditions with or and and to filter records in the post office table.

Master how or and logical operators evaluate left and right conditions to yield true or false results. See sorting by code, five-record limits, and null value handling in examples.

Master sql querying with offset, limit, and order by for ascending or descending results, using in, not in, and is null to filter by id or code values.

Master alter table statements to rename tables, add or drop columns, and adjust data types in PostGIS. Review integers, numeric, money, and text data types with practical examples.

Developers learn to write delete and update sql queries using where clauses, deleting records by id or class values and updating name or code with and/or conditions.

Learn how PostgreSQL handles mathematical operations and functions, including absolute value, seal function, law function, cosine, degrees and radians, remainder, round, sqrt, sum, count, min, max, and average.

Master function based queries in PostgreSQL by cleaning a table’s schema—renaming columns and converting data types—then use count and distinct to analyze camps and zones in Muzdalifa.

Master function based queries in PostGIS to count camps per zone, sum pilgrims, and group by zone, then filter with having and use avg and round to derive insights.

Explore function-based queries in PostGIS with average, sum, max, min, count, and round, applied to zone-based groupings of pilgrims in Muzdalifa camps.

Master subqueries in SQL, including inner and nested queries, and learn to use single-row and multi-row operators with practical examples of maximum, minimum, average, and second minimum values.

Explore advanced sql subqueries with any and all operators on the Afghanistan provinces table, including population as integer and deer population comparisons, plus zone averages and pilgrim counts.

Explore advanced subqueries in sql, retrieve minimum values per zone using the in keyword, and apply examples from zones and Afghanistan provinces to prepare for spatial function queries.

Explore geographic and projected coordinate systems, crs components, and the role of epsg and srid in postgis, with examples like wgs 84, utm, and robinson projections.

Find EPSG codes and SRIDs across ArcGIS Pro, QGIS, and PostgreSQL, understand geographic vs projected coordinate systems, and load spatial data into PostGIS using geometry or geography types.

Create a spatial table in Postgres and insert point, line, and polygon geometries using spatial functions, while weighing geography versus geometry and projection considerations for global versus local data.

Explore spatial functions in PostGIS, including ST_ID, ST_geometry, geometry type and SRID, as text, length, and coordinates for points, lines, and polygons with practical table examples.

Learn how to import external spatial data into PostGIS, create tables, and load CSV or JSON files with geometry fields using COPY, while handling text representations and exploring GeoJSON formats.

Import json and geojson into a postgis-enabled postgres database using QGIS, create a postgis connection, and explore geometry functions like geometry type, st_length, and geojson representation.

Convert spatial data formats in PostGIS, transforming geometry to text and back, and to GML, WKT, WKB, extended well known text/binary formats, GeoJSON, SVG, and Google Earth formats.

Explore spatial aggregate functions in PostGIS, testing relationships between multiple geometries using intersects, crosses, overlaps, touches, within, and contains, with practical subway station examples.

Explore spatial aggregate functions in PostGIS, including intersects and within, and compute distances, buffers, and nearest neighbors to analyze street and neighborhood relations.

Explore spatial aggregate functions in PostGIS, learn to use strpos for string conditions, and compare geometry and geography distances between major cities to compute accurate distances in meters and kilometers.

Learn how to perform spatial joins in PostGIS by linking a subway station to its neighborhood in one step, using inner, left, right, and full joins.

Master postgis spatial joins by linking Manhattan neighborhoods with census blocks to compute population, white and black percentages, density, and routing subway lines to areas.

Explore spatial constructing functions in PostgreSQL, including buffer, union, and intersection, to merge two or more geometries, compute buffers, and locate intersections.

learn to perform practical spatial queries with PostGIS, including loading shapefiles, adjusting schemas, creating extensions, and querying blocks, camps, tents, and electrical meters to find non-intersecting geometries.

Explore advanced spatial queries in postgis to determine which blocks are within camps, count tents and electrical meters, and update block numbers for camps, tents, and meters based on intersections.

Master spatial queries in PostGIS by understanding geographic vs projected CRS functions, and learn how PostgreSQL triggers use PL/pgSQL to track edits with a history table.

Learn to create PostgreSQL triggers and plpgsql functions that audit inserts, deletes, and updates on the New York streets table by updating a history table with timestamps and user info.

Demonstrates testing PostgreSQL triggers for insert, delete, and update, and verifies the New York streets history table logs edits via a plpgsql function.

Learn how to back up and restore PostgreSQL databases using pgAdmin, configure the binary path, and manage backups for reliable PostGIS workflows.

Conclude by mastering PostGIS essentials: create databases and extensions, import shapefile, json, geojson, and csv, run spatial and standard queries, perform spatial joins, and back up and restore databases.