Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Hy guys,

I'm new in SQL Server 2008 and I hope you will understand my question/need.

Thus, I have a table which contains 3 fields (Name, Lat and Long) in my data base (spatial). I want to create a geometry/geography column based on those fields (Lat and Long) but unfortunately without any success. My question is how can I managed to do that?

Thank you in advance. Tudor

share|improve this question
    

3 Answers 3

You can add a computed column like this

alter table yourTable add geographyColumn as geography::STGeomFromText('POINT('+convert(varchar(20),Long)+' '+convert(varchar(20),Lat)+')',4326)

I have add the conversion from lng or lat because I store the Long and Lats as numbers.

share|improve this answer
1  
Thank you for your answers, I managed to solve my problem using this line: UPDATE yourdatabase SET geometry_column=geometry::Point([ColX], [ColY], SRID) –  Tudor Mar 2 '12 at 6:49
2  
It is possible to create a trigger for this? Like when you populate your fields (columns X and Y) with information the script mentioned above will run. –  Tudor Mar 2 '12 at 7:28
    
+1, precise and simple –  Habib May 17 '12 at 11:51

Looks like you want the STGeomFromText() or STPointFromText() SQL method:

DECLARE @g geography;
SET @g = geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);
SELECT @g.ToString();

or

DECLARE @g geography;
SET @g = geography::STPointFromText('POINT(-122.34900 47.65100)', 4326);
SELECT @g.ToString();

Create your Geometry or Geography field, then use SQL to populate that field using your lat/lon values.

share|improve this answer
    
What would be the difference in using the following method instead... DECLARE @g geometry; SET @g = geometry::STPointFromText('POINT (100 100)', 0); –  awesomo Mar 1 '12 at 18:32
    
@awesomo - Yep, you could use that one if you want, actually. –  Chad Cooper Mar 1 '12 at 18:59

If rounding of the original Long/Lat values occur when using the CONVERT(VARCHAR, statement, try using GeoData = geometry:: STGeomFromText('POINT(' + CAST(CAST(X AS decimal(13, 2)) AS varchar) + ' ' + CAST(CAST(Y AS decimal(13, 2)) AS varchar) + ')', 4326)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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