1

I am trying to find all locations within a one mile radius of a specified point. I have longitude and latitude data for each point,

multi polygon geometry (Example :

MULTIPOLYGON(((2147312.63139525 161631.130590368,2147298.94808962 164286.665686698,2147623.79332922 164287.328517173,2149920.27996486 164292.162599235,2149944.29540875 161654.921437815,2147312.63139525 161631.130590368)))

and the_geom data

Example:

0106000020DA08000001000000010300000001000000060000003F8FD150F86140417EF6720BF9BA03412C005B79F16140412A8C5353F50D0441D7CF8BE593624041929CCDA0FA0D044177E3D52310674041E5D3004D210E044134F4CF251C67404106CA1A5FB7BB03413F8FD150F86140417EF6720BF9BA0341

I'm just not sure of the best way to approach finding all distances within one mile.

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
Nicole Evans
  • 51
  • 6

2 Answers2

1

To query geometries within a given radius you have to use ST_DWithin:

SELECT * FROM t
WHERE
  ST_DWithin(
    the_geom::geography,
    ST_MakePoint(longitude,latitude),1609.344); -- 1609.34 metres = 1 mile;

The geography cast enables you to search using metres as unit, which can be easily converted to miles.

Related post: Getting all Buildings in range of 5 miles from specified coordinates

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
1

I wanted to stop by and thank Jim Jones for your help! I wasn't able to end up to get the_geom to work, although this was probably user error. The link you shared did help me to come up with the fix below.

SELECT * FROM t where ST_DWithin('POINT($long $lat)'::geography, ST_MakePoint(longitude,latitude)::GEOGRAPHY, 1609.344);
Nicole Evans
  • 51
  • 6