[postgis-users] unexpected insert behavior
Kirk R. Wythers
kwythers at umn.edu
Wed Apr 12 07:24:47 PDT 2006
Hi all,
Sorry for the novel length question, but I need a hand figuring out
what I missed in database design and the only way I can think to ask
the question is to provide some background. Here goes...
Since the behavior on an INSERT statement is unexpected, I suspect I
set up the model incorrectly.
What I intended was to create a model where a combination of columns
would represent a unique 40 acer grid cell in a township range style
map. I thought I had done that, confirmed by the following SELECT
statement which returned the expected single grid cell:
timber_forties=# SELECT mn_pls_grid.gid, mn_pls_grid.township,
mn_pls_grid.range, rdir.rdir_shortname, mn_pls_grid.section,
forties.forty_name FROM mn_pls_grid, rdir, forties WHERE
mn_pls_grid.rdir_id = rdir.rdir_id AND forties.forty_id =
mn_pls_grid.forty_id AND mn_pls_grid.township = 61 AND
mn_pls_grid.range = 9 AND rdir_shortname = 'W' AND
mn_pls_grid.section = 6 AND forties.forty_name =
'NWNW';
gid | township | range |
rdir_shortname | section | forty_name
--------+----------+-------+----------------+---------+------------
596410 | 61 | 9 | W | 6 | NWNW
(1 row)
However, when I started inserting data (timber volume data) into the
database, I ran into some odd behavior. In this case I started with
an insert statement that was to insert NULL values into a grid cell
that contained no data. In this case use SELECT to specify a specific
grid_id (the gid from above which would refer to the above grid
cell), tt_id (which should insert ids for all 16 land cover types),
1920 (for the year), tt_id (use a sequence to increment a unique id
value)
timber_forties=# INSERT INTO timber_volume (grid_id, tt_id, year,
tv_id) select 596410, tt_id, 1920, nextval('timber_volume_seq') from
timber_type;
INSERT 0 16
Again, this worked as expected, inserting all 16 timber species/land
cover types, and NULL values in the volume columns (cords, mbm,
poles, and posts) as shown by the following SELECT:
timber_forties=# SELECT mn_pls_grid.gid, mn_pls_grid.township,
mn_pls_grid.range, rdir.rdir_shortname, mn_pls_grid.section,
forties.forty_name, timber_type.tt_name, timber_volume.cords,
timber_volume.mbm, timber_volume.poles, timber_volume.posts FROM
mn_pls_grid, rdir, forties, timber_volume, timber_type WHERE
mn_pls_grid.rdir_id = rdir.rdir_id AND forties.forty_id =
mn_pls_grid.forty_id AND timber_volume.grid_id = mn_pls_grid.gid AND
timber_type.tt_id = timber_volume.tt_id AND mn_pls_grid.township = 61
AND mn_pls_grid.range = 9 AND rdir_shortname = 'W' AND
mn_pls_grid.section = 6;
gid | township | range | rdir_shortname | section | forty_name
| tt_name | cords | mbm | poles | posts
--------+----------+-------+----------------+---------+------------
+-----------------+-------+-----+-------+-------
596410 | 61 | 9 | W | 6 | NWNW |
brush | | | |
596410 | 61 | 9 | W | 6 | NWNW |
muskeg | | | |
596410 | 61 | 9 | W | 6 | NWNW |
birch | | | |
596410 | 61 | 9 | W | 6 | NWNW |
cedar | | | |
596410 | 61 | 9 | W | 6 | NWNW |
jack_pine | | | |
596410 | 61 | 9 | W | 6 | NWNW |
spruce | | | |
596410 | 61 | 9 | W | 6 | NWNW |
spruce_tamarack | | | |
596410 | 61 | 9 | W | 6 | NWNW |
norway_pine | | | |
596410 | 61 | 9 | W | 6 | NWNW |
white_pine | | | |
596410 | 61 | 9 | W | 6 | NWNW |
aspen | | | |
596410 | 61 | 9 | W | 6 | NWNW |
grassland | | | |
596410 | 61 | 9 | W | 6 | NWNW |
cultivated | | | |
596410 | 61 | 9 | W | 6 | NWNW |
swamp | | | |
596410 | 61 | 9 | W | 6 | NWNW |
cutover | | | |
596410 | 61 | 9 | W | 6 | NWNW |
black_ash | | | |
596410 | 61 | 9 | W | 6 | NWNW |
unknown | | | |
(16 rows)
At this point I am quite proud of myself and grateful to the good
folks who helped me get this far. Now however, is where the
unexpected behavior begins. I then tried using a variation on the
above INSERT statement to insert actual timber volume data for a
single land cover type (in this case 21 cords of jack pine into the
grid cell township 61 range 9W section 6 forty NENW).
INSERT INTO timber_volume (grid_id, tt_id, year, cords, tv_id) SELECT
596524, 5, 1920, 21, NEXTVAL('timber_volume_seq') FROM timber_volume;
To my mind this should insert a single row of tt_name = jack_pine and
cords = 21 into the cell with grid_id = 596524. However the below
SELECT statement shows that 16 rows were inserted.
timber_forties=# SELECT mn_pls_grid.gid, mn_pls_grid.township,
mn_pls_grid.range, rdir.rdir_shortname, mn_pls_grid.section,
forties.forty_name, timber_type.tt_name, timber_volume.cords,
timber_volume.mbm, timber_volume.poles, timber_volume.posts FROM
mn_pls_grid, rdir, forties, timber_volume, timber_type WHERE
mn_pls_grid.rdir_id = rdir.rdir_id AND forties.forty_id =
mn_pls_grid.forty_id AND timber_volume.grid_id = mn_pls_grid.gid AND
timber_type.tt_id = timber_volume.tt_id AND mn_pls_grid.township = 61
AND mn_pls_grid.range = 9 AND rdir_shortname = 'W' AND
mn_pls_grid.section = 6;
gid | township | range | rdir_shortname | section | forty_name
| tt_name | cords | mbm | poles | posts
--------+----------+-------+----------------+---------+------------
+-----------------+-------+-----+-------+-------
596410 | 61 | 9 | W | 6 | NWNW |
brush | | | |
596410 | 61 | 9 | W | 6 | NWNW |
muskeg | | | |
596410 | 61 | 9 | W | 6 | NWNW |
birch | | | |
596410 | 61 | 9 | W | 6 | NWNW |
cedar | | | |
596410 | 61 | 9 | W | 6 | NWNW |
jack_pine | | | |
596410 | 61 | 9 | W | 6 | NWNW |
spruce | | | |
596410 | 61 | 9 | W | 6 | NWNW |
spruce_tamarack | | | |
596410 | 61 | 9 | W | 6 | NWNW |
norway_pine | | | |
596410 | 61 | 9 | W | 6 | NWNW |
white_pine | | | |
596410 | 61 | 9 | W | 6 | NWNW |
aspen | | | |
596410 | 61 | 9 | W | 6 | NWNW |
grassland | | | |
596410 | 61 | 9 | W | 6 | NWNW |
cultivated | | | |
596410 | 61 | 9 | W | 6 | NWNW |
swamp | | | |
596410 | 61 | 9 | W | 6 | NWNW |
cutover | | | |
596410 | 61 | 9 | W | 6 | NWNW |
black_ash | | | |
596410 | 61 | 9 | W | 6 | NWNW |
unknown | | | |
596524 | 61 | 9 | W | 6 | NENW |
jack_pine | 21 | | |
596524 | 61 | 9 | W | 6 | NENW |
jack_pine | 21 | | |
596524 | 61 | 9 | W | 6 | NENW |
jack_pine | 21 | | |
596524 | 61 | 9 | W | 6 | NENW |
jack_pine | 21 | | |
596524 | 61 | 9 | W | 6 | NENW |
jack_pine | 21 | | |
596524 | 61 | 9 | W | 6 | NENW |
jack_pine | 21 | | |
596524 | 61 | 9 | W | 6 | NENW |
jack_pine | 21 | | |
596524 | 61 | 9 | W | 6 | NENW |
jack_pine | 21 | | |
596524 | 61 | 9 | W | 6 | NENW |
jack_pine | 21 | | |
596524 | 61 | 9 | W | 6 | NENW |
jack_pine | 21 | | |
596524 | 61 | 9 | W | 6 | NENW |
jack_pine | 21 | | |
596524 | 61 | 9 | W | 6 | NENW |
jack_pine | 21 | | |
596524 | 61 | 9 | W | 6 | NENW |
jack_pine | 21 | | |
596524 | 61 | 9 | W | 6 | NENW |
jack_pine | 21 | | |
596524 | 61 | 9 | W | 6 | NENW |
jack_pine | 21 | | |
596524 | 61 | 9 | W | 6 | NENW |
jack_pine | 21 | | |
(32 rows)
I can confirm again that the grid_id for the above cell is 596524
with the statement:
timber_forties=# SELECT mn_pls_grid.gid, mn_pls_grid.township,
mn_pls_grid.range, rdir.rdir_shortname, mn_pls_grid.section,
forties.forty_name FROM mn_pls_grid, rdir, forties WHERE
mn_pls_grid.rdir_id = rdir.rdir_id AND forties.forty_id =
mn_pls_grid.forty_id AND mn_pls_grid.township = 61 AND
mn_pls_grid.range = 9 AND rdir_shortname = 'W' AND
mn_pls_grid.section = 6 AND forties.forty_name = 'NENW';
gid | township | range | rdir_shortname | section | forty_name
--------+----------+-------+----------------+---------+------------
596524 | 61 | 9 | W | 6 | NENW
(1 row)
Can anyone see what I have done wrong that allowed caused 16 rows of
jack pine cords to be inserted when I was expected only 1 row?
Thanks in advance,
Kirk
More information about the postgis-users
mailing list