critwire · Learn · Learn's data

Spatial weights s3

Prerequisites: Point patterns (s2)

Who counts as a neighbour? Every spatial statistic compares each unit with its neighbours, so the rule is part of the result. Three rules (a distance band, k-nearest and contiguity), row standardization, and where they break: ties, one-way links and islands, on s4's hexagons over Tasmania and on the countries' outlines. The SQL runs in your own PostgreSQL with PostGIS and the PySAL block in the Python environment, on the lab tables (the setup page).

Concept

Who counts as a unit's neighbour? Every spatial statistic compares each unit with its neighbours, so the answer is part of the result.

Worked example

The distance band from Global Moran's I, s4, read as weights. s4_cells holds the hexagons over Tasmania's main island at edges of 5 and 10 km and s4_neighbours their pairs, one row per ordered pair (i, j); s4's first block, s4-cells, makes both. Every download of this module runs that block first, right after connecting, so s3.sql and s3.py work on their own; to run the blocks one at a time, run s4-cells once first. How many neighbours the band gives each cell:

Checked 2026-11-02 on PostgreSQL 16.15, PostGIS 3.4.2, GEOS 3.12.1, GDAL 3.8.4; Python 3.12.3 with requirements.txt sha256 d5bbe28024cc.

s3-contiguity · PostgreSQL with PostGIS 

SELECT c.edge_m, k.k AS neighbours, count(*) AS cells
FROM s4_cells AS c
CROSS JOIN LATERAL (SELECT count(*) AS k FROM s4_neighbours AS nb WHERE nb.i = c.id) AS k
GROUP BY c.edge_m, k.k
ORDER BY c.edge_m, k.k;
-- result: s3-contiguity · 11 rows · sha256 13c0a4f1ea54
edge_m | neighbours | cells
5000 | 1 | 1
5000 | 2 | 7
5000 | 3 | 36
5000 | 4 | 66
5000 | 5 | 45
… 6 more rows

In s3.sql, with the blocks it runs after.

On the 5 km grid, 939 of the 1,094 cells have all six neighbours; the coast leaves the rest with fewer, down to one cell with a single neighbour (cell 1051). On the 10 km grid, 221 of the 296 cells have six and the fewest is two (6 cells). No cell has none, so the band leaves no island. A cell with no neighbour would show as a row with 0, since count(*) over no rows gives 0, and the result has no such row.

Lab

  1. k-nearest neighbours on the 5 km cells, ranked by distance rounded to the centimetre and then by id, so ties are broken the same way every run. For k = 4 and k = 6: the links, the links whose reverse is missing, and the cells whose k-th and (k + 1)-th neighbours are tied. LIMIT 7 keeps one more neighbour than the largest k, to see the tie:

    s3-knn · PostgreSQL with PostGIS 

    WITH near AS (
  SELECT a.id AS i, b.id AS j, b.d_m
  FROM s4_cells AS a
  CROSS JOIN LATERAL (
    SELECT c.id, round(ST_Distance(ST_Point(a.cx, a.cy), ST_Point(c.cx, c.cy))::numeric, 2) AS d_m
    FROM s4_cells AS c
    WHERE c.edge_m = a.edge_m AND c.id <> a.id
    ORDER BY d_m, c.id
    LIMIT 7
  ) AS b
  WHERE a.edge_m = 5000
), ranked AS (
  SELECT i, j, d_m, row_number() OVER (PARTITION BY i ORDER BY d_m, j) AS rank FROM near
)
SELECT k.k,
       count(*) FILTER (WHERE r.rank <= k.k) AS links,
       count(*) FILTER (WHERE r.rank <= k.k AND NOT EXISTS (
         SELECT 1 FROM ranked AS back WHERE back.i = r.j AND back.j = r.i AND back.rank <= k.k)) AS one_way,
       count(*) FILTER (WHERE r.rank = k.k AND r.d_m = (
         SELECT nx.d_m FROM ranked AS nx WHERE nx.i = r.i AND nx.rank = k.k + 1)) AS tied_cells
