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
Advertisement
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)