Latest Contributions by BruceHarold

GeoNet Ideas contains many customer requests for ODBC connectivity from Pro to databases that are not supported ArcGIS workspaces.  This blog is about implementing read-only import of ODBC data sources to Geodatabase.  See also the paragraph titled 'Update' for a simple way to move data sources between formats using the same underlying approach. Thumbnail:  We'll use a scripted approach, creating a Python script tool in a Pro toolbox.  You could make this a standalone script and a scheduled task.  The coding isn't scary.  You'll need permission to create an ODBC data source on your computer, and if you need to publish this to ArcGIS Enterprise the data source will need to be set up there too.  If multiple users need to use the tool on your machine the ODBC data source will need to be a system one.  Off we go... The ArcGIS Pro python environment ships with a module named osgeo, from the OSGeo organization.  This supplies the GDAL libraries that support conversion of hundreds of geospatial formats, and one of the supported sources is ODBC, which isn't a 'format' of course but handles moving tabular data around. For my example source I chose MariaDB, a binary equivalent to MySQL.  After installing MariaDB and the appropriate 64bit ODBC driver I imported some CSV data and created a user-level ODBC data source in Windows.  Here is how the admin tool looks (click on images to enlarge them): MariaDB ships with a handy administration utility - HeidiSQL - here is how the Data view of my target data looks in HeidiSQL (the names and addresses are made up): So that's my target data, now how to get at it?  To understand what the osgeo module needs to connect to my ODBC source I researched the relevant vector driver.  So far so good.  With a little more surfing some examples and the submodule osgeo.ogr API the parts were apparent.  Next step - code it!  Here is the result in my project: The blog download has the tool and source, plus the CSV data I used.  Disclaimer: this is a very simple example without any defensive code to handle variability in the input data.  The idea is to give you confidence you can script a repeatable workflow. How did I do?   I run the tool: ...and the output table is created in my project home geodatabase. Success!  I imported 6000 rows in about 8 seconds.  So that is the pattern I wanted to show.  The approach will handle more data types than just the string and integer values I used, and it is quite likely the part of my code where I map OGR field types to ArcGIS field types has issues.  Please do comment in this blog space on your challenges and successes. Now for the optional extra - Access databases! I have 64bit Office on my machine, I also have Microsoft Access Runtime 2013 installed, I'm not entirely sure if both are needed or just one but my ODBC datasource options include .mdb and .accdb.  Otherwise the pattern to reading Access databases is the same as the above.  I configured an ODBC MS Access Database connection in the 64bit ODBC administrator to connect to an .accdb database on disk.  I possibly should have added a new one and given it a descriptive name but you get the idea.  From there it is just like any other ODBC source, except it does have a dependency on 64bit Office and/or the runtime driver. Update:  I created this sample with a rudimentary knowledge of what the OGR drivers delivered with the osgeo module can do.  It is way easier to just copy an OGR layer to an OpenFileGDB layer than create one with ArcPy and use a cursor to write into a new table or feature class.  Re-purpose the approach I describe in the comments in the below post about the OpenFileGDB driver: https://community.esri.com/ideas/15746  ... View more

Everyone likes SQLite. It is a single portable file, performs and scales well, supports enough SQL to be useful and has a DB-API compliant Python module and API access in other languages. It is embedded in many mobile and desktop apps, and is directly usable in ArcGIS Pro. SQLite as a container has an incarnation - OGC GeoPackage - that supports the encoding of vector and raster features for direct use in ArcGIS Pro.  You can read about the standard on the OGC website. The GIS format most often compared with GeoPackage is the Esri-defined shapefile.  Shapefile is the most shared GIS format on the planet and its encoding of vector features is published.  Note however the publication date - 1998.  At the time the shapefile was designed, the components available had limitations that can frustrate today's advanced workflows.  These include file size limit, attribute field count and name width limits, dates not supporting time, complexity in handling character encodings and lack of null value support for most field types.  Shapefile has been spectacularly successful for handling simple vector features, but it can be limiting. I think of GeoPackage as the new shapefile without the old limitations and I encourage you to use it. It is a great format for, well, geo-packaging! However, don't go as far as thinking it is a full-blown GIS workspace, it doesn't have geodatabase behaviors like domains and attribute rules. What it does, it does well. GeoPackage is extensible, and there are approved OGC extensions for gridded tiles of elevation data and table relationships, and non-approved community extensions such as map styling of features and storing vector tiles. ArcGIS Pro does not yet implement support for any GeoPackage extensions (excepting table functionality adopted in the v1.2 release). What can you do with a GeoPackage in ArcGIS Pro 2.6? Read and write simple features (polygons, polylines, points, multipoints, circular arcs, tables) Create feature classes with the Feature Class to Feature Class tool Create tables with the Table to Table tool Use Copy/Paste in the Catalog pane Use the Append tool to add data to an existing feature class or table Use the Add Raster To Geopackage tool to store imagery Edit features or rows with the ability to undo and redo edits Modify the schema Geoprocess with any tool that takes a simple feature class or table as input Share GeoPackage data with other users as a static item in ArcGIS Online Use Geopackage  vector and raster data in map workflows Read or write GeoPackage in Data Interoperability ETL workflows Use SQL statements in SQLite's native dialect What can you not do with a GeoPackage in ArcGIS Pro 2.6? Publish a GeoPackage item as a hosted web layer Store or edit metadata Use any geoprocessing tool that requires geodatabase output Some recommendations:  You can add fields and calculate values with geoprocessing tools or ArcPy, but you may find it slower than native geodatabase operations.  Geometry storage in a GeoPackage is not compressed like a geodatabase, so they can get big.  Do your geoprocessing before creating your GeoPackage, then copy your data into it.  Think of GeoPackage as a sharing format. Move your data into the GeoPackage like this: Create a GeoPackage with the Create SQLite Database tool (using the GeoPackage spatial type) Use the Copy tool (Data Management, General toolset) to add vector data, or Copy/Paste in the Catalog pane Use the Add Raster to GeoPackage tool (Conversion, To GeoPackage toolset) to add raster mosaics Your GeoPackage is now ready for use. Note on sharing:  You can upload a .gpkg file to ArcGIS Online, the file type will be recognized.  You can send a link after sharing the item and others can then download it from the content gallery. Advanced topic:  Because it is based on SQLite, GeoPackage comes with a database engine and good SQL language support.  There are 3rd party tools for working with SQLite which you may find useful, but to include a spatial component in your work the ArcGIS Data Interoperability or Safe Software FME products support scripting SELECT, CREATE, DROP, DUPLICATE, TRUNCATE and CROSS JOIN statements within Spatial ETL tool transformers like SQLCreator and SQLExecutor.  This approach enables very powerful and performant use of a GeoPackage. ... View more