Create a grid of latitude/longitude pairs that are 50m apart

Question

question

jgarden asked Oct 6, '18

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.

geography latitude-longitude

10 |1200 characters needed characters left characters exceeded

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2018-10-09T12:17:11Z

Jon Crawford answered Oct 9, '18

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;

10 |1200 characters needed characters left characters exceeded

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question