Mapping People to Map Tiles in T-SQL

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 actual heavy lifting against SQL Server's geography type, and a verification step that checks the results visually before you trust them.

The setup: a table of map tile polygons (Map_Tiles) and a table of geocoded addresses (List_Of_Addresses), both already loaded into SQL Server with latitude/longitude or spatial geometry in place. The goal: for every address, find which map tiles it falls inside or near, and how far away it is.

Why Parallelize With SQL Agent Jobs

Comparing every address against every map tile is CPU-intensive, and a single sequential pass through a large address list would not finish anywhere near the deadline. The fix is to split the address list into equal-sized chunks by identity range, and process each chunk in its own SQL Agent job running at the same time as the others — better utilizing available CPU cores instead of running one long single-threaded pass.

A "main" SQL Agent job kicks off eleven worker jobs, each calling the same stored procedure with a different @iteration parameter so each one works on a different slice of the address table:

EXEC dbo.sp_start_job N'MapStuff_Relationship_Map_Tiles_Main_USP_001' ;
EXEC dbo.sp_start_job N'MapStuff_Relationship_Map_Tiles_Main_USP_002' ;
...
EXEC dbo.sp_start_job N'MapStuff_Relationship_Map_Tiles_Main_USP_010' ;
EXEC dbo.sp_start_job N'MapStuff_Relationship_Map_Tiles_Main_USP_011' ;

Each of those eleven jobs calls the same underlying stored procedure, passing in a different iteration number along with the names of the shape table and the location table:

EXEC [dbo].[MapStuff_Relationship_Map_Tiles_Main_USP]
  @iteration=1
  , @shape_tablename='Map_Tiles'
  , @location_tablename='List_Of_Addresses'

Knowing When the Work Is Done: The Check Job Pair

Once the eleven parallel jobs are running, something needs to watch for completion and send a notification — without that watcher job blocking or colliding with itself. The main job's next step fires off a check job:

EXEC dbo.sp_start_job N'MapStuff_Relationship_Map_Tiles_Check_USP_1';

That job calls a stored procedure that looks for any of the eleven main jobs still running. If everything has finished, it sends a completion email. If something is still running, it waits five minutes and starts its own "twin" job:

EXEC dbo.sp_start_job N'MapStuff_Relationship_Map_Tiles_Check_USP_2';
Why two check jobs instead of one? SQL Server will not let a running SQL Agent job start another instance of itself while it's still active. Alternating between two separate jobs — each one capable of restarting the other — works around that restriction while still polling on a five-minute cycle until the parallel workers finish.

Both check jobs call the same stored procedure underneath:

EXEC [dbo].[MapStuff_Relationship_Map_Tiles_Check_USP]

Here is the full check procedure. It looks for any still-running instance of the main job; if one exists, it waits, then determines which of the two check jobs is currently executing and starts the other one. If nothing is still running, it sends the completion email and exits:

CREATE PROCEDURE [dbo].[MapStuff_Relationship_Map_Tiles_Check_USP]
AS
create table #enum_job ( Job_ID uniqueidentifier, Last_Run_Date int, Last_Run_Time int, Next_Run_Date int, Next_Run_Time int, Next_Run_Schedule_ID int, Requested_To_Run int, Request_Source int, Request_Source_ID varchar(100), Running int, Current_Step int, Current_Retry_Attempt int, [State] int )

insert into #enum_job
exec [master].dbo.[xp_sqlagent_enum_jobs] 1,garbage

select top 1 parent.Job_ID
into #running_check
from #enum_job as parent (nolock)
inner join [msdb].dbo.sysjobs as child (nolock)
on parent.job_id=child.job_id
and child.[name] like 'MapStuff_Relationship_Map_Tiles_Main_USP%'
and parent.[State]=1

drop table #enum_job

