How To: Storing and retrieving spatial data on MySQL server with Connector/Net

Spatial data is being more used and needed at a larger number of applications. This type of data is not always easy to be managed or queried. Moreover calculations need to be done in the application code instead of doing them at the server. Recently we added a new class to manage spatial data with Connector/Net, so our users can have the option to handle spatial data operations at their application code.

Starting with Connector/Net 6.5.6, the API of MySql.Data has a new MySql Type: MySqlGeometry. Geometry data is known for representing data that belongs to a Cartesian or “flat-earth” plane and it can be defined as a point or an aggregate of points representing anything in the world that has a location. Some of the instantiable spatial types are listed below:

  • Point
  • LineString
  • Polygon
  • MultiPoint
  • MultiLineString
  • MultiPolygon

In this article we’ll be focusing on how to use the Geometry type and we’ll use the LineString type to calculate the distance between two points.

Requirements:

-A running instance of MySQL Server 5.6.10 download here

-Connector/Net 6.5.6 or greater download here

-VS 2010 Professional or greater

Step One - Creating the database

Our demo application will be storing some places and we’ll be querying our records to see what the farthest place from a given point is.

The DDL statement for creating a database:

CREATE DATABASE spatialdb;

The DDL statement for creating the table:

CREATE TABLE MyPlaces (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) DEFAULT NULL, location GEOMETRY NOT NULL) ENGINE=Innodb;

After the database is created, an application of type console should be created with Visual Studio. Open Visual Studio version 2008 or greater, go to File menu, New Project, then select C# templates group and finally the Console Application template.

The goal of this sample is to show how to enter your geometry data and get the results of a query that does some calculations to get the distance in Kilometers between two points.

Step Two – Adding some data

These are the different places to be used in the example. All the places are located at the New York city.

JFK INTERNATIONAL AIRPORT OF NEWYORK

40.644047 -73.782291

ALLEY POND PARK

40.745696 -73.742638

CUNNINGHAM PARK

40.735031 -73.768387

The code shown below should be placed inside the main method.

The using block in the sample code opens a connection that uses the database previously created called spatialdb. Then the 3 mentioned records are added to the MyPlaces table using the MySqlCommand class with the MySql Geometry type for the parameters. Finally a query is executed to list the places according to their distance from a given point.

using (var conn = new MySqlConnection("server=;uid=root;pwd=;database=spatialdb"))

      {

        conn.Open();

       MySqlCommand mysqlCommand = new MySqlCommand("DROP TABLE IF EXISTS MyPlaces");

        mysqlCommand.Connection = conn;

        mysqlCommand.ExecuteNonQuery();

        mysqlCommand = new MySqlCommand("CREATE TABLE MYPLACES (id int NOT NULL AUTO_INCREMENT PRIMARY KEY, " "Name varchar(50) NOT NULL, location Geometry NOT NULL)Engine=Innodb", conn);

        mysqlCommand.ExecuteNonQuery();

        mysqlCommand = new MySqlCommand("INSERT INTO MyPlaces (name, location) VALUES (?name, ?location)", conn);

         MySqlGeometry loc = new MySqlGeometry(40.644047, -73.782291);

        var par = new MySqlParameter("?location"MySqlDbType.Geometry);

        par.Value = loc;

        MySqlParameter name = new MySqlParameter("?name", "JFK INTERNATIONAL AIRPORT OF NEW YORK");

        mysqlCommand.Parameters.Add(par);

        mysqlCommand.Parameters.Add(name);

        mysqlCommand.ExecuteNonQuery();

        mysqlCommand.Parameters.Clear();

        loc = new MySqlGeometry(40.745696, -73.742638);

        par = new MySqlParameter("?location"MySqlDbType.Geometry);

        par.Value = loc;

        name = new MySqlParameter("?name""ALLEY POND PARK");

        mysqlCommand.Parameters.Add(par);

        mysqlCommand.Parameters.Add(name);

        mysqlCommand.ExecuteNonQuery();

        mysqlCommand.Parameters.Clear();

        loc = new MySqlGeometry(40.735031, -73.768387);

        par = new MySqlParameter("?location"MySqlDbType.Geometry);

        par.Value = loc;

        name = new MySqlParameter("?name""CUNNINGHAM PARK");

        mysqlCommand.Parameters.Add(par);

        mysqlCommand.Parameters.Add(name);

        mysqlCommand.ExecuteNonQuery();

        //lets suppose our current location is 

        //QUEENS VILLAGE STATION exact coordinates at lat=40.717957 long=-73.736501

        Console.WriteLine("The order from the farthest to the nearest place is as follows:");

        mysqlCommand = new MySqlCommand("SELECT name, GLength(LineStringFromWKB(LineString(GeomFromText(AsText(location)), GeomFromText('POINT(40.717957 -73.736501)'))))*100 as Distance " " FROM myplaces ORDER BY distance DESC", conn);

        var reader = mysqlCommand.ExecuteReader();

        while (reader.Read())  {

        Console.WriteLine("Name " + reader.GetString(0) + " Distance " + reader.GetString(1));

        }

        reader.Close();

      }

Console.ReadLine();

Output

The order of places from the farthest to nearest is as follows:

Name JFK INTERNATIONAL AIRPORT OF NEW YORK Distance 8.69448802402959

Name CUNNINGHAM PARK Distance 3.61695793727275

Name ALLEY POND PARK Distance 2.84097675104912

The output above is accurate since the current location is on Queens Village station and the farthest point is the JFK Airport.

There are some other common operations that also can be done with this type:

Create a geometry object based on its text representation

var v = MySqlGeometry.Parse("POINT (47.37 -122.21)");

Try parse a geometry text value:

MySqlGeometry v = new MySqlGeometry(); 
MySqlGeometry.TryParse("POINT (47.37 -122.21)"out v); 

Try parse a geometry text value that contains a SRID value

var mysqlGeometryResult = new MySqlGeometry();
MySqlGeometry.TryParse("SRID=101;POINT (47.37 -122.21)"out mysqlGeometryResult); 

Get the string representation out of the geometry object

MySqlGeometry v = new MySqlGeometry(47.37, -122.21); 
var valToString = v.ToString();  // val is equal to "POINT(47.37 -122.21)" 
Reading a geometry column using a DataReader
cmd.CommandText = "SELECT v as v FROM Test";
using (MySqlDataReader reader = cmd.ExecuteReader())
{
    reader.Read();
    var val = reader.GetMySqlGeometry(0); 

var valWithName = reader.GetMySqlGeometry("v");

Console.WriteLine("geometry value " + val.ToString());

Console.WriteLine( color: rgb(163, 21, 21);">"geometry value " + valWithName.ToString()); 

 } 

Note:

The calculations that we used in this example are only considered in short distances or cases when there is no need to used spherical coordinates. There are some considerations to take into account when using spatial types. When using big amount of data you’ll probably want to add a Spatial Index to improve performance. In that case the Engine recommended is MyISAM since InnoDB doesn’t have this functionality.

More information about MySQL Spatial support can be found here: http://dev.mysql.com/doc/refman/5.6/en/gis-introduction.html

Happy MySQL/.Net Coding! 

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Here's where you'll hear about all the new and fun stuff we are doing with MySQL on Windows.

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
5
6
7
8
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today