0

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.

Improve this question
8
  • Try the ST_DWithin function, and here's a post about it's use: postgis.net/2013/08/26/tip_ST_DWithin
    – Inactivated Account
    Commented Mar 24, 2015 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
    Commented Mar 24, 2015 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...)
    – Inactivated Account
    Commented Mar 24, 2015 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
    Commented Mar 24, 2015 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)
    – Inactivated Account
    Commented Mar 24, 2015 at 17:33

1 Answer 1

Reset to default
1

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)
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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