topic Adding data from sqlite fails (ArcGIS Pro 3.0) in ArcGIS Pro Questions

Adding data from sqlite fails (ArcGIS Pro 3.0)

TomGeo — Fri, 01 Jul 2022 06:54:40 GMT

AGP 3.0 has no problem reading the content of an sqlite file in, however, adding data from an sqlite file to map hangs at a 'New Query Layer' dialog, where it seems AGP is trying to read the column properties, and fails.

QGIS does not have any problem reading and visualizing the data. Hence, I don not think there is anything wrong with the sqlite file.

Re: Adding data from sqlite fails (ArcGIS Pro 3.0)

Robert_LeClair — Fri, 01 Jul 2022 19:24:06 GMT

Tom - I presume you're following the steps for connecting to and adding data from a sqlite database as referenced in this help document? Please advise.

Re: Adding data from sqlite fails (ArcGIS Pro 3.0)

TomGeo — Fri, 01 Jul 2022 20:24:20 GMT

@Robert_LeClair - yes, I do connect to the sqlite via ‘Add Database’ in the catalog pane, and AGP has no issue in listing all the tables within the database.
I did not try the ‘Add Data’ button, but used drag and drop instead - from the catalog pane onto an existing map. I did try a right click as well, adding the table to a new map. Then the map view ic created, and the dialog from my initial post shows up.

Whatever way I tried, the result is the same and I’m stuck at the dialog ‘New Query Layer’. Also, the only way to close the dialog is the close button, the cancel button does not work in this case.

The way I create the sqlite db and it’s data source might be of interest. Who knows.

I build the db in the FME Interoperability workbench by reading an OSM file in. I read every layer of the OSM file as single feature types and write them into the db file via the Spatialite writer, set to allow dynamic schemas.

Re: Adding data from sqlite fails (ArcGIS Pro 3.0)

Robert_LeClair — Fri, 01 Jul 2022 20:37:11 GMT

Interesting. Thank you for the response and the details. So what I do wonder is the WAY you create the sqlite database may be the issue. One thing to test is to use the Create Sqlite Database GP tool in ArcGIS Pro, bring in some data for 1 or 2 feature classes and see what happens in the UI when you add the data and/or drag/drop. Does the "New Query Layer" dialog open again and stops?

Re: Adding data from sqlite fails (ArcGIS Pro 3.0)

TomGeo — Fri, 01 Jul 2022 20:46:18 GMT

Will try tomorrow… it’s almost 11pm here now. 😉

Can AGP actually handle an OSM file by itself?

Also, as Said in the initial posting, QGIS reads and visualize the data from the sqlite db without breaking into sweat.

Re: Adding data from sqlite fails (ArcGIS Pro 3.0)

Robert_LeClair — Fri, 01 Jul 2022 21:00:23 GMT

11 PM is getting late certainly! 3 PM Mountain Time Zone in Denver, CO!

So ArcGIS can "kind of" handle OSM data. There are two ways - one using the Data Interability extension. BUT it writes the output to a file geodatabase vs. a Sqlite database. Tried to force it to write to the Sqlite database but it defaulted back to *.gdb. But it did import the OSM data just fine. Another way is to download and install the ArcGIS Editor for OpenStreetMap but it uses ArcMap for this workflow. Not sure if there will be an ArcGIS Pro version of this tool. You can read more about these workflows here.

Re: Adding data from sqlite fails (ArcGIS Pro 3.0)

TomGeo — Fri, 01 Jul 2022 21:19:19 GMT

Well, Copenhagen here, and after a full working day, plus Tour de France… Colorado sounds good, but my neck hurts immediately again when I think at my last time at the 1st flat iron, and it seems to get pretty hot during the next couple of days.

I will test the sqlite with some feature classes and see about the results for educational purposes.
If it works writing to FGDB than that’s fine for me as well. If everything goes well with the data I will write them anyway into an Enterprise DB.

Re: Adding data from sqlite fails (ArcGIS Pro 3.0)

Robert_LeClair — Fri, 01 Jul 2022 21:26:58 GMT

It's 29 C today with forecasted highs going to 35 or 36 C in the next 7-10 days. Going to be WARM! I've climbed the 1st, 2nd, and 5th Flatirons and would like to get the 3rd and 4th one of these days. Did you have an injury on the 1st Flatiron?

Sounds good re: testing purposes. Hopefully it works for you! Enjoy your weekend!

Re: Adding data from sqlite fails (ArcGIS Pro 3.0)

TomGeo — Fri, 01 Jul 2022 21:34:04 GMT

Wasn’t a climbing injury so to speak… climbing partner never showed but I couldn’t give it a pass once I was standing at its foot. No rope, but it took anyway a while and during a heatwave I burned my neck to a crisp. The second was closed at the time due to vultures nesting, or something like that.

Re: Adding data from sqlite fails (ArcGIS Pro 3.0)

TomGeo — Mon, 04 Jul 2022 10:16:57 GMT

Hi @Robert_LeClair I was just testing an sqlite database I created by using the Quick Export tool from the Interoperability Toolbox.

The created database and table have been correctly read into AGP 3.0, and when adding to the mapview I was promptly getting a list of columns to choose the unique identifiers from.

There seems to be an issue though with the coordinate systems.

As I left the default coordinate system setting ("Same as Source"), when adding the table to the mapview the dialog shows the coordinate system as unknown.
When setting the coordinate system manually to SRID = 25832, the layer is generated, and add to the content pane, but on visualizing the features nothing is shown.
On 'Zoom to Layer' nothing is shown either.
On the attempt to open the layers attribute table AGP return the following error message:

If I leave the SRID as unknown, then the layer is correctly visualized.

The other approach, of writing the OSM data in FME via a FeatureReader into an File Geodatabase works without problems.

Re: Adding data from sqlite fails (ArcGIS Pro 3.0)

Robert_LeClair — Thu, 21 Jul 2022 15:15:55 GMT

Tom - apologies for the delayed response, I was on vacation for the last 2.5 weeks. To your question!

1. I wonder if it has to do with spatial types (geography vs. geometry) for the sqlite database? Reference the following item here.

2. Are there any "custom" settings for the spatial reference? If so, use the Project GP tool to remove the custom settings and try again.