Friday, April 20, 2012

Slides from MOW 2012 presentation (SQL Spatial and .NET)

Developing web-based spatial applications using Microsoft SQL Server and .Net technology

MOW 2012 session link

If you attended this session, please don't hesitate to contact me, should you have any questions, or want to have a spatial discussion.

It was a cool conference. Nice meeting you all. Maximize! See you at the party house ;)

The slides:

Sunday, January 8, 2012

Transferring spatial data to Google Fusion Table using .NET

The code is not pretty. Its purpose in life is just to present the building blocks needed to do the job. If you want to insert lines or polygons you need to create a piece of KML instead of a coordinate pair. You'll find more information how to do this in the Google SQL API Developer's Guide.

Happy coding !

And remember to tell me, if you create some cool stuff using Google Fusion Tables :)

Libraries used:

Easy GIS .NET - is used to read a Shapefile containing San Francisco police stations.

Proj.NET - is used to transform projected NAD coordinates to geographic WGS 84 coordinates.

The result:

The code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ProjNet.CoordinateSystems;
using ProjNet.CoordinateSystems.Transformations;
using EGIS.ShapeFileLib;
using System.Net;
using System.IO;

namespace GISBandit.FusionInsertTest
    class Program
        static void Main(string[] args)
            // NAD 1983 StatePlane California III FIPS 0403 Feet 
            string nadWkt = @"

            // WGS 84
            string wgsWkt = @"

            // Initialize objects needed for coordinate transformation
            IProjectedCoordinateSystem fromCS = ProjNet.Converters.WellKnownText.CoordinateSystemWktReader.Parse(nadWkt) as IProjectedCoordinateSystem;
            IGeographicCoordinateSystem toCS = ProjNet.Converters.WellKnownText.CoordinateSystemWktReader.Parse(wgsWkt) as IGeographicCoordinateSystem;
            CoordinateTransformationFactory ctfac = new CoordinateTransformationFactory();
            ICoordinateTransformation trans = ctfac.CreateFromCoordinateSystems(fromCS, toCS);
            // Login in to Google
            string loginName = "storestyggeulv";
            string loginPass = "rødhætte";
            string clientLoginResponse = RESTInvoke("", "accountType=GOOGLE&Email=" + loginName + "&Passwd=" + loginPass + "&service=fusiontables&source=KyllingSync", null);
            string authToken = clientLoginResponse.Split('\n')[2];
            // Open shapefile containing San Francisco police stations
            string shapeFilename = @"C:\Projects\Gisdata\demodata\SF\sfpd_stations\sfpd_stations";
            ShapeFile sf = new ShapeFile(shapeFilename);
            ShapeFileEnumerator sfEnum = sf.GetShapeFileEnumerator();
            DbfReader dbfReader = new DbfReader(shapeFilename + ".dbf");

            // String variable to hold the insert statements
            string sql = "sql=";
            int numberOfInserts = 0;

            // Insert all features from shapefile into Google Fusion Table
            while (sfEnum.MoveNext())
                // Get the raw point data from sharefile
                PointD[] sfPoints = sfEnum.Current[0];

                // Get attributes from shapefile
                string[] fields = dbfReader.GetFields(sfEnum.CurrentShapeIndex);

                // Extract name of police station
                string stationName = fields[3];

                // Transform point to WGS84 latitude longitude 
                double[] fromPoint = new double[] { sfPoints[0].X, sfPoints[0].Y };
                double[] toPoint = trans.MathTransform.Transform(fromPoint);

                // Create location string (longitude space langitude)
                string location = (toPoint[1] + " " + toPoint[0]).Replace(",",".");

                // Add insert statement to sql string
                sql += "INSERT INTO 2582135 (Text, Location) VALUES ('" + stationName + "', '" + location + "');";

                // If we're near the limit of 1 MB or 500 inserts, then invoke the API
                if (sql.Length > 900000 || numberOfInserts > 500)
                    RESTInvoke("", sql, authToken);
                    sql = "sql=";
                    numberOfInserts = 0;

            // If there are inserts left in the sql string, then invoke the API
            if (sql != "sql=")
                RESTInvoke("", sql, authToken);

        static public string RESTInvoke(string url, string content, string token)
            // Create the web request  
            HttpWebRequest request = WebRequest.Create(new Uri(url)) as HttpWebRequest;

            if (token != null)
                request.Headers.Add("Authorization", "GoogleLogin " + token);

            // Set type to POST  
            request.Method = "POST";
            request.ContentType = "application/x-www-form-urlencoded";

            // Create a byte array of the data we want to send  
            byte[] byteData = UTF8Encoding.UTF8.GetBytes(content);

            // Set the content length in the request headers  
            request.ContentLength = byteData.Length;

            // Write data  
            using (Stream postStream = request.GetRequestStream())
                postStream.Write(byteData, 0, byteData.Length);

            // Get response  
            using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
                // Get the response stream  
                StreamReader reader = new StreamReader(response.GetResponseStream());
                return reader.ReadToEnd();