Spatial SQL for the Geographer – STDistance Example

When I put together the Spatial SQL for the Geographer posts I never wrote anything about measuring distance.  Geographers often need to know the distance between locations, as distance decay is one of those things we find important.  Fortunately for us, measuring distance between points is pretty easy using Spatial SQL (and pretty fast).  The following query demonstrates an example of measuring distance between a set of defined points from the populated places data available in the sample data.

t1.Id as ID, 
t1.NAMEASCII as t1_Name, 
t1.SOV0NAME as t1_SOV0NAME, 
t1.Latitude as t1_Latitude, 
t1.Longitude as t1_Longitude, 
t2.ID as t2_ID, t2.NAMEASCII as t2_Name, 
t2.SOV0NAME as t2_SOV0NAME, 
t2.LATITUDE as t2_Latitide, 
t2.LONGITUDE as t2_Longitude,
Geography::Point(t1.Latitude, t1.Longitude, 4326).STDistance(Geography::Point(t2.LATITUDE, t2.LONGITUDE, 4326)) as Distance_Meters
from dbo.Populated_Places t1 cross join dbo.Populated_Places t2
where t1.SOV0NAME = 'United States' and  t2.SOV0NAME = 'United States'

You will notice that in the query a few different things are happening:

  • A self cross join is used to take each record and join it to every other record.  This is done to get the lat/long combo for each city and matched up with every other city.  There is probably a better way to do this, but for this example it works.
  • In order to calculate distance two points are generated on the fly from the columns by calculating the point with Geography::Point(LAT, LONG, SRID).  Setting the SRID is important, as that will impact the measurement.  More often than not the user will be using an SRID of 4326 (WGS84), unless they are using projected data.  For more info on SRIDs check out this handy Wikipedia article.
  • You could replace Geography with Geometry when generating the points.  Doing so would lead to vastly different measurement results because of the way distance is calculated on the plane or on the spheroid.  When using the Geography data type the distance results are returned in meters while with the Geometry data type the results are reported in decimal degrees.
  • Once the points are created on either side of the STDistance method the query can be run.  The documentation provides a couple notes about using STDistance. The SRIDs need to match between the points and there is some error involved when measuring distance.  The distance measurement error is important to understand if one has tight tolerance they need to adhere to.

The results of this query will return a set of columns with the measurement in meters from every record to every other record for US cities.  With some additional coding you can generate a distance matrix, find the nearest locations, or create line features between points pretty quickly.  This query can also go pretty fast, if the proper indexes are generated and called in the query it should take less than a minute.  Not to bad when returning over 600k records.

Spatial SQL for the Geographer – Part 3 – More Basic Spatial SQL Scripts

The tutorial continues…

If you haven’t already read the first three parts of this guide check out:

The first three parts of the guide give some background information on SQL Server, covers some basic spatial and database topics, and provides links to download some software and data. Once the user has completed the first three part of this guide they will be able to understand this tutorial, which will provide some additional basic spatial SQL scripts.


In the last tutorial several scripts were introduced and examples were given.  In the last tutorial I covered the process to validate shapes, calculating centroids, and generate buffers.   In this tutorial several additional scripts will be covered, including generating the bounding box/extent of a feature, intersection and union operations, and difference operators.

Calculate Extent

Calculating the extent of a feature is something I use from time to time.  To find these values, such as the northern bounding latitude or eastern bounding longitude, can be calculated through Spatial SQL.  Spatial SQL has a built in method that will calculate the bounding box of a record, STEnvelope().  In its simplest context the user can generate the bounding box for any feature in a spatial table:

use Spatial_Database

geom.STEnvelope() as boundingbox
from dbo.States_Provinces
where NAME_1 = 'Massachusetts'

This example will return a field called “boundingbox” for the record where NAME_1 equals ‘Massachusetts’ that contains the minimum area rectangle that encompasses the shape.    The data in the “boundingbox” field is relatively useless the user adds some additional code to the query.  Building on the example provided by Microsoft the user can add the ToString() method to the query:

use Spatial_Database

geom.STEnvelope() as BoundingBox,
geom.STEnvelope().ToString() as BoundingBoxString
from dbo.States_Provinces
where NAME_1 = 'Massachusetts'

Here the user will see a new column, “BoundingBoxString” that returns a polygon with the five points that represent it.  You may ask yourself, why are there five points when you only need four to create the bounding box?  Well, for SQL Server to create a polygon the first point needs to be represented twice, so that the polygon closes itself properly.


