Create Geometry/Geography Field from Latitude & Longitude fields (SQL Server)

Question

I have a view that contains two fields for latitude and longitude, and I would like to create a new view that converts these lat/lon fields into a geometry/geography field (unsure which is most appropriate for ArcGIS). The fields in the original view are of double type, and I would like them cast as a spatial type in my new view.

Currently I am unsure how to cast these fields as spatial types. All the other similar questions on Stack Overflow never got me a working solution, so I apologize if this question appears to be a duplicate, but hopefully a clearer example could help others as well.

My new view is written pretty simply-

SELECT * FROM view_name WHERE (latitude <> 0) AND (longitude <> 0)

How can I create this new view, based on an existing view, and cast the two fields (or create a new spatial field populated with the lat/lon values) as a spatial type?

I am using the SQL Server Management Studio, 2012 edition. Please let me know if I omitted any pertinent information. I am happy to provide as many details as I can.

Answer 1

  SELECT  *, 
          geography::STGeomFromText('POINT(' + 
                CAST([Longitude] AS VARCHAR(20)) + ' ' + 
                CAST([Latitude] AS VARCHAR(20)) + ')', 4326) as GEOM,

          geography::Point([Latitude], [Longitude], 4326) as SAME_GEOM

  FROM view_name 
  WHERE (latitude <> 0) AND (longitude <> 0)

Answer 2

Juan's answer put me on the right track. When dealing with Geometry I initially used

geometry::Point([Latitude], [Longitude], 4326) as Geom

But when I tried to access the longitude with Geom.STX it would return the latitude value. And Geom.STY would actually return the longitude value.

I had to use

geometry::Point([Longitude], [Latitude], 4326) as Geom

Just wanted to provide this for anyone else that runs into some issues involving the geometry type.