30

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?

1

4 Answers 4

40

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.

2
  • 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, 2012 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, 2012 at 7:28
11

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.

2
  • 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, 2012 at 18:32
  • @awesomo - Yep, you could use that one if you want, actually. Mar 1, 2012 at 18:59
10

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 the constructor for Point takes x, y arguments, then you will want to switch your ordering as such: geography::Point(long, lat, 4326). Longitude is x and Latitude is y.
    – carusot42
    Dec 1, 2020 at 15:53
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)
1
  • I have apprehension in using this method, as it involves losing location precision. Dec 18, 2020 at 3:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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