0

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.

  • You might want to put that logic into your question - it would save anybody who's trying to help you having to go to the trouble of looking it up? It's an interesting question - +1. p.s. welcome to the forum! 👍 – Vérace Nov 26 '19 at 4:20
  • if the coordinates are within one mile of the business location, then insert, otherwise skip over the insert or update. When a query inserts/updates a pack of records, you cannot add/edit a part of them and eject all another (exclusion INSERT..ODKU, and IGNORE option, which is uncontrollable). So SP seems to be the only option. – Akina Nov 26 '19 at 8:30
0
  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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.