I’d like to make a database such that I can somehow search for entries that are within an x-mile radius of a specific zip code. Is there any way to do this on base?
Thanks!
Base is simply a database i.e., a container for storing various different types of data. The type of data you are talking about appears to be of a geolocation nature i.e., contains latitude and longitude data. If you are only dealing with a small area (and a flat landscape), you can use simple trigonometry to determine how close or distant a location is. What you are going to have to do though is create the logic to do this. I imagine this would be done via a Basic macro in a Form.
So let’s say you have two tables, places and zipcodes, both have x and y columns for latitude and longitude and a ‘name’ or ‘zip’ column.
So let’s say you search for places which are within a rectangle of 200 units from your specified zip point:
SELECT * FROM places WHERE x BETWEEN (SELECT (x - 200) FROM zipcodes
WHERE zip = 'Z123') AND (SELECT (x + 200) FROM zipcodes WHERE zip = 'Z123')
AND y BETWEEN (SELECT (y - 200) FROM zipcodes WHERE zip = 'Z123')
AND (SELECT (y + 200) FROM zipcodes WHERE zip = 'Z123')
Or if you search for places which are inside the radius of 200 units from your zip location (Pythagorean triple):
SELECT * FROM places WHERE
(POWER((x - (SELECT x FROM zipcodes WHERE zip = 'Z123')), 2) +
POWER((y - (SELECT y FROM zipcodes WHERE zip = 'Z123')), 2)) < 200
I have no idea which one would perform better, it may also depend on your data, if you have enormous amount of places then probably the first one might get an edge.