The intersect table operator is one of the most popular tools in the GIS work belt. Intersect is an overlay operation, where the area of the input feature is used as the basis to select features from the second feature, the intersect feature.  The following illustration will explain what an intersect is better than I can with words…

Source: Esri

In spatial SQL the STIntersects() tool is straight forward and easy to use.   However, it does not return a shape as other overlay methods do.  When run, STIntersects() will return a value of one if the two records spatially intersect and a zero if they do not.  Also, this method only tests the relationship between a single record in one table against a single record in another table.  The following provides an example of intersecting a single line object against a polygon object:

use Spatial_Database

DECLARE @poly_geom geometry
select @poly_geom = geom.STAsText()
From dbo.States_Provinces
where dbo.States_Provinces.name_1 like '%Massachusetts%'

DECLARE @line_geom geometry
select @line_geom = geom.STAsText()
from dbo.Roads
where ID = 2974

SELECT @poly_geom.STIntersects(@line_geom) as IntersectValue, @poly_geom as input1, @line_geom as input2

The Massachusetts record form the State and Provinces table is set as the polygon of interest and a single line object from the roads table is also selected.  Now, I know that the line object is in Massachusetts so the value returned in IntersectValue field should be a value of one.

Look, it worked!


Another spatial overlay method available in spatial SQL is STContains().  This is a yes/no operation.  If a geometry instance is completely within another geometry a value of one is returned. If the geometry instance is not wholly inside the other, a value of zero is returned.  The following provides a simple example of STContains():

use Spatial_Database

DECLARE @poly_geom geometry
select @poly_geom = geom.STAsText()
From dbo.States_Provinces
where dbo.States_Provinces.name_1 = 'Massachusetts'

DECLARE @pointgeom geometry
select @pointgeom = geom.STAsText()
from Spatial_Database.dbo.Populated_Places
where NAMEASCII = 'Boston'

SELECT @poly_geom.STContains(@pointgeom) as ContainValue, @pointgeom as input1, @poly_geom as input2

In this query I am simply testing if the point that represents the city of Boston is within the polygon that represents the state of Massachusetts.  After all the variables are declared the select statement tests the point against the polygon.  The results, in the ContainValue field returns a one, meaning that the selected point is within the selected polygon.


The union operator is an overlay analysis, like the intersect, however the results of a union are the combination of the two features, with overlapping areas obtaining the attributes of both.  Again, let’s turn to Esri for a nice graphic:

Source: Esri

Like the intersect operation, the STUnion() operation analyzes the spatial relationship between two different records.  The basic union query will return the area represented in the union.  In the following example the query will select two countries from the world countries table to union.  The results of the query return the unioned geomerty of the two selected polygons.

use Spatial_Database

DECLARE @poly_geom geometry
select @poly_geom = geom.STAsText()
from dbo.world_countries
where dbo.world_countries.ADMIN = 'Germany'

DECLARE @poly_geom2 geometry
select @poly_geom2 = geom.STAsText()
from dbo.world_countries
where dbo.world_countries.ADMIN = 'Switzerland'

SELECT @poly_geom2.STUnion(@poly_geom) as UnionGeo

The initial input are two distinct polygon records, one representing Germany and the other, Switzerland. If the user were to query these two records from the table they would see the following spatial result:

Two distinct records

However, during the union the geometry of these two records are morphed into a single feature, as seen below:

I don’t think Switzerland will union with anyone anytime too soon.

Using the example script the user could then add additional fields to the select query or perform any number of additional operations on the unioned record.


The final spatial operator that will be covered will be STDifference().  The difference overlay operation uses two layers with the area of one being removed from the overlapping area of the other.


In spatial SQL the difference method, STDifference(), returns the geometry of the difference.  In the following example the user will find the shape that is left behind between the difference of the United States and Massachusetts:

use Spatial_Database

DECLARE @poly_geom geometry
select @poly_geom = geom.STAsText()
From dbo.States_Provinces
where dbo.States_Provinces.name_1 = 'Massachusetts'

DECLARE @USA geometry
select @USA = geom.STAsText()
from dbo.world_countries
where dbo.world_countries.ID = 236

SELECT @USA.STDifference(@poly_geom) as Part1andPart2

The results, as seen below, displays the area that Massachusetts represents in the world_countries table.

I bet there are some parts of the country that wouldn’t mind this…


