On Tue, Apr 6, 2010 at 2:34 PM, Little, Douglas <douglas.lit...@orbitz.com> wrote: > Hey Lee, > > I’m on dm-disc...@yahoogroups.com
Thanks for the pointer. I'm looking at their archives now. > Ie a row is a point in time, or average for a period of time. Are the > Numbers actual, or estimates? To be useful, you’ll want to be able to > insert new records while retaining history and projections and easily be > able to build forecasts. Yes, each row represents employment for a given time period (usually year), geography (county, ZIP code, etc.), and NAICS code. I'm planning to partition by year, particularly since the agency we get data from releases preliminary data first, final data later, so that I can easily drop or disinherit preliminary data. Numbers are actual employment based on establishment reporting. > I suppose you can get data at the 61, 611, and 6111 level. You want to be > able to accurately sum where code like ‘61%’ We would never do a sum like 61%, because that would double or triple count data all the way down the hierarchy. The employment for NAICS 61 at a particular geography and time is the same as the sum of all 3-digit children (61#) which is also the same as the sum of all 4-digit grandchildren (61##), etc. On Tue, Apr 6, 2010 at 6:04 PM, Michael Glaesemann <g...@seespotcode.net> wrote: > Another is nested sets which performs quite nicely for loads which are more > read than write (which I suspect is the case here). You are right that we will be reading more than writing, but the SQL looks complicated, and I don't have the skills to build the kind of application layer that would allow our users to work with data stored as a nested set. >> The problem is that because of nondisclosure rules, the >> data is sometimes censored at the more specific level. > > I don't know if this is per-user or per-category or what, but it may be > something you store separately from the main table. Suppression is per-category. All users at our org will have access to the same data. More info below. On Tue, Apr 6, 2010 at 8:23 PM, Steve Crawford <scrawf...@pinpointresearch.com> wrote: > reports. The CTE/recursive-query features in 8.4 are great for this. But in > the case you have described, the number of levels is well defined as is the > type of information associated with each level. The number of levels is well-defined, but we won't have data for all time-periods/geographies/NAICS levels. Because of nondisclosure rules, we might have 4-digit data at the county level but only 2-digit data at the ZIP code, and not have full coverage for all years. > One question that might impact this is the coding of your source data. Is it > all full 5-digit coding or are some records coded at a high level of detail > and others only to the top-level? We actually don't usually get below 4-digit, but the answer is the latter: some data is available at a detailed level and some data only at the top level. > What do you mean by censored? Is the data supplied to you pre-aggregated to > some level and censored to preserve confidentiality or are do you have the > record-level source data and the responsibility to suppress data in your > reports? Is the data suppression ad-hoc (i.e. someone comes to you and says The state agency gives us pre-aggregated data not microdata. The exact suppression rule is we don't get any data for a cell with fewer than 3 firms or where one firm has >80% of the total employment. Thus, the smaller the cells (smaller geography, more specific NAICS categorization), the more likely we run into empty cells. On Wed, Apr 7, 2010 at 2:17 AM, Scott Marlowe <scott.marl...@gmail.com> wrote: > Since it's an identifier and not really a numeric per se, I'd store it > as text. I mean it could as easily be a 5 character alpha code as 5 > character number code. Yes, already decided to store as text. Thanks for the substring index suggestion. On Wed, Apr 7, 2010 at 4:26 AM, Sergey Konoplev <gray...@gmail.com> wrote: > Haven't you thought about ltree contrib? From the description of > ltree: "This module implements a data type ltree for representing > labels of data stored in a hierarchical tree-like structure". > > http://www.postgresql.org/docs/8.4/interactive/ltree.html No I was not familiar with this, and it looks really promising. Thanks for the pointer. It's a little repetitive, but it looks like the path should be stored as 61.611.6111. Assuming I define a column naics as ltree, being able to query WHERE nlevel(naics) = [2|3|4] will work nicely, and with the right views, my users never have to see it. Thanks again to everyone who replied. Any further remarks, questions, comments are welcome. --Lee -- Lee Hachadoorian PhD Student, Geography Program in Earth & Environmental Sciences CUNY Graduate Center -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
