I have a large dataset (500,000 rows) in cartoddb which contains data like this.(abbreviated for easier typing)
cartodb_id | count | lat | long | name | the_geom1 |1 | 52.4118634358102 | -4.088212251663207 | aber | theGeom2 |1 | 52.48842511440679 | -3.4982872009277344 | card | theGeom3 |3 | 52.41192887963411 | -4.088115692138672 | aber | theGeomWhat I would like to do is run a query which goes through the data and for each row, finds all the points within 5 meters AND that contain the same name.
This is from a transcription project and a lot of people have clicked around the same place but not on the same place. The count highlights how many times a place has been clicked/inputted.
I would then like to create a new dataset which contains a single location and a more accurate count, as you can see from above, there are two 'aber', which are close together. For this I will just take the first lat/long and discard all the others. So I end up with a table that has a lat long as the first one but with a count of 4.
Never having played with PostGis before I seem to need to use the to find the closest points and also st_Distance with ::Geography. I seem to be able to find the distance from a single point using the below
SELECT *, ST_Distance( the_geom::geography, CDB_LatLng(53.19100611356759,-3.8425111770629883)::geography ) / 1000 AS dist FROM gb1900 Order by dist ASCIm just not sure how to turn this into what I need.
أكثر...
cartodb_id | count | lat | long | name | the_geom1 |1 | 52.4118634358102 | -4.088212251663207 | aber | theGeom2 |1 | 52.48842511440679 | -3.4982872009277344 | card | theGeom3 |3 | 52.41192887963411 | -4.088115692138672 | aber | theGeomWhat I would like to do is run a query which goes through the data and for each row, finds all the points within 5 meters AND that contain the same name.
This is from a transcription project and a lot of people have clicked around the same place but not on the same place. The count highlights how many times a place has been clicked/inputted.
I would then like to create a new dataset which contains a single location and a more accurate count, as you can see from above, there are two 'aber', which are close together. For this I will just take the first lat/long and discard all the others. So I end up with a table that has a lat long as the first one but with a count of 4.
Never having played with PostGis before I seem to need to use the to find the closest points and also st_Distance with ::Geography. I seem to be able to find the distance from a single point using the below
SELECT *, ST_Distance( the_geom::geography, CDB_LatLng(53.19100611356759,-3.8425111770629883)::geography ) / 1000 AS dist FROM gb1900 Order by dist ASCIm just not sure how to turn this into what I need.
أكثر...