Tag Archives: database
Our lives are filled with things we often overlook. Case in point, what are the little blue reflectors on the road that you see on the way home from work every day? I’ve tried to establish a pattern. Are they marking the center of the lane? Sometimes they’re right on the road center line, sometimes they’re right in the middle of your wheel path, thump thump, and sometimes they’re barely on the road at all. If you look, you’ll see them all the time; you just have no earthly clue as to what they actually do. Continue reading
Are there reserved words for File Geodatabases?
Here at Esri Support Services, we often field questions related to reserved words within databases. While these are usually determined by third-party sources, such as the host RDBMS on which a database resides, Esri’s File Geodatabase is the exception, is proprietary, and contains its own reserved words. The following are the reserved words: ADD, ALTER, AND, AS, ASC, BETWEEN, BY, COLUMN, CREATE, DATE, DELETE, DESC, DROP, EXISTS, FOR, FROM, IN, INSERT, INTO, IS, LIKE, NOT, NULL, OR, ORDER, SELECT, SET, TABLE, UPDATE, VALUES and WHERE.
With the above list of reserved words in mind, please remember that all geodatabases are compatible and that importing or exporting data into another ArcSDE or Personal Geodatabase enforces reserved words found within each disparate RDBMS. It is worthwhile to be aware of these limitations when developing a data model.
Does ArcSDE have reserved words?
We are often asked whether or not there are ArcSDE reserved words that you should be aware of when creating a data model or feature class.
While all geodatabase field names must be SQL compliant and only allow an underscore (_) as a special character (No other special characters are permitted), it is important to note that ArcSDE does not have reserved words. These are determined by the host RDBMS on which ArcSDE resides. This also holds true for Personal Geodatabases, as they are built off of Microsoft Access.
Although not reserved words, the following is a list of words that are fully qualified within an ArcSDE geodatabase: FID, AREA, LEN, POINTS, NUMOFPTS, ENTITY, EMINX, EMINY, EMAXX, EMAXY, EMINZ, EMAXZ, MIN_MEASURE, MAX_MEASURE.
For a complete list of reserved words within each of the supported RDBMS’, please follow the links below:
- DB2 Reserved Words
- Informix Reserved Words
- Postgresql 8.3 Reserved Words
- Oracle Reserved Words
- SQL Server Reserved Words
- Microsoft Access (Personal Geodatabase) Reserved Words
Please post any questions or comments that you may have in the ‘Comments’ section below. NOTE: You must be logged into your Esri Global Account to post comments.
- Jon D., Geodata Support Analyst, Esri Support Services
Hello, this is Sigmund Frodo…er…Jim W., Geodata Analyst at ESRI Support Services in Charlotte, NC with some thoughts that might help to ‘enlighten’ you on the inner life of your relational database management system (RDBMS). Just as some humans will visit a psychiatrist to help them understand the inner workings of their minds, databases have been known to benefit from the help of a qualified professional practitioner who can give meaning to the vast number of disassociated bits and bytes that swirl around deep within their digital brains. Regular ‘analysis’ of a database can be a good thing and can help to maintain an optimum level of equilibrium necessary for quickly answering the ‘BIG’ queries of life, such as, “Where can I find a good burger?”
Now, geodatabases have been known to have a ‘spatial’ complex, and periodically analyzing them will keep them grounded in reality, thereby improving overall performance. But first, we digress with a brief interlude into the realm of statistics…
Analyzing a database involves collecting statistics that help us to get a handle on the nature of the data that is contained within the database. These statistical facts about objects such as tables, columns, and indexes are then stored internally within the RDBMS’s data dictionary tables. They help the database optimizer to determine the optimum path to data, ensuring the fastest response time for queries, while at the same time minimizing the cost of database resources. For example, getting a count of how many rows of data are contained in each table may help the database optimizer determine whether or not to use an index, or how best to join two tables when selecting the best execution plan for a given SQL statement.
What kinds of statistics are gathered? Analyzing a table can retrieve and store metadata such as the number of rows in each table, the average row length in bytes, the average column length, the minimum and maximum values contained in each column, and the number of null values in a column. Other statistics can describe the data by looking at the number of distinct values contained in a column (known as its cardinality), as well as by constructing histograms that give an idea of how the data is distributed (whether the data is evenly distributed throughout its range, or is it clumped together with a large number of rows containing similar values). Clinical terms such as platykurtosis, leptokurtosis, and skewness come fondly to mind, but that’s another story for another time…
So, you may ask, “Just how are statistics collected?” It can be as simple as pointing and clicking on an individual feature class in ArcCatalog and then analyzing. You could also use an ArcSDE command line tool called sdetable, or the Analyze geoprocessing tool available in ArcToolbox. Or, you might even want to set up an automated scheduled task using the tools to gather statistics provided by the specific RDBMS that you’re using.
For a more in-depth look at analyzing a geodatabase and collecting statistics, most of what you’ll need to get started is located at: About updating geodatabase statistics.
Just remember, since data can be dynamic and ever changing, it’s a good idea to frequently analyze in order to pick up any changes that may occur in the database. In a healthy geodatabase, where numerous edits may occur on a daily basis, it could be wise to schedule frequent analysis sessions. Regular analysis can help improve the display time of versioned feature classes, as well as speed up other edit processes where fast query response times are required to keep you from waiting. And best of all, there’s no charge!
- Jim W., Support Analyst – Geodata Unit, ESRI Support Services, Charlotte, NC.
A patch is now available on the ESRI Support Website for the ArcGIS Server Geoportal extension 9.3.1. It is called “ArcGIS Server Geoportal Extension 9.3.1 Distribution Database Scripts Patch” and fixed the following issue:
When the Geoportal extension 9.3.1 was initially released and shipped to customers, the database upgrade scripts and the Linux environment database scripts were not included. Users who want to upgrade their existing GPT 9.3 geoportals to 9.3.1 or users who want to implement a 9.3.1 geoportal on Linux will require these scripts.
This patch provides the missing scripts by installing them into the Geoportal extension 9.3.1 distribution folder, C:ESRIGeoportal Extension 9.3.1Database Scripts, by default. It should be installed by geoportal system administrators on the machine where the original Geoportal extension 9.3.1 distribution setup.exe was run. The patch installer checks for the presence of the distribution installation, and will not install the scripts unless that original distribution installation is found.
- SDI Solutions Team, ESRI