BEFORE INSERT validation trigger for GPS coordinates

Question

I have a MySQL database table that contains zipcodes and their corresponding lat/lng values.

I have another table that contains business locations (including lat/lng)

Prior to inserting new records for a particular business, or updating existing records, I need to validate that the lat/lng values being inserted or update ARE within specified tolerances for the the business location and zipcode. For example, if the coordinates are within one mile of the business location, then insert, otherwise skip over the insert or update.

Essentially the trigger or stored procedure needs to perform calculations on fields from the tables and make comparisons of the resulting calculated values against maximum allowable tolerances to DECIDE whether to allow the INSERT/UPDATE to occur.

I have the necessary calculations themselves, I just don't have the knowledge of how to structure it within a trigger or stored procedure.

Answer 1

1. Load the new data into a temporary table.
2. Batch compute distances (one UPDATE the temp table)
3. One INSERT..SELECT to copy the rows that are within the desired distance.

If you need to write an expression for distance, suggest using Pathegorus, with an adjustment to the longitude of COS(latitude).

Batching things is often a faster way to do bulk operations.