Calgary Join Range option problem

[blyons]

I created a Calgary database for geocoding IP addresses. Each record has a start IP number and an end IP number, which are converted from the normal dotted notation to a decimal number. The geographical info for each row matches all of the IP addresses in the range. I want to geocode the records in our streaming server logs based on their IP addresses.

The Calgary Join tool has the option of begin and end ranges, in addition to the exact match. I would like to match the IP Number of the log record between the "start_num" and "end_num" of the Calgary file record. However, the fields available for the "End of Range" are not from the Calgary file, but are the same as the input fields.

According to the the Help documentation, "Range - >=Begins AND <=End - Joins the Input file and the Calgary database where the input field falls within a defined range." Unless I am completely misunderstanding the intent of this tool, for that description to make sense, the end of the defined range must come from the Calgary database, not the input file. Yet, the fields available are not from the Calgary database.

Am I missing something? Is this a bug?

Thank you for your help!

[CristonS · Accepted solution]

Hi @blyons!

This type of Calgary Join is used to match Calgary records to a given input range for each record.  In the attached Home Value example, we're querying all homes sold in select cities at corresponding ranges.  The input ranges are Boulder county 500k - 600k; and Denver county 400k-500k.  You can query on price range alone, or use the second query to filter records for specific counties...

The second example (Calgary example) is more simple.  The cydb is a single list of numeric values, and the input is a set of ranges.  The result is each input record, appended with any values from the cydb that are in each input range.

Please let us know if you have further questions!

[blyons]

I see. I clearly misunderstood the intent.

This is disappointing, because this means I have to build a Calgary db that is more than 23 times the size. Even worse, it must have more than 4.2 billion rows (2^32), which is twice the limit of a Calgary db, so I have to split it in two and query both of them.

Thank you for your reply.