SQL Geography, Google Maps & Geolocalisation

The geography type has appeared with SQL Server 2008R2. Its purpose is to store and manipulate spatial data. It allows to represent points, lines, or polygons on the globe and to make calculations such as measuring the type distances between 2 points, checking if 2 areas intersect, etc.

We will make a web application to offer interesting places near the location of the user. These places are presented on Google Maps in the form of points or areas:

#Creation of the database

The database contains a single table containing for each place, a name, a description, and its position:

CREATE TABLE [dbo].[Place] (
    [Id]          [uniqueidentifier] NOT NULL,
    [Name]        [nvarchar] (256) NOT NULL,
    [Location]    [geography] NULL,
    [Description] [nvarchar](256) NULL,
)

Let's add a few rows:

INSERT [dbo].[Place] ([Id], [Name], [Location], [Description])
VALUES (N'9dcae9a1-67a1-472c-9e4e-84b3aef44159', N'SoftFluent', geography::STGeomFromText('POINT (2.299617 48.761214)', 4326), N'A nice company')

INSERT [dbo].[Place] ([Id], [Name], [Location], [Description])
VALUES (N'584cdd45-6afe-418f-9491-84eefb31b1d0', N'Tour Eiffel', geography::STGeomFromText('POINT (2.294423 48.858399)', 4326), N'The Eiffel tower')

INSERT [dbo].[Place] ([Id], [Name], [Location], [Description])
VALUES (N'dce96053-00a6-47ca-9fe3-36af39b3ae29', N'Parc de sceaux', geography::STGeomFromText('POLYGON ((2.299424 48.763629, 2.299896 48.762158, 2.303898 48.762908, 2.306269 48.765722, 2.304778 48.766309, 2.305666 48.767943, 2.303597 48.774081, 2.304488 48.774222, 2.30334 48.777538, 2.29729 48.776651, 2.297156 48.777022, 2.296544 48.776987, 2.296582 48.776113, 2.2956 48.776333, 2.294425 48.775148, 2.294608 48.774618, 2.286411 48.773253, 2.290579 48.766338, 2.299424 48.763629))', 4326), N'A good place to have a nap')

We also need to create a stored procedure to only select locations close to the user's location:

CREATE PROCEDURE [dbo].[Place_LoadByDistance]
(
    @Location [geography],
    @maxDistance [float]
)
AS
SET NOCOUNT ON
SELECT [Place].[Id], [Place].[Name], [Place].[Location], [Place].[Description]
FROM [Place]
WHERE (@Location.STDistance([Place].[Location]) < @maxDistance)

RETURN
GO

You can see that with the geography type the calculation of the distance is very simple: whatever the figure (Point, Line, Polygon) you can call the STDistance function. This function returns the distance in meters because we used the SRID 4326 (check the 3 INSERT above). You can also check this with the following query:

SELECT * FROM sys.spatial_reference_systems
WHERE spatial_reference_id = 4326

#Creating the API

The API uses ASP.NET Web API. The only problem is to transmit the SqlGeography type. The simplest in our case (limited to Points or Polygons) is to transform this type into a list of points representing latitude and longitude:

public class PlaceController : ApiController
{
    [HttpGet]
    public IEnumerable<PlaceModel> Get(double latitude, double longitude, double distance)
    {
        SqlGeography location = SqlGeography.Point(latitude.Value, longitude.Value, 4326);
        return PlaceCollection.LoadByDistance(location, distance.Value).Select(point => new PlaceModel(point));
    }
}

public class PlaceModel
{
    public PlaceModel(Place pointOfInterest)
    {
        Name = pointOfInterest.Name;
        Description = pointOfInterest.Description;
        SqlInt32 pointCount = pointOfInterest.Location.STNumPoints();
        Location = new LatitudeLongitude[(int)pointCount];
        for (int i = 0; i < pointCount; i++)
        {
            SqlGeography point = pointOfInterest.Location.STPointN(i + 1); // 1 based index
            Location[i] = new LatitudeLongitude((double)point.Long, (double)point.Lat);
        }
    }

    public string Name { get; }
    public string Description { get; }
    public LatitudeLongitude[] Location { get; }
}

public class LatitudeLongitude
{
    public LatitudeLongitude(double longitude, double latitude)
    {
        Lat = latitude;
        Long = longitude;
    }

    public double Lat { get; }
    public double Long { get; }
}

#Creation of the graphical interface

The first step is to register the application with Google to obtain a public key:

We can then add the script to our page (replace <public key> with your key):

<script type="text/javascript" src="https://maps.googleapis.com/maps/api/js?key=<public key>"></script>

Then we initialize the map:

<div id="map-canvas"></div>
<style type="text/css">
    #map-canvas {
        height: 500px;
        margin: 0;
        padding: 0;
    }
</style>

<script type="text/javascript">
    function initialize() {
        var mapOptions = {
            center: new google.maps.LatLng(48.761214, 2.299617),
            zoom: 14
        };
        var map = new google.maps.Map(document.getElementById('map-canvas'), mapOptions);
        }
    }
    google.maps.event.addDomListener(window, 'load', initialize);
</script>

Now it is necessary to recover the position of the user via the geolocation API:

if (navigator.geolocation) {
    navigator.geolocation.getCurrentPosition(function (position) {
        var req = new XMLHttpRequest();
        req.open('GET', '/api/place?latitude=' + position.coords.latitude + '&longitude=' + position.coords.longitude + '&distance=2000', true);
        req.onreadystatechange = function () {
            if (req.readyState === 4) {
                if (req.status === 200) {
                    var places = JSON.parse(req.responseText);

                    for (var i = 0; i < places.length; i++) {
                        var place = places[i];
                        addPlace(map, place);
                    }

                } else {
                    console.error("Error while loading the page.");
                }
            }
        };
        req.send(null);
    });
}

To add an element on the map it is necessary to differentiate whether it is a simple point (Marker) or a zone (Polygon). By default nothing happens when you click on a marker, so we also add a popup (InfoWindow) on click:

function addPlace(map, place) {

    // Popup on click
    var infowindow = new google.maps.InfoWindow({
        content: place.Description
    });

    if (place.Location.length === 1) {
        // Point
        var marker = new google.maps.Marker({
            position: locationToLatLng(place.Location[0]),
            map: map,
            title: place.Name
        });

        google.maps.event.addListener(marker, 'click', function () {
            infowindow.open(map, marker);
        });

    } else {
        // Polygon
        var paths = place.Location.map(locationToLatLng);
        var polygon = new google.maps.Polygon({
            paths: paths,
            map: map,
            strokeColor: '#00FFFF',
            strokeOpacity: 0.8,
            strokeWeight: 2,
            fillColor: '#00FFFF',
            fillOpacity: 0.35,
            title: place.Name
        });

        google.maps.event.addListener(polygon, 'click', function () {
            infowindow.open(map, polygon);
        });
    }
}

#Conclusion

Thanks to the geography data type, Google Maps, and geolocation, it is possible to quickly set up a graphical interface presenting geographic data specific to the user's location.

Do you have a question or a suggestion about this post? Contact me!