Gentoo tips & Additional Math Extension for sqlite required

[John L. Poole]

I'm still not in the clear, but here is what I've learned and had to
do to lessen the error output with the command:
bin/tiger_import /var/work/tiger/geocoder3.db /var/work/tiger/tiger

First, the version of dev-db/sqlite I obtained from Gentoo did not
have the function "ceil" [ceiling] available. I learned that Liam
Healy created a math extension pack for sqlite; it is available at:
http://www.sqlite.org/contrib

Then, I downloaded Healy's math extension and create a library:

downloaded healy's
wget http://www.sqlite.org/contrib/download/extension-functions.c?get=25
mv extension-functions.c\?get\=25 extension-functions.c
Compiled per instructions in the file:
hermes sqlite # gcc -fPIC -lm -shared extension-functions.c -o
libsqlitefunctions.so
extension-functions.c: In function 'print_elem':
extension-functions.c:1945: warning: format '%lld' expects type 'long
long int', but argument 3 has type 'int64_t'
hermes sqlite #

I moved the extension, libsqlitefunctions.so, to my working
directory, /var/work/geocoder. I then modified the ...geocoder/bin/
tiger_import shell script as follows:

HELPER_LIB="$BASE/../lib/geocoder/us/sqlite3.so"
DATABASE=$1
shift
#
# by jlpoole
#
HEALY_LIB="$BASE/../lib/geocoder/us/libsqlitefunctions.so"
...
echo "processing --- $county"
...
# Generate an SQL stream to feed into the sqlite3 binary.
# Start by loading the helper libs and initializing the temporary
tables
# that will hold the TIGER data before ETL.
# below is original
#(echo ".load $HELPER_LIB" && \00
#
# .load <library> need to be on separate lines,
# HELPER_LIB calls the function "ceil" which is
# not standard, so load $HEALY_LIB first
#
(echo ".load $HEALY_LIB" && \
echo ".load $HELPER_LIB" && \
cat ${SQL}/setup.sql && \

This got me to the point where now I have a cryptic "memory" message
during the load:

hermes geocoder # bin/tiger_import /var/work/tiger/geocoder3.db /var/
work/tiger/tiger
processing --- /var/work/tiger/tiger/06_CALIFORNIA/06055_Napa_County
memory
hermes geocoder #

Now... have to dig around and find what "memory" means... I fear it's
probably some memory limitation (I have 8 GB of ram on this Gentoo
AMD64/Quadcore box where I'm evaluating this project, so I'm guessing
there is a predetermined memory limit that is in play here).

For the record, my test results come back with bad longitude/
latitudes:

hermes geocoder # ruby -rubygems jlpooletest1.rb
[{:city=>"Napa", :number=>"2601", :street=>"1st
St", :precision=>:street, :zip=>"94558", :fips_county=>"06055", :lon=>0.000513, :prenum=>"", :score=>0.591, :lat=>0.000512, :state=>"CA"}]
[{:street1=>"1st Ave", :city=>"Napa", :street2=>"1st
St", :precision=>:intersection, :zip=>"94558", :fips_county=>"06055", :lon=>0.000513, :score=>0.507, :lat=>0.000512, :state=>"CA"},
{:street1=>"1st Ave", :city=>"Napa", :street2=>"1st
St", :precision=>:intersection, :zip=>"94558", :fips_county=>"06055", :lon=>0.000513, :score=>0.507, :lat=>0.000768, :state=>"CA"}]
[{:street1=>"1st Ave", :city=>"Napa", :street2=>"1st
St", :precision=>:intersection, :zip=>"94558", :fips_county=>"06055", :lon=>0.000513, :score=>0.507, :lat=>0.000512, :state=>"CA"},
{:street1=>"1st Ave", :city=>"Napa", :street2=>"1st
St", :precision=>:intersection, :zip=>"94558", :fips_county=>"06055", :lon=>0.000513, :score=>0.507, :lat=>0.000768, :state=>"CA"}]
hermes geocoder #

[John L. Poole]

Also, I browsed the database, /var/work/tiger/geocoder3.db, and found
that all the tables had data in them. Previously, the load failed as
evidenced by the range table being empty.

[John L. Poole]

Just to be complete, here's my ruby script:

#!/usr/bin/ruby
# -*- coding: utf-8 -*-
# Use this to run:
# ruby -rubygems jlpooletest1.rb
#

require 'text'
require 'geocoder/us'

db = Geocoder::US::Database.new("/var/work/tiger/geocoder3.db")
p db.geocode("2133 First Street, Napa, CA")
p db.geocode("First & Main, Napa, CA")
# try one that is suppose to fail
p db.geocode("First & Third, Napa, CA")

#
#from http://geocoder.us/ using "2133 1st Street, Napa, CA"
#
#Address
# 2133 1st St
#Napa CA 94559
#(38.297514, -122.296985)
#(it can take a bit for the map to load-wait for the red circle to
turn green. Stay in your happy place.)
#
#
#
#Latitude
# 38.297514 °
#N 38 ° 17' 51.1"
#38 ° 17.8508' (degree m.mmmm)
#Longitude
# -122.296985 °
#W 122 ° 17' 49.1"
#-122 ° 17.8191' (degree m.mmmm)