I loaded this (Quebec) OSM PBF file in my PostgreSQL database and from a .NET Core Web Api, I'm trying to do basic queries, like finding polygons within a specified area, but getting no results.
I have scaffolded a DbContext using this command:
Scaffold-DbContext "Host=52.0.0.0;Database=my-db;Username=postgres;Password=password" Npgsql.EntityFrameworkCore.PostgreSQL -OutputDir Models
I got this error though:
Could not find type mapping for column 'public.planet_osm_polygon.way' with data type 'geometry(Geometry,3857)'. Skipping column. Unable to scaffold the index 'planet_osm_polygon_way_idx'. The following columns could not be scaffolded: way.
So I manually added this property to the PlanetOsmPolygon.cs that was produced by the command
public Geometry Way { get; set; }
Weird thing is I do have Geometry available as I can declare a variable of that type and compile properly....
I think I have properly set up the context for PostgreSQL and PostGIS
services.AddDbContext<OSMContext>(x =>
{
var connectionString = Configuration.GetValue<string>("SQL:Prod");
x.UseNpgsql(connectionString, o =>
{
o.CommandTimeout(240);
o.UseNetTopologySuite();
});
}, ServiceLifetime.Scoped);
Here's the API method code
GeometryFactory geometryFactory = new GeometryFactory();
//values are from query string
//north=45.5154045103725&south=45.4901382447544&west=-73.5854034422038&east=-73.5493545534017
Polygon space = geometryFactory.CreatePolygon(new Coordinate[] {
new Coordinate(west, south),
new Coordinate(east, south),
new Coordinate(east, north),
new Coordinate(west, north),
new Coordinate(west, south) });
//var polygons = OSMContext.PlanetOsmPolygon.Where(x => x.Way.Covers(space)).ToList();
//var polygons = OSMContext.PlanetOsmPolygon.Where(x => x.Way.CoveredBy(space)).ToList();
//var polygons = OSMContext.PlanetOsmPolygon.Where(x => space.Covers(x.Way)).ToList();
//var polygons = OSMContext.PlanetOsmPolygon.Where(x => x.Way.Covers(space)).ToList();
var polygons = OSMContext.PlanetOsmPolygon.Where(x => x.Way.Within(space)).ToList();
As you can see, I've tried a few way around this, with no results ever. space is valid (IsValid returns true).
Is it the values that I'm using for space that may be a problem ? Or that scaffold issue ?
