Kent's BlogSpot

Finding the Closest Store: Three T-SQL Approaches to Distance Matching Three ways to match people to their nearest store in T-SQL — great-circle distance from latitude/longitude, arithmetic ZIP code matching when you have no coordinates at all, and a Pythagorean approximation compared against the trigonometric method. Same sample data, three techniques, one decision framework for picking the right one. Published · Tags: SQL, T-SQL, Mapping, Geolocation, Distance Calculation, Pythagorean Theorem, Latitude and Longitude, ZIP Codes, Nearest Store Matching "Which store is closest to this person?" sounds like a simple question, but the right T-SQL answer depends entirely on what data you actually have. If you're lucky enough to have latitude and longitude for both people and stores, the problem is straightforward geometry. If all you have is a five-digit ZIP code, you need a different strategy — one that degrades gracefully as your address data gets less precise. And i...

Mapping People to Map Tiles in T-SQL — Parallel SQL Agent Jobs and Spatial Geography A complete recipe for matching a large list of geocoded addresses against a set of map tile polygons under a tight deadline — using SQL Agent jobs to parallelize the work, SQL Server spatial geography functions to do the matching, and dynamic SQL to make the stored procedure reusable across different shape and location tables. Published · Tags: Microsoft SQL, T-SQL, SQL Server, Spatial Data, Geography, SQL Agent Jobs, Mapping, Shapefiles, Dynamic SQL, Cursors Finding which map tile a person falls inside — or near — is a meaningfully harder problem than calculating the distance between two points. It isn't just geometry; it's geometry at scale, against polygons instead of points, with a deadline attached. This recipe breaks the problem into three pieces: an orchestration layer that runs the work in parallel and lets you know when it's done, a spatial matching procedure that does the...

Concatenating Rows Into a Single Column With T-SQL's FOR XML PATH Turn multiple rows per person into one readable row with a comma-separated list — using FOR XML PATH to concatenate values, filtered down to only the people who match a given set of criteria. No CURSOR. No loop. One query. Published · Tags: T-SQL, Microsoft SQL, FOR XML PATH, String Concatenation, SQL Server, Subqueries Someone asks for a list of people who speak Spanish or French, along with every other language each of those people speaks. The underlying data is one row per person per language — exactly the shape you want for storage, and exactly the shape no one wants to read in a report. A person who speaks three languages shouldn't show up as three separate rows with the same name repeated. They should show up once, with their languages listed together in a single column. This is a classic row-to-column concatenation problem, and FOR XML PATH is one of the cleanest ways to solve it in T-SQL — no c...