[postgis-users] PostGIS query layer in ArcMap - error readingOID

Fri Jul 8 13:12:50 PDT 2011

Thanks, Paul. As I mentioned, I tried using and not using primary keys. That's not the issue. Maybe someone else has an idea about this...???

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paul Ramsey
Sent: Friday, July 08, 2011 12:05 PM
To: PostGIS Users Discussion
Cc: Conor Henley
Subject: Re: [postgis-users] PostGIS query layer in ArcMap - error readingOID

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
>
>
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users