[Geocoding] Problems while creating your own gazetteer server

[Ciprian Talaba]

Hi,

I found out how to turn on the debug mode (damn, taht was hard :-) ) and
here is the out when I search for 'galati' (there is a city called Galați in
Romania):

string(196) "select min(word_id) as word_id,word_token, word, class,
type, location,country_code from word where word_token in ('
galati','galati') group by word_token, word, class, type,
location,country_code"
array(1) {
  [0]=>
  array(3) {
    ["string"]=>
    string(6) "galati"
    ["words"]=>
    array(1) {
      [0]=>
      string(6) "galati"
    }
    ["wordsets"]=>
    array(1) {
      [0]=>
      array(1) {
        [0]=>
        string(6) "galati"
      }
    }
  }
}
array(2) {
  ["galati"]=>
  array(1) {
    [0]=>
    array(7) {
      ["word_id"]=>
      string(3) "510"
      ["word_token"]=>
      string(6) "galati"
      ["word"]=>
      NULL
      ["class"]=>
      NULL
      ["type"]=>
      NULL
      ["location"]=>
      NULL
      ["country_code"]=>
      NULL
    }
  }
  [" galati"]=>
  array(1) {
    [0]=>
    array(7) {
      ["word_id"]=>
      string(3) "509"
      ["word_token"]=>
      string(7) " galati"
      ["word"]=>
      string(7) "Galați"
      ["class"]=>
      NULL
      ["type"]=>
      NULL
      ["location"]=>
      NULL
      ["country_code"]=>
      NULL
    }
  }
}
array(2) {
  [1]=>
  array(1) {
    [0]=>
    array(11) {
      ["iSearchRank"]=>
      int(1)
      ["iNamePhrase"]=>
      int(0)
      ["sCountryCode"]=>
      bool(false)
      ["aName"]=>
      array(1) {
        [509]=>
        string(3) "509"
      }
      ["aAddress"]=>
      array(1) {
        [509]=>
        string(3) "509"
      }
      ["sClass"]=>
      string(0) ""
      ["sType"]=>
      string(0) ""
      ["sHouseNumber"]=>
      string(0) ""
      ["fLat"]=>
      string(0) ""
      ["fLon"]=>
      string(0) ""
      ["fRadius"]=>
      string(0) ""
    }
  }
  [10]=>
  array(1) {
    [0]=>
    array(11) {
      ["iSearchRank"]=>
      int(10)
      ["iNamePhrase"]=>
      int(0)
      ["sCountryCode"]=>
      bool(false)
      ["aName"]=>
      array(1) {
        [510]=>
        string(3) "510"
      }
      ["aAddress"]=>
      array(1) {
        [510]=>
        string(3) "510"
      }
      ["sClass"]=>
      string(0) ""
      ["sType"]=>
      string(0) ""
      ["sHouseNumber"]=>
      string(0) ""
      ["fLat"]=>
      string(0) ""
      ["fLon"]=>
      string(0) ""
      ["fRadius"]=>
      string(0) ""
    }
  }
}
<table border="1"><tr><td>1</td><td>#
galati#</td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>10</td><td>#galati#</td><td></td><td></td><td></td><td></td><td></td></tr></table>array(11)
{
  ["iSearchRank"]=>
  int(1)
  ["iNamePhrase"]=>
  int(0)
  ["sCountryCode"]=>
  bool(false)
  ["aName"]=>
  array(1) {
    [509]=>
    string(3) "509"
  }
  ["aAddress"]=>
  array(1) {
    [509]=>
    string(3) "509"
  }
  ["sClass"]=>
  string(0) ""
  ["sType"]=>
  string(0) ""
  ["sHouseNumber"]=>
  string(0) ""
  ["fLat"]=>
  string(0) ""
  ["fLon"]=>
  string(0) ""
  ["fRadius"]=>
  string(0) ""
}
string(697) "select
place_id,ST_Contains(ST_SetSRID(ST_MakeBox2D(ST_Point(-295.312499988,83.0509608196),ST_Point(295.312499988,-75.881174634)),4326),
centroid) as in_small,ST_Contains(ST_SetSRID(ST_MakeBox2D(ST_Point(-885.937499965,241.983096273),ST_Point(885.937499965,-234.813310088)),4326),
centroid) as in_large from search_name where name_vector @> ARRAY[509]
and nameaddress_vector @> ARRAY[509] order by
ST_Contains(ST_SetSRID(ST_MakeBox2D(ST_Point(-295.312499988,83.0509608196),ST_Point(295.312499988,-75.881174634)),4326),
centroid) desc,
ST_Contains(ST_SetSRID(ST_MakeBox2D(ST_Point(-885.937499965,241.983096273),ST_Point(885.937499965,-234.813310088)),4326),
centroid) desc, search_rank ASC limit 10"
array(0) {
}
array(11) {
  ["iSearchRank"]=>
  int(10)
  ["iNamePhrase"]=>
  int(0)
  ["sCountryCode"]=>
  bool(false)
  ["aName"]=>
  array(1) {
    [510]=>
    string(3) "510"
  }
  ["aAddress"]=>
  array(1) {
    [510]=>
    string(3) "510"
  }
  ["sClass"]=>
  string(0) ""
  ["sType"]=>
  string(0) ""
  ["sHouseNumber"]=>
  string(0) ""
  ["fLat"]=>
  string(0) ""
  ["fLon"]=>
  string(0) ""
  ["fRadius"]=>
  string(0) ""
}
string(697) "select
place_id,ST_Contains(ST_SetSRID(ST_MakeBox2D(ST_Point(-295.312499988,83.0509608196),ST_Point(295.312499988,-75.881174634)),4326),
centroid) as in_small,ST_Contains(ST_SetSRID(ST_MakeBox2D(ST_Point(-885.937499965,241.983096273),ST_Point(885.937499965,-234.813310088)),4326),
centroid) as in_large from search_name where name_vector @> ARRAY[510]
and nameaddress_vector @> ARRAY[510] order by
ST_Contains(ST_SetSRID(ST_MakeBox2D(ST_Point(-295.312499988,83.0509608196),ST_Point(295.312499988,-75.881174634)),4326),
centroid) desc,
ST_Contains(ST_SetSRID(ST_MakeBox2D(ST_Point(-885.937499965,241.983096273),ST_Point(885.937499965,-234.813310088)),4326),
centroid) desc, search_rank ASC limit 10"
array(0) {
}

I have tried to execute those statements manually, and I have found out that
the search_name table is empty. In what conditions does this happen? Am I
missing some steps while creating/updating the DB (from a short
investigation of the code - I am far from being a pgSQL expert - seems like
something is going wrong while reindexing the data)?

Thanks,
Ciprian
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/geocoding/attachments/20091127/503ae011/attachment.html>