×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Searching points within a bounding circle in excel
2

Searching points within a bounding circle in excel

Searching points within a bounding circle in excel

(OP)
I am trying to find points in an excel database that lie within a circle with a 50 mile radius.  I have over 100,000 points in the database all given in lat/long form.  The center of the circle will be lat/long (x,y).  How do I write a macro that finds the points bounded by this formula?

RE: Searching points within a bounding circle in excel

Your database contains a lot of points (referencea) with data in the lat/long form - and you want to select those where the distance to another reference point is less than X miles?

First make sure that your coordinates uses the same units as your criteria. Then use pythagoras to calulate the distance then select if<X.

You dont even need a macro. Just add the calculation in the next collum and use conditional formatting to highlight the matching points. If you wish to remove all non match, then write a macro, do the calc and erase all the non match. Use "record macro" to quickly determine the coding for navigation and deleting a row.

Best regards

Morten

RE: Searching points within a bounding circle in excel

Just to add to the very good suggestion.

You can rap your distance calculation into an "if" function and have it return 0 or a 1 e.g. =1 if within your distance (circle). You can then use the filter function to produce a list, which if required could be copied into another sheet. The filter function will be very useful for large non-sequential lists.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources