Creating Lat Lon Grid table from list of

Question

Post reply

Creating Lat Lon Grid table from list of

Dingo1Dave

SSC-Addicted

Points: 431
More actions
June 17, 2014 at 4:37 am

#287656

Hi,
I am trying to build a report that shows 250metre radius plast zones at 25metre intervals over an area in lower manhattan. I have the lat lons for the grid (186 latitude points and 282 Longitude points) and need to create an event table that takes the first Latitude point and then all the longitude points, then loops to take the 2nd latitude point with all the 282 longitudes and keep looping until I get 52,452 events. When you look at this in mapping software, it will be a grid over lowere manhattan with points every 25 metres. I am new to coding and have been able to find how to calculat the circle and points within it and also take into account the curvature of the earth at different lat lons, however, I am stuck on trying to make this event table. I have tried to search for anything similar to this and have so far not been able to find anything. If anyone has any suggestions, then it would be very much appreciated.
Regfards
Dave

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Dingo1Dave SSC-Addicted Points: 431 More actions · Answer 1

Dingo1Dave

SSC-Addicted

Points: 431

June 17, 2014 at 4:39 am

#1722229

And sorry for the poor spelling - appalling!

Lowell SSC Guru Points: 323518 More actions · Answer 2

well, here's an example of how to generate 52452 integers based on your existing data;

you'd probably want to plug that into a formula that calculates latitide increments for every 25 meters, is that what you are after?

you probably want half of the values before, and half after, right?

/*

CityNameLatitudeLongitudeNTheRange

Fort Lauderdale26.133380.15001-26225

Fort Lauderdale26.133380.15002-26224

Fort Lauderdale26.133380.15003-26223

Fort Lauderdale26.133380.15004-26222

*/

WITH E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1), -- 10 or 10E01 rows

E02(N) AS (SELECT 1 FROM E01 a, E01 b), -- 100 or 10E02 rows

E04(N) AS (SELECT 1 FROM E02 a, E02 b), -- 10,000 or 10E04 rows

E08(N) AS (SELECT 1 FROM E04 a, E04 b), --100,000,000 or 10E08 rows

E16(N) AS (SELECT 1 FROM E08 a, E08 b), --10E16 or more rows than you'll EVER need

Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E16),

MyData AS (SELECT 'Fort Lauderdale' As CityName,26.1333 As Latitude,80.1500 As Longitude)

--assuming the 52452 desired event

SELECT top 52452 MyData.*,

Tally.N,

-26226 + N As TheRange from MyData

CROSS APPLY Tally

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dingo1Dave SSC-Addicted Points: 431 More actions · Answer 3

Lowell - thanks for your reply.

The set of Lat Lons that I have are already the lat lon incrementals, but starting with a point and then creating in SQL sounds like a much better way of doing this. I have one table with the Lats and one table with the lons currently.

The bigger picture that I have is to try and build this out to cover larger areas of the world. I think trying to run a worldwide, land based grid every 25m in built up areas and lower resolution elsewhere is what I am aiming for (set your sights high right!) but having regions around the world that we deem to be of greater importance is certainly achievable I think.

The data that I have is Lat +40.76884 and Lon -74.02071 so the next result needs to be Lat +40.76884 and Lon -74.02034 and so on all the way to Lon -73.916739 then loop back to the next latitude of +40.76847 and go through the process until the Lat of +40.70039 is reached. What I would also like to do is create a unique ID for each of these points - something that is unique and remains the same so that any analysis we do can relate back to the same event set. This bit I know how to do! All these Lat Lons have been defined by me to create a grid. As I mentioned above, I would like to be able to set up different sets of code for different resolutions in different areas.

I will take a look through your code and see if I can produce what I need.

Appreciate your time looking at this for me.

Regards

Dave

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

is this as simple as a cross join?

eg:

SELECT TOP 186 IDENTITY(INT, 4070039 , 37) AS lat

INTO dbo.lat

FROM sys.all_columns ac1

--select * from lat

SELECT TOP 282 IDENTITY(INT, 7391674 , 37) AS lon

INTO dbo.lon

FROM sys.all_columns ac1

--select * from lon

SELECT lat.Lat, lon.Lon

FROM lat CROSS JOIN

lon

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Dingo1Dave SSC-Addicted Points: 431 More actions · Answer 5

It is easy when you know how!

Thank you very much - you have made my day.

Appreciate your help and hope you have a good rest of your day.

Regards

Dave

mickyT SSChampion Points: 10360 More actions · Answer 6

Dingo1Dave (6/17/2014)
...
The bigger picture that I have is to try and build this out to cover larger areas of the world. I think trying to run a worldwide, land based grid every 25m in built up areas and lower resolution elsewhere is what I am aiming for (set your sights high right!) but having regions around the world that we deem to be of greater importance is certainly achievable I think.
...

