Sort database based on longitude and latitude

1. Sort database based on longitude and latitude

Hey guys!

I have a big database with different information I want to sort out. The criteria are based on longitude and latitude. I want to show the rows with for instance longitude between 67.9014N-67.204N and 15.185E-15.30E.

And the problem is that Im trying to enter a spesific longitude and latitude and the the distance from that point. Lets say 10km (either a circle or square, what ever is the easiest).

Any suggestion on how to solve this? Big thanks if so!

-Alek

2. Re: Sort database based on longitude and latitude

Hi and welcome to the forum

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

3. Re: Sort database based on longitude and latitude

Here is an example file.

In sheet 2 you can see the database (just a tiny sample of it). It is a database about the numbers of lightning happened
in Norway. Beforehand I was brought into this project, did they calculate the min and max longitude/latitude from a given
coordinate with a area of 100km^2 and 450^km^2. However, I do not know how they did this as it do not show any
code or so...

I have tried to calcualte the distance between the max and min with the help from this pages, but I cant link the pages
in the forum until I have posted a few times...Im using this formula to calcualte the distance between two cordinates:

So the problem is that I want to input the distance in cell "B4" in sheet 1. Then sort out the database and
show me all the lightning within a 10km radius of coordinate.

Did this explain it somehow? Anyway, thanks for the responds! Hope to hear som toughts!

4. Re: Sort database based on longitude and latitude

Still not sure I understand what you are having trouble with. As a starting point, here's what I would expect to do:

1) I did not quite understand your distance formula (perhaps because I am not sure what A2, A3, B2, B3 represent, and those cells are not coordinates in either of the tabs in your file). I also do not see any attempt to enter that formula anywhere in your existing file. Assuming that formula is correct for calculating distance, A2 represents the latitude for the reference coordinate, A3 represents the latitude of an individual coordinate, and B2 and B3 represent the corresponding longitudes, I
1a) took what looks like the the reference coordinates in sheet1!C1:D1 and entered them (without the N and E) in P1 and Q1 of sheet2.
1b) Entered the distance formula (references adjusted appropriately and used an appropriate combination of relative and absolute references) into K2 and copied the formula down. This gives me a column of distances relative the given reference point.
1c) Looking over the results, I see several numeric results, and a few error values. The errors seem to be the result of feeding a value greater than 1 into the ACOS function, which is undefined. I did not explore the source of the error further.
2) With a column of distances for each point, I selected the table -> Data -> Filter. With the autofilter dropdowns activated, I selected the distance dropdown and filtered for values < 1000 (just a random number that looked suitable for the results given). This hid all rows (including error rows) that were greater than 1000.
3) Select those visible rows -> copy -> go into sheet 1 and select output cells -> paste special as values

The result was a list of data points in sheet1 that were within 1000 of the reference coordinates.

Obviously there is a lot more development to do, but I wasn't sure exactly what you are trying to do, so I didn't want to spend a lot of time on something more automated. That was a quick and easy (to program) overview of what I think you are trying to do. Did I understand correctly?

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1