I need a way to get the zipcode of a lat/long pair fast (<10 ms), and at a high rate (thousands of qps)
I've loaded a shapefile from the US Census Bureau into a mysql database with spatial indexing and I've been using the following code to query it:
select zipcode from geo.part where ST_Contains(boundary, POINT(?, ?)) LIMIT 1;
It works great, only I'm seeing response times between 20 and 100ms.
Do you have any suggestions to have it run faster?
A different stack? (postgres with postgis maybe?)
The shapefile had quite a lot of points per zipcode, I don't really need that much resolution. Would pruning those files make much of a difference (remove half of the points for each zipcode)?