Hi Dave

I think that it is worth while pointing out that since you are using Lat Lon coordinates to create you grid that you will not end up with a true 25m square for your grids due to the world be spherical. While working in a small area the slight differences are barely noticeable, but if you do scale up to the world, you will need to be aware of it.

If you run the following you will see the variations. These are especially noticeable for the Longitudinal measures, while quite minor for the Latitudinal measures

-- 0.00037 on the Lon

select Geography::STGeomFromText('LINESTRING (0.02034 0, 0.02071 0)', 4326).STLength()

select Geography::STGeomFromText('LINESTRING (0.02034 20, 0.02071 20)', 4326).STLength()

select Geography::STGeomFromText('LINESTRING (0.02034 40, 0.02071 40)', 4326).STLength()

select Geography::STGeomFromText('LINESTRING (0.02034 60, 0.02071 60)', 4326).STLength()

-- 0.00037 on the Lat

select Geography::STGeomFromText('LINESTRING (0 0.76847, 0 0.76884)', 4326).STLength()

select Geography::STGeomFromText('LINESTRING (0 20.76847, 0 20.76884)', 4326).STLength()

select Geography::STGeomFromText('LINESTRING (0 40.76847, 0 40.76884)', 4326).STLength()

select Geography::STGeomFromText('LINESTRING (0 60.76847, 0 60.76884)', 4326).STLength()

Dingo1Dave SSC-Addicted Points: 431 More actions · Answer 7

Hi MickyT

Thank you for your message.

I agree that while we are looking at a smallish area, the curvature of the earth is not that relevant, however, I do want the event list and results to be accurate. I will take a look and see how the different options stack up.

I do have the below which as I am lead to believe will also help with the calculation.

ACOS(COS(RADIANS(90-@lat1))*COS(RADIANS(90-latitude)) +SIN(RADIANS(90-@lat1)) *SIN(RADIANS(90-latitude))*COS(RADIANS(@lon1-longitude)))*6371

This is being used in the calculation of the number of locations that fall within each 250 metre zone.

Thanks again for your help.

Regards

Dave

mickyT SSChampion Points: 10360 More actions · Answer 8

Dingo1Dave (6/18/2014)
...
I do have the below which as I am lead to believe will also help with the calculation.
ACOS(COS(RADIANS(90-@lat1))*COS(RADIANS(90-latitude)) +SIN(RADIANS(90-@lat1)) *SIN(RADIANS(90-latitude))*COS(RADIANS(@lon1-longitude)))*6371
This is being used in the calculation of the number of locations that fall within each 250 metre zone.
Thanks again for your help.
Regards
Dave

No problem

The calculation you have posted gives the distance in km's from what I can see. Have you thought about using the spatial datatypes for your project. They have a range of methods that would be helpful for you.DECLARE @lat1 float = 40

DECLARE @Lon1 float = 0.02034

DECLARE @latitude float = 40

DECLARE @longitude float = 0.02071

DECLARE @g1 Geography = Geography::Point(@lat1, @lon1,4326)

DECLARE @G2 Geography = Geography::Point(@latitude, @longitude,4326)

-- Increased the mulitplier to report metres

SELECT ACOS(COS(RADIANS(90-@lat1))*COS(RADIANS(90-@latitude)) +SIN(RADIANS(90-@lat1)) *SIN(RADIANS(90-@latitude))*COS(RADIANS(@lon1-@longitude)))*6371000

SELECT @g1.STDistance(@g2)

Dingo1Dave SSC-Addicted Points: 431 More actions · Answer 9

I didn't know this.

I will take a look - thanks again for your help.

Regards

Dave

Dingo1Dave SSC-Addicted Points: 431 More actions · Answer 10

Hi,

Got a bit sidetracked, but now back looking to smooth this out.

I am working on the larger area calculations and want to know if there is any way that i can define a lat lon that is land based as opposed to over water? Basically I want to be able to define the land based points and say use these while ignoring the water based points.

I have search and searched for info on this, any help would be very much appreciated.

Good weekend all.

Dave

mickyT SSChampion Points: 10360 More actions · Answer 11

Hi Dave

You will need to get yourself a polygon that defines the coastline. Once this is done and loaded into the database, you can use the spatial data types and there comparison methods. For example:

SELECT *

FROM myPoints p

INNER JOIN myCoastline c ON p.Shape.STWithin(c.Shape)

Make sure you have spatial indexes if you do this. Performance can be quite poor.

Here's one place where you could get a coastline definition. http://planet.openstreetmap.org/

Dingo1Dave SSC-Addicted Points: 431 More actions · Answer 12

Dingo1Dave

SSC-Addicted

Points: 431

June 30, 2014 at 10:17 am

#1725930

Thank you - very helpful indeed!

Regards

Dave