finding sql geography point within rectangular (polygon)

Question

I have an interesting/annoying issue with finding lat and long of land marks inside the rectangular boundary. I believe my two points are inside my rectangular boundary. but as you can test yourself the result of the the first select is false instead of true!

DECLARE @boundingRect varchar(1000)
DECLARE @maxLat VARCHAR(20)
DECLARE @minLong VARCHAR(20)
DECLARE @minLat VARCHAR(20)
DECLARE @maxLong VARCHAR(20)


set @maxLat ='-36.06631759541187'
set @minLong ='125.23310677812492'
set @minLat ='-44.43329881450396'
set @maxLong='167.04707162187492'


SET @boundingRect = 'POLYGON((' +   @minLong + ' '  + @minLat + ', ' +
                                        @maxLong + ' ' + @minLat + ', ' + 
                                        @maxLong + ' ' + @maxLat + ', ' + 
                                        @minLong + ' ' + @maxLat + ', ' + 
                                        @minLong + ' ' + @minLat + '))'

DECLARE @Bounds AS Geography =GEOGRAPHY::STPolyFromText(@boundingRect,4326)

DECLARE @point1 AS GEOGRAPHY = GEOGRAPHY::Point(-37.81502, 144.94601, 4326)
DECLARE @point2 AS GEOGRAPHY = GEOGRAPHY::Point(-38.81502, 144.94601, 4326)


SELECT @Bounds.STIntersects(@point1)
SELECT @Bounds.STIntersects(@point2)

To give you background, I have list of land marks (lat,long) that I want to load on google maps. Since the number of landmarks are too many, I cannot return all of them at once. I need to return the landmarks that are in the areas that are visible to user , in their viewing boundary. I'm getting north west (max lat,min long) and south east (min lat, max long) of google maps boundary and sending it to my stored procedure to return back the list of the land marks within that boundary. However, as I explained above I have issues and some land marks are missing in list.

@point1 does not intersect due to the curvature of the earth: — Jason Horner, Feb 17 '16 at 15:14
Azadeh, If you want to use Geography polygon and still handle the curvature problem, I suggest you to add more points to the polygon. — Amin, Feb 17 '16 at 18:21
for small distances, the geometry data type is fine for very long distances is best to use geographyc, but few marks are in total, also you can use a cluster https://developers.google.com/maps/articles/toomanymarkers — phipex, Mar 10 '16 at 14:29

score 7 · Answer 1 · answered Feb 17 '16 at 15:16

@point1 does not intersect this can be verified by:

DECLARE @boundingRect varchar(1000)
DECLARE @maxLat VARCHAR(20)
DECLARE @minLong VARCHAR(20)
DECLARE @minLat VARCHAR(20)
DECLARE @maxLong VARCHAR(20)


set @maxLat ='-36.06631759541187'
set @minLong ='125.23310677812492'
set @minLat ='-44.43329881450396'
set @maxLong='167.04707162187492'


SET @boundingRect = 'POLYGON((' +   @minLong + ' '  + @minLat + ', ' +
                                        @maxLong + ' ' + @minLat + ', ' + 
                                        @maxLong + ' ' + @maxLat + ', ' + 
                                        @minLong + ' ' + @maxLat + ', ' + 
                                        @minLong + ' ' + @minLat + '))'

DECLARE @Bounds AS Geography =GEOGRAPHY::STPolyFromText(@boundingRect,4326);

DECLARE @point1 AS GEOGRAPHY = GEOGRAPHY::Point(-37.81502, 144.94601, 4326);
DECLARE @point2 AS GEOGRAPHY = GEOGRAPHY::Point(-38.81502, 144.94601, 4326);


SELECT @Bounds.STIntersects(@point1);
SELECT @Bounds.STIntersects(@point2); 


SELECT @point1, 'Point 1'
UNION ALL
SELECT @Point2, 'Point 2'
UNION ALL
SELECT @BoundingRect, 'Rect'

Thanks Jason! Loved the idea of using union and see the results in "Spatial results" window — Azadeh Khojandi, Feb 18 '16 at 00:00

xLiSPirit · Accepted Answer · 2016-02-17T07:15:18.363

3

Actually, geography polygon isn't rectangle:

If you want rectangle, you can use geometry polygon:

edited Feb 17 '16 at 07:15

answered Feb 17 '16 at 07:07

xLiSPirit

370
1
8

1

You *could* use geometry, but shouldn't. Why? Because the data represents points and a region on the oblate spheroid we call home and as such doesn't conform to Cartesian geometry. – Ben Thul Feb 17 '16 at 15:38

finding sql geography point within rectangular (polygon)

2 Answers2

Linked