[Geocoding] Take House Numbers from TIGER on Reverse Geocoding

[Silviu Simon]

On Saturday, September 08, 2012 1:51:51 PM, Sarah Hoffmann wrote:
> On Thu, Sep 06, 2012 at 10:43:58AM +0300, Silviu Simon wrote:
>> On 8/23/2012 10:12 PM, Sarah Hoffmann wrote:
>>> On Thu, Aug 23, 2012 at 10:50:57AM +0300, Silviu Simon wrote:
>>>> Hello,
>>>> I recently did an import of USA into my nominatim server database
>>>> and I've noticed that my searches on reverse-geocoding do not return
>>>> house numbers. I found out that in order to get house numbers,
>>>> nominatim uses TIGER on search, so I'm trying to make the program
>>>> use house numbers from TIGER on reverse geocoding.
>>>> I've noticed that this line
>>>>
>>>> $aAddress = getAddressDetails($oDB, $sLanguagePrefArraySQL,
>>>> $iPlaceID, $aPlace['country_code']);
>>>>
>>>> line 153 in website/reverse.php is responsible for building
>>>> @aAddress which contains all information about the address and is
>>>> then parsed in lib/template/address-xml.php to create the result
>>>> xml.
>>>> I'm thinking I should make a sql query in the tiger database to get
>>>> the house numbers, and then add it to $aAddress.
>>>> Am I correct? I would appreciate any help you could give me on this matter.
>>> getAddressDetails() will get you a nicely formatted TIGER address when you
>>> give it a $iPlaceID that is a place_id in the TIGER address table
>>> (location_property_tiger). This part of the code does not need to be changed.
>>> The only thing you need to do is find this nearest place_id.
>>>
>>> As twain already mentionened, the easiest way would most likely be to add
>>> an index on the centroid column of the location_property_tiger table and
>>> look for the nearest place_id. Add this around 111, compare with the result
>>> the while loop above returned and choose whichever suits you better. That
>>> is pretty much all.
>>>
>>> Sarah
>>>
>> Hello,
>> Could you please elaborate what do you mean with adding an index on
>> the centroid column ?
>
> CREATE INDEX idx_tiger_centroid ON location_property_tiger USING GIST(centroid);
>
> Regards
>
> Sarah
>
How do you apply it to the database?

 $sSQL = 'CREATE INDEX idx_tiger_centroid ON location_property_tiger 
USING GIST(centroid);';
 $vVar = $oDB->getRow($SSQL);

I know it's not getRow(). It should be something like execute(). I 
couldn't find anything by googling it.

Thank you!