[postgis-users] PostGIS query layer in ArcMap - error reading OID

[Paul Ramsey]

Well, it would be very unfortunate if ESRI implemented their
functionality to depend on OID, since it has been deprecated for many
years. Possibly they are just falling back on it if tables lack
primary keys. Does your table have a primary key? If not, add one and
see if things get better.

P.

On Fri, Jul 8, 2011 at 11:45 AM, Sheara Cohen <Sheara at calthorpe.com> wrote:
> 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)
> sheara at calthorpe.com | www.calthorpe.com
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>