PostGIS 101#

Let’s dive into some of the basic PostGIS commands used in managing spatial data, specifically within the context of our City Parks GIS Database example from our latest Database Normalization lesson.

PostGIS extends PostgreSQL by adding support for geographic objects, allowing for spatial queries. Here are explanations of the commands and concepts introduced:

1. Enabling PostGIS#

CREATE EXTENSION IF NOT EXISTS postgis;

  • What it does: This command adds the PostGIS extension to your PostgreSQL database, enabling spatial data types and functions.

  • Why use it: Before you can work with spatial data (e.g., locations, areas), you need to enable PostGIS to create columns of type GEOMETRY or GEOGRAPHY and use spatial functions for queries.

2. GEOMETRY Data Type#

ParkLocation GEOMETRY(Point, 4326)

  • What it does: This defines a column that can store geometric data, specifically point data in this case. 4326 is the SRID (Spatial Reference Identifier) for WGS 84, a common coordinate system used in GPS and web mapping applications.

    • Consistent with an EPSG code.

  • Why use it: To store precise locations of parks. A POINT in this context represents a single location in space defined by its longitude and latitude.

3. ST_GeomFromText#

ST_GeomFromText('POINT(longitude latitude)', 4326)

  • What it does: This function creates a geometry instance from the WKT (Well-Known Text) representation of the geometry. You provide it with a POINT expressed as a string ('POINT(longitude latitude)') and the SRID (4326).

  • Why use it: To insert spatial data into a GEOMETRY column. It converts a text representation of a point into a geometry object that PostGIS can understand and manipulate.

4. ST_DWithin#

ST_DWithin(
    ParkLocation,
    ST_MakePoint(longitude, latitude)::geography,
    distance
)

  • What it does: This function returns true if the geometries are within the specified distance of one another. The first two arguments are the geometries to compare, and the third argument is the distance in meters (when using GEOGRAPHY) or in the unit of the coordinate system (when using GEOMETRY).

  • Why use it: To find parks within a certain distance from a given point. This is useful for proximity searches, like finding all parks within 1 km of a user’s location.

5. ST_MakePoint#

ST_MakePoint(longitude, latitude)

  • What it does: Creates a POINT geometry from longitude and latitude values.

  • Why use it: This function is handy for generating points on-the-fly from numeric longitude and latitude values.

6. Casting to Geography#

ST_MakePoint(longitude, latitude)::geography

  • What it does: Casts the result of ST_MakePoint to the GEOGRAPHY data type.

  • Why use it: When dealing with distance calculations over the earth’s surface, casting to GEOGRAPHY allows for calculations that account for the earth’s curvature, making distance measurements more accurate.

Using PostGIS Functions#

When inserting or querying spatial data, these functions and data types allow you to work with geographic information in a relational database context efficiently. It’s crucial to practice these commands to understand spatial data’s storage, manipulation, and retrieval in a database system.

By incorporating these PostGIS capabilities into your SQL scripts, you can perform spatial analyses, such as proximity searches, spatial joins, and more, directly within your PostgreSQL database.

Additional reading.