PostGIS and Geospatial Basics for Developers

PostGIS is an extension for PostgreSQL that adds spatial types, functions and indexes. You do not need it to use UPCS, but it becomes valuable when you want to ask questions such as "which delivery points are within 3 km of this depot?". This page is a concept-level overview, not a full tutorial.

Core ideas

Points, lines, polygons. Spatial databases store shapes. A UPCS delivery location is a point; a service area is usually a polygon.

SRID. A spatial reference ID states which coordinate system the numbers use. GPS-style latitude and longitude use SRID 4326. Mixing systems silently gives wrong answers.

Geometry versus geography. geometry treats coordinates as flat plane values. geography models the Earth's surface so distances come out in metres. Many developers choose geography(Point, 4326) for delivery points.

Spatial index. A GiST index makes proximity and containment queries fast. Without one, every row is scanned.

A small example

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE delivery_points (
  id         BIGSERIAL PRIMARY KEY,
  upcs_code  VARCHAR(16) NOT NULL,
  location   geography(Point, 4326) NOT NULL
);

CREATE INDEX delivery_points_location_idx
  ON delivery_points USING GIST (location);

-- Insert: note the order is longitude first, then latitude
INSERT INTO delivery_points (upcs_code, location)
VALUES ('PK-1A2B-3C4D',
        ST_SetSRID(ST_MakePoint(74.3587, 31.5204), 4326)::geography);

-- Points within 3 km of a depot
SELECT upcs_code
FROM delivery_points
WHERE ST_DWithin(location, ST_SetSRID(ST_MakePoint(74.35, 31.52), 4326)::geography, 3000);

The coordinates are illustrative. Note that ST_MakePoint takes longitude first, which is a frequent source of bugs; see storing coordinates in a database.

How it relates to UPCS

UPCS provides the code and the cell for a location. PostGIS lets you analyse many locations yourself. A sensible pattern is to keep the upcs_code as the human-facing identifier and a geography point for queries. Use the lookup endpoint to verify codes and get coordinates.

When it is overkill

If you only store a point per order and display it, two decimal columns are enough. Add spatial types when you need radius search, routing zones or polygon containment. See also grid cell codes versus geohash.