I have a the NAICS schema loaded and all is working well. However I'm confused as how I'd query it in Postgrseql?
The table looks something like this,
=========NAICS_2012=========
naics | naics_parent | title
----------------------------
This is what I've wrote,
WITH RECURSIVE search_graph(naics12, naics12_parent, title, depth, path_info) AS (
SELECT naics12, naics12_parent, title, 1, array[naics12]
FROM naics.codes
WHERE naics12_parent IS NULL
UNION ALL
SELECT c.naics12, c.naics12_parent, c.title, sg.depth+1, sg.path_info||c.naics12
FROM naics.codes AS c, search_graph AS sg
WHERE c.naics12_parent = sg.naics12
)
SELECT * FROM search_graph ORDER BY path_info;
This produces output like this (be sure to scroll to the end)
naics12 | naics12_parent | title | depth | path_info
---------+----------------+------------------------------------------------------------------------------------------------------------------------+-------+----------------------------
11 | | Agriculture, Forestry, Fishing and Hunting | 1 | {11}
111 | 11 | Crop Production | 2 | {11,111}
1111 | 111 | Oilseed and Grain Farming | 3 | {11,111,1111}
11111 | 1111 | Soybean Farming | 4 | {11,111,1111,11111}
111110 | 11111 | Soybean Farming | 5 | {11,111,1111,11111,111110}
11112 | 1111 | Oilseed (except Soybean) Farming | 4 | {11,111,1111,11112}
111120 | 11112 | Oilseed (except Soybean) Farming | 5 | {11,111,1111,11112,111120}
11113 | 1111 | Dry Pea and Bean Farming | 4 | {11,111,1111,11113}
111130 | 11113 | Dry Pea and Bean Farming | 5 | {11,111,1111,11113,111130}
11114 | 1111 | Wheat Farming | 4 | {11,111,1111,11114}
111140 | 11114 | Wheat Farming | 5 | {11,111,1111,11114,111140}
11115 | 1111 | Corn Farming | 4 | {11,111,1111,11115}
111150 | 11115 | Corn Farming | 5 | {11,111,1111,11115,111150}
11116 | 1111 | Rice Farming | 4 | {11,111,1111,11116}
111160 | 11116 | Rice Farming | 5 | {11,111,1111,11116,111160}
11119 | 1111 | Other Grain Farming | 4 | {11,111,1111,11119}
111191 | 11119 | Oilseed and Grain Combination Farming | 5 | {11,111,1111,11119,111191}
111199 | 11119 | All Other Grain Farming | 5 | {11,111,1111,11119,111199}
1112 | 111 | Vegetable and Melon Farming | 3 | {11,111,1112}
11121 | 1112 | Vegetable and Melon Farming | 4 | {11,111,1112,11121}
111211 | 11121 | Potato Farming | 5 | {11,111,1112,11121,111211}
111219 | 11121 | Other Vegetable (except Potato) and Melon Farming | 5 | {11,111,1112,11121,111219}
1113 | 111 | Fruit and Tree Nut Farming | 3 | {11,111,1113}
11131 | 1113 | Orange Groves | 4 | {11,111,1113,11131}
111310 | 11131 | Orange Groves | 5 | {11,111,1113,11131,111310}
However, what I want is to see one tree, so I can write a query like,
SELECT * FROM search_graph WHERE naics12 = <foo>;
And, get a tableset back as if the table was only preloaded with the parents of the supplied NAICS12 code.
Any idea on how on further manipulate the data to get this kind of result out?