Generating random Latitude & Longitude pairs in the UK via SQL

·

1 min read

I recently needed test data consisting of many locations with latitude and longitude defined. They needed to be within the UK, but also needed to be different from each other so I could calculate distances between them. I needed them in a SQL Server database, so I wrote a little SQL script to generate random pairs of latitude and longitude.

SELECT
    Latitude = CAST((RAND(CHECKSUM(NEWID())) * 2.0 + 52.0) AS DECIMAL(10, 6))
  , Longitude = CAST((RAND(CHECKSUM(NEWID())) * 2.5 - 3.0) AS DECIMAL(11, 6));

This puts them all within the range (52,-3) to (54, -0.5), which is roughly this box on a map:

To actually update my existing data, I simply put this within an UPDATE statement.

UPDATE Locations
SET
    Latitude = CAST((RAND(CHECKSUM(NEWID())) * 2.0 + 52.0) AS DECIMAL(10, 6)),
    Longitude = CAST((RAND(CHECKSUM(NEWID())) * 2.5 - 3.0) AS DECIMAL(11, 6));