Fastest hierarchical geo-address, data search on least expensive equipment? NoSQL or SQL?
I have 350,000 city addresses with Latitude and Longitude values, for example:
2500 HardToSpellName Street NW (Quadrant), City, State, Country
It would seem that the best data structure would be a JSON file in mostly reverse order and the user must enter the request in the following order:
Country.State.City.Quadrant.StreetType - all repeated many times
Then switch to entering civil number data as numbers are easy to write :) From the above, we will implement a search to fill in "Autocomplete" in the street name as it is misspelled.
The data request is always the same, one input of the address gets the Lat / Long result.
Is this a good idea? How many entries would be reasonable? How would you convert a table (csv) to a JSON tree?
Is the main reason for using NoSQL for lower hardware / hosting costs?
source to share
I think the best idea is to limit the potential result to as few records as possible using user input. This can be achieved with a combined index [Country, State, City, Quadrant, StreetType] if users must enter search terms in that order.
The index will filter "Country" if it is the first and only entry. If a country is selected and "State" is entered, the index query will limit the results to reports for the entered country / state combination, and so on. In general, the more criteria you have, the further you can use it to narrow down your results. The requirement is that you are using some sort of sorted index and only ask for the indexed attributes on the left.
When the last criterion (StreetType) is entered, the result set is probably already quite small, so you can return all the street names from it to the application and create and auto-populate the input box. You can alternatively expand the index to include street names as well. This will allow you to efficiently get an alphabetical list of street names (and coordinates) for your search criteria.
As far as I understood, the data can be put into a flat table because all records have the same structure. A sorted index can then be created for the attributes to be indexed. Any relational database should support this.
You can also use a NoSQL docs database for this purpose, and it should work fine as well.
To decide which one is the best solution, I think you should also consider your load and other factors, for example. - Will you have data updates and how often? do i need transactional isolation for read and update? - what other operations should be performed within the database? - can you live with a flat table structure or do you really need hierological data, flexible schemas?
source to share