Hello - PLEASE HELP ;( I have been breaking my head over this for days. I need to do a store-level inventory analysis on a retail chain. The API allows me to query either: SKU+ZIP+100 mile radius - returns all stores and their respective inventory level Sku+StoreID - returns just that stores inventory level There are about 2278 SKUs and 1473 Stores. Right now I am using a cross-join to create API Calls: ?storeId=X&skuId=Y which gives me 3.3 Mil API calls which is too much! There must be a logical way to go through the store table and do some kind of calculation or grouping that eliminates all stores that are within 100 MIles of each other so that I can query the API based on the zip plus distance and that will include all stores in that radius. For example, Los Angeles has 30 of them within a few miles, I know that I only need 1 zip code. However I also know that BumbleF#ck, Ohio has only one store so i need that zip code. Something like: Cluster all zipcodes that are based on their lat long within 100 miles of each other. My data shold allow that: storeId|Zip|State|Country|Lat|Long 800460|6082|CT|US|42.00017|-72.582508| What am I missing?

Hi @nxfxcom , I have adapted an exercise from the “Center for Geographic Analysis at Harvard University” hub folder to (hopefully) meet your needs. Here’s the strategy: Get full list of stores + ZIP codes Make a map that is bigger than your covered area and divide it with a grid Make a spatial join between the grid blocks and the stores, so you can see which blocks of the grid contain stores and which not Compute the centre for each grid block and see which is the closest store to it; then associate the ZIP code of the nearest store to each block Now you’ll have a list of ZIP codes that you can use with your cross joiner node and you will be able to reduce the number of API calls Here is my solution: [image] Grouping stores – lelloba Solutions for data science: find workflows, nodes and components, and collaborate in spaces. Keep in mind that since the research area is a circle, there might be overlapping areas and one store result can be several times: you need to remove duplicate results if you use this method. [image] Hope it makes sense! Keep in mind I have used the metric system, so you might want to adapt it to your case. Have a nice day, Raffaello Barri

Geo, Distance Radius Calculations - Please Help

KNIME Analytics Platform

hmfa February 1, 2024, 6:13pm 3

Hi @nxfxcom

I think the crossjoin is a begin.

Have a look at this theread I found.

Maybe you can find some ideas on it.
Br

3 Likes