Gotcha: Drawing shapes on Maps gives me the complete opposite results to what I want

When you draw a shape on a Map (eg. Google Maps) to find all relevant whatever's in that shape (eg. Schools or Doctors in that area) ... the results that come back are the complete OPPOSITE to what you want! Everything -but- the real data, in that shape.

This is because the direction you are drawing your shape determines whether the geospatial query will say "everything inside this shape" or "everything outside of this shape".

Yes. The direction a person draws the shape determines the results.

For Sql Server, this drawing direction is called Ring Orientation and when you draw a shape anti-clockwise, then this means "everything outside of this shape". Clock-wise == inside this shape.

To make matters worse, a website cannot assume or stipulate for a user to draw a shape in a particular direction.

So - what do to?

See if a really random point of the earth is Inside or Outside of the shape. Basically, if this random point is in the shape, then the shape is the wrong way and we need to flip it.

Here's some Sql Server 2012 code.

-- Some randomly drawn shape in Australia.
-- This is dawn ANTI-CLOCKWISE. As such, Sql Server will create a shape that is the entire world EXCEPT this area.
DECLARE @Shape GEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON((145.0452884404339 -37.689788528917106,145.04666173145358 -37.689788528917106,145.04666173145358 -37.68870179112924,145.04940831348395 -37.68761503741733,145.05078160449463 -37.68652826778011,145.05215489551432 -37.68652826778011,145.0562747685554 -37.68652826778011,145.06176793262512 -37.68544148221917,145.06726109668585 -37.68544148221917,145.07687413379662 -37.68544148221917,145.08511387988773 -37.68652826778011,145.09610020800918 -37.68652826778011,145.10708653613966 -37.68761503741733,145.1208194462915 -37.689788528917106,145.1345523564523 -37.69087525077936,145.14828526660415 -37.69413532081646,145.16201817676497 -37.696308621213525,145.17575108691682 -37.69956845236444,145.19085728808835 -37.70391467093012,145.2018436162188 -37.70934708577984,145.21420323535995 -37.71477910243525,145.22518956348142 -37.72129699677896,145.2361758916029 -37.73215887964408,145.24716221973335 -37.743019169405386,145.25814854785483 -37.75604941400161,145.2691348759763 -37.771248465936765,145.2773746220764 -37.78752969820369,145.2842410771568 -37.80272227938165,145.28836095019787 -37.816826878524175,145.29110753222824 -37.8298441170335,145.2924808232479 -37.84069006184926,145.29385411425858 -37.84936566955604,145.29385411425858 -37.85804025660332,145.29385411425858 -37.86454552703363,145.2924808232479 -37.869966147102794,145.28973424121753 -37.8743023560463,145.2869876591872 -37.876470364810665,145.2842410771568 -37.87972225831982,145.28012120410676 -37.88189010756544,145.27600133105673 -37.88297400825934,145.2691348759763 -37.884057893001405,145.26226842090486 -37.88514176179004,145.24441563770299 -37.884057893001405,145.22244298145105 -37.880806190918406,145.21008336230992 -37.87972225831982,145.19772374316878 -37.8775543452658,145.18261754199725 -37.873218327737405,145.16613804980605 -37.86888205499009,145.15103184863455 -37.86454552703363,145.135925647463 -37.85912450821483,145.1208194462915 -37.85370309066704,145.10708653613966 -37.84828127440802,145.0947269169985 -37.842859059458476,145.08374058886807 -37.836351875273195,145.07687413379662 -37.833098067908416,145.06726109668585 -37.82767473672505,145.06039464160543 -37.82225100692435,145.0549014775447 -37.81791173609146,145.05078160449463 -37.81357221016529,145.0480350224643 -37.81140235154531,145.04666173145358 -37.80814744404734,145.0452884404339 -37.80597742600565,145.0452884404339 -37.803807344197445,145.0452884404339 -37.689788528917106))', 4326) 


 -- Does the point 0,0 exist in the shape? Yes - flip the shape. Else, we're good!
 IF GEOGRAPHY::STGeomFromText('POINT(0 0)', 4326).STIntersects(@Shape) = 1 SET @Shape = @Shape.ReorientObject()

-- Do our normal query..
SELECT HospitalId FROM [dbo].[Hospitals] WHERE LatitudeLongitude.STIntersects(@Shape) = 1

etc.

Sweet!

References: This post extends my previous post about Sql Server Geography shape that is accidently covering the entire planet!