1

I have query by distance working in a framework that generates a list of features within a fixed distance of a clicked feature. I would like to make the queried distance variable based on either:

Pixel Distance from clicked feature (something like):

select cartodb_id, label, title from full_list where st_distance( the_geom, st_GeomFromText('POINT("+lon+" "+lat+")', 4326), true ) < ("+pixels+")

Or current zoom level-based resolution (something like):

select cartodb_id, label, title from full_list where st_distance( the_geom, st_GeomFromText('POINT("+lon+" "+lat+")', 4326), true ) < (SELECT CDB_XYZ_Resolution("+zoom+"))

I wonder if this is possible. Here is the jsbin wherein CDB_XYZ_Resolution uses a fixed zoom variable: http://jsbin.com/vifaleri/1/edit

To update the distance variable when a feature is clicked, I'm not quite sure where to include something like this:

map.on('zoomend', function(){
    var zoom = map.getZoom();
    console.log(zoom);
  });

to be reachable by the CDB_XYZ_Resolution("+zoom+") part of the feature click query.

Thanks and apologies for my ignorance. If grids are the solution, I have been unable to implement grid queries of points in the same cell as the selected point. I've tried this, among other similar grid queries that return no records:

WITH hgrid AS (SELECT CDB_HexagonGrid(ST_Expand(!bbox!, greatest(!pixel_width!,!pixel_height!) * 15), greatest(!pixel_width!,!pixel_height!) * 15) as cell) SELECT hgrid.cell as the_geom_webmercator, 1 as cartodb_id, count(i.cartodb_id) as point_count FROM hgrid, full_list i WHERE i.cartodb = "+data.cartodb_id+" AND ST_Intersects(hgrid.cell, i.the_geom_webmercator)

Thanks.

  • I think I got it: After map.setView I put var zoom = map.getZoom(zoom); console.log(zoom); and used (SELECT CDB_XYZ_Resolution("+zoom+")*(("+zoom+")+1)) for the distance. – tbushman May 31 '14 at 21:20
  • Great. What are you trying to achieve exactly? – jatorre Jun 7 '14 at 11:57
  • Query by distance where the distance is in pixels, dynamically adjusted at zoom: helps list coincident points. Got it working here: pu.bli.sh/maps/web_map/bennioncenter/index.html. – tbushman Jun 7 '14 at 17:23
1

First, one logs the zoom after the click query:

var zoom = map.getZoom(zoom); console.log(zoom);

Then one changes the sql to: SELECT cartodb_id, label FROM table_name where ST_Distance(the_geom, st_GeomFromText('POINT("+lon+" "+lat+")', 4326), true ) < (SELECT CDB_XYZ_Resolution("+zoom+")*(("+zoom+")*1.75)) ORDER BY label

The number 1.75 is the resolution multiplier. See https://github.com/CartoDB/cartodb-postgresql/wiki/CDB_XYZ_Resolution

And a working example on github: https://github.com/tbushman/emergencyservices

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.