1

I am currently working with some GIS data and using the Geography data type in MS SQL Server 2012. I am having some difficulty when trying to display the Latitude and Longitude of the Geography type using the View designer, specifically if I have a join with another table.

For example the below works fine in the view designer:

SELECT dbo.table1.ID, dbo.table1.LocationTypeID, dbo.table1.Location, dbo.table1.Location.Lat as LocLatitude, dbo.table1.Location.Long as LocLongitude 
FROM dbo.table1

However, this does not:

SELECT dbo.table1.ID, dbo.table1.LocationTypeID, dbo.table2.LocationTypeName, dbo.table1.Location, dbo.table1.Location.Lat as LocLatitude, dbo.table1.Location.Long as LocLongitude
FROM dbo.table1 INNER JOIN
     dbo.table2 ON dbo.table1.LocationTypeID = dbo.table2.ID

I found that if I remove the schema (dbo) and manually create the view via an SQL command it will create the View and run correctly, however, attempting to edit it later via the designer will display errors.

For reference the error the designer throws out is the "multi-part identifier could not be bound".

Whilst the manual creation of View can resolve the issue I would really like to find a way to solve this as it is bugging me but also causes issues for other people working on the project not knowing that now the only way to edit the View in future is to create an "Alter View" SQL command.

I have tried searching for an answer to this but have yet to find anything, normally this would not be an issue as I would just grab the Geography type object from the code and get the Lat/Lon there to be displayed but I need to create an export/View for a 3rd party.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Nathan
  • 25
  • 9
  • 1
    I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". – John Saunders Jan 11 '14 at 12:17
  • 1
    Probably easiest solution will be to give the tables aliases. e.g. `t1`, `t2` etc. then the designer will use `t1.X` instead of `dbo.table1.X` – Martin Smith Jan 11 '14 at 12:48
  • Thanks @MartinSmith that does it, so simple yet so much time spent on it hah. – Nathan Jan 11 '14 at 13:30

1 Answers1

0

Need to reference tables by aliases instead of directly. Answer provided by @MartinSmith

Nathan
  • 25
  • 9