SQL Server Query to find nearest values

Question

I have a table of X and Y values relating to points on a mapping grid where each pair of values reference the bottom left corner of each square which are spaced in 500 square metre increments for each value to form the points in a grid structure.

Here is an example of the table with some similar data with the XY_ID just being a concatenation of the X and Y values.

XY_GridPointID	X_GridPoint	Y_GridPoint
250000650000	250000	650000
250500650500	250500	650500
251000651000	251000	651000
251500651500	251500	651500
252000652000	252000	652000
252500652500	252500	652500
253000653000	253000	653000
253500653500	253500	653500
254000654000	254000	654000
254500654500	254500	654500
255000655000	255000	655000

I then have another Table with each row having a specific X and Y value which is more specific in terms of the values being down to the metre. So for instance a particular row has an X_Value of 252996 and a Y_Value of 652818.

What I would like to achieve is to be able to lookup the Grid table shown above and pull through the X_GridPoint and Y_GridPoint of where that particular row's X_Value and Y_Value are within in terms of the 500m increments.

So for the X_Value of 252996 and Y_Value of 652818, this would find the X_GridPoint of 252500 and Y_GridPoint of 652500 which is row 6 of the example table above. The Values will essentially be 'falling back' into whatever 500m grid point that precedes that value and it would never 'round up' to the next 500m grid point.

The table with the grid points will consist of around 25,000 rows of these combinations and looking to link to a large dataset with the specific X and Y Values, so wondered if anyone can suggest the best way to approach this and what would work best in terms of performance. Any ideas would be greatly appreciated.

Using SQL Server 2016

• J.D.: What's the mathematical formula to calculate this?

Answer 1

You essentially want to divide both X_Value and Y_Value by 500, truncate the results, multiply them back by 500 and match the resulting values against X_GridPoint and Y_GridPoint accordingly.

If X_Value and Y_Value are an integer type (int, bigint...), the required expression would be as simple as

@Value / 500 * 500

where truncation would be done automatically, because / performs integer division when both operands are integers.

If the input values are a different type (float, numeric...), then one extra step would be required:

FLOOR(@Value / 500) * 500

If all you want is the coordinates, you do not really need to look up the table. The formula, applied to both X_Value and Y_Value, would give you the sought-for square's bottom left corner's X and Y:

SELECT
 X_GridPoint = @X_Value / 500 * 500 -- or FLOOR(@X_Value / 500) * 500
, Y_GridPoint = @Y_Value / 500 * 500 -- or FLOOR(@Y_Value / 500) * 500
;

However, if the real table's rows contain more than just the coordinates and you need to pull that data, then here is how the lookup query might look like:

SELECT
 * -- or list specific columns you need
FROM
 dbo.YourGridTable
WHERE
 X_GridPoint = @X_Value / 500 * 500 -- or FLOOR(@X_Value / 500) * 500
 AND Y_GridPoint = @Y_Value / 500 * 500 -- or FLOOR(@Y_Value / 500) * 500
;

• DynamoRanger: Thanks for your reply I can see the logic with this. If the X_GridPoint and the Y_GridPoint related to the centre point of each 500 square metre square rather than the corner point, would I still be able to achieve this mathematically similar to above or would it be much more complex to achieve?
• Andriy M: @DynamoRanger: I believe you should still be able to calculate the point. The bottom left corner of a square is its (0; 0) point. To go from that to the center, you would just need to add half the width to X and half the height to Y. In your case, therefore, the formula would be @Value / 500 * 500 + 250 or FLOOR(@Value / 500) * 500 + 250.
• DynamoRanger: Thanks for the followup response. This worked, only slight tweak I found I had to make when trying it out was to do the +250 shift on the value before the truncation so for example FLOOR((@Value + 250) / 500) * 500. This gave me the results I needed. Thanks again.