T-SQL

Using MsSqlSpatial with Google Maps

One of the many .NET open-source projects that don’t get the attention they deserve is MsSqlSpatial. This project provides spatial extensions to SQL Server 2005 – meaning you can perform various spatial calculations right in your T-SQL queries! Cool eh? But, this project lacks documentation which is a problem for us developers. I’m sure they are working their ass off to make one but in the mean time we have to play around with the code to understand its inner workings.

Since Google Maps is very popular among developers, and my personal favorite, I thought why not give out some tips.

Spatial Reference Identifiers (SRIDs)

Most of their functions, require this as a parameter. If your GIS application uses Google Maps then you should use 4326.

Code Examples

Convert a Point on the map from a Well-Known-Text(WKT) format to a Well-Known-Binary Format:

SELECT ST.PointFromText('POINT(-87.6673250 41.9489650)', 4326)

The Point should be of the format POINT( longitude latitude ).

Polygon to text:

SELECT [Bloggernate].[ST].[PolygonFromText] (
   'POLYGON((-87.71956443786621 41.96727630029047, 
             -87.64369010925293 41.96765920367816, 
             -87.6309871673584 41.920672548686824, 
             -87.72522926330566 41.91939525416699, 
             -87.71956443786621 41.96727630029047))'
   ,4326)

The polygon must be a closed one otherwise you’ll receive an exception.

Check if a polygon contains a specified point:

SELECT [ST].[Contains] (YOUR_WKB_POLYGON, YOUR_WKB_POINT)

I hope this provides a good starting point for those daring to step into the unknown 😉

Happy Programming!

Share

Perform Batch Inserts/Updates in SQL Server 2005 using XML

My current project requires me to download huge amounts of data from a remote server in the form of XML and save them to our local database. Lets see if we can do this using just one simple T-SQL stored procedure. Lets first create our table. For the purpose of this demonstration I will use a simple schema with the following signature

   1:  CREATE TABLE [dbo].[Employee](
   2:      [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
   3:      [Name] [nvarchar](50) NOT NULL,
   4:      [Gender] [varchar](1) NOT NULL,
   5:      [DateOfBirth] [datetime] NOT NULL,
   6:   CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
   7:  (
   8:      [EmployeeID] ASC
   9:  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  10:  ) ON [PRIMARY]

Next, we need to create the stored procedure that will do the batch insert for us.

CREATE PROCEDURE [dbo].[spr_Employee_ProcessXmlData](@employees xml)
AS
BEGIN
    DECLARE @TempTable TABLE
    (
        [Name] [nvarchar](50),
        [Gender] [varchar](1),
        [DateOfBirth] [datetime]
    );

    -- Populate the Temporary table from the XML
    INSERT INTO @TempTable (Name, Gender, DateOfBirth)
        SELECT 
                ParamValues.Data.value('Name[1]','nvarchar(50)'),
                ParamValues.Data.value('Gender[1]','varchar(1)'),
                ParamValues.Data.value('DateOfBirth[1]','datetime')             
        FROM
            @employees.nodes('NewDataSet/Employee') as ParamValues(Data);
            
    INSERT INTO Employee(Name, Gender, DateOfBirth)
        SELECT T.* FROM @TempTable T WHERE T.Name NOT IN (SELECT Name FROM Employee)
END

I won’t go into details about the XML programming – this great article are among several others that provide a good start for those unfamiliar with this feature of T-SQL. The stored procedure first creates a temporary in-memory table which will hold the data from the XML. Each node is read from the XML and mapped to the appropriate column of the temporary table. The temporary table is merely used to prevent duplicate data from being inserted into our actual table. If you are certain that your XML will never contain redundant data, then you can skip this step and directly insert the data to the Employee table.

We are now ready to perform batch inserts! The following SQL script demonstrates how.

EXEC    [dbo].[spr_Employee_ProcessXmlData] 
        @employees = '<NewDataSet>
                        <Employee>
                            <Name>Raihan Iqbal</Name>
                            <Gender>M</Gender>
                            <DateOfBirth>11-05-1983</DateOfBirth>
                        </Employee>
                        <Employee>
                            <Name>John Doe</Name>
                            <Gender>M</Gender>
                            <DateOfBirth>10-05-1972</DateOfBirth>
                        </Employee>
                    </NewDataSet>'

It is needless to say that we can generate the XML and execute the stored procedure using any .NET language.

Happy programming!

Share