[GIS] Finding points in Kilometre radius using PostGIS

postgisquery

I am new to PostGIS and GeoData.

I need to find where the lightning has stroke by a period of time and kilometre radius based on a given location.

I have given lon/lat and my table is:

latitude numeric not null,
longitude numeric not null,
time timestamp(0),
geom(point,4326)

I do not know how to use within or radius query. I basically need to find how many points (lightning i have in a radius of a given location(lon/lat).

I am using PostgreSQL 9.2 and PostGIS 2.0.

Best Answer

Like so,

CREATE INDEX mytable_gix ON mytable USING GIST (Geography(ST_MakePoint(lon, lat)));

SELECT * FROM mytable 
  WHERE ST_DWithin(
    Geography(ST_MakePoint(lon, lat)),
    Geography(ST_MakePoint($qlon, $qlat)),
    $radius_meters
  );

If you have your data already in geometry points, but want to do a geography-style query:

CREATE INDEX mytable_gix ON mytable USING GIST (Geography(geom));

SELECT * FROM mytable 
  WHERE ST_DWithin(
    Geography(geom),
    Geography(ST_MakePoint($qlon, $qlat)),
    $radius_meters
  );
Related Question