3

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_geom
1          |1      | 52.4118634358102  | -4.088212251663207  | aber |  theGeom
2          |1      | 52.48842511440679 | -3.4982872009277344 | card |  theGeom
3          |3      | 52.41192887963411 | -4.088115692138672  | aber |  theGeom

What 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 ASC

Im just not sure how to turn this into what I need.

7

You can do it via the geography type, using a geography index, or via the geometry type with some math to adjust for distortions in mercator.

With geography:

CREATE INDEX gb1900_geog_idx ON gb1900 USING GIST (geography(the_geom));

CREATE TABLE newtable AS 
WITH c AS (
  SELECT 
    a.cartodb_id, 
    count(*)
  FROM gb1900 a, gb1900 b 
  WHERE ST_DWithin(geography(a.the_geom), geography(b.the_geom), 5)
  AND a.name = b.name
  AND a.cartodb_id != b.cartodb_id
  GROUP BY a.cartodb_id
)
SELECT 
  c.cartodb_id, c.count,
  st_x(g.the_geom) AS long,
  st_y(g.the_geom) as lat,
  g.name, g.the_geom, g.the_geom_webmercator
FROM c JOIN gb1900 g USING (cartodb_id);

With geometry:

CREATE TABLE newtable AS 
WITH c AS (
  SELECT 
    a.cartodb_id, 
    count(*)
  FROM gb1900 a, gb1900 b 
  WHERE ST_DWithin(a.the_geom_webmercator, b.the_geom_webmercator,  5 / cos(radians(st_y(a.the_geom))))
  AND a.name = b.name
  AND a.cartodb_id != b.cartodb_id
  GROUP BY a.cartodb_id
)
SELECT 
  c.cartodb_id, c.count,
  st_x(g.the_geom) AS long,
  st_y(g.the_geom) as lat,
  g.name, g.the_geom, g.the_geom_webmercator
FROM c JOIN gb1900 g USING (cartodb_id);

Since your search radius is very small, there's no accuracy penalty to using the mercator solution, so I'd probably recommend that as it's minimally more efficient and avoids having to create a spare index (CartoDB automatically adds an index for the_geom_webmercator so that's taken care or for you already).

  • Brilliant :-) Just a few tweaks I needed to run. In the AND query a.cartodb should be a.cartodb_id. At the end you also need to run select cdb_cartodbfytable('newtable'); to make sure it shows up in the dashboard! – Paul M Aug 26 '15 at 11:29
  • Actually I am not sure if it has worked there appears to be a lot of duplicates, I will try and mine the data and see as there are a lot less rows than I thought. Thanks though im a lot further on than I was :-D – Paul M Aug 26 '15 at 11:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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