[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