Graph and spatial data

Two features of the new Finch Rewards app needed data that does not sit comfortably in rows and columns. The refer-a-friend program pays a bonus when a member brings in a new member, and a smaller bonus when that new member brings in someone else, up to three levels. Marketing also wants to know which members are at the centre of large referral networks, because those people are worth a thank-you. And the app's store finder must show the nearest stores to wherever the member is standing, with offers from those stores first. The first is a graph problem, the second a spatial one, and SQL Server and Azure SQL have built-in support for both.

Graph tables

A graph is a set of nodes connected by edges. In SQL Server's graph feature, a node table is an ordinary table created AS NODE, which adds a hidden node ID column identifying each node. An edge table is created AS EDGE and adds from and to ID columns that point at nodes, plus an edge ID of its own. In T-SQL these pseudo-columns are written with a leading dollar sign, as the listings show. Edges can have their own columns, such as the date of a referral or the bonus paid. Because they are tables, nodes and edges can have indexes, constraints and permissions like any other table, and graph queries can join them to ordinary tables.

CREATE TABLE graph.Person (MemberId bigint NOT NULL PRIMARY KEY) AS NODE;

CREATE TABLE graph.Referred
(
    ReferredOn date NOT NULL,
    BonusPoints int NOT NULL,
    CONSTRAINT EC_Referred CONNECTION (graph.Person TO graph.Person)
) AS EDGE;

-- Member 1001 referred member 2040 on March 3, 2026
INSERT graph.Referred ($from_id, $to_id, ReferredOn, BonusPoints)
SELECT a.$node_id, b.$node_id, '2026-03-03', 500
FROM graph.Person AS a, graph.Person AS b
WHERE a.MemberId = 1001 AND b.MemberId = 2040;

An edge constraint, written with CONNECTION, limits which node types an edge may join; without one, an edge table can connect any nodes to any nodes. The node and edge ID pseudo-columns are stored as JSON strings that identify the table and row, and the engine fills them in for you.

Graph queries use the MATCH clause, which describes a pattern with an ASCII-art arrow. MATCH(a-(r)->b) means node a has an edge r pointing to node b. Patterns can chain: MATCH(a-(r1)->b-(r2)->c) finds two-step referrals. The direction of the arrow matters, and patterns can point either way. For paths of unknown length, SHORTEST_PATH finds the shortest chain of edges between nodes, with a + for one or more steps or {1,3} for between one and three, and aggregate functions such as COUNT and STRING_AGG with WITHIN GROUP (GRAPH PATH) summarize each path.

-- Members referred by people that member 1001 referred (second level)
SELECT p3.MemberId AS SecondLevel
FROM graph.Person AS p1, graph.Referred AS r1, graph.Person AS p2,
     graph.Referred AS r2, graph.Person AS p3
WHERE MATCH(p1-(r1)->p2-(r2)->p3)
  AND p1.MemberId = 1001;

-- Everyone within three levels of member 1001, with the chain that links them
SELECT p1.MemberId AS Root,
       LAST_VALUE(p2.MemberId) WITHIN GROUP (GRAPH PATH) AS Reached,
       COUNT(p2.MemberId) WITHIN GROUP (GRAPH PATH) AS Levels,
       STRING_AGG(CAST(p2.MemberId AS varchar(20)), ' > ') WITHIN GROUP (GRAPH PATH) AS Chain
FROM graph.Person AS p1,
     graph.Referred FOR PATH AS r,
     graph.Person FOR PATH AS p2
WHERE MATCH(SHORTEST_PATH(p1(-(r)->p2){1,3}))
  AND p1.MemberId = 1001;

Hana compared this with the recursive CTE from lesson 3, which answers the same question from a ReferredBy column on Member. For a strict tree, where everyone has at most one referrer, the CTE is fine and needs no new tables. The graph model earns its place when the relationships are many-to-many or of several kinds. Marketing's next request was exactly that: members can now also share offers with friends and belong to household groups, so one person can be connected to another in three different ways. In the graph model that is two more edge tables and patterns that mix them. In the relational model it is three more self-joining tables and recursive queries that grow harder with every new kind of link.

Graph tables do not make every relationship query fast. MATCH patterns are turned into joins, so indexes on the from and to ID columns of each edge table matter just as foreign key indexes do, and SHORTEST_PATH across a very large, densely connected graph can be expensive. For the referral program, which has about 38,000 referral edges, the three-level query runs in a few milliseconds with indexes on both edge columns.

Spatial data

SQL Server has two spatial types. geography stores shapes on the round Earth, using latitude and longitude, and measures distances and areas in real units, by default metres and square metres for the common coordinate system, SRID 4326. geometry stores shapes on a flat plane, in whatever units the coordinates use, which suits floor plans, maps already projected to a flat grid, and the warehouse layout in StockLine. For anything that has a latitude and longitude, geography is the right choice, because distances on a flat plane drawn from latitude and longitude are wrong, and increasingly so far from the equator.