Well, that is it for this tutorial.  If I have some time over the next month I’d like to explore spatial indexes within spatial SQL and perhaps cover some more in-depth spatial queries.  All these queries will work within 2008 R2 and the user can, if desired, use tables from ArcSDE within the queries.

The tutorial and the examples are very simple and I do not profess to be an expert in spatial SQL, so, if there is a way to represent any of these queries more efficiently let me know.

Spatial SQL for the Geographer – Part 2 – Basic Spatial SQL Scripts

The tutorial continues…

If you haven’t already read the first two parts of this guide please read:

The first two parts of the guide give some background information on SQL Server, covers some basic concepts, provides some links to download some software and data.  Once the user has down completed the first two part of this guide they will be able to understand this section of the guide, which will provide some basic spatial SQL scripts.


Here the user will learn about a few basic Spatial SQL scripts.  Nothing too complicated, but enough to get the user started.  Hopefully the user will start to think about ways to expand the scripts into their own ideas.

This tutorial will cover the MakeValid, calculating centroids, and buffering a polygon.

Checking the Validity of Shapes

Before the user starts to calculate buffers, generate centroids, union or intersect tables or perform any other Spatial SQL methods the user should first run the MakeValid command. MakeValid will check the shape to see if the data set breaks any of SQL’s rules for storing the geomerty of the shape. As mentioned in the first part of this guide the geometry datatype is very flexible, but the user should run MakeValid first before doing any analysis because there still might be self intersecting polygons, bad ring order or any other number of topoligical errors within the dataset.

Many GIS programs won’t worry about these problems on the surface, but SQL will and users should perform these queries on all spatial datasets, especially polygons data, as it is usually full of errors.

This first example will update the State_Provinces table, checking the geometry of each record in the table.  This query will update the spatial data type column in a table so that the shape is valid, meaning that the shape meets SQL’s requirements for the datatype

In this example the State_Provinces table is being checked. The user will need to
run the MakeValid command against the column that stores the spatial data type, in this case, the geom column.

use Spatial_Database
update dbo.States_Provinces
set geom = geom.MakeValid()

This query make not catch all errors in the data set.  The following scripts will check the data for validity as well.  The third script will check to see if any of the records still contain any invalid geometries.  These scripts were modified for the geometry datatype from, a great resource the user should check out.

