1

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

Rohit Gupta
  • 1,626
  • 6
  • 17
  • 19
DynamoRanger
  • 13
  • 3

1 Answers1

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
;
Andriy M
  • 22,983
  • 6
  • 59
  • 103
  • 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? – DynamoRanger Feb 16 '23 at 16:00
  • @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. – Andriy M Feb 16 '23 at 19:08
  • 1
    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. – DynamoRanger Feb 19 '23 at 16:48