FROM ranked AS r, (VALUES (4), (6)) AS k (k)
GROUP BY k.k
ORDER BY k.k;
    -- result: s3-knn · 2 rows · sha256 160008ed6781
k | links | one_way | tied_cells
4 | 4376 | 1968 | 1025
6 | 6564 | 254 | 129

    In s3.sql, with the blocks it runs after.

    With k = 4, 1,025 of the 1,094 cells have their 4th and 5th neighbours at the same distance: an interior cell has six touching cells, all 8.66 km away, and must keep four of them. The rule here (lowest id first) gives the same answer every run, but it isn't neutral. s4-cells numbers the cells column by column from the west, so each cell keeps the cells directly above and below it and its two western neighbours; those western cells keep their own western neighbours, never it. 1,968 of the 4,376 links are one-way. With k = 6 an interior cell keeps all six touching cells, so only 129 cells, near the coast, are tied, and 254 links are one-way.

  2. Contiguity between countries, where the units are real outlines. Two tests can call outlines neighbours: ST_Touches (the boundaries meet and the interiors don't) and ST_Intersects (they share any point). For each country, the outlines it intersects and the ones it touches; then the countries with none (islands), those with more intersecting than touching outlines (overlapping), and the most neighbours any country has:

    s3-countries · PostgreSQL with PostGIS 

    WITH pairs AS (
  SELECT a.country_code, b.country_code AS other, ST_Touches(a.geom, b.geom) AS touches
  FROM country_shapes AS a
  LEFT JOIN country_shapes AS b ON b.country_code <> a.country_code AND ST_Intersects(a.geom, b.geom)
), per_country AS (
  SELECT country_code, count(other) AS intersecting, count(other) FILTER (WHERE touches) AS touching
  FROM pairs
  GROUP BY country_code
)
SELECT count(*) AS countries,
       count(*) FILTER (WHERE intersecting = 0) AS islands,
       count(*) FILTER (WHERE touching = 0) AS islands_by_touches,
       count(*) FILTER (WHERE intersecting > touching) AS overlapping,
       max(intersecting) AS most_neighbours
FROM per_country;
    -- result: s3-countries · 1 row · sha256 5c0dcf803a58
countries | islands | islands_by_touches | overlapping | most_neighbours
247 | 76 | 84 | 12 | 16

    In s3.sql, with the blocks it runs after.

    76 of the 247 outlines intersect no other: as units, they are islands. ST_Touches finds 84. The 12 overlapping countries are the eight pairs of nested outlines from Loading and validating shapes, g2: France's outline includes French Guiana, Réunion, Guadeloupe, Martinique and Mayotte, Norway's Svalbard and Jan Mayen, the Netherlands' the Caribbean Netherlands and New Zealand's Tokelau, and each of those places also has its own outline. A nested pair's interiors overlap, so ST_Touches says no, and eight of the twelve (New Zealand and seven of the places) have no other neighbour: 8 more islands. Neither test is right on its own: the layer isn't a coverage (shapes that tile the area without overlapping), so decide what a nested pair should be before building contiguity on it. The most neighbours any outline intersects is 16 (China).

  3. The islands that hold records:

    s3-islands · PostgreSQL with PostGIS 

    SELECT a.country_code, c.name, count(o.id) AS records
FROM country_shapes AS a
JOIN countries AS c ON c.code = a.country_code
JOIN occurrences AS o ON o.country_code = a.country_code
WHERE NOT EXISTS (SELECT 1 FROM country_shapes AS b
                  WHERE b.country_code <> a.country_code AND ST_Intersects(a.geom, b.geom))
GROUP BY a.country_code, c.name
ORDER BY records DESC, a.country_code;
    -- result: s3-islands · 10 rows · sha256 0039efb8454f
country_code | name | records
AU | Australia | 4636
MG | Madagascar | 2012
KM | Comoros | 109
LK | Sri Lanka | 26
PH | Philippines | 12
… 5 more rows

    In s3.sql, with the blocks it runs after.

    Ten islands hold records, among them Australia (4,636) and Madagascar (2,012). Contiguity weights over countries would give both no neighbour, so a statistic over countries would drop them or fail. The other five are Singapore (11), Japan (2), Trinidad and Tobago (2), Mauritius (1) and Niue (1); Singapore is here because its outline doesn't reach Malaysia's across the strait between them.

  4. The same weights in PySAL: queen contiguity from the hexagons' shapes against the distance band from their centroids, row standardization, and k = 4 nearest neighbours. Queen.from_dataframe reads the shapes from a GeoDataFrame (GeoPandas' table with a geometry column); DistanceBand with binary=True gives weights of 1 within the threshold, here 10 km, twice the edge. queen.s0 is the sum of all weights, so the number of links while they are binary; histogram counts the cells by number of neighbours. cKDTree.query(xy, k=6) returns each cell's six nearest centroids, itself first at distance 0, so columns 4 and 5 are its 4th and 5th neighbours:

    s3-pysal · Python

    Runs after: py-connect

    import geopandas as gpd
