CODEWITHSUNDEEP
sqlsql-server-2012spatialwkt
Kenneth
Kenneth

Reputation: 28737

Spatial query in SQL not correctly returning intersection on area and point

I'm doing a spatial query on MS SQL and I'm checking whether a given point is inside an area. I have a query for an area that does contain the point, but the query returns 0 (false):

declare @area geography;
declare @point geography;
set @area = geography::STGeomFromText('POLYGON((87.3924  76.73386,-61.67004 76.73386,-61.67004 27.12494,87.3924 27.12494,87.3924 76.73386))', 4326);
set @point = geography::STGeomFromText('POINT(-2.7933 56.3412)', 4326);
select @area.STIntersects(@point);
select @point.STIntersects(@area);

For both SELECT statements the return value is 0, meaning that they don't intersect. The strange thing is that when I make the area a little bit smaller, the queries return 1

I know the order of the polygon is important and if I specify it in the other direction, it does return a 1. The questions I have:

These are the visual representations of these WKT's: Area Point

EDIT: Here are three polygons of different sizes (from largest to smallest):

1. POLYGON((-171.173863132129  82.6444755487971,-109.298863132129 82.6444755487971,-109.298863132129 -2.35770630179323,-171.173863132129 -2.35770630179323,-171.173863132129 82.6444755487971))

2. POLYGON((77.2050431178711  76.1254124064562,-71.8574568821289 76.1254124064562,-71.8574568821289 37.7996777961385,77.2050431178711 37.7996777961385,77.2050431178711 76.1254124064562))

3. POLYGON((38.8847306178711  68.43690689125,-35.6465193821289 68.43690689125,-35.6465193821289 48.1758722609095,38.8847306178711 48.1758722609095,38.8847306178711 68.43690689125))

The strange thing here is that the 1st polygon matches all points in the UK. The second query matches none in the UK and the third one matches only a few points in the north.

If you visualize the queries you'll see that the second and third include the complete UK whereas the first one is completely off Visualization tool: http://thisissammysaccount.github.io/VisualizeWellKnownText/

So I guess, the question really is, how do I map google maps bounds to a spatial query. (Basically I want to to show get all the locations that are in the current viewport)

Upvotes: 1

Views: 1706

Answers (1)

Jon Bellamy
Jon Bellamy

Reputation: 3373

Whilst you'd think that your point is within your polygon (and on a planar model it is), its not on a ellipsoidal model.

You're suffering from a lack of coordinates in your polygons as they grow in size. The lack of coordinates, causes the lines that follow your longitude (top and bottom) of the rectangle to "bend" around the curvature of the earth as they have no additional coordinates (or anchor points) to keep them "straighter". This bend gets more prominent as the polygon increases in size, which is why you see results more like you expect when they are smaller. Because of the projection system Google Maps uses, you won't see this (although if memory serves me right, drawing the polygon with geodesic: true might show it).

I've attached an image below illustrating this in Mercator projection. The point has been buffered by 200KM just so you can see it clearly. This is the downside of using the 4326 at such large zoom levels.

You could do the following:

  1. Use Geometry (although I wouldn't normally recommend this for primary use of geography objects). The planar model will remove this problem.

  2. Manually add more coordinates to your bounding box, keeping the latitude value the same but altering the longitude along the top and bottom lines. However, this will still not be perfect, but it will get better results.

  3. Consider whether or not you really need to show points after a certain level of zoom - if there are lots, it may not be that useful.

Polygon and Point

Upvotes: 1

Related Questions