Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

Thank you.

share|improve this question
add comment

1 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
  );

Edit: 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
  );
share|improve this answer
    
Hi Paul, My field is Geometry does it work if i replace Geography to Geomtry? –  Marco Amaral Apr 3 '13 at 16:34
1  
You description seemed to indicate you had no geometry/geographic field at all, just a lon column and a lat column, and my example is based on that assumption. If you do in fact have a geometry column, then no, it won't work so well, because the geometry type does all calculations on the plane, using the units of the projection the data is stored in. Lon/lat coordinates are not planar, they are spherical in degrees, so planar calculations tend to spit out useless answers. Just convert your column to geography, everything will get much simpler very quickly. –  Paul Ramsey Apr 3 '13 at 16:39
1  
Or, if you absolutely want to keep your lon/lat in geometry, but search as if it was geography, you can do basically the same thing as the example, a function index on the cast of the data to geography, and a query that uses the same syntax to invoke the index. –  Paul Ramsey Apr 3 '13 at 16:41
    
I think ST_GeographyFromText('P‌​oint(X Y)')) is better since its only one function? –  nickves Apr 3 '13 at 16:45
1  
One function that has to parse text. Potato, potato. –  Paul Ramsey Apr 3 '13 at 17:58
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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