how to show results of postcodes within a radius of a point

hi back with another problem lol, i have a table with several columns; 2 of which latitude and longitude and other is crime types, what i need to do is work out how many crimes were committed within an x amount of meters from a certain point

what i need is to find the amount of crimes that took place 250m, 500m and 1km from E:307998m, N:188746m this point

help would be appreciated or even just a push in the right direction thanks

Answer

What an interesting question. The following may help.

You can use Pythagoras’s theorem to calculate the distance from a point ([100,100] in this case) and any incident, then count the total where this is less than a threshold and of the right type.

 # select * from test;
┌─────┬─────┬──────┐
│  x  │  y  │ type │
├─────┼─────┼──────┤
│ 100 │ 100 │    1 │
│ 104 │ 100 │    1 │
│ 110 │ 100 │    1 │
│ 110 │ 102 │    1 │
│  50 │ 102 │    2 │
│  50 │ 150 │    2 │
│  50 │ 152 │    3 │
│ 150 │ 152 │    1 │
│  40 │ 152 │    1 │
│ 150 │ 150 │    2 │
└─────┴─────┴──────┘
(10 rows)
 select count(*) from test where sqrt((x-100)*(x-100)+(y-100)*(y-100))<30 and type = 1;
┌───────┐
│ count │
├───────┤
│     4 │
└───────┘
(1 row)