I'm trying to use CartoDB to Count all of the records a given distance from a selected location.

I have the lat/long for the selected location (call this a store). Now I need to find all of the records (representing customers) that are a given distance (say, 8046 meters/5 miles) from the selected location.

What SQL query should I be using? I've tried some basic things like this, but they didn't work:

'SELECT COUNT() from my_table_name WHERE the_geom && ST_SetSRID(ST_Point(39.8282, -98.5795), 8046)'

or this:

SELECT COUNT() from my_table_name WHERE ST_INTERSECTS(the_geom,CIRCLE(LATLNG(39.8282, -98.5795),8046))

What query do I actually need? All I want to do is count the records that meet this criteria. Step 2 would be to filter the points on the map to show only those that meet the criteria, but one thing at a time.

  • Try the ST_DWithin function, and here's a post about it's use: postgis.net/2013/08/26/tip_ST_DWithin – DPSSpatial_BoycottingGISSE Mar 24 '15 at 15:58
  • @mapBaker I found this code, but not sure what the INNER JOIN would be for me and how this would fit: SELECT roads.roadname, pois.poiname FROM roads INNER JOIN pois ON ST_DWithin(roads.geog, pois.geog, 1609); – jonmrich Mar 24 '15 at 16:09
  • inner join means the query will only return the records that match the query (rather than returning ALL records with NULL data for the ones that are outside the distance). The query you've got there returns the POI name, the road name it is closest to, and within the ST_DWithin function specifies the distance to look for features from the road - 1609 (metres or feet I don't know...) – DPSSpatial_BoycottingGISSE Mar 24 '15 at 16:35
  • @mapBaker That makes sense, but I'm having a hard time applying it to my case. I want to specify a location to check a distance from like this: SELECT * FROM my_table INNER JOIN my_table.id ON ST_DWithin(40.4000, -90.6833, 16090) but there's a lot wrong with what I've got. Can't figure out what my equivalent is for "pois" and how the join should work given that I'm only querying one table, not two like this example. – jonmrich Mar 24 '15 at 16:42
  • I'm trying to get this to work on my table, but can't... maybe someone will chime in! SELECT * FROM tableName where ST_DWithin(tableName.the_geom_webmercator, ST_GeomFromText('POINT(39.704017 -104.959488)', 4326), 5000) – DPSSpatial_BoycottingGISSE Mar 24 '15 at 17:33

Duplicated: Count of points within range of another point is all or nothing

And as answered there, but applying here a count of all the values that match the where condition, you can use the following SQL query:

SELECT count(*)
FROM location_fusion_table 
WHERE ST_DWithin(location_fusion_table.the_geom::geography, ST_GeomFromText('POINT(39.704017 -104.959488)', 4326)::geography, 50000)

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.