0

I have a MySQL table of Florists and I'm trying to write a search function which will take Zipcode as an input and find all Florists who deliver to that zipcode. Each florist record in my table has 3 items

  • geocoded longitude (centered at the florist's address)
  • geocoded latitude (centered at the florist's address)
  • a string field of townnames and zipcodes where the florist delivers e.g. - Boston (02215, 02108, 02109), Chelsea (02150), Somerville(02143, 02144, 02145)

Lets say I want to find all florists who deliver to "02108". What is the best way to implement this search in MySQL?

I was thinking

  1. Exact Match search - but this would require every search to scan the entire table. Inefficient?

  2. Haversine search + Exact Match - Use the latitutde/longitude value to narrow down the search to a 15 mile radius

    MySQL Great Circle Distance (Haversine formula) And then perform the Exact Match within these results.

  3. Something else?

Would appreciate your thoughts and input.

Community
  • 1
  • 1
rogerb
  • 251
  • 1
  • 4
  • 11
  • Do you have the ability to add a child table as a 1 to many on the florist id and zip codes? It would make your search a lot more efficient. – gmiley Oct 14 '14 at 18:45
  • At some point you are going to do a full table read to narrow your results down. The most economical seems like a `LIKE` on the zipcode/town field. `SELECT * FROM table WHERE like '%%'` for instance. Doing a Haversine on all that to narrow down before doing the `LIKE` is lot of processing. – JNevill Oct 14 '14 at 18:46
  • Thanks gmiley and JNevill for your quick response. I agree, a child or junction table is the way to go here. – rogerb Oct 14 '14 at 18:56

1 Answers1

1

The proper way is to represent the list of zip codes as a junction table, with one row per florist and one row per zip code. So, your first florist might have these rows:

FloristId   Zip
1           02215
1           02108
1           02109
1           02150
1           02143
1           02144
1           02145

Then, build an index on the zip column. Then you can use a simple join to get florists who deliver to a particular zip code.

You can have a reference table to look up the primary town for a zip code. The US Census Bureau has a mapping from zip code tabulation areas to town names. The USPS probably has something similar.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks Gordon. I think what you are suggesting makes sense. Thanks for your quick response! – rogerb Oct 14 '14 at 18:57
  • However you do implement it - try and avoid expecting a 5 digit zip. Users who have leading zeros often don't supply them so make sure you can cope with '803' and '2145' use cases. – Al Mills Oct 14 '14 at 19:46
  • @AlMills . . . In the United States, people always provide leading zeros (at least in my experience). Perhaps it is part of that national psyche, but zip codes are five digits long (unless they are nine digits). – Gordon Linoff Oct 15 '14 at 17:03