SQL Server 2008 – Return Geography Datatype Lat/Long in View

cspatialsql serversql-server-2008sql-server-2008-r2

Hopefully the question is fairly self-explanatory.

I've been ferreting through the, what appears to be quite minimal/poor microsoft documentation regarding both views and geography datatypes in the sql server 2008, and I think the answer is "no, geography datatypes are either not supported or minimally supported within a view"; but I'm not entirely sure, and I'd love some corroboration.

I've been attempting to add geography data to an existing view. And my end goal is to retrieve longitude/latitude data from the field in question.

Here's the select statement (simplified) that works when applied to the table but doesn't work when defined within the view:

SELECT     
    [dbo.vendor.location].Lat AS latitude,
FROM         
    dbo.vendor

Note that [dbo.vendor.location] is the field, and Lat is the latitude property of the geography datatype.

And when that sql is run within the view, the following error is returned:

The multi-part identifier "dbo.vendor.location.Lat" could not be bound

Edit:

Note that I've also tried the following query, with similar results:

SELECT     
    [dbo].[vendor].[location].Lat AS latitude,
FROM         
    dbo.vendor

In this case, SQL Management Studio removes the quotes and returns the same "multi-part … could not be bound" error.

Best Answer

Don't reference it as [dbo].[vendor].[location].Lat in the SELECT list.

use simply vendor.location.Lat or location.Lat or define a table alias and use that.

CREATE TABLE dbo.Vendor([location] geography)

GO

/*Works fine*/
CREATE VIEW V1
AS
SELECT     
   v.location.Lat AS latitude
FROM         
    dbo.vendor v

GO

/*Fails*/
CREATE VIEW V2
AS
SELECT     
   dbo.vendor.location.Lat AS latitude
FROM         
    dbo.vendor

The use of the column dbo.vendor.location on its own (without the CLR property call) does in fact still work in the column list but the use of "More than two-part column name" is on the list of deprecated TSQL features anyway.

Related Question