topic Re: Obtain Globalids From Another Table in Data Management Questions

Obtain Globalids From Another Table

KellyArmstrong — Wed, 09 Jun 2021 16:05:22 GMT

I have a standalone table that has an id field named "media_table_id". I also have a feature class that has an id field with those same values and it has a globalid field. I would like to be able to populate my standalone table with the globalid's from the feature class, where the id's match and contains 'tbl_flare_ends" from the layername field in the standalone table.

Would I need to write an sql to do this? Something like: standalone.media_table_id = feature_class.id_field WHERE standalone.layername = 'tbl_flare_ends' ? Or would I have to do some type of join to populate globalid values that match the criteria?

Re: Obtain Globalids From Another Table

VinceAngelo — Thu, 10 Jun 2021 19:20:23 GMT

In ArcGIS parlance there's a difference between a "GlobalID" and a "GUID". Both utilize UUID field types, but a GlobalID is a primary key ("GlobalIDs uniquely identify a feature or table row in a geodatabase and across geodatabases."), while a GUID is a field that utilizes the native UUID datatype (possibly referencing a GlobalID in a different table).

There are many ways to manifest a GlobalID as a GUID in a table, but the details vary depending on the storage types of the table sources. If both are in a single Enterprise geodatabase instance, the UPDATE is as easy as the supporting RDBMS permits cross-table joins to be. If you're using file geodatabase or a number of different data sources, then the tools that support this are limited (but there are still multiple ways).

Ironically, as good as the UUID type is for distinguishing unique records, it is large and inefficient at that task; integer types make for much more efficient storage and comparison in very-large tables.

How "standalone" is your standalone table? How large are the tables (in rows)? What data source(s) are you using? What have you tried?

- V

Re: Obtain Globalids From Another Table

KellyArmstrong — Thu, 10 Jun 2021 20:39:25 GMT

Vince,

Yes, I apologize. I should have stated I want to obtain Globalid's and copy them to another table as GUID's.

Both are in a single Enterprise geodatabase. The table I am looking to populate the GUID field with the globalid's has 55,000 rows...

I have tried creating a query table, but that failed. I have also tried utilizing joins, but when joining the GlobalID field doesn"t come along...

Re: Obtain Globalids From Another Table

KellyArmstrong — Thu, 10 Jun 2021 21:40:08 GMT

Vince,

I am guessing a query table might be the best solution? Something like this?

SELECT sde.sde.tbl_flare_ends.globalid
FROM sde.sde.tbl_flare_ends INNER JOIN sde.sde.tbl_media2
ON sde.sde.tbl_flare_ends.originaloid = sde.sde.tbl_media2.media_table_id_integer
WHERE media_table = 'tbl_flare_ends'

Re: Obtain Globalids From Another Table

KellyArmstrong — Thu, 10 Jun 2021 22:33:37 GMT

I got it to work. This is what I did in pgAdmin:

CREATE OR REPLACE VIEW v_flare_ends_global AS
SELECT DISTINCT tbl_flare_ends.globalid,
tbl_media_2.media_table,
tbl_media_2.media_name,
tbl_media_2.media_date_added,
tbl_media_2.media_path,
tbl_media_2.media_table_id
FROM tbl_flare_ends
JOIN tbl_media_2 ON tbl_flare_ends.originaloid = tbl_media_2.media_table_id_integer
WHERE tbl_media_2.media_table::text = 'tbl_flare_ends'::text;

ALTER TABLE v_flare_ends_global
OWNER TO sde;
GRANT ALL ON TABLE v_flare_ends_global TO sde;

Re: Obtain Globalids From Another Table

VinceAngelo — Mon, 14 Jun 2021 01:48:25 GMT

The SQL looks good, though making the 'sde' login the owner isn't best practice (the 'sde' login should be treated as an administrative account, used only when absolutely necessary, and never to own geodata).

- V

Re: Obtain Globalids From Another Table

KellyArmstrong — Mon, 14 Jun 2021 14:08:55 GMT

Thank you for that tip. I have decided to create an UPDATE query in pgAdmin instead of creating a query table.