[postgis-users] help with functions/looking to buy data

[Mark Thomas]

Hello All,

I am looking for anyone (company or individual) who has data consisting
of zip codes, carrier routes, and the geometries for each carrier route.
I currently have the USPS TIGER/ZIP+4 product to match USPS carrier
routes to TIGER/line records by TLID, but alas, it is missing over 15000
zip codes worth of data.  What I am doing is allowing the user to input
an address and one or more radii in miles and I am returning the
demographics associated with the resulting region of interest.  I have
all the geocoding complete and all the demographics at the carrier route
level complete, but what I'm missing is the in-between step of
determining which carrier routes apply to the ROI by using PostGIS
functions on the carrier route geometries.  Of course this is only a
problem for the missing zip codes all others work great.  I am also open
to other forms of calculating these values such as using census
block/tract geometries to match against ROI, percetange of ZCTA covered
by ROI, etc. and if anyone has any advice or suggestions they are warmly
welcomed.

I could also really use some advice on how to make these functions below
the most efficient.  

create or replace function getdemographicsbyaddress (text, numeric) returns setof demographics as $$
declare
    g geometry;
    e geometry;
    rec record;
    d demographics;
begin
    --returns polygon resulting from buffing point geometry result of geocode function by $2 miles
    select getBufferedGeometry(geocode($1),$2) into g;
    select Envelope(g) into e;
    for rec in (
        select distinct(zipcode)
        from usps_tiger_zipplus4
        where geom && e
            and Intersects(geom,g)
        order by zipcode
    ) loop
        select * from getDemographicsByAddress(rec.zipcode,g,e) into d;
        return next d;
    end loop;
    return;
end;
$$ language plpgsql;

create or replace function getdemographicsbyaddress (varchar, geometry, geometry) returns demographics as $$
declare
    d demographics;
begin
    select * from getDemographicsByZip($1) into d;
    select sum(homes), sum(apts), sum(businesses)
        into d.homes, d.apts, d.businesses
    from carrier_route_address_totals
    where zipcode = $1
        and carrierroute in (
            select distinct(carrierroute)
            from usps_tiger_zipplus4
            where geom && $3
                and Intersects(geom,$2)
                and zipcode = $1
        );
    return d;
end;
$$ language plpgsql;

        Table "public.usps_tiger_zipplus4"
    Column    |         Type          | Modifiers
--------------+-----------------------+-----------
 zipcode      | character varying(5)  |
 tlid         | character varying(10) |
 carrierroute | character varying(4)  |
 geom         | geometry              | not null
Indexes:
    "idx_geom_usps_tiger_zipplus4" gist (geom)
Check constraints:
    "enforce_dims_wkb_geometry" CHECK (ndims(geom) = 2)
    "enforce_geotype_wkb_geometry" CHECK (geometrytype(geom) = 'POLYGON'::text OR geom IS NULL)
    "enforce_srid_wkb_geometry" CHECK (srid(geom) = 32767)

   Table "public.carrier_route_address_totals"
    Column    |         Type         | Modifiers
--------------+----------------------+-----------
 zipcode      | character varying(5) | not null
 carrierroute | character varying(4) | not null
 homes        | integer              |
 apts         | integer              |
 businesses   | integer              |
Indexes:
    "carrier_route_address_totals_zipcode" btree (zipcode)


If any more info is needed I'm happy to share.

Thanks!

Mark Thomas
Sun Certified Java 5 Programmer
Sun Certified Java 2 Programmer
Sun Certified Web Component Developer
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20060411/0540656c/attachment.html>