topic Re: Transfering Data to a Database in Data Management Questions

Transfering Data to a Database

Ozan — Fri, 24 May 2024 08:25:29 GMT

Edit: Today I wrote two posts detailing what I tried, to solve the problem and the results (yes, unsuccessful). Both were deleted, no explanation, no reason, just gone. From my point of view, esri does not actually want to help me here.

Re: Transfering Data to a Database

Ozan — Wed, 22 May 2024 13:30:16 GMT

I forgot, we are using ArcGIS Desktop 10.7.1

Re: Transfering Data to a Database

George_Thompson — Wed, 22 May 2024 13:41:18 GMT

What is the version of the RDBMS and geodatabase?

What are the permissions of the user you are connecting as to load the data?

Re: Transfering Data to a Database

Ozan — Wed, 22 May 2024 13:48:57 GMT

I was told that it's Geoserver 2.23 using latest Postgresql database.

User is admin on the postgresql server.

Re: Transfering Data to a Database

George_Thompson — Wed, 22 May 2024 16:14:31 GMT

I am wondering if the latest version of PostgreSQL is causing an issue with ArcMap 10.7.1. Here are the supported versions for 10.7.1: https://desktop.arcgis.com/en/system-requirements/10.7/database-requirements-postgresql.htm

What is the specific version of PostgreSQL (version + patch level)?

Is the PostgreSQL DB an Enterprise Geodatabase?

Can you test with the latest version of ArcGIS Pro (3.3)?

Re: Transfering Data to a Database

VinceAngelo — Thu, 23 May 2024 00:19:38 GMT

An insanely large objectid (shapefiles can't store 80M features, much less 867+M) wakes me wonder what your exact procedure is.

Admin rights is not a particularly good feature for a geodatabase connection.

Pro (or ArcMap or ArcCatalog) can create new tables from shapefile or file geodatabase with drag-and-drop, with or without a geodatabase enabled, but the database name and username must be lowercase and the default schema for the login must match (e.g., login 'dataloader', schema 'dataloader').

- V

Re: Transfering Data to a Database

MarceloMarques — Wed, 22 May 2024 16:50:42 GMT

@Ozan - Tip to test the data load of the shapefile into the PostgreSQL ArcSDE Geodatabase.

1. Import the shapefile into a File Geodatabase

2. export the file geodatabase featureclass to XML Document Schema-Only ( no data )

3. import the XML Document Schema-Only into the PostgreSQL Geodatabase

4. Use the ArcCatalog Simple Data Loader to load the data from the File Geodatabase Featureclass into the PostgreSQL Geodatabase Featureclass

About the error message.

Underlaying DBMS error [ERROR: duplicate key value violates unique constraint "test1_pkey"

Detail: Key (objectid)=(867896168) already exists.

(cbs.user342.test1)::SQLSTATE=23505]

The source data in the shapefile might have duplicated values for a column that needs to be unique, and you might need to clean up the data before you can load the data in a File Geodatabase and in the PostgreSQL Geodatabase.

You can access the PostgreSQL Geodatabase via PgAdmin and look the Featureclass table and inspect the details of the "test1_pkey" unique constraint and which column it refers and then check if the data of the column has unique values.

I hope this helps.

Re: Transfering Data to a Database

MarceloMarques — Wed, 22 May 2024 16:54:30 GMT

@Ozan - best practices to create the PostgreSQL Geodatabase.

Mapping and Charting Solutions (MCS) Enterprise Da... - Esri Community

Best Practices Production Mapping 3.x Workspace in PostgreSQL®

Re: Transfering Data to a Database

Ozan — Fri, 24 May 2024 07:03:16 GMT

I tried this, nothing changed, same error. "test1_pkey" is the objectid column, the one ArcGIS creates while creating a shapefile in a database.

Re: Transfering Data to a Database

Ozan — Fri, 24 May 2024 07:27:40 GMT

Sorry for late reply, I had some other work and could not look into this. While I tried loading and importing data without much success, I noticed a thing. On some attempts, arcgis manages to import one ot two data into database. The objectids of the data in database is like; 1243424325. That long, on one occasion even with negative number like -1234245656.

I tried a couple more import and at last created a new shapefile with no Z or M values,with just id and shape* columns.I copied only vector data to this file and I could not import that file or load the data either. I checked geometry and it gives no problems. I have city centers data, point shapefile. Very simple and I can not import or load data of that either. Arcgis does create table but does not fill it. I get Error 000224 can not insert features while trying to import. And the same error I mentioned in the first post when i try to load data, and maybe a single data with a weird long objectid.

Re: Transfering Data to a Database

Ozan — Fri, 24 May 2024 07:55:46 GMT

I did write a long reply, but it seems it was deleted....

Long story short, I tried importing data or loading data without success. Even with a shapefile with only id and shape* columns, seperately with a polyline and a point data. Arcgis does create table but does not fill the table. Only, sometimes arcgis manages to enter a data or two with long objectid like 123246567. It was even a negative number sometimes.

Re: Transfering Data to a Database

VinceAngelo — Sat, 25 May 2024 00:58:44 GMT

Well, I don't know what happened, but Esri uses a third party app for Community, and it's possible something went wonky and a spontaneous rollback happened on the server; I very much doubt someone has maliciously removed content.

I would note that this Community is not Esri Tech Support. If you want help from Esri, you should contact Tech Support according to the terms of your support contract. Community is a user engagement forum, in which some Esri employees also post, as fellow users.

Railing against Esri in the forums Esri provides for user interaction is probably not a good way to get help from staff contributors.

I've loaded tens of billions of rows into thousands of PostgreSQL tables in the past decade (a couple hundred million in the past week alone -- dev migration), many of which with plain vanilla ArcPy scripts and Python toolboxes into PostgreSQL databases without enterprise geodatabases enabled. There is a trick to it, though, and without details on the exact database in use, and how you've configured the database, logins, and schemas, and the actual load procedure you used, it's difficult to guess what may have gone wrong.

Good luck.

- V