IF EXISTS (SELECT top 1 * FROM #running_check )
BEGIN
WAITFOR DELAY '00:05'

create table #enum_job2 ( Job_ID uniqueidentifier, Last_Run_Date int, Last_Run_Time int, Next_Run_Date int, Next_Run_Time int, Next_Run_Schedule_ID int, Requested_To_Run int, Request_Source int, Request_Source_ID varchar(100), Running int, Current_Step int, Current_Retry_Attempt int, [State] int )

insert into #enum_job2
exec [master].dbo.[xp_sqlagent_enum_jobs] 1,garbage

select top 1 child.[name]
into #running_check2
from #enum_job2 as parent (nolock)
inner join [msdb].dbo.sysjobs as child (nolock)
on parent.job_id=child.job_id
and child.[name] like 'MapStuff_Relationship_Map_Tiles_Check_USP%'
and parent.[State]=1

drop table #enum_job2

declare @jobname varchar(999)
set @jobname = (select top 1 [name] from #running_check2)

drop table #running_check2

IF @jobname = 'MapStuff_Relationship_Map_Tiles_Check_USP_1'
BEGIN
EXEC msdb.dbo.sp_start_job N'MapStuff_Relationship_Map_Tiles_Check_USP_2'
END

IF @jobname = 'MapStuff_Relationship_Map_Tiles_Check_USP_2'
BEGIN
EXEC msdb.dbo.sp_start_job N'MapStuff_Relationship_Map_Tiles_Check_USP_1'
END

GOTO EXIT_PROCEDURE
END

EXECUTE [msdb].[dbo].[sp_send_dbmail] @profile_name = 'reporting', @recipients = 'redacted@example.com', @copy_recipients = NULL, @blind_copy_recipients = NULL, @subject = 'MapStuff_Relationship_Map_Tiles_Main_USP complete', @body = 'MapStuff_Relationship_Map_Tiles_Main_USP complete', @importance = 'High'

EXIT_PROCEDURE:

DROP TABLE #running_check

GO
PiecePurpose
xp_sqlagent_enum_jobsEnumerates currently running SQL Agent jobs so the procedure can check job state without querying job history tables directly.
#running_checkHolds any still-running instance of the eleven main worker jobs — if this has rows, the work isn't done yet.
WAITFOR DELAY '00:05'Pauses five minutes before polling again, instead of hammering the job table in a tight loop.
Twin-job restart logicDetermines which check job is currently executing and starts the other one, working around SQL Agent's rule that a running job cannot start another instance of itself.
sp_send_dbmailSends the completion notification once no main worker jobs are still running.

The Heavy Lifting: Matching Addresses to Map Tiles

This is the stored procedure that does the actual spatial work. It's written to accept the shape table name and the location table name as parameters, using dynamic SQL so the same procedure can be reused against other map tile sets and other address lists without rewriting it each time.

Code that writes code: dynamic SQL — building a SQL string and executing it with EXECUTE — is usually something to avoid, since it sacrifices some compile-time safety and makes the procedure harder to read. Here it's a deliberate tradeoff: it's what makes one procedure reusable across multiple shape and location tables instead of maintaining a near-duplicate copy for each one.

After validating its parameters, the procedure creates an empty staging table sized for one iteration's results, then opens a cursor that loops through that iteration's slice of the address table. For each address, it builds a point geography and two buffer zones — 20 feet and 50 feet — and checks which map tile polygons those buffers intersect, recording the distance to each match:

CREATE PROCEDURE [dbo].[MapStuff_Relationship_Map_Tiles_Main_USP]
(
@iteration [int] = NULL
, @shape_tablename [varchar](99) = NULL
, @location_tablename [varchar](99) = NULL
)
AS
--declare @iteration [int] = 1
--declare @shape_tablename [varchar](99) = 'Map_Tiles'
--declare @location_tablename [varchar](99) = 'List_Of_Addresses'

-- SET NOCOUNT ON
DECLARE @SQL [varchar](max)
DECLARE @increment int
SET @increment=25000

IF @iteration IS NULL GOTO EXIT_PROCEDURE
IF @iteration < 1 GOTO EXIT_PROCEDURE
IF @iteration > 9999 GOTO EXIT_PROCEDURE
IF @shape_tablename IS NULL GOTO EXIT_PROCEDURE
IF @location_tablename IS NULL GOTO EXIT_PROCEDURE
IF NOT EXISTS (SELECT 1 FROM [sysobjects] WHERE id = OBJECT_ID(@shape_tablename)) GOTO EXIT_PROCEDURE
IF NOT EXISTS (SELECT 1 FROM [sysobjects] WHERE id = OBJECT_ID(@location_tablename)) GOTO EXIT_PROCEDURE
IF @increment IS NULL GOTO EXIT_PROCEDURE
IF @increment < 1 GOTO EXIT_PROCEDURE
IF @increment > 999999999 GOTO EXIT_PROCEDURE

DECLARE @iteration_char VARCHAR(4)
SET @iteration_char = (SELECT RIGHT('0000'+CAST(@iteration AS VARCHAR(4)),4))
DECLARE @iteration_start varchar(9)
SET @iteration_start = (select convert(varchar(9),(@iteration*@increment)-@increment))

DECLARE @iteration_stop varchar(9)
SET @iteration_stop = (select convert(varchar(9),(@iteration*@increment)-1))

SET @SQL =
'
/* create empty staging table for the results of the cursor */
IF EXISTS (SELECT 1 FROM [sysobjects] WHERE id = OBJECT_ID(~' + @location_tablename + '_results_' + @iteration_char + '~)) DROP TABLE ' + @location_tablename + '_results_' + @iteration_char + '
CREATE TABLE ' + @location_tablename + '_results_' + @iteration_char + '(
[results_id] [int] IDENTITY(1,1) NOT NULL
, [location_ID] [int] NULL
, [location_rooftop_geo] [tinyint] NULL
, [buffer20_intersect] [tinyint] NULL
, [buffer50_intersect] [tinyint] NULL
, [location_distance] [float] NULL
, [ZONE_ID] [int] NULL
, [ZONE_DESCR] [nvarchar](255) NULL )
TRUNCATE TABLE ' + @location_tablename + '_results_' + @iteration_char + '
/* use cursor to loop through the list of locations to process, then sequentially process the map file */
DECLARE @ID int
DECLARE @geom geography
DECLARE @rooftop_geo tinyint
DECLARE @buffer20 geography
DECLARE @buffer50 geography
DECLARE location_cursor CURSOR FOR
SELECT ID
, rooftop_geo
, geography::Point(latitude, longitude, 4326) as geom
, geography::Point(latitude, longitude, 4326).STBuffer(6.096) as buffer20 /* 20 feet is 6.096 meters */
, geography::Point(latitude, longitude, 4326).STBuffer(15.24) as buffer50 /* 50 feet is 15.24 meters */
from ' + @location_tablename + ' as [outer] (nolock)
where ID between ' + @iteration_start + ' and ' + @iteration_stop + '
order by ID
OPEN location_cursor
/* prime the pump */
FETCH NEXT FROM location_cursor INTO @ID, @rooftop_geo, @geom, @buffer20, @buffer50
/* begin looping, checking @@FETCH_STATUS to see if there are any more rows to fetch */
WHILE @@FETCH_STATUS = 0
BEGIN
/* load matching map file and location file records into table */
insert into ' + @location_tablename + '_results_' + @iteration_char + '
select
@ID as location_ID
, @rooftop_geo as location_rooftop_geo
, convert(tinyint,@buffer20.STIntersects(geom)) as buffer20_intersect
, convert(tinyint,@buffer50.STIntersects(geom)) as buffer50_intersect
, @geom.STDistance(geom) as location_distance
, convert(int,income.ID) as ZONE_ID
, ZONE_DESCR
from ' + @shape_tablename + ' as income (nolock)
where @buffer50.STIntersects(geom) = 1
FETCH NEXT FROM location_cursor INTO @ID, @rooftop_geo, @geom, @buffer20, @buffer50
/* end of loop */
END
/* close the looping structure */
CLOSE location_cursor
DEALLOCATE location_cursor
/* end query */
'

set @SQL=(select replace(@SQL,'~',char(39)))

--select (@SQL)
EXECUTE (@SQL)
EXIT_PROCEDURE:

GO
Parameter / ElementPurpose
@iterationIdentifies which 25,000-row slice of the address table this call should process, used to compute the identity range for the cursor's WHERE clause.
@shape_tablename / @location_tablenameTable names passed in as parameters so the same procedure can run against different map tile sets and address lists.
geography::Point(latitude, longitude, 4326)Builds a spatial point from each address's coordinates using SRID 4326 (standard WGS 84 lat/long).
STBuffer(6.096) / STBuffer(15.24)Creates 20-foot and 50-foot buffer zones (in meters) around each address point.
STIntersects(geom)Tests whether a buffer zone intersects a given map tile polygon — the core spatial match.
STDistance(geom)Calculates the distance from the address point to the map tile geometry for matched records.
Cursor loopProcesses one address at a time against the full map tile table, inserting every intersecting tile into the results table.
Expect multiple results per person: because each address is checked against every map tile within its 50-foot buffer, one person can intersect more than one tile. Once all eleven result tables are populated, you'll need a separate de-duplication pass — using the location_distance, buffer20_intersect, and buffer50_intersect columns — to decide which relationship to keep for each person if your use case needs exactly one.

Preparing the Spatial Data

The address table needed no special preparation — latitude and longitude were loaded as ordinary floating-point numbers. The map tile table was different: it was uploaded from a shapefile using the Shapefile Importer for SQL Server, configured for spheric (not planar) geography, SRID 4326, with a spatial index and the geometry column named geom. After the upload, the following index and constraint were added on expert advice:

CREATE SPATIAL INDEX [geog_sidx] ON [dbo].[Map_Tiles] ([geom]) USING GEOGRAPHY_GRID
WITH (
GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Map_Tiles] WITH CHECK ADD CONSTRAINT [enforce_srid_geometry_Map_Tiles] CHECK (([geom].[STSrid]=(4326)))
GO
ALTER TABLE [dbo].[Map_Tiles] CHECK CONSTRAINT [enforce_srid_geometry_Map_Tiles]
GO
ElementPurpose
USING GEOGRAPHY_GRID spatial indexSpeeds up STIntersects() and STDistance() lookups against the map tile polygons, which matters enormously at this row count.
GRIDS = MEDIUM (all four levels)Controls the spatial index's grid density at each of its four hierarchical levels — a reasonable default for general-purpose polygon data.
enforce_srid_geometry_Map_Tiles constraintGuarantees every row in the geometry column uses SRID 4326, preventing silent mismatches if a future load uses a different spatial reference.

Verifying the Results

Never trust spatial output without checking it visually. To confirm the matches looked right, the following query pulls a couple of map tile polygons alongside one address's buffer zones into a single result set, which can then be compared against the same location on Google Maps:

SELECT geom, ZONE_ID as ID from dbo.Map_Tiles (nolock) where ZONE_ID in (10843, 3364 /*, 10556*/ )
union all
SELECT geography::Point(latitude, longitude, 4326).STBuffer(6.096) as geom /* 20 feet is 6.096 meters */
, ID as ID from dbo.List_Of_Addresses (nolock) where ID = 236102
union all
SELECT geography::Point(latitude, longitude, 4326).STBuffer(15.24) as geom /* 50 feet is 15.24 meters */
, ID as ID from dbo.List_Of_Addresses (nolock) where ID = 236102

SQL Server Management Studio's Spatial Results tab will render these geometries directly, making it easy to eyeball whether a given address buffer actually falls where you'd expect relative to the map tiles it matched.

Lessons Learned

Two honest notes from running this in production. First, the map tile shapefile and the address geocoding were both prepared by a spatial data specialist — this recipe covers only the SQL matching logic, not the upstream data preparation. Second, the eleven parallel jobs finished at noticeably uneven times, because some chunks of the address list happened to be near far more map tiles than others. Randomizing the address order before assigning the identity column — rather than loading it in whatever order it arrived — would likely have spread the workload more evenly across the eleven jobs.

What You Can Do With This

  • Reuse the same matching stored procedure against other shape and location table pairs by passing different table names — no code changes required
  • Adjust the number of parallel SQL Agent jobs and the @increment chunk size to match the row count and available CPU on your own server
  • Use the buffer20_intersect and buffer50_intersect flags as a simple confidence tier — a 20-foot match is a much tighter relationship than a 50-foot one
  • Adapt the check-job pair pattern any time you need a SQL Agent job to poll for completion of other jobs without colliding with its own still-running instance
  • Always validate spatial results visually in SSMS's Spatial Results tab, or against an external map, before trusting a large batch run
  • If you control the load order, randomize it before assigning identity values, to spread spatially dense clusters more evenly across parallel chunks

Comments

Post a Comment

Popular posts from this blog

Site Landing & Site Referrer Preservation

Site Landing Page & Site Referrer to Cookies in Google Tag Manager — A Complete Recipe Without Custom JavaScript Persist the user's landing page host, landing page path, and referral source into session or long-lived persistent cookies using only GTM sandboxed Custom Templates. No Custom HTML tags. No new CSP hashes. Published · Tags: GTM, Google Analytics 4, Cookies, Site Referrer, Landing Page, First-Touch Attribution, User Journey, dataLayer, CSP, GA4 Where did this user first land on your site — and what referred them there? Those two pieces of information are foundational to understanding any user journey. But they only exist at the moment of the first page load. Navigate to a second page, return in a new browser session, or submit a form three days after the original visit, and the browser's native referrer is gone and the landing URL has long since changed. Without a persistence layer, you are flying blind. This recipe solves that problem cleanly...
Read more

GTM Browser Viewport Measurement

Browser Viewport Measurement in Google Tag Manager — A Complete Recipe Without Custom JavaScript Send the user's current browser viewport dimensions as an event-level parameter on every GA4 hit — using only GTM's built-in JavaScript Variable type and a Constant. No Custom HTML tags. No new CSP hashes. Published · Tags: GTM, Google Analytics 4, Browser Viewport, Window Size, JavaScript Variable, CSP, GA4, Shared Event Settings How big is the browser window when a user converts? Does your checkout funnel drop off more on narrow viewports than wide ones? These questions sound like they should be answerable from GA4's built-in device category dimension — but device category is derived from the User-Agent string and tells you nothing about how much screen space the browser is actually using. A desktop user with a browser snapped to half-screen is classified as "desktop" even though their experience is closer to a narrow tablet. This recipe measures ...
Read more

UTM & URL Query String 2 Cookies

UTM & URL Query String to Cookies in Google Tag Manager — A Complete Recipe Without Custom JavaScript Persist any URL query string parameter — UTM values, click IDs, or custom campaign codes — into session or long-lived persistent cookies using only GTM sandboxed Custom Templates. No Custom HTML tags. No new CSP hashes. Published · Tags: GTM, Google Analytics 4, UTM, Cookies, Query String, dataLayer, CSP, GA4, First-Touch Attribution, Lead Attribution UTM parameters tell you exactly how a visitor arrived at your site. But they only exist in the URL on the landing page. The moment a user navigates to a second page, clicks an internal link, or returns to the site in the same browser session, those parameters vanish from the address bar. If your marketing team needs to know which campaign drove a lead form submission that happened three days after the original visit — a common scenario in B2B with longer consideration cycles — you cannot read the UTMs from the URL. ...
Read more