ALTER TABLE rewards.Store ADD Location geography NULL;

UPDATE rewards.Store
SET Location = geography::Point(49.882, -97.200, 4326)   -- latitude, longitude, SRID
WHERE StoreId = 11;

CREATE SPATIAL INDEX SX_Store_Location ON rewards.Store (Location);

The argument order catches everyone at least once. geography::Point takes latitude, then longitude, then the SRID. Well-known text, used by geography::STGeomFromText and by many other tools, writes POINT(longitude latitude), the other way round. A store placed with its coordinates swapped ends up in the wrong hemisphere, and the error is silent until someone looks at a map. Hana's import checks that every store's latitude is between 40 and 60 and its longitude between −100 and −90, which covers every Harrow & Finch location and catches any swap.

Spatial methods answer the store finder's questions. STDistance returns the distance between two shapes, in metres for geography with SRID 4326. STBuffer draws a zone around a shape, and STIntersects or STWithin test whether shapes overlap or one is inside another, which is how the app will decide whether a member is inside a store's delivery area. A nearest-neighbour query, TOP (n) with ORDER BY STDistance and a WHERE clause that filters out null distances, can use the spatial index.

DECLARE @here geography = geography::Point(49.870, -97.150, 4326);

SELECT TOP (3) s.StoreId, s.StoreName,
       CAST(s.Location.STDistance(@here) / 1000.0 AS decimal(6,1)) AS DistanceKm
FROM rewards.Store AS s
WHERE s.Location.STDistance(@here) IS NOT NULL
ORDER BY s.Location.STDistance(@here);

from math import radians, sin, cos, asin, sqrt
def distance_km(lat1, lon1, lat2, lon2):
    # great-circle distance on a sphere the size of the Earth
    lat1, lon1, lat2, lon2 = map(radians, (lat1, lon1, lat2, lon2))
    a = sin((lat2 - lat1) / 2) ** 2 + cos(lat1) * cos(lat2) * sin((lon2 - lon1) / 2) ** 2
    return 2 * 6371.0 * asin(sqrt(a))
member = (49.870, -97.150)
stores = {"St. Vital": (49.830, -97.100), "Polo Park": (49.882, -97.200),
          "Kildonan": (49.940, -97.080), "Transcona": (49.890, -97.000)}
ranked = sorted((distance_km(*member, *pos), name) for name, pos in stores.items())
for km, name in ranked:
    print(f"{name:10s} {km:5.1f} km")
print("nearest store:", ranked[0][1])

Polo Park    3.8 km
St. Vital    5.7 km
Kildonan     9.3 km
Transcona   11.0 km
nearest store: Polo Park

Privacy

Location is personal information. Carmen Ortiz reviewed the store finder and set two rules. The app sends the member's current position for each search and the database does not store it; the query uses it and forgets it. The member's home location is stored only as the postal or ZIP code centre the member typed, never a precise address. Both rules show up in the design: there is no MemberLocation table, and the search procedure takes the position as a parameter. MSAZ-281 covers masking and access control for the columns that are stored.

Loading and maintaining the graph

Node and edge tables are filled with ordinary INSERT statements, usually from existing relational tables. Hana loaded graph.Person with one row per member from rewards.Member, and graph.Referred from the ReferredBy column, joining each member to the referring member's node to find the two node IDs. The edges then need to stay in step with the app. When a new member signs up with a referral code, the sign-up procedure inserts the member, the Person node and the Referred edge in one transaction, so the graph can never show a referral to someone who does not exist. Deleting a node does not automatically delete its edges, so the account-closure procedure removes a member's edges before marking the member closed, and a weekly check counts edges whose from or to node no longer exists. In the test data it has always returned zero.

Marketing's question about members at the centre of large networks needs a count of everyone reachable from each member, which is SHORTEST_PATH with the + quantifier and a COUNT per root member. Run for every member at once, that query is expensive, so it runs weekly into a summary table, rewards.ReferralReach, and the app reads the summary. Elena's general advice applies to graph queries as to any other: compute expensive answers on a schedule when they do not need to be live, and keep the live queries small.

How spatial indexes work

A spatial index cannot sort shapes the way an ordinary index sorts numbers, so SQL Server divides space into a hierarchy of grids and records which grid cells each shape touches. A query such as a distance test first uses the index to find candidate shapes in nearby cells, the primary filter, and then runs the exact spatial method on those candidates, the secondary filter. The grid density and the number of cells per object can be tuned, but the defaults for geography suit most point data such as store locations. With only 23 stores, the store finder hardly needs the index; it matters for the delivery-zone checks the app will run against thousands of addresses, and for any future table of member postal or ZIP code centres. The query must be written so the optimizer can use the index: the spatial method on the indexed column, compared with a value or a parameter, as in the store finder's ORDER BY and WHERE.