Create a grid of latitude/longitude pairs that are 50m apart
Hi,
I'm looking to achieve the below in sql 2012 (this is the option at work).
Query a table of lat/longs to find the greatest number of points in a 200m radius. I believe that using the Geography capabilities in sql I can accomplish much of this. To be able to sample the table of points I wanted to create a grid (say of New York - 50m x 50m cells) and move a circle (200m radius) into the center of each square grid cell and then use the buffer function to find what was within the circle. However, I've been searching online and I haven't been able to find a way to create the grid and the central lat/longs. The lat longs 50m apart is what I'm looking for here, using the word grid is just to help explain the concept.
I am aware of the haversine equation and have used it before (the inverse of this I suppose is what I'm looking for). I would just appreciate some guidance to make sure I don't go down the wrong avenue for too long.
lat/long are already a grid, why don't you just figure out your precision (1,852 meters in one minute of arc, if my napkin-math is right, that's 1/60 (0.016777) divided 1852/200 (9.26) = 0.00179ish), so if you looked for lat/long out to the 4th decimal, count how many in each of those, that gives you the best place to start
something like:
SELECT count(*) as points, latitude, --round(latitude,4) as roundedLat, longitude--, --round(longitude*-1,4) as roundedLong FROM pointsTable group by latitude,longitude ORDER BY points desc;
