1

I am using the below query for calculation but I don't know how to pass the radius first time I need 2 km and in second query need 5 km and so on:

SELECT *
FROM pwd_roads AS r
WHERE ST_Distance_Sphere(r.geom, ST_SetSRID(
        ST_MakePoint(75.083333, 29.583333), 4326)) < 8
0

Your query is technically correct; ST_Distance_Sphere returns distance in meter, though (< 8 doesn't make sense in the context of your question).

Note that with PostGIS version > 2.2.0 this function is called ST_DistanceSphere, and AFAIK most legacy function signatures will be removed at some point in the 3.x release!


However:

For proximity searches, better use ST_DWithin to get index enhanced performance.

Depending on your actual goal it might make sense to query for spatial proximity only once and work with a derived attribute from there. Since this query would repeatedly create the reference point, I outsourced that expression into a CTE:

WITH
    pt AS (
        SELECT ST_SetSRID(ST_MakePoint(75.083333,29.583333), 4326)::GEOGRAPHY AS geog
    )

SELECT  *,
        CASE
            WHEN ST_Distance(a.geom::GEOGRAPHY, b.geog, FALSE) <= 2000 THEN
                2  --within 2km distance
            WHEN ST_Distance(a.geom::GEOGRAPHY, b.geog, FALSE) <= 5000 THEN
                5  --within 5km distance
            ELSE
                20 --within 20km distance
        END AS proximity
FROM   pwd_roads AS a
JOIN   pt AS b
  ON   ST_DWithin(a.geom::GEOGRAPHY, b.geog, 20000)
;

This retrieves all geometries in pwd_roads that are within 20km of the reference point and assigns an attribute (proximity) value (corresponding to the km range) to each geometry based on actual distance.

To work with meter as unit in ST_DWithin this uses the GEOGRAPHY type; while the type has other uses, ST_Distance(<GEOGRAPHY>, <GEOGRAPHY>, FALSE) is equivalent to ST_DistanceSphere.

Note that the reference point is cast to GEOGRAPHY within the CTE.

| improve this answer | |
  • Can i use like this select * from my_table where ST_Distance_Sphere(geom,ST_SetSRID(ST_MakePoint(76, 29),4326))<2000 for 2 km distance? – sumit kundan Nov 12 '19 at 11:41
  • @sumitkundan sorry for the late reply; yes, you could. note that the above is optimized for performance, while a simple distance filter is probably the worst performing option. – geozelot Nov 18 '19 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.