6

I have the North, East, South and West values of a Rectangular location, how can I search only those locations (Latitude/Longitude) that are within the bounds of that Rectangular location?

enter image description here

Please note that I need to search the Locations (Latitude/Longitude) within a database. I need to find the locations in the database which are in the bounds of a given rectangular and I will draw markers for them on the map.

I am not talking about Google search, the APIs for search already support passing the rectangular bound to filter the search.

The googlemaps APIs can do this for me like:

var request = {
    bounds: myMapObject.getBounds(), // Search only within the Bound of the Map
    query: someTextToFind
};

OR

var isWithInMapsBounds = myMapObject.getBounds().contains(myLocation);

But I need to do it while getting the locations from database, I mean on Server side.

Can someone guide me in the right direction, how to achieve this functionality?

EDIT:

I want to search within database; so I need an SQL solution.

Yaqub Ahmad
  • 27,569
  • 23
  • 102
  • 149
  • Loading your image tooks hours. In the meanwhile I found this thread on Google groups: [Point in Polygon Function?](https://groups.google.com/forum/?fromgroups=#!topic/google-maps-js-api-v3/VkFyBBqWYb8) – Alina B. Mar 23 '13 at 06:28
  • This thread can not help me.. as I mentioned I can do it using googlemaps .. but I need to do it at the time when I am getting locations from the database. – Yaqub Ahmad Mar 23 '13 at 06:39
  • possible duplicate of [google maps v3: check if point exists in polygon](http://stackoverflow.com/questions/6522484/google-maps-v3-check-if-point-exists-in-polygon) – CanSpice Mar 24 '13 at 18:50
  • @CanSpice Its not the duplicate, actually I want to search within database. – Yaqub Ahmad Mar 25 '13 at 03:15

2 Answers2

3
DECLARE @Bounds AS Geography = GEOMETRY::STGeomFromText('polygon-wkt-here',0)

SELECT columns-list
FROM   table
WHERE  1 = @Bounds.STIntersects(table.point-column)

Quick note for those that are in the dark here about why you can't just compare your point location to the north/south/east/west coordinates of your bounding box:

The bounding box may not be lat/long-aligned (eg. its north/south/east/west lines may not follow lat/long axis). This will happen if you draw a planar rectangle on a Lat/Long map projection. The distortion will be more apparent the closer to the edge of the bounding box the point is, and the farther from the Equator.

The second reason to use the Geography data types is that it provides a uniform method of performing these kinds of operations for ANY polygon, not just rectangles. So now you can compare if a single point is within your box, or find all the addresses in a zipcode, all with the same piece of code.

Sam Axe
  • 33,313
  • 9
  • 55
  • 89
1

SQL Query

SELECT *  FROM mytable WHERE lat >= bottomlat  AND lat <= toplat AND lng >= leftlng AND lng <= rightlng

If crossing Prime Meridian or Equator you will need to modify the query 

SELECT * FROM mytable WHERE lat >=bottomlat AND lat <= toplat  AND   lng >= leftLng AND lng < 0 
UNION
SELECT * FROM mytable WHERE lat >=bottomlat AND lat <= toplat  AND  lng >= 0 AND lng  <= rightLng
david strachan
  • 7,174
  • 2
  • 23
  • 33