Creae View from Sql Table

Posted on 2011-05-02
Medium Priority
Last Modified: 2012-05-11
Dear Experts

I have a sql table consisting of the following fields:

Postcode: (Char 10)
Latitude: (nvarchar(7))
Longitude: (nvarchar(7))

See below some example records from this table:

PostCode                     Latitude                   Longitude
TD5 7PR                       55.4742                  2.55399
TD1 1NU                      55.6816                   2.03314

I would like to be able to create a view of this table.  My problem is that I need to convert the longitude value to a negitive value - for example see below what I need from the example above:

PostCode                     Latitude                   Longitude
TD5 7PR                       55.4742                  -2.55399
TD1 1NU                      55.6816                   -2.03314

Can anyone suggest the syntax to accomplish this?


Question by:grantballantyne
LVL 93

Expert Comment

by:Patrick Matthews
ID: 35506124
SELECT Postcode, Latitude, '-' + Longitude AS Longitude
FROM SomeTable

Open in new window

LVL 13

Accepted Solution

dwkor earned 2000 total points
ID: 35506148
Something like that. I'd suggest you to switch to numeric/float for geographical coordinates

CREATE VIEW dbo.PostCodeLocations(Postcode, Latitude, Longitude)
	select Postcode, convert(numeric(9,6),Latitude) as Latitude, CONVERT(numeric(9,6),Longitude) * -1 as Longitude
	from dbo.MyTable

Open in new window


Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question