Find top 10 nearest locations from DB near known location
Hey guys
This questions is not directly Umbraco related but I'm going to ask it in here anyway hoping I can get some tips on how to best achieve my goal.
Ok...so I'm working on a pet project where I have a known location for a venue on my model. Based on the latitude and longitude of this venue I want to list the top 10 of the nearest restaurants.
I have the latitude/longitude for the venue on the Model in Umbraco but want to fetch the restaurants from a custom restaurant table, which is stored in the Umbraco database. The table contains more than 100.000 restaurants.
I have been trying to Google how to do this but I'm a bit confused about how to handle the calculations of the distance and whether I should do it directly in the database using a stored procedure and how to make the search performant - How can one enable caching for this? And should I make a surface controller for the functionality etc?
I hope my question makes sense - Otherwise I'm happy to provide further details if needed.
In short...I know point A and want to display the 10 nearest locations based on that known coordinate.
Looking forward to receive any tips, suggestions and hints.
Thank you for the suggestions - Yes I'm using 2008...Express on local machine though but that should not matter, right?
Is the geography datatype supported in newer version of MSSQL as well? Think I read somewhere to avoid using it. But all coordinates are currently saved as decimals...does that matter any?
Aaaah, nearest neighbor! I'm going to google a bit more then :)
If you were looking at google again.. here's a code sample I have from my bookmark using polylines and as an aside measuring distances. Which you could abstract out and use to get a cached distance calc?
Thanks for the suggestion but that's not quite what I need I think? :)
The thing is that I have a table in my database where I need to make the lookup and return some informations like distance in KM/Miles, Name of the restaurant, stars and address for instance.
I'm still quite puzzled about how to make it happen - Afterall I'm primarily a frontend developer :D
If all your locations are already in the db.. you could write a one off query to compare all the locations for distance via google geocoding (to get the lat/lang positions and then calc route lengths) and then store in a lookup table. Then each publish of a new location do the same for that location vs all the others, maintaining your lookup. Maybe even using http://ucomponents.org/data-types/textstring-array/ as a storage mechanism native to umbraco rather than separate db tables? And you could have this against a location... so it knows all the other places distances from itself... or as a overrriding all locations information??
I don't think you are likely to be able to have a real time calculation, google maps api throttles api lookups to ~1per second and 2000 a day for the geocoding from my experience.
I appreciate the suggestions - But I have several different venues - Each venue will be displayed on it's own page. Since there are more than 100.000 restaurants, I don't want to manage those inside Umbraco. Hence it's own table in the database. I might consider making it a custom section but I won't create the data in Umbraco.
All I need to do is to feed the known coordinate to my own method and have the 5-10 nearest returned. I tried looking at the google distance matrix stuff but it's prohibited to use if data is not going to be displayed on a map. This data is maybe only going to be displayed on a list.
Sorry if I'm missing something here :) Just trying to figure out the best way of doing this...and maybe I'm a bit slow :)
I just found this https://gist.github.com/tugberkugurlu/2870461 - Would I be able to use this for my described scenario? I'm not sure how to put it into practice though but perhaps it makes sense to you guys? I'm not sure what should be placed in the part where the comment reads "Insert the place locations here...".
Looks like that gist is using entity framework, maybe you should search for a peta poco alternative since that is used internally by Umbraco, if I got some time this week I'll try to write you some example code
Hmm, seems like I'm not able to use a spatial index on MSSQL 2008 express? I'm getting this when I try running the code from you 2nd example "Cannot find either column "SpatialLocation" or the user-defined function or aggregate "SpatialLocation.ToString", or the name is ambiguous."
Ah yes, of course! That works fine...however the query is taking 24 seconds...But I guess the performance can be fine tuned? And do you know if it would be possible to have the distance calculated in kilometers for instance?
I'm not sure how it works exactly but I found this on another forum and it's executing really fast and seems to be reliable.
DECLARE @Location GEOGRAPHY
SET @Location = GEOGRAPHY::STPointFromText('POINT(-71.0571571 42.3133735)',4326).STBuffer(10000);
SELECT TOP(10) Coordinates.ToString(), * FROM Hotels WHERE Coordinates.STIntersects(@Location) = 1
ORDER BY Coordinates.STDistance(@Location);
Now I'm just not sure what the next step would be in order to be able to expose it in a view? And how would I get the distance displayed in Kilometers?
Ok, so I think I have figured out how to fetch the data and render it in my view...however I'm currently stuck with my POC since I get this error
Object must implement IConvertible.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidCastException: Object must implement IConvertible.
Source Error:
Line 19: public IEnumerable
I have a model that looks like this
using System;
using System.Data.Entity.Spatial;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Umbraco.Core.Persistence;
namespace Hotels.Models
{
[TableName("Hotels")]
[PrimaryKey("Id", autoIncrement = true)]
public class Hotel
{
public int Id { get; set; }
public int EANHotelID { get; set; }
public string Name { get; set; }
public string Address1 { get; set; }
public string City { get; set; }
public string CountryCode { get; set; }
public string Country { get; set; }
public float Latitude { get; set; }
public float Longitude { get; set; }
public string StarRating { get; set; }
public string Confidence { get; set; }
public string Location { get; set; }
public DbGeography Coordinates { get; set; }
public string ZipCode { get; set; }
}
}
My Controller looks like this
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Hotels.Models;
using Umbraco.Core;
using Umbraco.Core.Persistence;
namespace Hotels.Controllers
{
public class HotelRepository
{
private readonly UmbracoDatabase _database;
public HotelRepository() {
_database = ApplicationContext.Current.DatabaseContext.Database;
}
public IEnumerable<Hotel> GetAll()
{
return _database.Fetch<Hotel>("SELECT TOP 2 * FROM Hotels");
}
}
}
And finally my view looks like this
@using Hotels.Controllers
@using Hotels.Models
@inherits Umbraco.Web.Mvc.UmbracoTemplatePage
@{
Layout = "Master.cshtml";
var repo = new HotelRepository();
}
@{
foreach (var hotel in repo.GetAll()){
<p>
@hotel.City
<br />
@hotel.Name
</p>
}
}
I haven't worked with DbGeography and the coordinate type in MSSQL before, so I can't say why it fails. So here is what I would do instead:
Since you also have Latitude and Longitude in your table and your model, I would simply remove the Coordinates property from your model (but still leave it in the database for searches). After this change, your view will succeed, and fetch the first two hotels.
Then to get the distance in meters, you can use my Location class:
using System;
public class Location {
// Based on:
// https://github.com/abjerner/Skybrud.Social/blob/master/src/Skybrud.Social/Location.cs
// https://github.com/abjerner/Skybrud.Social/blob/master/src/Skybrud.Social/SocialUtils.cs
#region Properties
/// <summary>
/// Gets the latitude of the location. The latitude specifies the north-south position of a
/// point on the Earth's surface.
/// </summary>
public double Latitude { get; set; }
/// <summary>
/// Gets the longitude of the location. The longitude specifies the east-west position of a
/// point on the Earth's surface.
/// </summary>
public double Longitude { get; set; }
#endregion
#region Constructors
public Location() {
// Default constructor
}
public Location(double latitude, double longitude) {
Latitude = latitude;
Longitude = longitude;
}
#endregion
#region Static methods
/// <summary>
/// Calculates the distance in meters between two GPS locations.
/// </summary>
/// <param name="loc1">The first location.</param>
/// <param name="loc2">The second location.</param>
public static double GetDistance(Location loc1, Location loc2) {
return GetDistance(loc1.Latitude, loc1.Longitude, loc2.Latitude, loc2.Longitude);
}
/// <summary>
/// Calculates the distance in meters between two GPS locations.
/// </summary>
public static double GetDistance(double lat1, double lng1, double lat2, double lng2) {
// http://stackoverflow.com/a/3440123
double ee = (Math.PI * lat1 / 180);
double f = (Math.PI * lng1 / 180);
double g = (Math.PI * lat2 / 180);
double h = (Math.PI * lng2 / 180);
double i = (Math.Cos(ee) * Math.Cos(g) * Math.Cos(f) * Math.Cos(h) + Math.Cos(ee) * Math.Sin(f) * Math.Cos(g) * Math.Sin(h) + Math.Sin(ee) * Math.Sin(g));
double j = (Math.Acos(i));
return (6371 * j) * 1000d;
}
#endregion
}
Your view could then look like:
@{
// Get a reference to the database
UmbracoDatabase db = ApplicationContext.Current.DatabaseContext.Database;
// Set the location we should use for comparison
Location location = new Location(51.508054, -0.128081);
// Database stuff
List<Hotel> hotels = db.Fetch<Hotel>("SELECT TOP 2 * FROM Hotels");
foreach (var hotel in hotels) {
Location hotelLocation = new Location(hotel.Latitude, hotel.Longitude);
<p>
@hotel.City
<br />
@hotel.Name
<br />
@hotel.Latitude
<br />
@hotel.Longitude
<br />
@String.Format("{0:N2}", Location.GetDistance(location, hotelLocation) / 1000.00) km
</p>
}
}
I've skipped your HotelRepistory class in the example above, but that was simply because it was easier for me to test.
Thank you very much for your reply - I'm beginning to learn / get into how petapoco works and how the logic should be defined in MVC serverside wise in an Umbraco context.
I uncommented the Coordinates line from my code and now it works. But I'm not quite there yet. I need to get the 10 nearest hotels to a known location. I'm thinking I should have this logic in the controller so it's only those 10 hotels that are ever returned to the view. For that I need to execute this SQL code somehow
DECLARE @Location GEOGRAPHY
SET @Location = GEOGRAPHY::STPointFromText('POINT(-71.0571571 42.3133735)',4326).STBuffer(10000);
SELECT TOP(10) Coordinates.ToString(), * FROM Hotels WHERE Coordinates.STIntersects(@Location) = 1
ORDER BY Coordinates.STDistance(@Location);
It's executing instantly when I fire it in my DB. So I just need to figure out how to get the known location from my CurrentPage passed to a POINT in the query and how it can be constructed in my controller.
using System;
using System.Data.Entity.Spatial;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Runtime.Serialization;
using Umbraco.Core.Persistence;
using Umbraco.Core.Persistence.DatabaseAnnotations;
namespace Hotels.Models
{
[TableName("Hotels")]
[DataContract(Name = "hotel")]
public class Hotel
{
[PrimaryKeyColumn(AutoIncrement = true)]
[DataMember(Name = "id")]
public int Id { get; set; }
[DataMember(Name = "eanHotelId")]
public int EANHotelID { get; set; }
[DataMember(Name = "name")]
public string Name { get; set; }
[DataMember(Name = "address1")]
public string Address1 { get; set; }
[DataMember(Name = "city")]
public string City { get; set; }
[DataMember(Name = "countryCode")]
public string CountryCode { get; set; }
[DataMember(Name = "country")]
public string Country { get; set; }
[DataMember(Name = "latitude")]
public float Latitude { get; set; }
[DataMember(Name = "longitude")]
public float Longitude { get; set; }
[DataMember(Name = "starRating")]
public string StarRating { get; set; }
[DataMember(Name = "confidence")]
public string Confidence { get; set; }
[DataMember(Name = "location")]
public string Location { get; set; }
/*[DataMember(Name = "coordinates")]
public DbGeography Coordinates { get; set; }*/
[DataMember(Name = "zipCode")]
public string ZipCode { get; set; }
}
}
The surface controller
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Tourbowler.Models;
using Umbraco.Core;
using Umbraco.Web.Mvc;
namespace Hotels.SurfaceControllers
{
public class HotelSurfaceController : SurfaceController
{
public ActionResult RenderHotels() {
var db = ApplicationContext.DatabaseContext.Database;
//Not used here so far
var currentNodeId = UmbracoContext.PageId.Value;
var hotels = db.Fetch<Hotel>("SELECT TOP 2 * FROM Hotels");
return PartialView("viewHotels", hotels);
}
}
}
The macro partial view
@using Hotels.SurfaceControllers
@using Hotels.Models
@inherits Umbraco.Web.Mvc.UmbracoViewPage<IEnumerable<Hotel>>
@foreach (var hotel in Model){
<p>
@hotel.City
<br />
@hotel.Name
</p>
}
So I'm thinking I could use the contentService to figure out what known location is stored on the currentpage and then pass the coordinates somehow so that I in the view can just loop over the X returned locations.
If we just look at making this work, the following snippets does the job. In order to be able to reference the SqlGeography class, you should install the Microsoft.SqlServer.Types package from NuGet. distance is simply an integer with the search distance/radius in meters.
SqlGeography point = SqlGeography.Point(location.Latitude, location.Longitude, 4326);
List<Hotel> hotels = db.Fetch<Hotel>(
"SELECT * FROM Hotels WHERE @0.STDistance(Coordinates) <= " + distance + " ORDER BY @0.STDistance(Coordinates) ASC",
point.STBuffer(10000)
);
The snippet may calculate the distance twice for each row since the distance is sued both for the WHERE clause and for ordering the rows. I can't tell whether this affect performance based on the rows you posted earlier.
To only return the nearest five hotels, you could simply update the SQL to:
SqlGeography point = SqlGeography.Point(location.Latitude, location.Longitude, 4326);
List<Hotel> hotels = db.Fetch<Hotel>(
"SELECT TOP 5 * FROM Hotels WHERE @0.STDistance(Coordinates) <= " + distance + " ORDER BY @0.STDistance(Coordinates) ASC",
point.STBuffer(10000)
);
I'm not sure that I understand the purpose of the surface controller. Having a repository like the one you posted earlier is generally a very good idea, since you have isolated the code responsible for the database stuff, and then you simply use your repository in the rest of your code rather than doing the database stuff again. If the surface controller makes sense to you, it could be updated to use the repository.
If you stick with the repository class, my take would look like:
using System.Collections.Generic;
using Microsoft.SqlServer.Types;
using Umbraco.Core;
using Umbraco.Core.Persistence;
namespace Hotels.Models {
public class HotelRepository {
private readonly UmbracoDatabase _database;
public HotelRepository() {
_database = ApplicationContext.Current.DatabaseContext.Database;
}
public IEnumerable<Hotel> GetAll() {
return _database.Fetch<Hotel>("SELECT TOP 2 * FROM Hotels");
}
/// <summary>
/// Gets a list of all hotels within the specified <code>radius</code> from <code>location</code>.
/// </summary>
/// <param name="location">The location used as reference.</param>
/// <param name="radius">The search radious specified in meters.</param>
/// <param name="count">The maximum amount of hotels to return. If set to <code>0</code>, all hotels within <code>distance</code> will be returned.</param>
public List<Hotel> GetAll(Location location, int radius, int count = 0) {
SqlGeography point = SqlGeography.Point(location.Latitude, location.Longitude, 4326);
return _database.Fetch<Hotel>(
"SELECT " + (count > 0 ? "TOP " + count + " " : "") + "* FROM Hotels WHERE @0.STDistance(Coordinates) <= " + radius + " ORDER BY @0.STDistance(Coordinates) ASC",
point.STBuffer(10000)
);
}
}
}
Usage would be something like (no limit):
Location location = new Location(51.508054, -0.128081);
HotelRepository repo = new HotelRepository();
List<Hotel> hotels = repo.GetAll(location, distance);
or (with limit):
Location location = new Location(51.508054, -0.128081);
HotelRepository repo = new HotelRepository();
List<Hotel> hotels = repo.GetAll(location, distance, 5);
Also, to simplify your view a bit, you could add a location/coordinates property to your Hotel class rather initializing the location in the view:
using Umbraco.Core.Persistence;
namespace Hotels.Models {
[TableName("Hotels")]
[PrimaryKey("Id", autoIncrement = true)]
public class Hotel {
private Location _location;
public int Id { get; set; }
public int EANHotelID { get; set; }
public string Name { get; set; }
public string Address1 { get; set; }
public string City { get; set; }
public string CountryCode { get; set; }
public string Country { get; set; }
public float Latitude { get; set; }
public float Longitude { get; set; }
public string StarRating { get; set; }
public string Confidence { get; set; }
public string Location { get; set; }
//public DbGeography Coordinates { get; set; }
public string ZipCode { get; set; }
[Ignore]
public Location Coordinates {
get { return _location ?? (_location = new Location(Latitude, Longitude)); }
}
}
}
The only reason I switched to using a surface controller is honestly because I have no clue as to what I'm doing really. I found a video on youtube where the Repository approach was being used but then along the way it was said..."Ok, this is just for coding speed but is not the best way to do it" once the controller was tied to the view.
So in short I'm happy to keep using the HotelRepository...I just need to figure out what the best approach to tie it to my view is? :)
And finally...how would I be able to add the distance in KM's to be displayed in my view? I suppose I can use some of the code you posted earlier...but not sure about the context.
Repository vs surface controller
The idea of the repository is to isolate the code into pieces that can be re-used, and then only have a single place in the code to maintain the database stuff. The repository doesn't exclude the surface controller though.
Say you start out by implementing the surface controller, and make the calls to the database directly in the class. Things will work fine.
Then a month later, you need do something else, that also needs to pull hotels from the database, but yet is so different that you can't use your surface controller from before. If you just copy the bits of the code that you need, and place it in a new surface controller, WebApi controller or wherever you need it, you will end up having two almost identical pieces of code.
Then a bit later, a change needs to be made to the "Hotels" table in the database, and you will now have to update your code two places in your code.
If you had initially implemented the repository, your would most likely only have had to update the repository class, and the rest of your code would work fine again.
The repository can somewhat be compared to the service layer in Umbraco. Everything in Umbraco uses the services - even the backoffice. The Umbraco services are probably a bit more complex, but the idea is the same: the places in your code you need to update/maintain is minimized.
Your view
Anyways, given the changes in my previous post, my view would look like:
@{
// Set the location we should use for comparison (Trafalgar Square)
Location location = new Location(51.508054, -0.128081);
// Initialize a new instance of the repository
HotelRepository repo = new HotelRepository();
// Declare the distance (here 5000 meters)
int distance = 5000;
// Get a list of the 10 nearest hotels
List<Hotel> hotels = repo.GetAll(location, distance, 10);
// List each hotel
<table border="1">
@foreach (var hotel in hotels) {
<tr>
<td>@hotel.City</td>
<td>@hotel.Name</td>
<td>@hotel.Latitude</td>
<td>@hotel.Longitude</td>
<td>@Location.GetDistance(location, hotel.Coordinates) meters</td>
<td>@String.Format("{0:N2}", Location.GetDistance(location, hotel.Coordinates)/1000.00) km</td>
</tr>
}
</table>
}
Seems like you need the private field - see line 8:
using Umbraco.Core.Persistence;
namespace Hotels.Models {
[TableName("Hotels")]
[PrimaryKey("Id", autoIncrement = true)]
public class Hotel {
private Location _location;
public int Id { get; set; }
public int EANHotelID { get; set; }
public string Name { get; set; }
public string Address1 { get; set; }
public string City { get; set; }
public string CountryCode { get; set; }
public string Country { get; set; }
public float Latitude { get; set; }
public float Longitude { get; set; }
public string StarRating { get; set; }
public string Confidence { get; set; }
public string Location { get; set; }
//public DbGeography Coordinates { get; set; }
public string ZipCode { get; set; }
[Ignore]
public Location Coordinates {
get { return _location ?? (_location = new Location(Latitude, Longitude)); }
}
}
}
Thanks for your patience - Overlooked that one. Build finally succeeded. But now I'm getting this error in the log file when i'm trying to render my partial view
2015-03-15 17:15:33,497 [39] WARN umbraco.macro - [Thread 31] Error loading Partial View (file: ~/Views/MacroPartials/GetNearByHotels.cshtml). Exception: System.InvalidOperationException: The model item passed into the dictionary is of type 'Umbraco.Web.Models.PartialViewMacroModel', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[Hotels.Models.Hotel]'.
My view looks like this
@using Hotels.Controllers
@using Hotels.Models
@inherits Umbraco.Web.Mvc.UmbracoViewPage<IEnumerable<Hotel>>
@{
// Set the location we should use for comparison (Trafalgar Square)
Location location = new Location(51.508054, -0.128081);
// Initialize a new instance of the repository
HotelRepository repo = new HotelRepository();
// Declare the distance (here 5000 meters)
int distance = 5000;
// Get a list of the 10 nearest hotels
List<Hotel> hotels = repo.GetAll(location, distance, 10);
// List each hotel
<table border="1">
@foreach (var hotel in hotels) {
<tr>
<td>@hotel.City</td>
<td>@hotel.Name</td>
<td>@hotel.Latitude</td>
<td>@hotel.Longitude</td>
<td>@Location.GetDistance(location, hotel.Coordinates) meters</td>
<td>@String.Format("{0:N2}", Location.GetDistance(location, hotel.Coordinates)/1000.00) km</td>
</tr>
}
</table>
}
tells the compiler that the type of the expected model (could be described as a kind of scope) is IEnumerable<Hotel>, but is instead of the type Umbraco.Web.Models.PartialViewMacroModel. To fix it, simply replace the line with:
Anders, you're da man! :) Thank you very very much for all you help - It's freaking working -Yipee! #h5yr sir!
I had to switch the coordinates though...for some reason when using POINT it's expecting Lng/Lat...instead og Lat/Lng. But once I switched those it worked like a charm.
I have not taken the time to get fully into what the code does - But in regards to the query then I don't think it would succeed without making use of the geography datatype (Coordinates) since there is a spatial index on this column...if that's what you're thinking about?
However I can see there is some weird stuff going on with calculations...I just tested with a coordinate for my own address - I know there is a hotel less than 2 km from here but the distance returned is around 6,4 km...But time to eat, will have a look at things afterwards :)
Just had a closer look at the code - Now I get it...you're not even using the Geography data but only looking at the coordinates. Well it seems to be fast. But I don't know if it can have an impact on the accuracy of the distance calculation perhaps?
I just checked with Google maps - There is a hotel 2.2 km from me but according to the distance calculation it's 6.4 km away...that's a bit too much inaccuracy for the use case. 500 meters +/- would be acceptable...but not sure how to make calculations more exact?
Yes, since're the table also has columns for latitude and longitude individually, I thought it was easiest to simply use them instead. So the coordinates field is for searching (unless you use it for something else of course).
The calculation in the code is only for the hotels being displayed, and it will take less than a millisecond to execute.
Regarding the distance being wrong, are you able to send me the coordinates? According to my own tests of the logic in Skybrud.Social (which is where I copied it from), it works correctly.
Ok, we have found out via Twitter that you get the correct distance of 1.49 km in your setup and I for some reason get a wrong distance of 6.4 km on my setup. So therefore I'm posting my exact code to make sure it's not because I have overlooked something or done something wrong :)
Here we go...
Model
using System;
using System.Data.Entity.Spatial;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Runtime.Serialization;
using Microsoft.SqlServer.Types;
using Umbraco.Core.Persistence;
using Umbraco.Core.Persistence.DatabaseAnnotations;
namespace Hotels.Models
{
[TableName("Hotels")]
[DataContract(Name = "hotel")]
public class Hotel
{
// public Hotel(){}
private Location _location;
[PrimaryKeyColumn(AutoIncrement = true)]
[DataMember(Name = "id")]
public int Id { get; set; }
[DataMember(Name = "eanHotelId")]
public int EANHotelID { get; set; }
[DataMember(Name = "name")]
public string Name { get; set; }
[DataMember(Name = "address1")]
public string Address1 { get; set; }
[DataMember(Name = "city")]
public string City { get; set; }
[DataMember(Name = "countryCode")]
public string CountryCode { get; set; }
[DataMember(Name = "country")]
public string Country { get; set; }
[DataMember(Name = "latitude")]
public float Latitude { get; set; }
[DataMember(Name = "longitude")]
public float Longitude { get; set; }
[DataMember(Name = "starRating")]
public string StarRating { get; set; }
[DataMember(Name = "confidence")]
public string Confidence { get; set; }
[DataMember(Name = "location")]
public string Location { get; set; }
/*[DataMember(Name = "coordinates")]
public DbGeography Coordinates { get; set; }*/
[DataMember(Name = "zipCode")]
public string ZipCode { get; set; }
[Ignore]
public Location Coordinates {
get { return _location ?? (_location = new Location(Latitude, Longitude)); }
}
}
}
Controller
sing System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Hotels.Models;
using Umbraco.Core;
using Umbraco.Core.Persistence;
using Microsoft.SqlServer.Types;
namespace Hotels.Controllers
{
public class HotelRepository
{
private readonly UmbracoDatabase _database;
public HotelRepository() {
_database = ApplicationContext.Current.DatabaseContext.Database;
}
public List<Hotel> GetAll(Location location, int radius, int count = 0) {
SqlGeography point = SqlGeography.Point(location.Longitude, location.Latitude, 4326);
return _database.Fetch<Hotel>(
"SELECT " + (count > 0 ? "TOP " + count + " " : "") + "* FROM Hotels WHERE @0.STDistance(Coordinates) <= " + radius + " ORDER BY @0.STDistance(Coordinates) ASC", point.STBuffer(10000)
);
}
}
}
View
@using Hotels.Controllers
@using Hotels.Models
@inherits Umbraco.Web.Macros.PartialViewMacroPage
@{
//I fetch some data from a selected venue where to I want to list the 10 nearest hotels
dynamic venue = Model.Content.GetPropertyValue("venue");
if(!String.IsNullOrEmpty(@venue.ToString())){
var lat = Convert.ToDouble(venue.latitude);
var lng = Convert.ToDouble(venue.longitude);
// Set the location we should use for comparison
Location location = new Location(@lng, @lat);
// Initialize a new instance of the repository
HotelRepository repo = new HotelRepository();
// Declare the distance (here 5000 meters)
int distance = 5000;
// Get a list of the 10 nearest hotels
List<Hotel> hotels = repo.GetAll(location, distance, 10);
// List each hotel
<table border="1">
@foreach (var hotel in hotels) {
<tr>
<td>@hotel.City</td>
<td>@hotel.Name</td>
<td>@Location.GetDistance(location, hotel.Coordinates) meters</td>
<td>@String.Format("{0:N2}", Location.GetDistance(location, hotel.Coordinates)/1000.00) km</td>
</tr>
}
</table>
}
}
I have not touched the Location class but posting it anyway
Location.cs
**
using System;
public class Location
{
// Based on:
// https://github.com/abjerner/Skybrud.Social/blob/master/src/Skybrud.Social/Location.cs
// https://github.com/abjerner/Skybrud.Social/blob/master/src/Skybrud.Social/SocialUtils.cs
#region Properties
/// <summary>
/// Gets the latitude of the location. The latitude specifies the north-south position of a
/// point on the Earth's surface.
/// </summary>
public double Latitude { get; set; }
/// <summary>
/// Gets the longitude of the location. The longitude specifies the east-west position of a
/// point on the Earth's surface.
/// </summary>
public double Longitude { get; set; }
#endregion
#region Constructors
public Location()
{
// Default constructor
}
public Location(double latitude, double longitude)
{
Latitude = latitude;
Longitude = longitude;
}
#endregion
#region Static methods
/// <summary>
/// Calculates the distance in meters between two GPS locations.
/// </summary>
/// <param name="loc1">The first location.</param>
/// <param name="loc2">The second location.</param>
public static double GetDistance(Location loc1, Location loc2)
{
return GetDistance(loc1.Latitude, loc1.Longitude, loc2.Latitude, loc2.Longitude);
}
/// <summary>
/// Calculates the distance in meters between two GPS locations.
/// </summary>
public static double GetDistance(double lat1, double lng1, double lat2, double lng2)
{
// http://stackoverflow.com/a/3440123
double ee = (Math.PI * lat1 / 180);
double f = (Math.PI * lng1 / 180);
double g = (Math.PI * lat2 / 180);
double h = (Math.PI * lng2 / 180);
double i = (Math.Cos(ee) * Math.Cos(g) * Math.Cos(f) * Math.Cos(h) + Math.Cos(ee) * Math.Sin(f) * Math.Cos(g) * Math.Sin(h) + Math.Sin(ee) * Math.Sin(g));
double j = (Math.Acos(i));
return (6371 * j) * 1000d;
}
#endregion
}
Thanks for the ping pong on Twitter - I just noticed something in the rendering of the view.
When I try to write out @hotel.Coordinates I just get a string that says "Location"...I don't know if that's correct? I would expect to see the geography stuff from the database, which looks like this
But if I change my view to use the coordinates for the hotels in the distance calculations it seems to be more correct - So if I write it like this, then I get a distance of 2.3 KM to the hotel...and Google was giving me 2.2...so that's seems to be fairly accurate, no? :)
<table border="1">
@foreach (var hotel in hotels) {
Location hotelLocation = new Location(@hotel.Longitude, @hotel.Latitude);
<tr>
<td>@hotel.City</td>
<td>@hotel.Name</td>
<td>@hotel.Latitude</td>
<td>@hotel.Longitude</td>
<td>@Location.GetDistance(location, hotelLocation) meters</td>
<td>@hotel.Coordinates</td>
<td>@String.Format("{0:N2}", Location.GetDistance(location, hotelLocation)/1000.00) km</td>
</tr>
}
</table>
That is actually the expected behavior. The Coordinates property is of the type Location. Location is a class, and writing it out on the page like you're doing, will simply result in "Location". The Location class has two properties - Latitude and Longitude both of the type double. You can write these out like @hotel.Coordinates.Latitude and @hotel.Coordinates.Longitude.
Technically the Coordinates property is constructed from the Latitude and Longitude fields in the database, so it has nothing directly to do with the Coordinates field in the database.
Regarding your view, it should still do the same whether you do it one way or the other. But I'm too tired to see if that is the case now. I'll have a look at it later with some fresh eyes ;)
Find top 10 nearest locations from DB near known location
Hey guys
This questions is not directly Umbraco related but I'm going to ask it in here anyway hoping I can get some tips on how to best achieve my goal.
Ok...so I'm working on a pet project where I have a known location for a venue on my model. Based on the latitude and longitude of this venue I want to list the top 10 of the nearest restaurants.
I have the latitude/longitude for the venue on the Model in Umbraco but want to fetch the restaurants from a custom restaurant table, which is stored in the Umbraco database. The table contains more than 100.000 restaurants.
I have been trying to Google how to do this but I'm a bit confused about how to handle the calculations of the distance and whether I should do it directly in the database using a stored procedure and how to make the search performant - How can one enable caching for this? And should I make a surface controller for the functionality etc?
I hope my question makes sense - Otherwise I'm happy to provide further details if needed.
In short...I know point A and want to display the 10 nearest locations based on that known coordinate.
Looking forward to receive any tips, suggestions and hints.
/Jan
Comment author was deleted
Are you using at least sql server 2008? Then you should look at using https://technet.microsoft.com/en-us/library/cc280766(v=sql.100).aspx ;The geography data type
Comment author was deleted
fyi this is usually referred to as nearest neighbor search so if you google for that
Hi Tim
Thank you for the suggestions - Yes I'm using 2008...Express on local machine though but that should not matter, right?
Is the geography datatype supported in newer version of MSSQL as well? Think I read somewhere to avoid using it. But all coordinates are currently saved as decimals...does that matter any?
Aaaah, nearest neighbor! I'm going to google a bit more then :)
/Jan
If you were looking at google again.. here's a code sample I have from my bookmark using polylines and as an aside measuring distances. Which you could abstract out and use to get a cached distance calc?
http://www.geocodezip.com/v3_SO_drawLineFroGeocodedResults.html
Hi Mike
Thanks for the suggestion but that's not quite what I need I think? :)
The thing is that I have a table in my database where I need to make the lookup and return some informations like distance in KM/Miles, Name of the restaurant, stars and address for instance.
I'm still quite puzzled about how to make it happen - Afterall I'm primarily a frontend developer :D
/Jan
If all your locations are already in the db.. you could write a one off query to compare all the locations for distance via google geocoding (to get the lat/lang positions and then calc route lengths) and then store in a lookup table. Then each publish of a new location do the same for that location vs all the others, maintaining your lookup. Maybe even using http://ucomponents.org/data-types/textstring-array/ as a storage mechanism native to umbraco rather than separate db tables? And you could have this against a location... so it knows all the other places distances from itself... or as a overrriding all locations information??
I don't think you are likely to be able to have a real time calculation, google maps api throttles api lookups to ~1per second and 2000 a day for the geocoding from my experience.
Hi Mike
I appreciate the suggestions - But I have several different venues - Each venue will be displayed on it's own page. Since there are more than 100.000 restaurants, I don't want to manage those inside Umbraco. Hence it's own table in the database. I might consider making it a custom section but I won't create the data in Umbraco.
All I need to do is to feed the known coordinate to my own method and have the 5-10 nearest returned. I tried looking at the google distance matrix stuff but it's prohibited to use if data is not going to be displayed on a map. This data is maybe only going to be displayed on a list.
Sorry if I'm missing something here :) Just trying to figure out the best way of doing this...and maybe I'm a bit slow :)
/Jan
Hi guys
I just found this https://gist.github.com/tugberkugurlu/2870461 - Would I be able to use this for my described scenario? I'm not sure how to put it into practice though but perhaps it makes sense to you guys? I'm not sure what should be placed in the part where the comment reads "Insert the place locations here...".
Another options that seems to fit my scenario is this example http://stackoverflow.com/questions/12835851/find-closest-location-with-longitude-and-latitude#answer-12860259 - Again not quite sure how to put it into practice...hints appreciated.
Cheers, Jan
Comment author was deleted
Hi Jan,
You'll need to store your data in the geography datatype so not as decimal, once that is done you should be able to query the data pretty easily
Comment author was deleted
Looks like that gist is using entity framework, maybe you should search for a peta poco alternative since that is used internally by Umbraco, if I got some time this week I'll try to write you some example code
Comment author was deleted
Just did a quick test, what you will need to do is move away from the decimal datatype and use geography (you should be able to write a script to do the move, something like https://science.nature.nps.gov/im/units/arcn/data_management/code/210.htm) ;
Then create a stored procedure that takes in the location you want to search from, query will look like https://msdn.microsoft.com/en-us/library/ff929109.aspx
And then use peta poco to execute that stored procedure and fetch the results http://stackoverflow.com/questions/20126844/execute-stored-procedure-with-petapoco
Any change you can share a create script for your table that holds the data?
Hi Tim
Awesome, thanks - Will have a look later this evening. I'll see if I can script it and send you a link using Twitter.
Cheers, Jan
Hi Tim
I have completed step one of creating the coordinates column based on the approach mentioned in the first link you provided.
Below is a sample of my hotels database - Is it enough for you to do some tests?
Cheers, Jan
Hmm, seems like I'm not able to use a spatial index on MSSQL 2008 express? I'm getting this when I try running the code from you 2nd example "Cannot find either column "SpatialLocation" or the user-defined function or aggregate "SpatialLocation.ToString", or the name is ambiguous."
/Jan
Comment author was deleted
@Jan spatiallocation is the column containing the geography data :) so that will change depending on how you named that column
Comment author was deleted
So in your case it should be Coordinates
Comment author was deleted
So query will look like
DECLARE @g geography='POINT(-121.626 47.8315)';
SELECT TOP(5) Coordinates.ToString(), * FROM Hotels
ORDER BY Coordinates.STDistance(@g);
Hi Tim
Ah yes, of course! That works fine...however the query is taking 24 seconds...But I guess the performance can be fine tuned? And do you know if it would be possible to have the distance calculated in kilometers for instance?
Cheers, Jan
Comment author was deleted
Not a sql guy but I assume it can be made more performant , how many entries do you have in your database?
Hi Tim
Around 120.000 :)
/Jan
Comment author was deleted
Oh my :) well you need to find yourself a db wizard to see if things can be speed up
Comment author was deleted
Seems this offers some help http://blogs.msdn.com/b/isaac/archive/2008/10/23/nearest-neighbors.aspx several alternative queries you can give a try
Comment author was deleted
Can you try the following
First do:
SELECTTOP 100000 IDENTITY(int,1,1)AS n
INTO numbers
FROM MASTER..spt_values a, MASTER..spt_values b
CREATEUNIQUECLUSTEREDINDEX idx_1 ON numbers(n)
Then do
DECLARE @g geography='POINT(-121.626 47.8315)';
DECLARE @start FLOAT = 1000;
WITH NearestPoints AS
(
SELECT TOP(1) WITH TIES *, Hotels.Coordinates.STDistance(@g) AS dist
FROM Numbers JOIN Hotels
ON Hotels.Coordinates.STDistance(@g) < @start*POWER(2,Numbers.n)
ORDERBY n
)
SELECT TOP(1) * FROM NearestPoints
ORDER BY n, dist
Hi Tim
I just tried but unfortunately it does not make any difference. The query takes around 24-26 seconds to execute still :-/
Appreciate the effort though :)
/Jan
Comment author was deleted
dammit :( wel not sure what you can do next...
Hi Tim
I'm not sure how it works exactly but I found this on another forum and it's executing really fast and seems to be reliable.
Now I'm just not sure what the next step would be in order to be able to expose it in a view? And how would I get the distance displayed in Kilometers?
/Jan
Ok, so I think I have figured out how to fetch the data and render it in my view...however I'm currently stuck with my POC since I get this error
Object must implement IConvertible. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidCastException: Object must implement IConvertible. Source Error: Line 19: public IEnumerable
I have a model that looks like this
My Controller looks like this
And finally my view looks like this
Any hints appreciated :)
/Jan
Hi Jan,
I haven't worked with
DbGeographyand the coordinate type in MSSQL before, so I can't say why it fails. So here is what I would do instead:Since you also have
LatitudeandLongitudein your table and your model, I would simply remove theCoordinatesproperty from your model (but still leave it in the database for searches). After this change, your view will succeed, and fetch the first two hotels.Then to get the distance in meters, you can use my
Locationclass:Your view could then look like:
I've skipped your
HotelRepistoryclass in the example above, but that was simply because it was easier for me to test.Hi Anders
Thank you very much for your reply - I'm beginning to learn / get into how petapoco works and how the logic should be defined in MVC serverside wise in an Umbraco context.
I uncommented the Coordinates line from my code and now it works. But I'm not quite there yet. I need to get the 10 nearest hotels to a known location. I'm thinking I should have this logic in the controller so it's only those 10 hotels that are ever returned to the view. For that I need to execute this SQL code somehow
It's executing instantly when I fire it in my DB. So I just need to figure out how to get the known location from my CurrentPage passed to a POINT in the query and how it can be constructed in my controller.
Since I posted my previous question I decided to rewrite my code following Warrens blogpost http://creativewebspecialist.co.uk/2013/07/16/umbraco-petapoco-to-store-blog-comments/ using a surface controller.
So now my code looks like this
The model
The surface controller
The macro partial view
So I'm thinking I could use the contentService to figure out what known location is stored on the currentpage and then pass the coordinates somehow so that I in the view can just loop over the X returned locations.
Hope this makes sense :)
/Jan
If we just look at making this work, the following snippets does the job. In order to be able to reference the
SqlGeographyclass, you should install theMicrosoft.SqlServer.Typespackage from NuGet.distanceis simply an integer with the search distance/radius in meters.The snippet may calculate the distance twice for each row since the distance is sued both for the WHERE clause and for ordering the rows. I can't tell whether this affect performance based on the rows you posted earlier.
To only return the nearest five hotels, you could simply update the SQL to:
I'm not sure that I understand the purpose of the surface controller. Having a repository like the one you posted earlier is generally a very good idea, since you have isolated the code responsible for the database stuff, and then you simply use your repository in the rest of your code rather than doing the database stuff again. If the surface controller makes sense to you, it could be updated to use the repository.
If you stick with the repository class, my take would look like:
Usage would be something like (no limit):
or (with limit):
Also, to simplify your view a bit, you could add a location/coordinates property to your
Hotelclass rather initializing the location in the view:Hi Anders
Thanks a lot - The above stuff is awesome! :)
The only reason I switched to using a surface controller is honestly because I have no clue as to what I'm doing really. I found a video on youtube where the Repository approach was being used but then along the way it was said..."Ok, this is just for coding speed but is not the best way to do it" once the controller was tied to the view.
So in short I'm happy to keep using the HotelRepository...I just need to figure out what the best approach to tie it to my view is? :)
And finally...how would I be able to add the distance in KM's to be displayed in my view? I suppose I can use some of the code you posted earlier...but not sure about the context.
/Jan
Hmm, getting
Error 2 'Hotels.Models.Hotel.Location' is a 'property' but is used like a 'type' in my model where I declare this
and in the controller I get this message - Even though I have installed the Microsoft.SqlServer.Types using nuget and referenced it in my code?
Error 1 The type or namespace name 'Location' could not be found (are you missing a using directive or an assembly reference?)
The code is copy/pasted from the example. What to do? :)
/Jan
Repository vs surface controller
The idea of the repository is to isolate the code into pieces that can be re-used, and then only have a single place in the code to maintain the database stuff. The repository doesn't exclude the surface controller though.
Say you start out by implementing the surface controller, and make the calls to the database directly in the class. Things will work fine.
Then a month later, you need do something else, that also needs to pull hotels from the database, but yet is so different that you can't use your surface controller from before. If you just copy the bits of the code that you need, and place it in a new surface controller, WebApi controller or wherever you need it, you will end up having two almost identical pieces of code.
Then a bit later, a change needs to be made to the "Hotels" table in the database, and you will now have to update your code two places in your code.
If you had initially implemented the repository, your would most likely only have had to update the repository class, and the rest of your code would work fine again.
The repository can somewhat be compared to the service layer in Umbraco. Everything in Umbraco uses the services - even the backoffice. The Umbraco services are probably a bit more complex, but the idea is the same: the places in your code you need to update/maintain is minimized.
Your view
Anyways, given the changes in my previous post, my view would look like:
Regarding your build errors, Location is my custom class posted in this comment: https://our.umbraco.org/forum/developers/razor/61832-Find-top-10-nearest-locations-from-DB-near-known-location?p=2#comment212701
Adding this to the project should fix the build errors.
Hi Anders
Ah yes, of course :) Added the class now but now getting
Error 3 The name '_location' does not exist in the current context in the model?
/Jan
Seems like you need the private field - see line 8:
Hi Anders
Thanks for your patience - Overlooked that one. Build finally succeeded. But now I'm getting this error in the log file when i'm trying to render my partial view
2015-03-15 17:15:33,497 [39] WARN umbraco.macro - [Thread 31] Error loading Partial View (file: ~/Views/MacroPartials/GetNearByHotels.cshtml). Exception: System.InvalidOperationException: The model item passed into the dictionary is of type 'Umbraco.Web.Models.PartialViewMacroModel', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[Hotels.Models.Hotel]'.
My view looks like this
So what am I doing wrong here? :)
/Jan
As your view is right now,
tells the compiler that the type of the expected model (could be described as a kind of scope) is
IEnumerable<Hotel>, but is instead of the typeUmbraco.Web.Models.PartialViewMacroModel. To fix it, simply replace the line with:Anders, you're da man! :) Thank you very very much for all you help - It's freaking working -Yipee! #h5yr sir!
I had to switch the coordinates though...for some reason when using POINT it's expecting Lng/Lat...instead og Lat/Lng. But once I switched those it worked like a charm.
/Jan
Glad to see it work.
As far as I could tell it still worked fine even though the coordinates are stored as lng/lat in the database?
I have not taken the time to get fully into what the code does - But in regards to the query then I don't think it would succeed without making use of the geography datatype (Coordinates) since there is a spatial index on this column...if that's what you're thinking about?
However I can see there is some weird stuff going on with calculations...I just tested with a coordinate for my own address - I know there is a hotel less than 2 km from here but the distance returned is around 6,4 km...But time to eat, will have a look at things afterwards :)
Very happy about the breaktrough though.
/Jan
Hi Anders
Just had a closer look at the code - Now I get it...you're not even using the Geography data but only looking at the coordinates. Well it seems to be fast. But I don't know if it can have an impact on the accuracy of the distance calculation perhaps?
I just checked with Google maps - There is a hotel 2.2 km from me but according to the distance calculation it's 6.4 km away...that's a bit too much inaccuracy for the use case. 500 meters +/- would be acceptable...but not sure how to make calculations more exact?
/Jan
Hi again,
Yes, since're the table also has columns for latitude and longitude individually, I thought it was easiest to simply use them instead. So the coordinates field is for searching (unless you use it for something else of course).
The calculation in the code is only for the hotels being displayed, and it will take less than a millisecond to execute.
Regarding the distance being wrong, are you able to send me the coordinates? According to my own tests of the logic in Skybrud.Social (which is where I copied it from), it works correctly.
Hi Anders
Aaah, that's how it works - Cool and thanks for explaining :)
I just sent you the coordinates from my place to the hotel I mentioned in DM.
/Jan
Here is the data for the hotel in question
/Jan
Hi Anders
Ok, we have found out via Twitter that you get the correct distance of 1.49 km in your setup and I for some reason get a wrong distance of 6.4 km on my setup. So therefore I'm posting my exact code to make sure it's not because I have overlooked something or done something wrong :)
Here we go...
Model
Controller
View
I have not touched the Location class but posting it anyway
Location.cs
**
/Jan
Hi Anders
Thanks for the ping pong on Twitter - I just noticed something in the rendering of the view.
When I try to write out @hotel.Coordinates I just get a string that says "Location"...I don't know if that's correct? I would expect to see the geography stuff from the database, which looks like this
But if I change my view to use the coordinates for the hotels in the distance calculations it seems to be more correct - So if I write it like this, then I get a distance of 2.3 KM to the hotel...and Google was giving me 2.2...so that's seems to be fairly accurate, no? :)
Does this make sense?
/Jan
Hi Jan,
That is actually the expected behavior. The
Coordinatesproperty is of the typeLocation.Locationis a class, and writing it out on the page like you're doing, will simply result in "Location". TheLocationclass has two properties -LatitudeandLongitudeboth of the typedouble. You can write these out like@hotel.Coordinates.Latitudeand@hotel.Coordinates.Longitude.Technically the
Coordinatesproperty is constructed from theLatitudeandLongitudefields in the database, so it has nothing directly to do with theCoordinatesfield in the database.Regarding your view, it should still do the same whether you do it one way or the other. But I'm too tired to see if that is the case now. I'll have a look at it later with some fresh eyes ;)
is working on a reply...