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!

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();


                }
            }
        }
    }
}