inside lat,lng bounds where query

1
votes

I am getting lat/lng bounds from google map in client side through xhr to my php script. which is making an sql query to search location within that bounds. My table stores lat, lng as different columns. now how can I arrange my where queries such that only the points within the specified NE, SW bounds are returned.

In database its stored as number and I don't know if there already is any postgresql specific aggregate function to do the same.

2
phppostgresqlgoogle-mapsgoogle-maps-api-3
Look into PostGIS to add geographic support to PostgreSQL. – carmenism
I've never used this. however if this is my only requirement should I opt for installing a new extension ? – Dipro Sen

2 Answers

3
votes

Am I missing something? It's just a rectangular bounds check. It's not like you need to calculate the distance between the points.

WHERE lat BETWEEN lat_of_corner_a AND lat_of_corner_b
  AND lon BETWEEN lon_of_corner_a AND lon_of_corner_b
2
votes

I posted the working solution here: Get all records from MySQL database that are within Google Maps .getBounds?

Alex's solution only works in USA, specifically in the north hemisphere, west region (were decreasing values fit the order in which Google provides the bouds values.

This one works for the other 3/4 of the world.

SELECT * FROM tilistings WHERE
(CASE WHEN a < c
        THEN lat BETWEEN a AND c
        ELSE lat BETWEEN c AND a
END) 
AND
(CASE WHEN b < d
        THEN lng BETWEEN b AND d
        ELSE lng BETWEEN d AND b
END)

(See the linked post for more details)