Creae View from Sql Table

Posted on 2011-05-02
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 92

    Expert Comment

    by:Patrick Matthews
    CREATE VIEW dbo.MyView AS
    SELECT Postcode, Latitude, '-' + Longitude AS Longitude
    FROM SomeTable

    Open in new window

    LVL 13

    Accepted Solution

    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

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now