<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
<META NAME="GENERATOR" CONTENT="GtkHTML/3.8.0">
</HEAD>
<BODY>
Hello All,<BR>
<BR>
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.<BR>
<BR>
I could also really use some advice on how to make these functions below the most efficient.
<PRE>
create or replace function <A HREF="http://localhost/%7Emark/phpPgAdmin-4.0.1/redirect.php?server=%3A5432&database=tiger&schema=public&function_oid=17039&subject=function&function=getdemographicsbyaddress+%28text%2C+numeric%29">getdemographicsbyaddress (text, numeric)</A> 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 <A HREF="http://localhost/%7Emark/phpPgAdmin-4.0.1/redirect.php?server=%3A5432&database=tiger&schema=public&function_oid=17039&subject=function&function=getdemographicsbyaddress+%28text%2C+numeric%29">getdemographicsbyaddress (varchar, geometry,</A> geometry<A HREF="http://localhost/%7Emark/phpPgAdmin-4.0.1/redirect.php?server=%3A5432&database=tiger&schema=public&function_oid=17039&subject=function&function=getdemographicsbyaddress+%28text%2C+numeric%29">)</A> 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)
</PRE>
If any more info is needed I'm happy to share.<BR>
<BR>
Thanks!<BR>
<BR>
<TABLE CELLSPACING="0" CELLPADDING="0" WIDTH="100%">
<TR>
<TD>
Mark Thomas<BR>
<I>Sun Certified Java 5 Programmer</I><BR>
<I>Sun Certified Java 2 Programmer</I><BR>
<I>Sun Certified Web Component Developer</I>
</TD>
</TR>
</TABLE>
</BODY>
</HTML>