Sheara Cohen
2011-07-08 18:45:46 UTC
Hi All -
I'm running into a problem adding my PostGIS files as query layers in
ArcMap. I can see the layer and it's polygons, but the identify tool
doesn't detect it and when I try to open the attribute table I get this
message: "Error reading OID form table. Reading rows has been stopped.
Check that the datasource is valid. OID mapped column has null value."
This is happening in two different situations. I'm going to share some
details in case it helps any of you all help me.
In the first case, I had a Census block ID field (STFID) that had unique
values and no nulls as well as a duplicate field (STFID_1) that was a
result of a previous join operation. When I removed this second field, I
could use the file as a query layer with no problem. Both fields were
character varying and it didn't matter if any field was set as a primary
key or not. The conclusion I drew - correctly or not - in this situation
was that you can't have two fields that ArcMap might interpret as an ID
field.
But then...
I had another situation where a PostGIS Census blockgroup file can be
used as a query layer with no problem, but once I join fields from a
couple other tables to it, it returns the errors I described above. Both
files have an ID field (BKGPIDFP00) that is character varying and has
unique values and no nulls.
In the second file, the four added join fields are also character
varying ID fields that identify the nearest neighbor for *some* of the
records. The values are IDs akin to those in the original ID field, but
only some records have a value at all and the values may occur more than
once (not unique) since I only needed to find the nearest neighbor for a
select set of records and some of those records may share the nearest
neighbor.
If I select all the fields except these last four fields (rather than
saying "select *") in the add query layer dialog, the layer functions
with no problems.
I'm including, below, the script I used to join these four fields onto
the original file in case it helps. Does anyone know what is going on
here? And what my alternatives might be?
Thanks so much for any help.
Best,
Sheara
CREATE TABLE public.census_blockgroups_sf3_ctpp_with_near_feature_IDs
as
SELECT
wkb_geometry,
o.bkgpidfp00,
sf3_totalpop_p001001, sf3_totalhu_h030001, sf3_totalocchu_h044001,
sf3_totalhh_p013001,
ctpp_workers_all_industries, ctpp_workers_all_commutemodes,
ctpp_workers_all_vehicleamounts, ctpp_workers_all_commutetimes,
h018001,
h030002, h030003, h030004, h030005, h030006, h030007, h030008, h030009,
h030010, h030011,
h038003, h038004, h038005, h038006, h038007, h038008, h038009, h038010,
h038011, h038012, h038013, h038014, h038015,
h044002, h044003, h044004, h044005, h044006, h044007, h044008, h044009,
h044010, h044011, h044012, h044013, h044014, h044015,
h046001,
p007003, p007004, p007005, p007006, p007007, p007008, p007009, p007010,
p008002, p008003, p008004, p008005, p008006, p008007, p008008, p008009,
p008010, p008011, p008012, p008013, p008014, p008015, p008016, p008017,
p008018, p008019, p008020, p008021, p008022, p008023, p008024, p008025,
p008026, p008027, p008028, p008029, p008030, p008031, p008032, p008033,
p008034, p008035, p008036, p008037, p008038, p008039, p008040, p008041,
p008042, p008043, p008044, p008045, p008046, p008047, p008048, p008049,
p008050, p008051, p008052, p008053, p008054, p008055, p008056, p008057,
p008058, p008059, p008060, p008061, p008062, p008063, p008064, p008065,
p008066, p008067, p008068, p008069, p008070, p008071, p008072, p008073,
p008074, p008075, p008076, p008077, p008078, p008079, p009005, p009006,
p009018, p009021,
p013003, p013004, p013005, p013006, p013007, p013008, p013009, p013010,
p013011, p013012, p013013, p013014, p013015, p013016, p013017, p013018,
p013019,
p032003, p032004, p032005, p032006, p032007, p032008, p032009, p032010,
p032011, p032012, p032013,
p036001, p036003, p036006, p036009, p036012, p036015, p036018, p036021,
p036026, p036029, p036032, p036035, p036038, p036041, p036044,
p037001, p037003, p037004, p037005, p037006, p037007, p037008, p037009,
p037010, p037011, p037012, p037013, p037014, p037015, p037016, p037017,
p037018, p037019, p037020, p037021, p037022, p037023, p037024, p037025,
p037026, p037027, p037028, p037029, p037030, p037031, p037032, p037033,
p037034, p037035,
p043001, p043004, p043005, p043006, p043007, p043008, p043009, p043010,
p043011, p043012, p043013, p043014, p043015,
p049001, p049004, p049005, p049006, p049007, p049008, p049009, p049010,
p049011, p049012, p049013, p049014, p049015, p049016, p049017, p049018,
p049019, p049020, p049021, p049022, p049023, p049024, p049025, p049026,
p049027, p049028, p049029, p049030, p049031, p049032, p049033, p049034,
p049035, p049036, p049037, p049038, p049039, p049040, p049041, p049042,
p049043, p049044, p049045, p049046, p049047, p049048, p049049, p049050,
p049051, p049052, p049053, p049054, p049055,
p050005, p050006, p050007, p050008, p050009, p050010, p050011, p050012,
p050013, p050014, p050015, p050016, p050017,p050018, p050019, p050020,
p050021, p050022, p050023, p050024, p050025, p050026, p050027, p050028,
p050029, p050030, p050031, p050032, p050033, p050034, p050035, p050036,
p050037, p050038, p050039, p050040, p050041, p050042, p050043, p050044,
p050045, p050046, p050047, p050048, p050049, p050050, p050051, p050052,
p050053, p050054, p050055, p050056, p050057, p050058, p050059, p050060,
p050061, p050062, p050063, p050064, p050065, p050066, p050067, p050068,
p050069, p050070, p050071, p050072, p050073, p050074, p050075, p050076,
p050077, p050078, p050079, p050080, p050081, p050082, p050083, p050084,
p050085, p050086, p050087, p050088,
p052002, p052003, p052004, p052005, p052006, p052007, p052008, p052009,
p052010, p052011, p052012, p052013, p052014, p052015, p052016, p052017,
p053001,
p054001,
means_dralone, means_taxi, means_mcycle, means_carpool, means_bus_tb,
means_trolley, means_subway, means_railrd, means_ferry, means_bicycle,
means_walk, means_athome, means_total, means_other,
commute_da, commute_taxi, commute_mcycl, commute_cp2, commute_cp3,
commute_cp4, commute_cp56, commute_cp7p, commute_bus, commute_troll,
commute_sub, commute_rr, commute_ferry, commute_bike, commute_walk,
commute_athom, commute_other, commute_vehicles,
ind_retail, ind_artfood, ind_inform, ind_fire, ind_educhss, ind_pubadm,
ind_trans, ind_whole, ind_manuf, ind_constr, ind_extract, ind_other,
ind_armed,
time_0004, time_0509, time_1014, time_1519, time_2024, time_2529,
time_3034, time_3539, time_4044, time_4549, time_5054, time_5559,
time_6074, time_7589, time_9099,
vehicles_zero, vehicles_one, vehicles_two, vehicles_three,
vehicles_fourp,
near_bkgpidfp00_pop as bg_pop_imputed_near_id,
near_bkgpidfp00_hu as bg_hu_imputed_near_id,
near_bkgpidfp00_hh as bg_hh_imputed_near_id,
near_bkgpidfp00_emp as bg_emp_imputed_near_id
FROM census_blockgroups_sf3_ctpp as o
LEFT OUTER JOIN census_blockgroups_pop_near_features_table ON
(o.bkgpidfp00 = census_blockgroups_pop_near_features_table.bkgpidfp00)
LEFT OUTER JOIN census_blockgroups_hu_near_features_table ON
(o.bkgpidfp00 = census_blockgroups_hu_near_features_table.bkgpidfp00)
LEFT OUTER JOIN census_blockgroups_hh_near_features_table ON
(o.bkgpidfp00 = census_blockgroups_hh_near_features_table.bkgpidfp00)
LEFT OUTER JOIN census_blockgroups_emp_near_features_table ON
(o.bkgpidfp00 = census_blockgroups_emp_near_features_table.bkgpidfp00);
Sheara Cohen
Planner
C A L T H O R P E A S S O C I A T E S
2095 ROSE STREET, SUITE 201, BERKELEY, CALIFORNIA, 94709 USA
510 809-1165 (direct) | 510-548-6800 x35 (main) | 510 548-6848 (fax)
***@calthorpe.com <mailto:***@calthorpe.com> | www.calthorpe.com
I'm running into a problem adding my PostGIS files as query layers in
ArcMap. I can see the layer and it's polygons, but the identify tool
doesn't detect it and when I try to open the attribute table I get this
message: "Error reading OID form table. Reading rows has been stopped.
Check that the datasource is valid. OID mapped column has null value."
This is happening in two different situations. I'm going to share some
details in case it helps any of you all help me.
In the first case, I had a Census block ID field (STFID) that had unique
values and no nulls as well as a duplicate field (STFID_1) that was a
result of a previous join operation. When I removed this second field, I
could use the file as a query layer with no problem. Both fields were
character varying and it didn't matter if any field was set as a primary
key or not. The conclusion I drew - correctly or not - in this situation
was that you can't have two fields that ArcMap might interpret as an ID
field.
But then...
I had another situation where a PostGIS Census blockgroup file can be
used as a query layer with no problem, but once I join fields from a
couple other tables to it, it returns the errors I described above. Both
files have an ID field (BKGPIDFP00) that is character varying and has
unique values and no nulls.
In the second file, the four added join fields are also character
varying ID fields that identify the nearest neighbor for *some* of the
records. The values are IDs akin to those in the original ID field, but
only some records have a value at all and the values may occur more than
once (not unique) since I only needed to find the nearest neighbor for a
select set of records and some of those records may share the nearest
neighbor.
If I select all the fields except these last four fields (rather than
saying "select *") in the add query layer dialog, the layer functions
with no problems.
I'm including, below, the script I used to join these four fields onto
the original file in case it helps. Does anyone know what is going on
here? And what my alternatives might be?
Thanks so much for any help.
Best,
Sheara
CREATE TABLE public.census_blockgroups_sf3_ctpp_with_near_feature_IDs
as
SELECT
wkb_geometry,
o.bkgpidfp00,
sf3_totalpop_p001001, sf3_totalhu_h030001, sf3_totalocchu_h044001,
sf3_totalhh_p013001,
ctpp_workers_all_industries, ctpp_workers_all_commutemodes,
ctpp_workers_all_vehicleamounts, ctpp_workers_all_commutetimes,
h018001,
h030002, h030003, h030004, h030005, h030006, h030007, h030008, h030009,
h030010, h030011,
h038003, h038004, h038005, h038006, h038007, h038008, h038009, h038010,
h038011, h038012, h038013, h038014, h038015,
h044002, h044003, h044004, h044005, h044006, h044007, h044008, h044009,
h044010, h044011, h044012, h044013, h044014, h044015,
h046001,
p007003, p007004, p007005, p007006, p007007, p007008, p007009, p007010,
p008002, p008003, p008004, p008005, p008006, p008007, p008008, p008009,
p008010, p008011, p008012, p008013, p008014, p008015, p008016, p008017,
p008018, p008019, p008020, p008021, p008022, p008023, p008024, p008025,
p008026, p008027, p008028, p008029, p008030, p008031, p008032, p008033,
p008034, p008035, p008036, p008037, p008038, p008039, p008040, p008041,
p008042, p008043, p008044, p008045, p008046, p008047, p008048, p008049,
p008050, p008051, p008052, p008053, p008054, p008055, p008056, p008057,
p008058, p008059, p008060, p008061, p008062, p008063, p008064, p008065,
p008066, p008067, p008068, p008069, p008070, p008071, p008072, p008073,
p008074, p008075, p008076, p008077, p008078, p008079, p009005, p009006,
p009018, p009021,
p013003, p013004, p013005, p013006, p013007, p013008, p013009, p013010,
p013011, p013012, p013013, p013014, p013015, p013016, p013017, p013018,
p013019,
p032003, p032004, p032005, p032006, p032007, p032008, p032009, p032010,
p032011, p032012, p032013,
p036001, p036003, p036006, p036009, p036012, p036015, p036018, p036021,
p036026, p036029, p036032, p036035, p036038, p036041, p036044,
p037001, p037003, p037004, p037005, p037006, p037007, p037008, p037009,
p037010, p037011, p037012, p037013, p037014, p037015, p037016, p037017,
p037018, p037019, p037020, p037021, p037022, p037023, p037024, p037025,
p037026, p037027, p037028, p037029, p037030, p037031, p037032, p037033,
p037034, p037035,
p043001, p043004, p043005, p043006, p043007, p043008, p043009, p043010,
p043011, p043012, p043013, p043014, p043015,
p049001, p049004, p049005, p049006, p049007, p049008, p049009, p049010,
p049011, p049012, p049013, p049014, p049015, p049016, p049017, p049018,
p049019, p049020, p049021, p049022, p049023, p049024, p049025, p049026,
p049027, p049028, p049029, p049030, p049031, p049032, p049033, p049034,
p049035, p049036, p049037, p049038, p049039, p049040, p049041, p049042,
p049043, p049044, p049045, p049046, p049047, p049048, p049049, p049050,
p049051, p049052, p049053, p049054, p049055,
p050005, p050006, p050007, p050008, p050009, p050010, p050011, p050012,
p050013, p050014, p050015, p050016, p050017,p050018, p050019, p050020,
p050021, p050022, p050023, p050024, p050025, p050026, p050027, p050028,
p050029, p050030, p050031, p050032, p050033, p050034, p050035, p050036,
p050037, p050038, p050039, p050040, p050041, p050042, p050043, p050044,
p050045, p050046, p050047, p050048, p050049, p050050, p050051, p050052,
p050053, p050054, p050055, p050056, p050057, p050058, p050059, p050060,
p050061, p050062, p050063, p050064, p050065, p050066, p050067, p050068,
p050069, p050070, p050071, p050072, p050073, p050074, p050075, p050076,
p050077, p050078, p050079, p050080, p050081, p050082, p050083, p050084,
p050085, p050086, p050087, p050088,
p052002, p052003, p052004, p052005, p052006, p052007, p052008, p052009,
p052010, p052011, p052012, p052013, p052014, p052015, p052016, p052017,
p053001,
p054001,
means_dralone, means_taxi, means_mcycle, means_carpool, means_bus_tb,
means_trolley, means_subway, means_railrd, means_ferry, means_bicycle,
means_walk, means_athome, means_total, means_other,
commute_da, commute_taxi, commute_mcycl, commute_cp2, commute_cp3,
commute_cp4, commute_cp56, commute_cp7p, commute_bus, commute_troll,
commute_sub, commute_rr, commute_ferry, commute_bike, commute_walk,
commute_athom, commute_other, commute_vehicles,
ind_retail, ind_artfood, ind_inform, ind_fire, ind_educhss, ind_pubadm,
ind_trans, ind_whole, ind_manuf, ind_constr, ind_extract, ind_other,
ind_armed,
time_0004, time_0509, time_1014, time_1519, time_2024, time_2529,
time_3034, time_3539, time_4044, time_4549, time_5054, time_5559,
time_6074, time_7589, time_9099,
vehicles_zero, vehicles_one, vehicles_two, vehicles_three,
vehicles_fourp,
near_bkgpidfp00_pop as bg_pop_imputed_near_id,
near_bkgpidfp00_hu as bg_hu_imputed_near_id,
near_bkgpidfp00_hh as bg_hh_imputed_near_id,
near_bkgpidfp00_emp as bg_emp_imputed_near_id
FROM census_blockgroups_sf3_ctpp as o
LEFT OUTER JOIN census_blockgroups_pop_near_features_table ON
(o.bkgpidfp00 = census_blockgroups_pop_near_features_table.bkgpidfp00)
LEFT OUTER JOIN census_blockgroups_hu_near_features_table ON
(o.bkgpidfp00 = census_blockgroups_hu_near_features_table.bkgpidfp00)
LEFT OUTER JOIN census_blockgroups_hh_near_features_table ON
(o.bkgpidfp00 = census_blockgroups_hh_near_features_table.bkgpidfp00)
LEFT OUTER JOIN census_blockgroups_emp_near_features_table ON
(o.bkgpidfp00 = census_blockgroups_emp_near_features_table.bkgpidfp00);
Sheara Cohen
Planner
C A L T H O R P E A S S O C I A T E S
2095 ROSE STREET, SUITE 201, BERKELEY, CALIFORNIA, 94709 USA
510 809-1165 (direct) | 510-548-6800 x35 (main) | 510 548-6848 (fax)
***@calthorpe.com <mailto:***@calthorpe.com> | www.calthorpe.com