Select to view content in your preferred language

Adding data from sqlite fails (ArcGIS Pro 3.0)

2239
10
06-30-2022 11:54 PM
Labels (1)
TomGeo
by
Frequent Contributor

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.

TomGeo_0-1656657393267.png

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

- We are living in the 21st century.
GIS moved on and nobody needs a format consisting out of at least three files! No, nobody needs shapefiles, not even for the sake of an exchange format. Folks, use GeoPackage to exchange data with other GIS!
Tags (2)
0 Kudos
10 Replies
Robert_LeClair
Esri Esteemed Contributor

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.

0 Kudos
TomGeo
by
Frequent Contributor

@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. 

- We are living in the 21st century.
GIS moved on and nobody needs a format consisting out of at least three files! No, nobody needs shapefiles, not even for the sake of an exchange format. Folks, use GeoPackage to exchange data with other GIS!
0 Kudos
Robert_LeClair
Esri Esteemed Contributor

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?

0 Kudos
TomGeo
by
Frequent Contributor

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. 

- We are living in the 21st century.
GIS moved on and nobody needs a format consisting out of at least three files! No, nobody needs shapefiles, not even for the sake of an exchange format. Folks, use GeoPackage to exchange data with other GIS!
0 Kudos
Robert_LeClair
Esri Esteemed Contributor

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.

0 Kudos
TomGeo
by
Frequent Contributor

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. 

- We are living in the 21st century.
GIS moved on and nobody needs a format consisting out of at least three files! No, nobody needs shapefiles, not even for the sake of an exchange format. Folks, use GeoPackage to exchange data with other GIS!
0 Kudos
Robert_LeClair
Esri Esteemed Contributor

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!

0 Kudos
TomGeo
by
Frequent Contributor

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. 

- We are living in the 21st century.
GIS moved on and nobody needs a format consisting out of at least three files! No, nobody needs shapefiles, not even for the sake of an exchange format. Folks, use GeoPackage to exchange data with other GIS!
0 Kudos
TomGeo
by
Frequent Contributor

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:

TomGeo_0-1656929617974.png

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.

 

- We are living in the 21st century.
GIS moved on and nobody needs a format consisting out of at least three files! No, nobody needs shapefiles, not even for the sake of an exchange format. Folks, use GeoPackage to exchange data with other GIS!
0 Kudos