import numpy as np
import shapely
from libpysal.weights import KNN, DistanceBand, Queen
from scipy.spatial import cKDTree

with connect() as conn:
    rows = conn.execute(
        "SELECT id, cx, cy, ST_AsBinary(geom) FROM s4_cells WHERE edge_m = 5000 ORDER BY id"
    ).fetchall()
ids = [r[0] for r in rows]
xy = np.array([(r[1], r[2]) for r in rows])
cells = gpd.GeoDataFrame({"id": ids}, geometry=shapely.from_wkb([bytes(r[3]) for r in rows]), crs=3577)

queen = Queen.from_dataframe(cells, ids="id")
band = DistanceBand(xy, threshold=10000, binary=True, ids=ids)
same = all(set(queen.neighbors[i]) == set(band.neighbors[i]) for i in ids)
print(f"queen = distance band: {same}  links: {queen.s0:.0f}  islands: {queen.islands}")
print("neighbours: cells | " + "  ".join(f"{k}: {c}" for k, c in queen.histogram))

queen.transform = "r"
rows_sum_to_one = np.allclose(np.asarray(queen.sparse.sum(axis=1)).ravel(), 1)
lone = next(i for i in ids if queen.cardinalities[i] == 1)
inner = next(i for i in ids if queen.cardinalities[i] == 6)
print(f"rows sum to 1: {rows_sum_to_one}  cell {lone} (1 neighbour): {queen.weights[lone]}  "
      f"cell {inner} (6): {[round(v, 4) for v in queen.weights[inner]]}")

knn = KNN(xy, k=4, ids=ids, silence_warnings=True)
one_way = sum(1 for i in ids for j in knn.neighbors[i] if i not in knn.neighbors[j])
d, _ = cKDTree(xy).query(xy, k=6)
tied = int(np.sum(np.round(d[:, 4], 2) == np.round(d[:, 5], 2)))
print(f"k = 4: links: {knn.s0:.0f}  one-way: {one_way}  cells with the 4th and 5th tied: {tied}")
    -- result: s3-pysal · python · sha256 b8f324551172
queen = distance band: True  links: 6246  islands: []
neighbours: cells | 1: 1  2: 7  3: 36  4: 66  5: 45  6: 939
rows sum to 1: True  cell 1051 (1 neighbour): [1.0]  cell 8 (6): [0.1667, 0.1667, 0.1667, 0.1667, 0.1667, 0.1667]
k = 4: links: 4376  one-way: 600  cells with the 4th and 5th tied: 1025

    In s3.py and s3.ipynb, with the blocks it runs after.

    Queen contiguity from the shapes gives exactly the band's 6,246 links, the same histogram as s3-contiguity and no island. After queen.transform = "r" every row sums to 1: cell 1051's one neighbour has weight 1.0, and an interior cell's six have 0.1667 each. PySAL's k = 4 has the same 4,376 links and the same 1,025 tied cells as the SQL, but leaves 600 links one-way, not 1,968: its k-d tree (a structure for finding nearest points fast) breaks the ties in its own order. Same data, same distances, different weights.

Result