--Fix invalid shapes
update dbo.States_Provinces
set geom = geometry::STGeomFromWKB(geom.MakeValid().STAsBinary()

--Correct the order of the points in the polygon ring
update dbo.States_Provinces
set geom = geometry::STGeomFromWKB

--Check to see if any records still have any invalid geometries. 
--The results should return an empty table.
select  ID, geom.STIsValid() as ValidCheck,
geom, geom.ToString() as Spatial_Text_String
from dbo.States_Provinces
where geom.STIsValid() != 1

When these queries are run the geometry in the table be updated.  From there the user will be able to repeat this process for each of the tables loaded in the first tutorial.  If the user does not run the MakeValid query some of the examples in this tutorial may not work correctly.

Calculating Centroids

The next set of queries will take a polygon layer, calculate the centroids, and convert the centroids into latitude and longitude.  Using the world_countries table in the Spatial_Database database the user can run this query and return the name of the feature and the X and Y of the polygon’s centroid.  This query uses a subquery where the centroid is first calculated.  Once this is done the outer query converts the centroid into latitude (Centroid.STX) and longitude (Centroid.STY).

select U.Region_Name, Centroid.STY as Longitude,
Centroid.STX as Latidude
(	 select geom.STCentroid() as Centroid, NAME as Region_Name
	 from dbo.world_countries

The following will expand on the previous query.  In the next example the user will calculate the centroid for a specific polygon and then buffer the centroid.  In the subquery a where statement is set to select a specific record that meets a certain requirement. In this example the user will select records from the name_1 column that equal ‘Massachusetts’.  Also, when the STCentroid() method is called no parameters are needed, unlike other spatial methods which can require several methods.

In the outer query the centroid is buffered by 0.05 decimal degrees, set in the STBuffer method. Why are decimal degrees used here?  Well, it is because the data were loaded in as WGS84.   There will be a future tutorial on how to tackle this problem.

The user can run this query and return a buffer around the centroid for the state of Massachusetts.

select U.Centroid, Centroid.STBuffer(0.05) as buffer, U.StateBound
(	select geom.STCentroid() as Centroid, geom as StateBound
	from dbo.states_Provinces
	where name_1 =  'Massachusetts'
) U

Calculating a Buffer

The last query that will be demonstrated will introduce the buffer, even though I just gave an example. The buffer operation is a common operation within GIS tools. The following script will give a basic example of how to generate a buffer in Spatial SQL.

The user will notice that the shape column, geom, is set and the buffer method, STBuffer, is called. STBuffer accepts a parameter, the size of the buffer.  In this example the buffer size will be 0.05 decimal degrees.  Also, in this example a where statement is set to query a specific record from the table. When this is done only that record would be buffered. Again, the States_Provinces table is used.

select geom.STBuffer(0.05) as buffer
from dbo.States_Provinces
where name_1 =  'Massachusetts'

What’s Next

That is it for this section of the tutorial. As I said earlier, pretty basic stuff. In the next tutorial I will get into some other topics including unions, intersects, spatial indexes, joins and other methods and operations will be covered. And, it looks like I may end up creating several more tutorials than I originally planned!

Unitl next time…

Spatial SQL for the Geographer – Part 1 – Welcome to Spatial SQL

Before we begin I would like to make it clear that learning all the capabilities and limitations of SQL Server and Spatial SQL cannot be covered in three short lessons.  If you believe you might need some more training please visit the Microsoft SQL Transact reference guide.

What is Microsoft SQL Server?

Microsoft SQL Server is a relational database system that stores data and provides several mechanisms to retrieve stored data.  One of the beauties of relational database systems is that they allow you to effectively store a variety of data sets and they are both scalable and securable, and in my experience, much more trustworthy than any flat file.

There are a number of good references out there explaining SQL Server in much more detail than I would attempt.  I recommend that you check a couple of them out:

Explanation of Spatial Data Types

As you probably know by now a column in a table or view can be assigned one of many different data types.  Most are familiar with the traditional data types such as varchars, integers, bits, floats, or decimals.  With SQL Server 2008 two new spatial data types were introduced.  These data types support the storage of spatial data, and are broken up into two different types.  Their characteristics are important to understand:

  • Geometry:  Primary characteristic – spatial data stored in a planar system.
  • Geography: Primary characteristic – spatial data stored using the ellipsoid.
  • Here is a great blog post that describes the spatial data types in more detail.


Step One: Getting Started

The user first needs to download some software and data to their local machine.

Downloading Microsoft SQL Server 2008 R2 may take a few minutes.  The user needs to make sure that they read the download and installation instructions before they proceed. Also, when installing the software the users needs to pay particular attention to the installation instructions.

Note:  This tutorial was completed on a 64 bit machine running Windows 7.  I cannot guarantee that any of this will work on anything different.

Notes about the Data.  The populated places, countries, and states and provinces data sets were downloaded from Natural Earth and the roads data set came from The data represent the three different vector data types, points, lines and polygons. They also provide a variety of query options to work with during for the tutorials. Also, I renamed the data from the original names so for clarity during the tutorials.

Once you download the data I recommend first viewing it in a GIS program to make sure you downloaded it correctly.  Here the user will see the four data sets in Quantum:

Tutorial Data

Step Two: Create a Database

Now that the software has been downloaded the user can open Microsoft SQL Server Management Studio.  To do this the user will select to Start–> All Programs–> Microsoft SQL Server 2008 R2–>SQL Server Management Studio.  Since this will probably be the first time the user opens MSSQL it may take a couple minutes to configure and load.

Next, the user will be prompted to connect to a server.  In this case the server is the local machine.   In this example I connect to my local machine instance and use Windows Authentication.  Unless you configured your SQL instance differently you will connect the same way.  Once these options are selected click connect.

connect to server

Now that the user is connected to the server they can create a new database.  First, the user will expand the database instance.  Then the user will right click on the database folder and select New Database from the menu.

Create Database

The new database window will now open.  Here the user can configure a number of settings before the database is created.  In this tutorial we want to name the database Spatial_Database which is set in the Database Name text box.  Another option I like to configure is the location of the data and log files.  This is done by clicking the … button under the Path column.  I recommend storing the database in a location that has plenty of room to grow, as the default location on the C drive may not have space to expand on your local machine.

Change Location

Once the settings are all set the user will click OK to create the database.  The user can refresh the Databases folder to see the new, empty database.

Spatial Database

The user is the system admin on this sever and this database be default.  This means the user has permissions to create databases, delete databases, create tables and load data into them.  With these privileges already in place the admin user can load data into the database without any further configuration.

Step Three: Load Data into the Database using Shape2SQL

Using Shape2SQL is really easy.  The user will first open Shape2SQL and add a shapefile that will be loaded into the database as an individual table.  To do this the user will first navigate to shapefile stored on the local machine by clicking on the Select Button.

Select  Shapefile

The next step is to select the server and database where the data will live.  The user will click on the Configure Button to do this.  In the Configure window the user enter the name of the server in the Server Name window.  The user will then select Use Windows Authentication and then select the appropriate database.  In this tutorial the database has been named Spatial_Database.  Once this information has been configured the user will click OK.

Configure Server

Now, back in the uploader window the user has several additional options to configure before loading the data into the database.  First, the user needs to select the data type. The user will select either the geometry or geography data type.  In this tutorial we load the data as the geometry data, as it is more flexible, in terms of what it will accept, than the geography data type.

The second setting that needs to be set is the name of the geometry column.  The default for this setting is ‘geom’, but the user can change this if desired.  Remember, all the tutorials will use the ‘geom’ column.

The user will then make sure the Create Spatial Index option is selected.  This will ensure that a spatial index is built, which will improve the performance of the table in SQL Server.  Finally the user will set the name of the table in the Table Name window.

There are a few settings that do not need to be selected or altered.  Since this tutorial is using the geometry data type the user does not need to set the SRID.  The user can also change which columns are loaded into the table.  By default all columns are taken from the shapefile and loaded into the table. If a user does not want certain columns they will simply uncheck the columns they don’t want.  Once all the settings are selected the user will select Upload to Database.  Again, I may use all the columns in future tutorials, so you are best off keeping all of them.

Once the user clicks Upload to Database the shapefile will be loaded into the database as a table and the spatial index will be created.  Depending on the size of the shapefile and the power of the user’s machine it may take several minutes to load the table into the database.

The user will repeat these steps and load all four shapefiles into their database.  Once that is completed the user can proceed to the next section of the tutorial.

Step Four: Perform a Basic Query

Now that the shapefiles have been loaded into SQL Server the user can start to perform some queries.  Now, if the user does not have any SQL experience at all it may make sense to read through this tutorial from Microsoft.

The first example query is probably the most simple SQL query one could perform. In this first example I will use the dbo.States_Provinces table.  The user will open SQL Server, connect to their server, and then click the New Query button.  The user will then paste the following into the query window:

use spatial_database

select top 100 * from dbo.States_Provinces

The results will return the first 100 records from the dbo.States_Provinces table.  In SQL Server the user will notice there is a Results tab and a Spatial results tab.  The results tab will display the tabular data and the spatial results will display the spatial data.  The Spatial results tab will only appear if there is spatial data returned in the query.

Step Five: Set a Where Statement to Query Specific Data

The user can narrow down what is selected by incorporating some basic SQL statements, such as a where statement.  The basic premise of a where clause is to limit the data returned from a query. The user is setting a condition in regards to what they want returned.  For example, let’s say we only want the return records from the States_Provinces table that are for the United States.  If the user explores the table they will set that this information is stored in the Name_0 column. However, we are unsure of the exact syntax of “United States” so we can use a like operator in the where clause. For example:

use spatial_database

select * from dbo.States_Provinces
where NAME_0 like '%United States%'

The user will see only those records that have ‘United States’ in the Name_0 column. If the user clicks on the spatial results tab they will see the spatial representation of those records.

Spatial Results

Alright, that is it! We have completed the first part of this tutorial. The user has downloaded the appropriate software and data, installed the software, loaded a number of shapefiles using Shape2SQL into SQL Server, and have completed two basic queries.

Before the next tutorial I recommend the user, if they are light in their SQL experience, read up on some basic queries from either the W3 Schools or from Microsoft.

The next part to this tutorial will cover some more Spatial SQL queries.  I’ll go into more detail about some spatial operations and provide a number of examples.  If there is anything the you want to see let me know!

Spatial SQL for the Geographer – Introduction

During WhereCamp Boston one of the impromptu break-out sessions was on spatial SQL. In WhereCamp fashion the person who proposed the session was looking to learn more about spatial SQL and its many uses.  With about 20 people in the session I volunteered to give a few examples of spatial SQL within Microsoft SQL Server.  Now, I was not prepared to give an in-depth or very informative session.  In fact, it was probably the worst presentation I have ever given.  I went through a number of examples of of some basic principals and I said I would put together a “Hello World” example of spatial SQL sometime after the meeting.

But, to complete a “Hello World” example we need to develop some background first.  In order to develop the guide I will be putting it together from the ground-up.  I use Microsoft SQL Server everyday, but I am GIS professional first, and many of the readers of this site are also GIS professionals.  Therefore, I will write the how-to from this point of view.  If you are a database professional this guide might be a little boring, but if you are a GIS professional I hope you’ll get something out of it.

The guide will be broken into three additional posts.  The first will be cover some background and vocab.  Part two will discuss basic spatial SQL syntax, and the third part will walk through how to expand basic scripts into larger sql processes. Once the guides have been finished I will provide links below.

Part 1Welcome to Spatial SQL – 11/14/2011

Part 2Basic Spatial SQL Scripts – 11/21/2011

Part 3 – More Basic SQL Scripts – 1/2012

Part 4 – Working Spatial SQL into Larger Processes – 2/2012

*Update – 12/4/2011- with the holidays and other commitments I am going to push back the last two installments!

This guide is being developed for use with Microsoft SQL Server.  That’s the DBMS that I use on a daily basis.

Before I release these posts feel free to browse some resources on spatial SQL and Microsoft SQL Server:

The Query Layer Query

The Problem

I am a big fan of the query layer in ArcGIS 10.  I routinely create and manipulate spatial data that lives in the geography data type in SQL Server.  The query layer tool has opened up a whole new set of analysis and visualization possibilities for a number of large data sets I work with.  Overall, I am a big fan…

But, I’ve run into a problem.  Using the query layer tool I couldn’t connect to a number of SQL Server databases.  When I would try to connect to certain databases I would get this cryptic error.

What Does this Mean?

What is going on? Is this an actual error?  I am missing some font pack therefore displaying the characters I see on screen?

With this useless error I started to trouble shoot.  I knew I had SQL Server databases that worked correctly and some that generated this error.  The only difference between the working and non-working databases was the length of the database name.  As it turns out the length of the database file name that is allowed by the query layer is limited to 31 characters.  Anything greater than 31 characters will generate this error.  This is not listed in the query layer documentation.

What the Heck!

In my job I work with dozens and dozens of databases that have a prescribed naming convention.  Sometimes, these SQL Server database names can get long, but no where near the SQL Server character limit.  Within the Esri software family there are described limits to database names.  When using ArcSDE the name of the database is limited to 31 character, which is understandable.

However, if a user adds a SQL Server database to the “Add OLE DB Connection” tool in ArcCatalog the database name can be more than 31 characters, but if the user adds a SQL Server database with a name greater than 31 characters to the query layer tool it will error out.  What a bummer.

What’s Next

I posted this question in the Esri forums and the response I got from the Esri rep was accurate, but this limit is a pain to those want to use this tool.  In the online documentation for the query layer the text states “Query  layers allow both spatial and nonspatial information stored in a DBMS to be easily integrated into GIS projects within ArcMap.”   I agree with this statement 100%, unless my database name has more than 31 characters…

By limiting the length of the database name Esri is saying that I cannot take full advantage of the tools provided by SQL Server.  Also, if I can only use 31 characters, please document it in the query layer documentation.  Either this is a bug, an undocumented limitation that needs to be addressed, or it is documented somewhere and I can’t find it (which is a real possibility).  I am thinking that this problem is probably a 32 bit issue, in fact a couple of my coworkers are thinking the same thing.  I’m using a 32 bit software in a 64 bit world!

As a geospatial database admin (GeoDBA for short), I need to make a decision that will impact the workflow for my organization.  I have business requirements that limit me from renaming these business critical databases, but I have new business requirements that are greatly benefited by the query layer functionality.  Do I rename databases (and modify the tools that access this information) or do I wait for Esri to fix the problem? Moving forward I’ll try to limit the number of characters in my databases but I’m using a SQL Server database, not an SDE database. Why is Esri limiting what I can do with that piece of software?  Unfortunately, it may be faster for me to rebuild everything than it would be to get a solution from Redlands.

I’ll submit this problem to Esri tech support soon.


And…I am running ArcMap 10 with SP 2 using SQL Server 2008 R2 that is fully patched and updated.  The error described has been repeated on several machines within my organization.

Until next time GIS geeks!