9

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.

csterling
  • 704
  • 3
  • 7
  • 18
  • I googled an article for you, this seems to have a lot of information on what you're after: http://www.sql-server-helper.com/sql-server-2008/convert-latitude-longitude-to-geography-point.aspx – Kris B Nov 05 '15 at 18:25
  • I already found that article. It does seem to have the information I'm after, but it's for 2008, and is dealing with table creation. I'm specifically after view creation for 2012. Thanks – csterling Nov 05 '15 at 18:31

2 Answers2

23
  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)
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • The `Point` method is so great. Less nonsense with string concatenation. – Ben Thul Nov 05 '15 at 19:53
  • 1
    Just an FYI, this is correct, but depending on the data type you have for lat/long and their precision, this can cause issues. If the lat/longs are stored as a float, casting to a varchar will truncate your decimals so the point will be somewhere slightly different. Casting to a decimal of higher precision and then to a varchar will fix this issue. – hcaelxxam Nov 05 '15 at 20:18
  • @hcaelxxam When I tried to implement that code, I received this SQL Execution Error- http://imgur.com/DQifNJN The columns do get created in the view, however, but their values look like this- http://imgur.com/ObOTDbR I copied your example code exactly and only substituted out the view_name placeholder for the actual view. The error says Latitude values must be between -90 and 90 degrees. I reversed the lat/lon field names in the POINT args thinking maybe the lat/lon were reversed, but I still get the same error. I'll be googling error 24201 now. – csterling Nov 05 '15 at 21:04
  • 2
    @csterling, in GEOGRAPHY::POINT() the order is (lat,long), in GEOGRAPHY::STGeomFromText('POINT()') the order is (long,lat). If you run the query "SELECT MIN(latitude), MAX(latitude), MIN(longitude), MAX(longitude) FROM view_name" What results do you get? If your points are from 0 to 360, not in decimal degrees you may experience some issues. – hcaelxxam Nov 05 '15 at 21:09
  • @hcaelxxam ah! I think you've discovered the issue- http://i.imgur.com/aGyaMdZ.png So I need to clean the data first. Could I do some sort of exclusionary WHERE clause, to keep only records with values between 0 and +/-118? – csterling Nov 06 '15 at 16:53
  • @csterling, excellent, I am glad you found it! I don't see why you couldn't do that; it should work. I would expand to BETWEEN -180 AND 180 personally, but that is really just a matter of taste. – hcaelxxam Nov 06 '15 at 17:15
  • @csterling, you may have more issues now that I look closer. Your minimum latitude is approximately the same as your minimum longitude. You may have a mixture of lat/longs transposed. – hcaelxxam Nov 06 '15 at 17:20
  • @hcaelxxam there are two records in the dataset with incorrect lat/lon values. The lat should definitely not be -118, and fortunately it is the same record that has the max lon of 1307. – csterling Nov 06 '15 at 17:57
  • @hcaelxxam For some reason when I modify the WHERE clause to include the BETWEEN, the negative values I add in front of the numbers get shifted on space to the left. I get the same error about latitude needing to be between -90 and 90 with this statement: WHERE (latitude <> 0) AND (longitude <> 0) AND (longitude BETWEEN - 180 AND 180) AND (latitude BETWEEN - 90 AND 90) – csterling Nov 06 '15 at 18:02
  • @csterling, very interesting. If you alter the query to be SELECT TOP 1, does it work? If so, try increasing the number of records until the query fails (1000=good, 2000=good, 3000=bad, 2500=good, 2750=bad...2524=bad) and see what the values are where it fails. If that does not work however, see if you have longitude/latitude switched. If you are imputing longitude in the latitude spot, you will get values outside of -90->90. – hcaelxxam Nov 06 '15 at 18:58
  • wouldnt be easy `SELECT * FROM Table WHERE NOT latitud between -90 and 90` – Juan Carlos Oropeza Nov 06 '15 at 19:01
9

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.

Petey
  • 2,819
  • 1
  • 14
  • 23