SQL How to select properties from given multiple suburbs within 5KM range with the given suburbs?

Question

I have two table as shown below:

It shall list the properites in surrounding 5 kms of the selected postcode/s.

I am trying to get the result with below query but its not getting correct results.

SELECT* FROM (

SELECT p.*, ROUND( 60 * 1.1515 * 1.609344 * DEGREES( ACOS( COS(RADIANS(pc.latitude)) * COS(RADIANS(p.lat)) * COS(RADIANS(pc.longitude - p.lng)) + SIN(RADIANS(pc.latitude)) * SIN(RADIANS(p.lng)) ) ), 2 ) AS distance

FROM properties AS p JOIN postcodes_geo AS pc ON (p.postcode_id = pc.id)

WHERE p.postcode_id IN (792,790,786,12613) ) AS ftbl WHERE distance <= 5

Possible solution is already discussed here: http://stackoverflow.com/a/8994850/4737924 — Aleksander Wons, Jan 08 '16 at 10:51
Thanks for the help. This work only for one location at a time. But i want to search the records if there are multiple postcodes. — Krishna, Jan 08 '16 at 11:22
what do you mean by "not getting correct results"? are you looking for properties at most 5km away? or at a minimum of 5km? — Olli, Jan 08 '16 at 12:53
@ awons: UNION is taking longer time. If there is another way please suggest. — Krishna, Jan 11 '16 at 03:04
@Olli: Every postcode must be compared with every properties to get the result with in 5Km (sorry i am just trying distance >=5 to check the result). — Krishna, Jan 11 '16 at 03:06
@Krishna: can you setup a sqlfiddle with some data to check some queries? — Olli, Jan 11 '16 at 12:31

score 0 · Answer 1 · answered Jan 08 '16 at 10:44

Following is the web service which provides same feature which you require for searching based on postcode.

http://www.geonames.org/export/web-services.html

Here you can pass postcode and radius as parameter and it will return list of zipcodes related to that in XML and JSON format.

for example: http://api.geonames.org/findNearbyPostalCodesJSON?postalcode=2032&country=AU&radius=10&username=demo

score -1 · Answer 2 · edited Feb 10 '16 at 16:29

@krishna how about query like

SELECT *
FROM (
    SELECT properties.*
        ,(111.045000 * DEGREES(ACOS(COS(RADIANS(- 33.867487)) * COS(RADIANS(lat)) * COS(RADIANS(151.206990 - lng)) + SIN(RADIANS(- 33.867487)) * SIN(RADIANS(lat))))) AS distance
    FROM `properties`
    WHERE `properties`.`deleted_at` IS NULL
        AND lat BETWEEN - 33.912514
            AND - 33.822460
        AND lng BETWEEN 151.152763
            AND 151.261218
    ) AS properties
WHERE `properties`.`deleted_at` IS NULL

UNION

SELECT *
FROM (
    SELECT properties.*
        ,(111.045000 * DEGREES(ACOS(COS(RADIANS(- 33.914437)) * COS(RADIANS(lat)) * COS(RADIANS(151.241642 - lng)) + SIN(RADIANS(- 33.914437)) * SIN(RADIANS(lat))))) AS distance
    FROM `properties`
    WHERE `properties`.`deleted_at` IS NULL
        AND lat BETWEEN - 33.959464
            AND - 33.869411
        AND lng BETWEEN 151.187384
            AND 151.295899
    ) AS properties
WHERE `properties`.`deleted_at` IS NULL

UNION

SELECT *
FROM (
    SELECT properties.*
        ,(111.045000 * DEGREES(ACOS(COS(RADIANS(- 33.914437)) * COS(RADIANS(lat)) * COS(RADIANS(151.241642 - lng)) + SIN(RADIANS(- 33.914437)) * SIN(RADIANS(lat))))) AS distance
    FROM `properties`
    WHERE `properties`.`deleted_at` IS NULL
        AND lat BETWEEN - 33.959464
            AND - 33.869411
        AND lng BETWEEN 151.187384
            AND 151.295899
    ) AS properties
WHERE `properties`.`deleted_at` IS NULL Limit 10
    ,10

This may help you as you need but you need to little bit work out for preparing query

SQL How to select properties from given multiple suburbs within 5KM range with the given suburbs?

2 Answers2