0

I have two separate Columns, one for Lat and one for Long. What I need to do is to cast both into a third column that I have called Shape and is of type Geometry.

I was able to find the below as to how to cast into geometry but am unaware if this is the best way. Is also like suggestions as to how to drop it into the third column.

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 **table**
  WHERE ('latitude' <> 0) AND ('longitude' <> 0) AND ('latitude' IS NOT NULL) AND ('longitude' IS NOT NULL)
  • 1
    The second method in your select statement is probably the best option (easiest to use). The quotes around the latitude and longitude in the where clause are incorrect. – MickyT Nov 4 '16 at 22:43
1

I think, You should use static method on geometry type, not geography. Point method on geography returns geography instances, not geometry. Easiest way how to populate third column is simple update. You can use something like this:

update <table_name>
  set shape = geometry::Point(longitude, latitude, 4326)
where 
  latitude <> 0 
  and latitude is not null
  and longitude <> 0
  and longitude is not null 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.