sql-serverazure-mapsnettopologysuite

How do I create a NetTopologySuite.Polygon from a Atlas.BoundingBox?


I need to create a Polygon so that in my Entity Framework query I can do:

point.Intersects(polygon)

The code to create the point is:

var center = new TopologyPoint(longitude, latitude)
{
    SRID = 4326
};

I think the code to create the rectangle is:

// Define the four corners of the rectangle
Coordinate lowerLeft = new Coordinate(box.West, box.South);
Coordinate upperLeft = new Coordinate(box.West, box.North);
Coordinate upperRight = new Coordinate(box.East, box.North);
Coordinate lowerRight = new Coordinate(box.East, box.South);

// Create a linear ring (a closed line string) from the corners
Coordinate[] coordinates = new Coordinate[] { lowerLeft, upperLeft, upperRight, lowerRight, lowerLeft };
LinearRing shell = new LinearRing(coordinates);

// Create the polygon from the linear ring
var rectangle = new Polygon(shell);

Is this correct? Will this be matching lat/long units? Or do I need something like the SRID = 4326 for the Point?

I am using this to determine what entities to return from a query to then populate an Azure Map with pins. Will the earth's curvature mean it will return points that are not visible in the map (ok)? Or does it mean it will not return some points that are on the edge of the bounding box of the map?

Should I add to the extents of the rectangle so I'm sure to get everything? If so, is there a multiple of the width & height to add to get everything?


Solution

  • As noted int he comments, ring orientation matters in spatial databases, and for a simple exterior polygon, you will want the points to be order in counter-clockwise order.

    To be a valid polygon in a spatial database there are a few other requirements as noted here: https://learn.microsoft.com/en-us/sql/relational-databases/spatial/polygon?view=sql-server-ver16 For your scenario the main two requirements to be aware of is the point order (ring orientation), and that the first and last point are the same.

    I believe you will need to set the SRID as the spatial database EF connects to will likely check that the SRID's between the polygon and the data in your database match.

    The earths curvature and bounding boxes actually does get complicated here. Azure Maps uses what's called Pseudo-Mercator map projection (EPSG:3857) which is how the map is stretched out (most online maps use this projection). However, using the Geography type with 4326 will use an ellipsoidal projection (squashed sphere). As such a large bounding box from a Mercator map will not match up to the same area as the ellipsoidal projection along the north/south edges of the bounding box (the middle of an edge can end up including or excluding a bit of area. This effect becomes more noticeable the further you get from the equator. This is why the Geography type in SQL does not have a STEnvelope method, but the Geometry type does. Instead the Geography type has EnvelopeCenter and EnvelopeAngle methods which is a more accurate bounding area. If you take the polygon you are calculating and display it in as a SQL Geography object in the SQL management studio, you will see it looks like the following:

    enter image description here

    Now there are a couple of options here:

    1. Use the polygon you are creating from the bounding box (Mercator map). The margin of error is only really noticeable when zoomed out a lot and using large bounding boxes (edges are hundreds of miles long). Most go this route to keep things simple. I recommend starting here, the info below this is just for deeper insights.
    2. Make your data in the database Geometry but keep the coordinates the same (4326 decimal degrees). You can test this in a query by doing the conversion as part of the query (it will be slower, but little work to try and see if it provides better results for your scenario). A simple way to convert from Geography to Geometry in SQL geometry::STGeomFromWKB(MyGeographyColumn.STAsBinary(), 4326); Here is a related thread: In Leaflet, how to convert leaflet viewport bounding box in EPSG3857 so I can retrieve data stored in EPSG4326
    3. If you only care about ensuring that you have all data in the map view and are fine with a little extra data being loaded that's just outside of the view, you can use a larger search area. A circle for example could be calculated from the center of the map to the any corner of the map. A circle intersection calculation can be a lot faster as you would simply calculate distances from the center to each point and see if it is less than or equal to the radius and would not do a polygon intersection test.
    4. Convert the data in your database to a Pseudo-Mercator projection (3857), then the polygon created from the bounding box will match up 100%. This is the approach used by most large map platforms for maintaining their data behind the scenes as this also simplifies a lot of calculations as you can get away with using regular geometry calculations and not need complex spherical calculations (less use of trig functions in calculations). That said, few take this approach for smaller datasets as it can be a pain to maintain if you are not used to converting between projections. If you are storing point data, then having two columns in your database is a decent option. One column with the original Geography 4326 data, and a second with the Mercator 3857 Geometry. You can then query against the geometry column and return the geography column for displaying on the map. This saves you having to reproject the data on the fly. That said, reprojecting data in SQL isn't built in and you would likely want to create a stored procedure to do this conversion as I don't believe their is an add-in for Azure SQL for this. PostgreSQL with the PostGIS plugin in Azure however has built in projection calculations and better supports these complex scenarios. That said, using Mercator coordinates will make it difficult to handle the scenario where your bounding box crosses the anti-Merdian (-180/180 longitude).

    Note, if you convert your Geography data to Geometry some calculations, such as distance, will not provide desired results. 4326 data as a Geometry will provide distances in degrees and not meters. While Geography distance calculations will provide meter distances.