public ActionResult FindHotel(string corLat, string corLong, int radius = 2000)
{
double corLatParsed = Double.Parse(corLat, CultureInfo.InvariantCulture);
double corLongParsed = Double.Parse(corLong, CultureInfo.InvariantCulture);
var hotelList = new List<GeoJsonModel>();
using (var conn = new NpgsqlConnection())
{
conn.ConnectionString = "PORT=5432;TIMEOUT=15;POOLING=True;MINPOOLSIZE=1;MAXPOOLSIZE=20;COMMANDTIMEOUT=20;DATABASE=pdtgis;HOST=localhost;USER ID=postgres;PASSWORD=morty";
conn.Open();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = String.Format("SELECT hotel.name, hotel.amenity, ST_AsGeoJson(hotel.way), park.name, park.amenity, ST_AsGeoJson(ST_Centroid(park.way)) FROM planet_osm_point hotel, planet_osm_polygon park WHERE hotel.tourism = \'hotel\' AND park.leisure = \'park\' AND ST_DWithin(ST_SetSRID(ST_MakePoint({0}, {1}), 4326)::geography, Geography(hotel.way), {2}) ORDER BY ST_Distance(Geography(hotel.way), park.way) LIMIT 10", corLong, corLat, radius);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var hotel = new GeoJsonModel();
var park = new GeoJsonModel();
hotel.type = "Feature";
hotel.properties = new GeoJsonProperties();
hotel.properties.name = !reader.IsDBNull(0) ? reader.GetString(0) : String.Empty;
hotel.properties.amenity = !reader.IsDBNull(1) ? reader.GetString(1) : String.Empty;
hotel.properties.MarkerSymbol = "lodging";
hotel.properties.MarkerColor = "#FF0000";
hotel.properties.MarkerSize = "medium";
hotel.properties.title = !reader.IsDBNull(0) ? reader.GetString(0) : String.Empty;
hotel.geometry = JsonConvert.DeserializeObject<GeoJsonGeometry>(reader.GetString(2));
hotelList.Add(hotel);
park.type = "Feature";
park.properties = new GeoJsonProperties();
park.properties.name = !reader.IsDBNull(3) ? reader.GetString(3) : String.Empty;
park.properties.amenity = !reader.IsDBNull(4) ? reader.GetString(4) : String.Empty;
park.properties.MarkerSymbol = "park";
park.properties.MarkerColor = "#00FF00";
park.properties.MarkerSize = "medium";
park.properties.title = !reader.IsDBNull(3) ? reader.GetString(3) : String.Empty;
park.geometry = JsonConvert.DeserializeObject<GeoJsonGeometry>(reader.GetString(5));
hotelList.Add(park);
}
}
}
}
var geoJsonFeatures = new GeoJsonFeatureCollection();
geoJsonFeatures.type = "FeatureCollection";
geoJsonFeatures.features = hotelList;
return new JsonDotNetResult(geoJsonFeatures);
}
public ActionResult GetAtmSupermarketInRadius(int radius)
{
var atmList = new List<GeoJsonModel>();
using (var conn = new NpgsqlConnection())
{
conn.ConnectionString = "PORT=5432;TIMEOUT=15;POOLING=True;MINPOOLSIZE=1;MAXPOOLSIZE=20;COMMANDTIMEOUT=20;DATABASE=pdtgis;HOST=localhost;USER ID=postgres;PASSWORD=mato777bleskino";
conn.Open();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = string.Format("SELECT x.operator,y.name,ST_AsGeoJson(x.way), ST_AsGeoJson(y.way), ST_Distance(Geography(x.way),Geography(y.way)) from planet_osm_point x, planet_osm_point y where(x.amenity = \'atm\' AND x.operator IS NOT NULL) AND(y.shop = \'supermarket\') AND ST_DWithin(Geography(x.way), Geography(y.way), {0})", radius);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var atm = new GeoJsonModel();
var shop = new GeoJsonModel();
atm.type = "Feature";
atm.properties = new GeoJsonProperties();
atm.properties.name = !reader.IsDBNull(0) ? reader.GetString(0) : String.Empty;
atm.properties.title = !reader.IsDBNull(0) ? reader.GetString(0) : String.Empty;
atm.properties.description = !reader.IsDBNull(4) ? reader.GetDouble(4).ToString() : String.Empty;
atm.properties.MarkerSymbol = "bank";
atm.properties.MarkerColor = "#FF0000";
atm.geometry = JsonConvert.DeserializeObject<GeoJsonGeometry>(reader.GetString(2));
atmList.Add(atm);
shop.type = "Feature";
shop.properties = new GeoJsonProperties();
shop.properties.name = !reader.IsDBNull(1) ? reader.GetString(1) : String.Empty;
shop.properties.title = !reader.IsDBNull(1) ? reader.GetString(1) : String.Empty;
shop.properties.description = !reader.IsDBNull(4) ? reader.GetDouble(4).ToString() : String.Empty;
shop.properties.MarkerSymbol = "shop";
shop.properties.MarkerColor = "#00FF00";
shop.geometry = JsonConvert.DeserializeObject<GeoJsonGeometry>(reader.GetString(3));
atmList.Add(shop);
}
}
}
}
var geoJsonFeatures = new GeoJsonFeatureCollection();
geoJsonFeatures.type = "FeatureCollection";
geoJsonFeatures.features = atmList;
return new JsonDotNetResult(geoJsonFeatures);
}
public ActionResult GetAtmAll()
{
var atmList = new List<GeoJsonModel>();
using (var conn = new NpgsqlConnection())
{
conn.ConnectionString = "PORT=5432;TIMEOUT=15;POOLING=True;MINPOOLSIZE=1;MAXPOOLSIZE=20;COMMANDTIMEOUT=20;DATABASE=pdtgis;HOST=localhost;USER ID=postgres;PASSWORD=mato777bleskino";
conn.Open();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText =
"SELECT operator, ST_AsGeoJson(way) FROM planet_osm_point WHERE amenity = \'atm\' AND operator IS NOT NULL";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var atm = new GeoJsonModel();
atm.type = "Feature";
atm.properties = new GeoJsonProperties();
atm.properties.name = !reader.IsDBNull(0) ? reader.GetString(0) : String.Empty;
atm.properties.title = !reader.IsDBNull(0) ? reader.GetString(0) : String.Empty; ;
atm.properties.MarkerSymbol = "bank";
atm.properties.MarkerColor = "#AD42F5";
atm.properties.MarkerSize = "small";
atm.geometry = JsonConvert.DeserializeObject<GeoJsonGeometry>(reader.GetString(1));
atmList.Add(atm);
}
}
}
}
var geoJsonFeatures = new GeoJsonFeatureCollection();
geoJsonFeatures.type = "FeatureCollection";
geoJsonFeatures.features = atmList;
return new JsonDotNetResult(geoJsonFeatures);
}
public ActionResult GetTescoAll()
{
var tescoList = new List<GeoJsonModel>();
using (var conn = new NpgsqlConnection())
{
conn.ConnectionString = "PORT=5432;TIMEOUT=15;POOLING=True;MINPOOLSIZE=1;MAXPOOLSIZE=20;COMMANDTIMEOUT=20;DATABASE=pdtgis;HOST=localhost;USER ID=postgres;PASSWORD=mato777bleskino";
conn.Open();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText =
"SELECT name, ST_AsGeoJson(way) FROM planet_osm_point WHERE shop='supermarket' AND lower(name)LIKE '%tesco%'";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var tesco = new GeoJsonModel();
tesco.type = "Feature";
tesco.properties = new GeoJsonProperties();
tesco.properties.name = !reader.IsDBNull(0) ? reader.GetString(0) : String.Empty;
tesco.properties.title = !reader.IsDBNull(0) ? reader.GetString(0) : String.Empty; ;
tesco.properties.MarkerSymbol = "shop";
tesco.properties.MarkerColor = "#FA1E1E";
tesco.properties.MarkerSize = "small";
tesco.geometry = JsonConvert.DeserializeObject<GeoJsonGeometry>(reader.GetString(1));
tescoList.Add(tesco);
}
}
}
}
var geoJsonFeatures = new GeoJsonFeatureCollection();
geoJsonFeatures.type = "FeatureCollection";
geoJsonFeatures.features = tescoList;
return new JsonDotNetResult(geoJsonFeatures);
}