Here's my go at it. Similar but broken down more and with some differences. These are the main ones. (1) In the [Stores] table is a column named [Total Pop Needed]. I read this as a target population threshold per store, rather than there being a fixed common target. So for each store, census areas are added (going outward) until the total population reaches [Total Pop Needed] for that store. (In one of the examples the target is 9000, for the other two stores it is 2500.) This might not be what's needed, I'm not sure. (Easy to remove.) (2) The nearest cumulative population is found for each target. E.g. given a target population of 9000, a total of 8900 out to census area X, and a total of 9050 out to census area X+1, all areas out to X+1 are included. (The mechanism for this uses the [rank] and [target area rank] columns.) There are four queries. Running query 4 is enough if you only want the result, since it calls the others, but these are useful for testing and for understanding what happens. To get the resulting combined area for each store, link a drawing from [Census areas] in query 4. There are notes and tests, and a headnote for each query saying what it does. [1 Census areas by store] -- For each store, list all census units -- with their populations and distances SELECT [D].[ID] AS [store ID], [D].[Name] AS [store name], [D].[Total Pop Needed] AS [target P], [E].[ID] AS [census area ID], [E].[TotPop] AS [census P], -- note 1 Distance([D].[ID], [E].[ID]) AS [R] -- notes 2, 3 FROM [Stores] AS [D] CROSS JOIN [CensusUnit] AS [E] ; -- Notes -- 1 -- Source column [TotPop] is FP64 -- but was originally formatted to show 0 decimal places -- (I have changed this) -- The raw result here *inherents the source formatting* -- (so if rounded at source, rounded here too) -- but an *operation* on the result -- (here or in the next query) -- will use the underlying value, unrounded -- 2 -- Better? use Distance(CentroidWeight to CentroidWeight) -- Best? use sum of Distance(CentroidWeight to each vertex) (vectors) -- 3 -- For a large dataset, only search within a given radius -- Add a PARAMETER, and replace replace CROSS JOIN with LEFT JOIN [2 Cumulative population by store] -- For each store -- sum the cumulative population -- out to (and including) each census area SELECT [T].[store ID], [T].[store name], [T].[target P], [T].[census area ID], [T].[census P], [T].[R], COUNT([U].[census area ID]) AS [rank], --SUM([U].[census P]) AS [closer P], -- for testing --SUM(CAST([U].[census P] AS INTEGER)) AS [closer P'], -- for testing [T].[census P] + Coalesce(SUM([U].[census P]), 0) AS [cumulative P] -- population out to (and including) this census area -- may not be integer (note 1) FROM [1 Census areas by store] AS [T] LEFT JOIN [1 Census areas by store] AS [U] ON [T].[store ID] = [U].[store ID] --AND [T].[store name] = [U].[store name] -- implicit --AND [T].[census area ID] <> [U].[census area ID] -- implicit --AND [T].[R] > [U].[R] -- without tiebreaker (see below) AND CASE WHEN [T].[R] > [U].[R] THEN TRUE -- census unit in T further from store than census unit in U WHEN [T].[R] < [U].[R] THEN FALSE -- census unit in T closer to store than census unit in U -- (ignore) ELSE -- census units are equidistant (unlikely but possible) [T].[census area ID] < [U].[census area ID] -- tiebreaker to ensure unique ordering END GROUP BY [T].[store ID], [T].[store name], [T].[target P], [T].[census area ID], [T].[census P], [T].[R] ORDER BY -- optional [T].[store ID] ASC, [T].[R] ASC ; -- Notes -- 1 -- See note 1 to query [1 Census areas by store] [3 Nearest match population by store] -- For each store -- find the census area -- where the total census population -- living as close or closer -- is nearest to the target population SELECT [store ID], [store name], [target P], FIRST([census area ID]) AS [target area ID], FIRST([rank]) AS [target area rank], --FIRST([R]) AS [target R], FIRST([cumulative P]) AS [nearest cumulative P], ABS([target P] - FIRST([cumulative P])) AS [delta P] -- for checking FROM (SELECT [store ID], [store name], [target P], [census area ID], [census P], [R], [rank], [cumulative P] FROM [2 Cumulative population by store] ORDER BY --[store ID] ASC, -- optional ABS([target P] - [cumulative P]) ASC -- closest match first ) GROUP BY [store ID], [store name], [target P] ; [4 Combine census areas with matched population] -- For each store -- combine the census nearby areas -- having the total population -- nearest the target OPTIONS CoordSys("Stores" AS COMPONENT); SELECT [T].[store ID], [T].[store name], [T].[target P], [U].[nearest cumulative P], SUM([T].[census P]) AS [cumulative census P], -- should match [nearest cumulative P] UnionAll([T].[census area ID]) AS [Census areas] FROM [2 Cumulative population by store] AS [T] RIGHT JOIN [3 Nearest match population by store] AS [U] ON [T].[store ID] = [U].[store ID] AND [T].[rank] <= [U].[target area rank] --AND [T].[R] <= [U].[target R] -- implicit GROUP BY [T].[store ID], [T].[store name], [T].[target P], [U].[nearest cumulative P] ; Attachments: 1 Census areas by store.txt 2 Cumulative population by store.txt 3 Nearest match population by store.txt 4 Combine census areas with matched population.txt Result.png
|