1

Building a LAMP services application that will have 10000's of Vendors providing delivery to Customers, and upon the Customer entering their address, we need to generate a delivery list of Vendors which can provide service to that location. Each Vendor will have a delivery boundary that will be defined by one of these three criteria:

  • A. List of Zip Codes
  • B. Distance of delivery point from Vendor in miles (X) (point to point)
  • C. Defined polygon drawn (most likely) in GME and imported as KML (point in polygon)

A is straightforward, but after extensive research we are unsure of what would be the most efficient and scalable way to approach B and C. Should we use MySQL to store the data and calculate results using code/classes/library, or should we setup a spatial DB like PostGIS to handle all geo storage and calculation, and what about API solutions for some or all, etc.?

Here is our current line of thinking in broad strokes:

  1. Store polygon data (as KML?)
  2. Convert Vendor address to verified lat/long coordinates
  3. Convert B and C boundaries to zip code array to generate subset of likely matches
  4. Convert Customer address to verified lat/long coordinates

The algorithm would then have 3 parts to return a master delivery list:

Part (a):

  • Query all A Vendors who deliver to Customer's delivery zip code

Part (b):

  • Filter out all B Vendors that don't have the Customer's zip in likely match array
  • Query that subset of B Vendors where the distance between coordinates is less than specified

Part (c):

  • Filter out all C Vendors that don't have the Customer's zip in likely match array
  • Query that subset of C Vendors where Customer's coordinate is within the polygon

Seeking advice on best practice and what tool/technology/APIs to use, for each step starting with address verification, long/lat of the verified addresses, auto-generate zip array based on spatial data of B and C, calculating point-to-point, creating polygon, storing/converting polygon data, using KML or what?, and calculating point-in-ploygon. Pointers to posts/research/resources very welcome!

user30363
  • 11
  • 1

0 Answers0