Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 manage to do that?

share|improve this question
    

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
2  
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
3  
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.