How to implement a searchable addresses database (for a form)? I'm not sure if I should ask this type of question in this site, but I already tried to ask in the Drupal forum here with no reply so far.

I'm trying to find out the best way to implement a solution in an existing Drupal 7 site that must meet the following requirements:


a option to import addresses to the database from a csv/xls file, with:


fields: id, city, locality, street, street-number, postal code
around 100000 records/addresses

a form that include a address section/field(s) with a search functionality to search the addresses


where the user would fill up some of the address fields (ex: city, street; or just street)


and it would show a list with the matched addresses
that would be sorted by relevance


for example: is more relevant if it's found in both "street" and "city" fields, than only in the "street" field
(ideally it would be nice to replicate the google maps search)



a option to export the submitted form values to a csv/xls
  (if this is too of topic, just ignore this step (3))
(The site is on a Drupal 7, with mysql 5.5, and php 5.5)


Previously the client had tried to implement the import requirement, but it was to slow (a few days just to load the csv file), using the feeds plugin and a taxonomy structure to save part of the address fields.

I'm relatively new to Drupal backend development, mostly frontend, theming (templates with html, js, css/sass, and just a bit of php) in Drupal and doing some simple forms in backend, but I have some MySQL and PHP background.

So I was thinking in building a module to:


create database tables of:


addresses (fields: id, city, locality, ...)
addresses_searchable, where it would have addresses strings fields, by converting the strings:


to lowercase
replacing non-latin text to US-ASCII (ex: "á", "à" to "a")
in order to help speeding up the search


when using "like" in Drupal database search api


form_submitions (fields: id (of the submitted form), name, ... (other form fields) , adress_id)

create a form
. using ajax to load the search addresses list by pressing a button 
set up a backend page (using permissions) for the import and export pages


import:


include a button to load addresses file (csv/xls), and other to run latest loaded file, and show a progress bar, and a log with possible errors

export:


using a button to download all form submissions up to now, or up to / after a selected date




From what I researched so far:


for reading files:


xls: could use the Drupal module phpexcel
csv: by using PHP native functions

to search the addresses:


by checking the Drupal api,


(in https://www.drupal.org/docs/7/api/database-api/dynamic-queries/expressions)
it doesn't seem possible to do relevant searches, like using the MySQL Match function:


SELECT *, MATCH ('city','street',...) AGAINST (':adressInputText' IN NATURAL LANGUAGE MODE) as 'relevance' ...


there is a Search API module that maybe would be the way to go


I haven't used it before, but maybe it would work if i create a entity for the address with the matching fields. Would be faster that a dedicated table?
in case it's better this way maybe, i could still use the address table as a temporary table, just to speed up the importing of the csv/xls addresses file




I'm not sure if this is the best approach to the problem, and what are the best practices to implement this in Drupal 7.
Or even if it is something that should other solution/framework like for example:


use something like Laravel (v5.4) (which i also would have to learn), and load the forms in Drupal 7 through a iframe that would show the form from the other server and framework.


I've searched Google but I’m still a bit confused. Can someone point me in the right direction?
My first approach would be to build a basic importer using the BatchAPI and create a custom entity to hold the address data (NOT a content type), combined with Address Field for proper storage.

Then, I would use Search API and Search API Solr to index and store those entities, afterwhich you can build custom forms with Form API or use Views to expose and search the index - which will be much faster than Drupal db queries. Whenever possible, keep MySQL from doing work, as this is what will bog down your site.

This keeps things close to "Drupal native" while solving many of the performance issues you raised with Solr.

You can use Form API to build forms with #ajax callbacks to query and return data from Search API queries as well.

The import will never be lightning fast unless you're not using the Drupal API to store them as entities. When you don't store them as entities, you lose a lot of the flexibility of say, plugging into Views or Search API without a decent bit of work. You couldn't also add fields with field API, at that point it's just flat data, so say goodbye to easy addition of taxonomy fields or using Address Field.

With Solr constantly indexing new and updated entities, having the import be lightning fast isn't really a priority (IMO) as long as it is reasonable. If you have millions of addresses, Drupal will only be able to parse rows so fast, and a lot of this is due to the event system (save an entity, fields attach, hooks fire, alters fire, etc) on every call to entity_save. The price you pay in speed here is paid off later when you want to manipulate or integrate that data with the contrib space.

The gist I linked to was an experiment I was doing with the Data module and CSV files. This loads the entire CSV file and dumps its contents into MySQL using load infile. This will plant data into a database very quickly, but, as I said, you will have a lot more work trying to get Drupal to recognize, communicate with, and extend that data.

Another approach, totally non-Drupal, would be to import the data to a PostgreSQL (9.4+) database or a no-SQL equivalent, possibly even something like Firebase, and execute searches against that. Then again, this is almost the same as doing Search API + Search API Solr, except you'd have to build the wrappers to create and send queries to those servers. So, it is still valid to offload all of this work in a separate system just to crunch data. It really depends on how much programming you feel like doing.

Just to be clear, there are a handful of options here, it just depends on how much you can stomach if it doesn't use Drupal in some way. Personally, I would not want to give up the flexibility the Drupal API provides in terms of understanding your data model, and therefore, I would create a custom entity and start there. It will be slower, but in the end provide more points of integration for you in the module ecosystem.