+ Reply to Thread
Results 1 to 4 of 4

Sort database based on longitude and latitude

  1. #1
    Registered User
    Join Date
    11-15-2018
    Location
    Oslo
    MS-Off Ver
    2010
    Posts
    2

    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. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    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.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    11-15-2018
    Location
    Oslo
    MS-Off Ver
    2010
    Posts
    2

    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:

    =ACOS(COS(RADIANS(90-A2)) *COS(RADIANS(90-A3)) +SIN(RADIANS(90-A2)) *SIN(RADIANS(90-A3)) *COS(RADIANS(B2-B3))) *6371

    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!
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    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?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Finding Near by ATMS in 1 km range based on Latitude and Longitude
    By aabhi2251 in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 08-30-2016, 02:58 PM
  2. [SOLVED] Lookup values based on longitude and latitude
    By MattRNR in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-05-2014, 06:18 PM
  3. Distance between latitude and longitude
    By fazna ali in forum Excel General
    Replies: 2
    Last Post: 11-06-2012, 05:03 AM
  4. Replies: 7
    Last Post: 05-02-2012, 01:00 AM
  5. MGRS to Longitude/Latitude
    By ajocius in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2009, 11:26 AM
  6. [SOLVED] formula for longitude/latitude
    By Leo in forum Excel General
    Replies: 2
    Last Post: 06-03-2005, 02:05 PM
  7. [SOLVED] Help with Latitude and Longitude.
    By LadiFireBug in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 01-22-2005, 05:09 AM

Tags for this Thread

Bookmarks

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