I'm developing a web application with heavy usage of geospatial data and am trying to decide the best course of action on how to proceed with a problem:
I need to be able to take the coordinates of a point, reference them against a table of areas and assign the point to one or more of those areas.
My first thought was to do this by having N,S,E,W points in the database and loop through "if" statements, comparing the point coordinates against the bounding coordinates of the area to see if the point falls into the range.
After thinking about it, though there has to be an easier mathematical way to calculate those relationships. Since the areas table contains over 100,000 records to start with, using a single mathematical calculation instead of a series of conditional loops is also going to be easier on the server and the database.
I've been digging through formulas trying to find something that fits the situation, however, I'm coming up empty. Can anyone who's better at the math side give me a hand? Thanks.