PostGIS from Scratch: Mastering PostGIS with QGIS

Write basic PostgreSQL queries in DB manager or Pgadmin, selecting all columns or specific fields from buildings, filtering by type, and load the results as a QGIS layer.

Learn how to use column aliases in PostgreSQL to display readable names like place name and place type; use double quotes for multi-word aliases and know single quotes denote strings.

Master string concatenation in PostgreSQL using two vertical bars and the concat function to join name and type, with spaces or commas as separators, and use concat_ws for separators.

Explore ST_X and ST_Y to extract longitude and latitude from point geometry, and build geometry from longitude and latitude with ST_MakePoint and ST_SetSRID to 4326 in QGIS.

Discover how to create point geometries in postgis using st_make_point and st_point, with 2d and 3d coordinates plus m values, and compare direct srid input versus st_setsrid.

Learn how to create multipoint geometries in PostGIS using ST_Collect, including two-geometry, array, and aggregate variants, with example coordinates in 3857 and 4326 and visualizing in QGIS.

explore multi-geometry creation using ST_Collect as an aggregate function to merge geometries by type, such as viewpoints or parking, and visualize grouped results in QGIS.

Explore how ST_NumGeometries counts components in multi-part geometries, convert multi-part to single with ST_Dump, and access subgeometries via the pos and geom fields in ST_Dump results.

Learn how to create lines in PostGIS using ST_MakeLine, with three variants: a two-point straight line, an array-based multi-point line, and an aggregate function for GPS data, demonstrated in QGIS.

Learn how to create lines in PostGIS using the ST_MakeLine aggregate function, by ordering point geometry (OSM_id) and filtering with type alpine hut, then visualize the resulting line in QGIS.

Learn how to extract points from lines in PostGIS with dump points, return geometry and positions, group by OSM id for unique ids, and load as a new QGIS layer.

Learn to create polygons in PostGIS with ST_MakePolygon by using a line string or outer and inner rings, ensuring a closed, four-point shell and verifying with ST_IsClosed.

Learn how to extract a linestring from a polygon in PostGIS using the exterior ring function, demonstrated with a simple polygon in QGIS.

Explore returning data in GeoJSON with PostGIS using ST_AsGeoJSON, including converting geometry, building a single feature collection with JSON build object, and using subqueries for limits.

Learn how to represent geometry with well-known text (WKT) and extended well-known text (EWKT) in PostGIS, using as text to control decimal places and display the SRID like 4326.

Convert well-known text (wkt) and extended well-known text (ewkt) into geometry in PostGIS using st_geomfromtext and st_geomfromewkt, optionally supplying an integer srid.

Explore additional PostGIS geometry formats beyond GeoJSON, including GML, KML, lat long, and SVG by reviewing the PostGIS reference 8.9.3; the docs make implementation straightforward.

Learn how to use CRS functions in PostGIS: ST_SRID, ST_SetSRID, and ST_Transform to inspect, assign, and convert a geometry's coordinate reference system between 4326 and 3857.

Calculate polygon areas using st_area and apply st_transform to convert coordinates. Transform to meters with a suitable projected CRS, then express results in hectares, square kilometers, or square feet.

Use a where clause to filter rows, e.g., type equals viewpoint. Know the order from, where, then select, and avoid using select aliases in the where clause.

Master comparison and logical operators in a where clause, using greater than, and, or, and an area hectares alias to filter buildings by area.

Master pattern matching with like and i like in PostgreSQL, using wildcards to find records that start or end with strings, and note that i like is case insensitive.

Explore the underscore wildcard in PostGIS pattern matching, contrast it with the percent wildcard, and learn how to constrain characters by position using examples with like and ilike.

Explore the order by clause to sort query results, choose ascending or descending order, and handle null values with nulls first or last, illustrated with a buildings table.

Master the limit clause in PostGIS with order by, returning the top or last ten records by name or osm_id in ascending or descending order, using offset to skip rows.

Explore PostGIS vector geometry types: point, linestring, polygon, and their multi point, multi line string, multi polygon, and multi geometry variants with 4326 (wgs84) coordinates.

Upload a geotiff elevation raster to a PostGIS database via raster to pgsql, creating elevation table in public.elevation. View the raster envelope and add the elevation layer to the project.

Discover PostgreSQL integer types—smallint, integer, and bigint—and how serial auto-increments primary keys. Learn decimal and numeric types with precision and scale, including decimal(10,3) for price.

Learn how the boolean data type stores true or false in PostgreSQL, with representations like true, yes, and on, and see how an active boolean column can default to true.

Compare varchar, char, and text data types for storing strings in PostgreSQL, showing variable length, fixed length, and unlimited length, with practical examples of length limits and spaces.

Learn how PostgreSQL stores dates and times using the date and timestamp data types, with and without time zone, plus querying with a specific time zone such as New York.

Learn how to use arrays in PostgreSQL by creating a table with an array data type, inserting and selecting records, and updating or appending phone number values.

Create a university table in pgAdmin with a serial primary key university_id, not null university_name and located_country, and a PostGIS geometry column, illustrating table creation and constraints.

Learn how to add a geometry column to an existing table with alter table, specify the geometry type (point) and a 4326 coordinate reference system.

Learn how a primary key uniquely identifies a row in a table, enforces not null and unique constraints, and supports single or composite keys, with create table and insert into examples.

Create and link tables using a foreign key, explain parent and child relationships, and apply primary key and references constraints in a university and students example.

Learn foreign key on delete actions in PostgreSQL, including no action, restrict, set null, cascade, and set default, with a practical universities–students example and notes on update behavior.

Explore PostgreSQL database relationships: one to one, one to many, and many to many, with examples using universities and students, and learn how unique constraints enforce these connections.

Explore enforcing a one-to-one relationship between universities and students with unique constraints on university name, university ID, and the first name–last name pair; this approach is uncommon.

Drop the university_id_unique constraint to enable a one to many relationship between universities and students. Insert additional students linked to the same university to demonstrate the linkage.

Create a many-to-many relationship with a join table university_student in PostgreSQL, using a composite primary key (university_id, student_id) and foreign keys to link universities and students.

Use the insert statement to add a record to a spatial table, specify columns and values, and optionally return the inserted row. The POI example uses geometry with srid 4326.

Insert multiple rows into a spatial table with a multi-insert statement in PostGIS, highlighting syntax, performance gains, and atomicity within a single transaction.

Update spatial data with the update statement by setting geometry and attributes and using a where clause to target records, as demonstrated moving point of interest to Joondalup, srid 4326.

Apply PostgreSQL update join to update POIs with building IDs by joining points to buildings where POIs lie within the polygon, limited to the first ten.

Use delete from in PostGIS to remove rows with a where condition. Optionally return columns or all columns with *, and verify deletion in POI table by osm id.

Explore PostgreSQL upsert, which combines insert and update in one statement using on conflict and unique constraints. Learn when to use do nothing versus do update.

Spatial indexing accelerates spatial queries by narrowing searches to relevant geometries instead of evaluating all. Create a spatial index in PostGIS on [table] using gist, and reindexing to maintain performance.

Explore non-spatial PostgreSQL indexes, including B-tree for equality, range, and order by queries, and hash indexes for equality, with create index syntax on a table and column.

Learn how inner join combines rows from two tables using a related column, returning only matching records, with a practical PostgreSQL example joining students and universities by university ID.

Explore left outer joins and right joins, preserving the left table while returning matched right-table rows; unmatched right-side data become null in the students and university tables.

Master how a full outer join combines rows from both tables and fills unmatched rows with nulls, illustrated with university and student data and prep for spatial joins.

Learn how ST_Intersects detects geometry intersections and returns matching records. See poi and building example in QGIS to join by intersection and visually verify overlaps.

Discover how ST_Contains differs from ST_Intersects in PostGIS with QGIS, showing when a geometry is completely inside another, and why partial overlaps fail.

Explore how ST_Touches detects when geometries touch without interior overlap, enabling boundary contact checks between polygons, linestrings, and points.

Use st_dwithin in postgis to find points of interest within ten meters of buildings, demonstrating buffer logic. Transform coordinates from 4326 to 3112 for meter-based calculations.

Explain ST_Distance, the function that computes the distance between two geometries in QGIS, using the 3112 CRS for metres and a 1 km buffer with a spatial index.