Maintain Geodatabase
Maintain Geodatabase An application for Spatializing acQuire and other Non-spatial Databases Dennis Geasan – GIS Technologies Haines, AK and Reno, NV [email protected] 775-750-1619
Introduction • The challenge of using non-spatial data sources in ArcGIS. • Maintain Geodatabase Highlights • ArcGIS and Other Solutions • Maintain Geodatabase Advantages • Demo
Using non-spatial data sources in ArcGIS. • What is a Non-Spatial Database? – A relational database that is not an ESRI Geodatabase.
• Common non-spatial databases used in Mining: – acQuire – EDM (Exploration Data Management) – Equis (Environmental Data Management) – Several others.
• The Challenge… Synchronizing point feature classes derived from dynamic non-spatial data sources.
Maintain Geodatabase • A Windows application that….. – builds and maintains feature classes derived from non-spatial data sources. – re-projects mixed coordinate systems. – does fast updates of active feature classes. – does not rebuild the entire feature class when performing updates.
• Provides a GUI for setup and performing updates of target feature classes. • Includes batch capability as a Windows scheduled task or via the GUI.
Maintain Geodatabase Processing EDM Data Entry
Active ArcMap Session
(Exploration Data Management)
Updates appear on the next map or table refresh.
Maintain Geodatabase Update EDM record ID = 4 Add EDM record ID = 5 Delete EDM record ID = 1
Run Update
Update GeoDB record (ID=4) Add new GoeDB record (ID=5) Delete GeoDB record (ID=1)
EDM Non Spatial Database
Detect Changes Get EDM record ID = 4 Get EDM record ID = 5
Geodatabase
ArcGIS and other Solutions •
ArcGIS Methods for spatializing database records: – Add XY Data in ArcMap • •
OLE Connection Query Layer
– Use a feature class with Joins • •
Feature class has only Coordinates and a ‘unique ID’ attribute field. Append attribute fields from a non-spatial data source via an OLE or Query Layer table and perform a join using the ‘unique ID’.
– Python… Rebuild entire feature class on demand or as a Windows scheduled task. – Spatial Data types in a relational database.
•
Other Methods – Web Services • •
ASP.Net (SOAP) REST
Why Maintain Geodatabase?
It’s Better!!!
Add XY Data Pros/Cons • Pros – – – –
Easy to learn and maintain in a map document. Point features are updated each time map doc is opened or refreshed. No other application or processing required. Recommended for small data sets.
• Cons – Slow when working with a large data set. – As of 10.0 SP5 some query and selection operations do not work for OLE Connections. Query Layers + Add XY can cause ArcMap to die. – The ‘Add XY Data’ setup is required for each new map doc using the same source database table or view. – All source data records have to be of the same coordinate system.
• Not recommended if creating ArcGIS Server map services.
Using a Feature Class with Joins Pros/Cons • Pros – Decent performance in ArcMap. – Feature class display and query performance is always better than “Add XY Data”.
• Cons – Feature class often out of synch with data source. – Requires ArcMap Edit session to add new points or edit coordinates of existing points. Some chance for introducing errors. – Join definition needs to be maintained. Loss of data connection a common cause for join to be dropped. Join definition required to be defined for each new map doc. Layer files can help improve this. – All source data records have to be in the same coordinate system. – Editing can only be done when feature class is not in use.
Python Pros/Cons • Pros – Feature class is updated as either a Windows Scheduled Task or manual running of the script. – No user interaction required to update the feature class.
• Cons – Python programming skills required to build and maintain the app. – Entire feature class has to be rebuilt on each run of the script. • Can take over 30 minutes for large data sets.
– Feature class will not be rebuilt if it is currently being used in Desktop or ArcGIS Server. – Difficult to accommodate source records of different coordinate systems and datums.
Spatial Data Types • A relational database data type for storing one or more coordinate points. – Similar to the ‘Shape’ field in a Geodatabase feature class or an ESRI Shape file. – Most major RDMS systems provide this data type. – Maybe a future release of ArcGIS will replace the ‘Shape’ field with this data type .
• Pros – An application independent means for defining spatial feature coordinates. – Spatial comparisons can be performed within the database independent from ArcGIS and other GIS applications.
• Cons – Currently not supported in most non-spatial mining databases. – Current technology has some challenges in regards to data entry of coordinate values and identification of coordinate system. – ArcGIS 10.0 can only use these fields via Query Layers and Add XY. •
ArcGIS 10.1 automatically loads tables with a spatial field as a Query Layer.
– For ArcGIS, all coordinate values have to be of the same coordinate system. – Slower performance than Geodatabase feature classes.
Web Services An application or computer ‘function’ accessed via the Internet. Examples… Online loan payment calculators, currency converter, etc.
• Pros – Feature classes are always in synch with data source. – Multiple applications can utilize the same web service.
• Cons – Requires fairly complicated application development. • Source database requires functionality that calls web service when updates occur.
– High Cost • ESRI Licensing requires an ArcGIS Server license.
– Vendors reluctant to utilize a web service that is not part of their code base.
Web Service Example * Web Service
EDM Data Entry
(Currently does not utilize a web service) EDM Requests UpdatePointFC(LocationID=4) AddPointFeature(LocationID=3000)
2 Update record with LocationID = 4 Add record with LocationID=3000
ArcMap
EDM asks Web Service to do some work.
3 Web Service Does Work (SpatializeLib.DLL) UpdatePointFeatures(LocationID=4) InsertPointFeature(LocationID=3000)
1
Get record LocationID = 4 Get record LocationID=3000 EDM Non Spatial Database
4
Update Geodatabase record LocationID = 4 Add new record LocationID=3000
5 * EDM currently does not utilize a Web Service. This slide is only illustrating Web Service functionality.
6 Geodatabase
Maintain Geodatabase Advantages • Mixed coordinate systems and datums can be in the data source. – Requires a field that identifies the coordinate system on each record.
• Updates are very fast.
– Maintain GDB does not rebuild the entire feature class. Only updated, deleted, or new data source records are used to update the target feature class.
• Currently active feature classes can be updated.
– Updates appear on next ArcMap/ArcCatalog data frame or table refresh. – Updates also appear in ArcGIS Server map services.
• Batch Processing Capability
– GUI, Command Window, Windows Scheduled Task.
• Easy to keep feature classes in synch with source data. • Reduced training requirements and increased productivity.
– Desktop community need only use feature classes and is not required to learn and remember other methods for spatializing data from non-spatial data sources
• Provides SDE Geodatabase update capability for users without specific SQL Server privileges to perform updates.
Other Features and Requirements • SQL Server databases required for the source data. – Currently supports: • acQuire Sample Management Database (Metech – Perth, WA Australia) • EDM (Exploration Data Management – Reno, NV USA) • Equis (Environmental Data Management application - USA)
– Other SQL Server databases can be used if they provide a unique record ID and a date/time field that is updated when any record field is changed. – Other database vendors will be supported in the future.
• Users who are not administrators cannot see database login/password information. • Supports Windows and SQL Server Authentication. • ArcGIS Desktop Basic license for file Geodatabases. • ArcGIS Desktop Standard or Advanced license for SDE Geodatabases.
Maintain Geodatabase Demo
Maintain Geodatabase Workflow • Update lookup tables with the server and database names for the data source and the target Geodatabase. • Enter the coordinate systems and datum transformations relevant to your projects. • Add administrators for Maintain GDB. • Finally, add parameter records that define the source and target information to build and maintain feature classes.
Update Database Lookup Tables
Coordinate System and Datum Transform Lookup Tables
Coordinate System and Datum Transform Lookup Tables
Add Parameter Records
• This table defines everything the application needs to know to build and maintain one or more feature classes. • The target location can be either a file or SDE Geodatabase.
Maintain Geodatabase Processing Maintain Geodatabase
EDM Data Entry
(Exploration Data Management)
Run Update
An open ArcMap session.
Updates appear on the next map or table refresh. Update record with ID = 4 Add record with ID = 3000 Delete record with ID = 4000
3 Do the Updates
1
EDM Non Spatial Database
Detect Changes
Get record ID = 4 Get record ID= 3000
4
UpdatePointFeatures(ID=4) InsertPointFeature(ID=3000) DeletePointFeature(ID=4000)
Detect Changes
6
Update GeoDB record ID = 4 Add new GeoDB record ID=3000 Delete GeoDB record ID = 4000
5
Geodatabase
The first step is to build the target feature class. After that only updates are required.
Examine the results in ArcMap. There is a problem with record number 1.
Open a data entry session on the source database table.
Result of editing record 1. The ‘STATE’ is now TN.
Select the target feature class parameter record and click Update. One record updated. Record ID = 1.
To update the attribute table display, click the table button and choose ‘Reload Cache’.
The value for the ‘STATE’ field is now correct.
Update the latitude value to 37.
One record updated. Record ID = 1.
To update the map click ‘Refresh” or move the map. Update table display by reloading the attribute table cache.
The point is moved and the table is updated. The selection graphic remains. Clear the selection.
The SDE layer is now displaying correctly.
The original coordinate system definition for the record is WGS84. Change it to ‘NAD27’.
Updated record in database table.
Notice the difference between the latitude values in the table. Source record is NAD 27. Target feature class coordinate system is WGS84. The datum transform worked!
The File Geodatabase layer has not been updated.
Select the parameter record for the file Geodatabase and click Update.
Both layers are now updated.
Maintain Geodatabase • New and Deleted records in the data source will also be updated in the target feature class. • Multiple updates, inserts, and deletes in the data source are handled by one update run of MGDB. • Maintain Geodatabase includes batch capability that runs as a Windows scheduled task. • The ArcMap table and map refresh operation is required for display purposes only and is not required to update the feature class. • A live internet demonstration can be provided. Call or send me an email. • [email protected] • 775-750-1619
Introduction • The challenge of using non-spatial data sources in ArcGIS. • Maintain Geodatabase Highlights • ArcGIS and Other Solutions • Maintain Geodatabase Advantages • Demo
Using non-spatial data sources in ArcGIS. • What is a Non-Spatial Database? – A relational database that is not an ESRI Geodatabase.
• Common non-spatial databases used in Mining: – acQuire – EDM (Exploration Data Management) – Equis (Environmental Data Management) – Several others.
• The Challenge… Synchronizing point feature classes derived from dynamic non-spatial data sources.
Maintain Geodatabase • A Windows application that….. – builds and maintains feature classes derived from non-spatial data sources. – re-projects mixed coordinate systems. – does fast updates of active feature classes. – does not rebuild the entire feature class when performing updates.
• Provides a GUI for setup and performing updates of target feature classes. • Includes batch capability as a Windows scheduled task or via the GUI.
Maintain Geodatabase Processing EDM Data Entry
Active ArcMap Session
(Exploration Data Management)
Updates appear on the next map or table refresh.
Maintain Geodatabase Update EDM record ID = 4 Add EDM record ID = 5 Delete EDM record ID = 1
Run Update
Update GeoDB record (ID=4) Add new GoeDB record (ID=5) Delete GeoDB record (ID=1)
EDM Non Spatial Database
Detect Changes Get EDM record ID = 4 Get EDM record ID = 5
Geodatabase
ArcGIS and other Solutions •
ArcGIS Methods for spatializing database records: – Add XY Data in ArcMap • •
OLE Connection Query Layer
– Use a feature class with Joins • •
Feature class has only Coordinates and a ‘unique ID’ attribute field. Append attribute fields from a non-spatial data source via an OLE or Query Layer table and perform a join using the ‘unique ID’.
– Python… Rebuild entire feature class on demand or as a Windows scheduled task. – Spatial Data types in a relational database.
•
Other Methods – Web Services • •
ASP.Net (SOAP) REST
Why Maintain Geodatabase?
It’s Better!!!
Add XY Data Pros/Cons • Pros – – – –
Easy to learn and maintain in a map document. Point features are updated each time map doc is opened or refreshed. No other application or processing required. Recommended for small data sets.
• Cons – Slow when working with a large data set. – As of 10.0 SP5 some query and selection operations do not work for OLE Connections. Query Layers + Add XY can cause ArcMap to die. – The ‘Add XY Data’ setup is required for each new map doc using the same source database table or view. – All source data records have to be of the same coordinate system.
• Not recommended if creating ArcGIS Server map services.
Using a Feature Class with Joins Pros/Cons • Pros – Decent performance in ArcMap. – Feature class display and query performance is always better than “Add XY Data”.
• Cons – Feature class often out of synch with data source. – Requires ArcMap Edit session to add new points or edit coordinates of existing points. Some chance for introducing errors. – Join definition needs to be maintained. Loss of data connection a common cause for join to be dropped. Join definition required to be defined for each new map doc. Layer files can help improve this. – All source data records have to be in the same coordinate system. – Editing can only be done when feature class is not in use.
Python Pros/Cons • Pros – Feature class is updated as either a Windows Scheduled Task or manual running of the script. – No user interaction required to update the feature class.
• Cons – Python programming skills required to build and maintain the app. – Entire feature class has to be rebuilt on each run of the script. • Can take over 30 minutes for large data sets.
– Feature class will not be rebuilt if it is currently being used in Desktop or ArcGIS Server. – Difficult to accommodate source records of different coordinate systems and datums.
Spatial Data Types • A relational database data type for storing one or more coordinate points. – Similar to the ‘Shape’ field in a Geodatabase feature class or an ESRI Shape file. – Most major RDMS systems provide this data type. – Maybe a future release of ArcGIS will replace the ‘Shape’ field with this data type .
• Pros – An application independent means for defining spatial feature coordinates. – Spatial comparisons can be performed within the database independent from ArcGIS and other GIS applications.
• Cons – Currently not supported in most non-spatial mining databases. – Current technology has some challenges in regards to data entry of coordinate values and identification of coordinate system. – ArcGIS 10.0 can only use these fields via Query Layers and Add XY. •
ArcGIS 10.1 automatically loads tables with a spatial field as a Query Layer.
– For ArcGIS, all coordinate values have to be of the same coordinate system. – Slower performance than Geodatabase feature classes.
Web Services An application or computer ‘function’ accessed via the Internet. Examples… Online loan payment calculators, currency converter, etc.
• Pros – Feature classes are always in synch with data source. – Multiple applications can utilize the same web service.
• Cons – Requires fairly complicated application development. • Source database requires functionality that calls web service when updates occur.
– High Cost • ESRI Licensing requires an ArcGIS Server license.
– Vendors reluctant to utilize a web service that is not part of their code base.
Web Service Example * Web Service
EDM Data Entry
(Currently does not utilize a web service) EDM Requests UpdatePointFC(LocationID=4) AddPointFeature(LocationID=3000)
2 Update record with LocationID = 4 Add record with LocationID=3000
ArcMap
EDM asks Web Service to do some work.
3 Web Service Does Work (SpatializeLib.DLL) UpdatePointFeatures(LocationID=4) InsertPointFeature(LocationID=3000)
1
Get record LocationID = 4 Get record LocationID=3000 EDM Non Spatial Database
4
Update Geodatabase record LocationID = 4 Add new record LocationID=3000
5 * EDM currently does not utilize a Web Service. This slide is only illustrating Web Service functionality.
6 Geodatabase
Maintain Geodatabase Advantages • Mixed coordinate systems and datums can be in the data source. – Requires a field that identifies the coordinate system on each record.
• Updates are very fast.
– Maintain GDB does not rebuild the entire feature class. Only updated, deleted, or new data source records are used to update the target feature class.
• Currently active feature classes can be updated.
– Updates appear on next ArcMap/ArcCatalog data frame or table refresh. – Updates also appear in ArcGIS Server map services.
• Batch Processing Capability
– GUI, Command Window, Windows Scheduled Task.
• Easy to keep feature classes in synch with source data. • Reduced training requirements and increased productivity.
– Desktop community need only use feature classes and is not required to learn and remember other methods for spatializing data from non-spatial data sources
• Provides SDE Geodatabase update capability for users without specific SQL Server privileges to perform updates.
Other Features and Requirements • SQL Server databases required for the source data. – Currently supports: • acQuire Sample Management Database (Metech – Perth, WA Australia) • EDM (Exploration Data Management – Reno, NV USA) • Equis (Environmental Data Management application - USA)
– Other SQL Server databases can be used if they provide a unique record ID and a date/time field that is updated when any record field is changed. – Other database vendors will be supported in the future.
• Users who are not administrators cannot see database login/password information. • Supports Windows and SQL Server Authentication. • ArcGIS Desktop Basic license for file Geodatabases. • ArcGIS Desktop Standard or Advanced license for SDE Geodatabases.
Maintain Geodatabase Demo
Maintain Geodatabase Workflow • Update lookup tables with the server and database names for the data source and the target Geodatabase. • Enter the coordinate systems and datum transformations relevant to your projects. • Add administrators for Maintain GDB. • Finally, add parameter records that define the source and target information to build and maintain feature classes.
Update Database Lookup Tables
Coordinate System and Datum Transform Lookup Tables
Coordinate System and Datum Transform Lookup Tables
Add Parameter Records
• This table defines everything the application needs to know to build and maintain one or more feature classes. • The target location can be either a file or SDE Geodatabase.
Maintain Geodatabase Processing Maintain Geodatabase
EDM Data Entry
(Exploration Data Management)
Run Update
An open ArcMap session.
Updates appear on the next map or table refresh. Update record with ID = 4 Add record with ID = 3000 Delete record with ID = 4000
3 Do the Updates
1
EDM Non Spatial Database
Detect Changes
Get record ID = 4 Get record ID= 3000
4
UpdatePointFeatures(ID=4) InsertPointFeature(ID=3000) DeletePointFeature(ID=4000)
Detect Changes
6
Update GeoDB record ID = 4 Add new GeoDB record ID=3000 Delete GeoDB record ID = 4000
5
Geodatabase
The first step is to build the target feature class. After that only updates are required.
Examine the results in ArcMap. There is a problem with record number 1.
Open a data entry session on the source database table.
Result of editing record 1. The ‘STATE’ is now TN.
Select the target feature class parameter record and click Update. One record updated. Record ID = 1.
To update the attribute table display, click the table button and choose ‘Reload Cache’.
The value for the ‘STATE’ field is now correct.
Update the latitude value to 37.
One record updated. Record ID = 1.
To update the map click ‘Refresh” or move the map. Update table display by reloading the attribute table cache.
The point is moved and the table is updated. The selection graphic remains. Clear the selection.
The SDE layer is now displaying correctly.
The original coordinate system definition for the record is WGS84. Change it to ‘NAD27’.
Updated record in database table.
Notice the difference between the latitude values in the table. Source record is NAD 27. Target feature class coordinate system is WGS84. The datum transform worked!
The File Geodatabase layer has not been updated.
Select the parameter record for the file Geodatabase and click Update.
Both layers are now updated.
Maintain Geodatabase • New and Deleted records in the data source will also be updated in the target feature class. • Multiple updates, inserts, and deletes in the data source are handled by one update run of MGDB. • Maintain Geodatabase includes batch capability that runs as a Windows scheduled task. • The ArcMap table and map refresh operation is required for display purposes only and is not required to update the feature class. • A live internet demonstration can be provided. Call or send me an email. • [email protected] • 775-750-1619
