0

We are currently trying to 'clean up' our geodatabase. For example: we have 30 datasets of districts, each of a different year (1986-2016). To make it more visible, we want to combine them all into 1 dataset where we use a view for the most recent one. There are some issues when we create a view. First, we cannot set a primary key (we use PostgreSQL together with PostGIS) in a view. A user must specify a primary key each time he drags the view into ArcMap. Second, for some reason, when creating a view, ArcGIS recognizes 2 datasets: a table and a query layer (see image). I have no idea why. In PgAdmin it looks like one tableview. Catalog also crashes when trying to open the table version.

Is there a way to set and save a primary key in ArcGIS, since it cannot be done using PostgreSQL? And what causes ArcGIS to show two datasets of the same view, a table and a query layer? If not, are there any alternatives to these problems?

enter image description here

My table definition:

CREATE TABLE gemeentegrenzen
(
  objectid serial NOT NULL,
  gem_nr integer,
  gem_naam character varying(30),
  jaar smallint,
  prov_code character varying(10),
  prov_naam character varying(28),
  cbs_code character varying(6),
  shape geometry,
  CONSTRAINT gemeentegrenzen_pkey PRIMARY KEY (objectid),
  CONSTRAINT enforce_srid_shape CHECK (st_srid(shape) = 28992)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE gemeentegrenzen
  OWNER TO geo;

-- Index: sidx_54890_8

-- DROP INDEX sidx_54890_8;

CREATE INDEX sidx_54890_8
  ON gemeentegrenzen
  USING gist
  (shape);

My view definition:

CREATE OR REPLACE VIEW gemeenten_actueel AS 
 SELECT gemeentegrenzen.objectid,
    gemeentegrenzen.gem_nr,
    gemeentegrenzen.gem_naam,
    gemeentegrenzen.jaar,
    gemeentegrenzen.prov_code,
    gemeentegrenzen.prov_naam,
    gemeentegrenzen.cbs_code,
    st_transform(gemeentegrenzen.shape, 28992)::geometry(Geometry,28992) AS shape
   FROM gemeentegrenzen
  WHERE gemeentegrenzen.jaar = 2016;

ALTER TABLE gemeenten_actueel
  OWNER TO geo;

Both are stored in a PostgreSQL 9.4 database with postgis 2.0. ArcGIS is version 10.3.

  • If you don't include the view SQL and column types, it's unlike you will get a useful answer. The most likely cause is use of unsupported column types. Please edit the question to contain table and view definitions as well as versions of all software to three digits (e.g., "10.4.1",...) You also have a laundry-list of issues, whic violates the "One question per Question" policy. – Vince Nov 1 '16 at 0:57
  • I edited my question, thanks for the feedback. I do have 2 issues, but both are related to the same single question, and that's using views of PostgrSQL in ArcGIS. – user30058 Nov 1 '16 at 11:38
  • Two questions is two Questions. The environment is related but the answers are not. Since the possiblilty of end-around of rowid column selection has already been answered (not possible), you'll need to focus on the real issue. – Vince Nov 1 '16 at 12:38
  • Why are you using ST_Transform when the SRID does not change? The "Put all the features in one table and select out what I need" approach has been proven to be very inefficient (slow) time and time again. If you must follow this pattern, I recommend a materialized view, so a spatial index can be used. – Vince Nov 1 '16 at 12:46
  • I had to use this, because otherwise the view gets no record in the geometry_columns view and is therefore not visible in ArcGIS (postgis.net/docs/…). Since Postgis 2.0 the geometry_columns is a view instead of a table, where I could just insert the record into the table. If it is proven slow, does that mean you recommend having separate tables for all district-years as best solution in the end? – user30058 Nov 1 '16 at 13:00
1

It would be useful to know what version of everything you are using, and how the view was created but, I think you have a bit of a permissions issue going on. If you see data twice in ArcMap or ArcCatalog then your schema permissions are somehow not right. Most commonly folks forget to grant "usage" or " all" on the schema.

A view created in a db is not registered with the gdb, which is why in ArcMap you are asked to indicate the column to be used as a primary key, because that information is usually read from the metadata.

Without any further information, I would assume that ArcMap is crashing on you because either of the view definition ( does the query used to create the view successfully resolve in PostGIS?) or the assumed priv problem.

  • I have created my district table (gemeentegrenzen) by importing a feature into my database using ArcCatalog. From there I created a view (see OP how I created my view using SQL). My schema is default, although I did grant all to the schema, without difference. I also created the view in ArcCatalog, instead of in PostgreSQL itself, but also with no positive result. – user30058 Nov 1 '16 at 11:47
  • so, for clarification purposes.... – user52786 Nov 2 '16 at 20:06
  • 1. use AC to import or c/p a feature class – user52786 Nov 2 '16 at 20:06
  • a. were you logged in as a user with had a corresponding schema created in pg? For example: gis login user, with a schema called gis, with 'all' or 'usage' granted to the schema – user52786 Nov 2 '16 at 20:08
  • 2 create the view, either in pg with sql, or as a right click on the connection file or using a gp tool? – user52786 Nov 2 '16 at 20:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.