[postgis-devel] [postgis-users] TIGER Help/Clarification
Paragon Corporation
lr at pcorp.us
Tue Aug 4 15:37:42 PDT 2009
Bo,
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.
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.
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.
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)
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.
My advice to you which is going to sound kind of odd coming from a database
consultant is ----
1) Take a deep breath
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 addr and the Side field tells you if its L or R
(left of right side of street) and TLID is link to edges table.
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.
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.
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.
http://trac.osgeo.org/postgis/ticket/135
Perhaps Steve (if he is listening) can verify the status of it. I don't
have GIT client configured so can't tell.
Hope that helps,
Regina
_____
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Bo
Coughlin
Sent: Tuesday, August 04, 2009 2:15 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] TIGER Help/Clarification
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.
So, my high level schematic is roughly;
tiger.us --> tiger.state -->tiger.county(edges/point/lndmk/tabblock/ctract);
these will then all be fkey to --> address: which will be associated with
'contact'
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.
- bo
On Tue, Aug 4, 2009 at 1:44 PM, Stephen Woodbridge <woodbri at swoodbridge.com>
wrote:
Bo Coughlin wrote:
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
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.
What are your goals regarding the Tiger data?
What kind of questions will you need to get answered using this data?
What queries will you be making?
etc.
-Steve
On Tue, Aug 4, 2009 at 10:42 AM, Stephen Woodbridge <woodbri at swoodbridge.com
<mailto:woodbri at swoodbridge.com>> wrote:
Bo Coughlin wrote:
Using the TIGER shp files is a no brainer (feel free to
disagree). My question is on the relationship tables, and
setting up the DB in postGIS. What role do these tables play?
While it seems self explanatory in that the foreignkey
relationship etc. is outlined in a very nice diagram by the
census - I am still a little lost on how to integrate into the
DB - do I import those create the foreign key relationships
between them and then do the same with each shp. file?
This is definitely a noob question, but I am simply struggling
with the exact relationship of these tables with the shp files -
there doesn't appear to be a foreign key relationship between
the shp's - but there does between shp and relationship files.
Any help much appreciated. - thanks, bo
The Tiger shapefiles are a dump of the Census relational data model.
You can reload all the data you need and then use the relationships
described to create foreign keys or to create views or new tables.
There are a lot of files and tables to deal with, so do not expect
it to be trivial especially if you want to load all the data.
-Steve W
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
<mailto:postgis-users at postgis.refractions.net>
http://postgis.refractions.net/mailman/listinfo/postgis-users
------------------------------------------------------------------------
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20090804/829530fb/attachment.html>
More information about the postgis-devel
mailing list