<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.6001.18812"></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial>Bo,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial>I get the feeling you are a bit confused between the concept
of shape and tables or maybe not so excuse me if my explanation is not
necessary. </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial>Shape is an ESRI concept .shp files. when it gets in the
database, they become combined with the same named dbf and become a single
table with a geometry column.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial>So shp --> think the_geom (a postgis geometry object), dbf
thing other attribute fields. When in the database they become one table
so no need to relate them.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial>Some tiger tables don't have geometries so they are just DBF
files with no corresponding shp and when they get loaded they have no geometry
column (e.g those addr tables with a tlid to tie to an edge)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial>So your discussion about fkey with shape is rather confusing
to me; no such concept in the database. Relationships with geometries
tend to be fluid -- not with foreign keys -- just all about
proximity.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial>My advice to you which is going to sound kind of odd
coming from a database consultant is ---- </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial>1) Take a deep breath</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial>2) Forget about foreign keys for starters just focus on
what uniquely identifies each record - in case of tiger tlid and arid (TLID
is by far the most important key in tiger data) and what each table holds that
is of value or can be thrown out. Sounds like you may have already done
this. - address ranges as I recall are in a <STRONG>addr</STRONG>
and the Side field tells you if its L or R (left of right side of street) and
TLID is link to edges table.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial>3) You may find you may not even need foreign keys for your
purposes and you may not want to tie your contacts to a specific tiger address
since those things change with the wind. You may just want to use a
point geometry or something of that sort using linear
interpolation.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial>4) Do you want all your state edges in one table or separated
by county. When doing all state I tend to lump all the data in a state
table and forget about county tables, unless a state is sufficiently big where
breaking by county makes things a bit more bearable.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial>You might want to take a look at Stephen Frost's Tiger
Geocoder for ideas or for a way to geocode your contacts. I recall
he mentioning he has the data all as a single PostgreSQL dump already
too.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial><A
href="http://trac.osgeo.org/postgis/ticket/135">http://trac.osgeo.org/postgis/ticket/135</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial>Perhaps Steve (if he is listening) can verify the status
of it. I don't have GIT client configured so can't
tell.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial>Hope that helps,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial>Regina</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=484120222-04082009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV><FONT color=#0000ff size=2
face=Arial></FONT><BR>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Bo
Coughlin<BR><B>Sent:</B> Tuesday, August 04, 2009 2:15 PM<BR><B>To:</B> PostGIS
Users Discussion<BR><B>Subject:</B> Re: [postgis-users] TIGER
Help/Clarification<BR></FONT><BR></DIV>
<DIV></DIV>Well - that's just it.
I've got to associate specific addresses, with contact information to geography and then provide necessary geolytics at nearly all levels. Now the scope (in terms of amount of geography) is only 4 states at this time. This will continue to grow over time.
<DIV><BR></DIV>
<DIV>So, my high level schematic is roughly;</DIV>
<DIV><BR></DIV>
<DIV><A
href="http://tiger.us">tiger.us</A> --> tiger.state -->tiger.county(edges/point/lndmk/tabblock/ctract); these will then all be fkey to --> address: which will be associated with 'contact'</DIV>
<DIV><BR></DIV>
<DIV>The question I have is related to the relationship files & associating side of street etc.. with everything to ensure accuracy; and the how in terms of fkey in the DB - meaning is it a function of creating fkey with shape or using the feature tables as many2many etc. binding.</DIV>
<DIV><BR></DIV>
<DIV>- bo<BR><BR>
<DIV class=gmail_quote>On Tue, Aug 4, 2009 at 1:44 PM, Stephen Woodbridge <SPAN
dir=ltr><<A
href="mailto:woodbri@swoodbridge.com">woodbri@swoodbridge.com</A>></SPAN>
wrote:<BR>
<BLOCKQUOTE
style="BORDER-LEFT: #ccc 1px solid; MARGIN: 0px 0px 0px 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote>
<DIV class=im>Bo Coughlin wrote:<BR>
<BLOCKQUOTE
style="BORDER-LEFT: #ccc 1px solid; MARGIN: 0px 0px 0px 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote>That's for sure...huge amount. So, the feature
tables should be used as a reference? Meaning it should not actually be
loaded but used as a "reference" to make the necessary foreign key index
within the shape tables? - thanks very much - bo<BR></BLOCKQUOTE><BR></DIV>I
think there is no simple one answer fits all cases solution. You have to think
about what you want as an end result based on how you plan to use the data
once you have it loaded, then work from the source data files to that end
result or vica versa.<BR><BR>What are your goals regarding the Tiger
data?<BR>What kind of questions will you need to get answered using this
data?<BR>What queries will you be making?<BR>etc.<BR><BR>-Steve<BR><BR>
<BLOCKQUOTE
style="BORDER-LEFT: #ccc 1px solid; MARGIN: 0px 0px 0px 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote>
<DIV class=im>On Tue, Aug 4, 2009 at 10:42 AM, Stephen Woodbridge <<A
href="mailto:woodbri@swoodbridge.com"
target=_blank>woodbri@swoodbridge.com</A> <mailto:<A
href="mailto:woodbri@swoodbridge.com"
target=_blank>woodbri@swoodbridge.com</A>>> wrote:<BR><BR>
Bo Coughlin wrote:<BR><BR> Using the TIGER
shp files is a no brainer (feel free to<BR>
disagree). My question is on the relationship tables, and<BR>
setting up the DB in postGIS. What role do these
tables play?<BR> While it seems self explanatory
in that the foreignkey<BR> relationship etc. is
outlined in a very nice diagram by the<BR> census
- I am still a little lost on how to integrate into the<BR>
DB - do I import those create the foreign key
relationships<BR> between them and then do the
same with each shp. file?<BR> This is definitely a
noob question, but I am simply struggling<BR> with
the exact relationship of these tables with the shp files -<BR>
there doesn't appear to be a foreign key relationship
between<BR> the shp's - but there does between shp
and relationship files.<BR> Any help much
appreciated. - thanks, bo<BR><BR><BR> The Tiger shapefiles are a
dump of the Census relational data model.<BR> You can reload all
the data you need and then use the relationships<BR> described
to create foreign keys or to create views or new tables.<BR><BR>
There are a lot of files and tables to deal with, so do not
expect<BR> it to be trivial especially if you want to load all
the data.<BR><BR> -Steve W<BR>
_______________________________________________<BR>
postgis-users mailing list<BR> <A
href="mailto:postgis-users@postgis.refractions.net"
target=_blank>postgis-users@postgis.refractions.net</A><BR></DIV>
<mailto:<A href="mailto:postgis-users@postgis.refractions.net"
target=_blank>postgis-users@postgis.refractions.net</A>>
<DIV class=im><BR> <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR><BR><BR></DIV>------------------------------------------------------------------------
<DIV
class=im><BR><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A href="mailto:postgis-users@postgis.refractions.net"
target=_blank>postgis-users@postgis.refractions.net</A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></DIV></BLOCKQUOTE>
<DIV>
<DIV></DIV>
<DIV
class=h5><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A href="mailto:postgis-users@postgis.refractions.net"
target=_blank>postgis-users@postgis.refractions.net</A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR></DIV></DIV></BLOCKQUOTE></DIV><BR></DIV></BODY></HTML>