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?

Vince
  • 20,017
  • 15
  • 45
  • 64
Tudor
  • 331
  • 1
  • 3
  • 4
  • http://www.sql-server-helper.com/sql-server-2008/convert-latitude-longitude-to-geography-point.aspx – kinkajou Mar 02 '12 at 06:09

4 Answers4

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.

Jamo
  • 1,457
  • 1
  • 14
  • 21
  • 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 02 '12 at 06: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 02 '12 at 07: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.

Chad Cooper
  • 12,714
  • 4
  • 46
  • 87
  • What would be the difference in using the following method instead... DECLARE @g geometry; SET @g = geometry::STPointFromText('POINT (100 100)', 0); – awesomo Mar 01 '12 at 18:32
  • @awesomo - Yep, you could use that one if you want, actually. – Chad Cooper Mar 01 '12 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)?"

Mr. T
  • 133
  • 5
Evan Carroll
  • 7,071
  • 2
  • 32
  • 58
  • 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 01 '20 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)
Arulkumar
  • 285
  • 3
  • 11
Liz
  • 11
  • 1