26

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?

36

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.

  • 3
    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
10

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.

  • 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
7

SQL Server 2012

You can do it like this,

SELECT geography::Point(lat, long, 4326)

For more information see my post on Database Administrators, "SQL Server Point Constructor or version of ST_Point(x,y)?"

1

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)

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.