I have Postgre database with network table that contains country, street name, numbers from left – to left, numbers from right-to right.

I finished geocoding function to use like this: user fills 3 text boxes for country, street and number and then I have query like this: Select * from network where country like (text box country) and street name like (text box street) …etc … For house number I have interpolation and the geocode function returns lon/lat.

Everything is fine with this function.

But I want to do geocoding like Google, user has only one text box and user can insert whatever he wants, and Google knows what to search.

For instance if user insert “Via Romma Palermo” or “Via Roma Palermo” or “Via Roma 16 palermo” Google always has the right answer and is very fast.

What is the trick? How to do fast search, do I need some algorithm for this or what? Do I need separate table for all addresses? How to handle errors in typing Romma, Roma …etc? How to know what is first country, street, what field in the database to search for?

  • 1
    It is called AJAX - en.wikipedia.org/wiki/Ajax_%28programming%29
    – Mapperz
    Jun 25 '12 at 13:33
  • Note that Google doesn't "always [have] the right answer" -- it merely approximates and takes good guesses to help find addresses; it doesn't actually verify them. In many cases, it is actually technically incorrect.
    – Matt
    Jun 25 '12 at 14:58
  • I have to implement my geocoding function both on web and desktop application. AJAX is for web, and how to do this on desktop application? Jun 25 '12 at 21:53

Your question contains a number of sub-questions - this answer addresses solving the server side part of spell checking.

A while back I've implemented a spelling corrector for addresses based on Peter Norvig's How to Write a Spelling Corrector.

Instead of using books to build your training corpus, your dictionary will be based on words tokenized out of toponyms, street names, city names and so on.

Once you have a training corpus based on correct addresses (tokenized into words), you will be able to run "Romma" through the spelling corrector and get "Roma".

  • Thank you, it is useful site ... but spelling is not my primary problem. I must solve search engine first ... Jun 25 '12 at 21:55

Here is an AJAX Tutorial that explains and shows how to set up a service with it.

Using MySQL (can be modified for Postgres) php javascript.


NOTE: The search box on the dynamicjax site is suggesting when you start typing/searching.

You will need to modify this to suit your DB.

The database structure for this tutorial is going to consist of two simple columns: suggest_id and title. All we really need is the title, but I always provide a primary key for any table that I create.

CREATE TABLE `suggest` (
  `suggest_id` INT(11) NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY  (`suggest_id`)

required: '="JavaScript" type="text/javascript" src="ajax_search.js"'

The powerful side is using the code:

//Called from keyup on the search textbox.
//Starts the AJAX request.
function searchSuggest() {
    if (searchReq.readyState == 4 || searchReq.readyState == 0) {
        var str = escape(document.getElementById('txtSearch').value);
        searchReq.open("GET", 'searchSuggest.php?search=' + str, true);
        searchReq.onreadystatechange = handleSearchSuggest; 
  • Sorry, but I don’t understand how to implement addresses from my database in table Suggest. Should I put all addresses in field Title? For instance should I fill title field with all combination like this: Via Roma Palermo, Via Roma 2 Palermo, Via Roma 25 Palermo, Palermo via Romma, Palermo via Roma 67, …. etc Could you give me some example? Jun 25 '12 at 21:44
  • no you can change the table name to what you already have [it's only an example how to achieve it]. This method only matches as you type with any keystrokes that match your db rows.
    – Mapperz
    Jun 26 '12 at 13:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.