Insert Geography data into SQL Server 2008

Question

Can someone guide me to or give me an example on how would one go about insert/query Geography data in Sql Server 2008 ( Polygon with 4 points and Point in specific) using C# EF or Linq2Sql.

I've been searching for few days now and i can't figure it out :(

Thanks in advance!

• Mapperz: blogs.msdn.com/b/adonet/archive/2011/06/30/…
• Aviatrix: anything not in beta ?
• Mapperz: Do you have a development instance? Can test the beta on that.
• Aviatrix: yeah , i do , but i need to use it in production with a moderetly big project wiht lots of dependancies ( a pure hell to work on ) , even if i want to , i can't use the beta :(

Answer 1

Hopefully you've found the answer you're looking for by now but I just came across this and figured I'd take a stab at answering it for others at least.

From C# here's what I do for inserting a Polygon. I'm deriving this on the fly from some working code I have but not actually testing it so forgive any typo errors.

You'll need the following assemblies. System.Data.SqlClient; System.Data.SqlTypes; System.Data.SqlServer.Types;

public void Main()
{
 // the coordinate parameters below are doubles that can gathered from wherever, you can add additional points as necessary to the POLYGON list just make sure that they from a counter-clockwise 'circle'
 string areaName = "Texas";
 string extents = string.Format("POLYGON(({0} {1}, {0} {2}, {3} {2}, {3} {1}, {0} {1}))", leftLongitude, upperLatitude, lowerLatitude, rightLongitude));
 InsertArea(areaName, extents);
}
public void InsertArea(string nameParameter, string extentsString)
{
 SqlConnection sqlConn = new SqlConnection(...)
 sqlConn.Open();
 SqlCommand sqlCom = new SqlCommand("INSERT INTO areas (name, extents) VALUES (@name, @extents)", sqlConn);
 sqlCom.Parameters.AddWithValue("@name", nameParameter);
 SqlParamater extents = new SqlParameter("@extents", SqlDbType.Udt);
 extents.UdtTypeName = "Geography";
 extents.Value = GetGeographyFromText(extentsString);
 sqlCom.Parameters.Add(extents);
 sqlCom.ExecuteNonQuery();
 sqlConn.Close();
}
public SqlGeography GetGeographyFromText(String pText)
{
 SqlString ss = new SqlString(pText);
 SqlChars sc = new SqlChars(ss);
 try
 {
 return SqlGeography.STPolyFromText(sc, 4326);
 }
 catch (Exception ex)
 {
 throw ex;
 }
}

I hope this helps someone.

Answer 2

Following is a simple and quick way to pass a SqlGeography point straight to sql parameter (assuming latitude and longitude are of type double). As explained above, you need to add using Microsoft.SqlServer.Types;

SqlGeography geoLocationIn = null;
 if (latitude != 0 && longitude != 0 )
 geoLocationIn = SqlGeography.Point(latitude, longitude, 4326);
 System.Data.SqlClient.SqlParameter p = new System.Data.SqlClient.SqlParameter();
 p.ParameterName = "@GeoLocationIn";
 p.SqlDbType = System.Data.SqlDbType.Udt;
 p.UdtTypeName = "geography";
 p.Value = geoLocationIn;

Answer 3

DECLARE @Point geography = 'POINT(-83.12345 45.12345)'

SELECT * FROM GEOTAGS WHERE GEOTAG_GEOLOCATION.STDistance(@point) < 1 //Note STDIstance in km

Selects all records within a 1km radius

Answer 4

The following code demonstrates how to do it:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Microsoft.SqlServer.Types;
namespace spatial
{
 public partial class Form1 : Form
 {
 public Form1()
 {
 InitializeComponent();
 }
 private void Form1_Load(object sender, EventArgs e)
 {
 }
 private void button1_Click(object sender, EventArgs e)
 {
 // SqlConnection con = new SqlConnection("Data Source=TARINI-HP;Initial Catalog=SPATIAL_DATABASE;Integrated Security=True");
 // con.Open();
 SqlGeography polygon = SqlGeography.Parse("POLYGON((85.83300667 20.301905, 85.83351833 20.30728167, 85.83391833 20.311375, 85.83211 20.31235833, 85.83026333 20.31294, 85.82653 20.314345, 85.82518167 20.31442167, 85.82148333 20.31460333, 85.820625 20.31461833, 85.82028333 20.31313, 85.82028333 20.31313, 85.82059833 20.307355, 85.82175833 20.30559333, 85.82264833 20.30286667, 85.82334667 20.30054167, 85.82468333 20.296075, 85.82536667 20.29392, 85.82592833 20.291745, 85.82642667 20.28819333, 85.82675 20.28585833, 85.82694 20.28301, 85.82681833 20.28149, 85.82675 20.280695, 85.82639667 20.27831833, 85.82566333 20.27587167, 85.82491667 20.27417833, 85.82480333 20.27391333, 85.82375833 20.27218167, 85.82240167 20.27021833, 85.82482667 20.26924, 85.827245 20.26814833, 85.82923167 20.26690667, 85.83036833 20.267255, 85.83162 20.26916333, 85.832885 20.27154167, 85.83332833 20.27251333, 85.83370667 20.27344667, 85.83434833 20.27550167, 85.83487833 20.27801667, 85.83515667 20.28007667, 85.83523167 20.282455, 85.83490833 20.28502833, 85.83481 20.28578167, 85.83453333 20.28757667, 85.83396 20.29172, 85.83354833 20.29443167, 85.83303 20.29769167, 85.832915 20.30001667, 85.83215833 20.30082, 85.82914667 20.30031667, 85.82594833 20.2998, 85.83300667 20.301905))");
 using (var MyCon = new SqlConnection("Data Source=TARINI-HP;Initial Catalog=SPATIAL_DATABASE;Integrated Security=True;"))
 {
 using (var MyCmd = new SqlCommand())
 {
 MyCmd.Connection = MyCon;
 MyCmd.CommandText = "insert into test (geom) values(@shape)";
 MyCmd.Parameters.Add(new SqlParameter("@shape", System.Data.SqlDbType.Udt));
 MyCmd.Parameters["@shape"].UdtTypeName = "geography";
 MyCmd.Parameters["@shape"].Value = polygon;
 MyCon.Open();
 MyCmd.ExecuteNonQuery();
 MessageBox.Show("successfully inserted");
 MyCon.Close();
 }
 }
 }
 }
}