On the 5 km grid, 939 of the 1,094 cells have all six neighbours and the coast leaves the rest with fewer, down to one cell with a single neighbour (cell 1051); on the 10 km grid 221 of 296 have six and the fewest is two. No cell has none. PySAL's queen contiguity from the hexagons' shapes gives exactly the distance band's 6,246 links, the same histogram and no island. After row standardization every row sums to 1: cell 1051's one neighbour has weight 1.0, an interior cell's six have 0.1667 each. k-nearest is lopsided: with k = 4, 1,025 cells have their 4th and 5th neighbours at the same distance, so the choice is a tie-break; SQL's rule (lowest id) leaves 1,968 of 4,376 links one-way, PySAL's leaves 600, with the same distances. With k = 6 only 129 cells are tied and 254 links one-way. Between countries, 76 of the 247 outlines intersect no other: they are islands, and 10 of them hold records (Australia 4,636, Madagascar 2,012, Comoros 109, …), so contiguity weights over countries would give Australia and Madagascar no neighbour at all. ST_Touches alone finds 84 islands, because 12 countries belong to g2's nested pairs (France and its overseas regions, among others), whose interiors overlap; the most neighbours any outline intersects is 16.

Exercise

k-nearest weights never leave a cell without neighbours: with k = 6, every 5 km cell gets six. How far does each cell have to reach for its 6th neighbour? Group the 5 km cells by their number of neighbours in s4's distance band (s4_neighbours), and show for each group the cells, their records, and the shortest and longest reach in km. What does k = 6 do to a coastal cell, and which of the two would you use for s4's Moran's I (s4)?

Show answer

s3-ex · PostgreSQL with PostGIS 

WITH band AS (
  SELECT i, count(*) AS k FROM s4_neighbours WHERE edge_m = 5000 GROUP BY i
), reach AS (
  SELECT band.k, a.n,
         (SELECT ST_Distance(ST_Point(a.cx, a.cy), ST_Point(c.cx, c.cy))
          FROM s4_cells AS c
          WHERE c.edge_m = a.edge_m AND c.id <> a.id
          ORDER BY 1, c.id
          OFFSET 5 LIMIT 1) AS d6_m
  FROM s4_cells AS a JOIN band ON band.i = a.id
  WHERE a.edge_m = 5000
)
SELECT k AS band_neighbours, count(*) AS cells, sum(n) AS records,
       round((min(d6_m) / 1000)::numeric, 2) AS shortest_km,
       round((max(d6_m) / 1000)::numeric, 2) AS longest_km
FROM reach
GROUP BY k
ORDER BY k;
-- result: s3-ex · 6 rows · sha256 3f25bd602944
band_neighbours | cells | records | shortest_km | longest_km
1 | 1 | 0 | 22.91 | 22.91
2 | 7 | 57 | 17.32 | 17.32
3 | 36 | 16 | 15.00 | 17.32
4 | 66 | 243 | 15.00 | 15.00
5 | 45 | 133 | 15.00 | 15.00
… 1 more row

In s3.sql, with the blocks it runs after.

Check

The answer: the band's neighbour count per cell, then each cell's 6th-nearest centroid with the same ordering as s3-knn (distance, then id); OFFSET 5 LIMIT 1 skips the first five.

The 939 cells with six neighbours in the band reach 8.66 km (√3 times the edge), the touching cells, so for them k = 6 and the band agree. Every cell on the coast reaches farther: 15.00 km (3 times the edge, the second ring) for the cells with four or five band neighbours, 15.00 to 17.32 km for those with three, 17.32 km for the cells with two, and 22.91 km for cell 1051, more than two and a half times an interior cell's reach. Those 155 coastal cells hold 449 of the 1,972 records in cells. k = 6 keeps the number of neighbours equal and lets the distance grow; the band keeps the distance equal and lets the number shrink. For s4 the band is the better choice: the cells are equal, they cover one island with no gap, and the band leaves no island, so a coastal cell is compared with the cells it touches, not with cells two rings inland. k-nearest earns its place where units differ in size and spacing, like countries, or where a band would leave islands; then state k and the tie rule with the result.

Dialects

Lab schema: the PostGIS tables

critwire Learn lab database, PostGIS schema learn_curriculum

Every column, in words · The diagram alone