Queries to convert TIGER edges data to labeled address ranges. See: http://mike.teczno.com/img/tiger-ranges.png

queries.pgsql

DROP TABLE IF EXISTS tiger_2012_edges_102008;
DROP TABLE IF EXISTS tiger_2012_ends_102008;

CREATE TABLE tiger_2012_edges_102008
AS
(
    SELECT statefp, countyfp, mtfcc, fullname,
           fromadd, toadd, zip, "offset",
           
           --
           -- Snip off first and last 15m of each road to distance it from intersection.
           --
           ST_Line_Substring(s.geom, 15 / ST_Length(s.geom), (ST_Length(s.geom) - 15) / ST_Length(s.geom)) AS geom
    FROM
    (
        SELECT statefp, countyfp, mtfcc, fullname,
               lfromadd AS fromadd, ltoadd AS toadd, zipl AS zip, offsetl AS offset,

               --
               -- Offset 5m to the left.
               --
               ST_OffsetCurve(ST_Transform(ST_GeometryN(geom, 1), 102008), 5) AS geom

        FROM tiger_2012_edges
        WHERE roadflg = 'Y'
          AND lfromadd IS NOT NULL
    
        UNION
        
        SELECT statefp, countyfp, mtfcc, fullname,
               rfromadd AS fromadd, rtoadd AS toadd, zipr AS zip, offsetr AS offset,

               --
               -- Offset 5m to the right and reverse: "For a negative distance
               -- it'll be at the right side and in the opposite direction."
               -- http://postgis.refractions.net/docs/ST_OffsetCurve.html
               --
               ST_Reverse(ST_OffsetCurve(ST_Transform(ST_GeometryN(geom, 1), 102008), -5)) AS geom

        FROM tiger_2012_edges
        WHERE roadflg = 'Y'
          AND lfromadd IS NOT NULL

    ) AS s
    
    --
    -- Limit to those road segments long enough to be meaningful.
    --
    WHERE ST_Length(s.geom) > 40
);

CREATE TABLE tiger_2012_ends_102008
AS
(
    SELECT statefp, countyfp, mtfcc, fullname,
           add, zip, "offset", p.geom
    FROM
    (
        SELECT statefp, countyfp, mtfcc, fullname,
               fromadd AS add, zip, "offset",
               
               --
               -- Get the first point on the line.
               --
               ST_Line_Interpolate_Point(geom, 0) AS geom

        FROM tiger_2012_edges_102008
        WHERE ST_GeometryType(geom) = 'ST_LineString'
        
        UNION
        
        SELECT statefp, countyfp, mtfcc, fullname,
               toadd AS add, zip, "offset",
               
               --
               -- Get the last point on the line.
               --
               ST_Line_Interpolate_Point(geom, 1) AS geom

        FROM tiger_2012_edges_102008
        WHERE ST_GeometryType(geom) = 'ST_LineString'

    ) AS p
);