As @John Barça says in the comments, you'll need CartoDB.js.
CartoDB has available a tutorial named "Toggle map view" here which basically has the basics of what you are trying.
First, it's creating the CartoDB layer:
cartodb.createLayer(map, layerUrl)
.addTo(map)
.on('done', function(layer) {
// change the query for the first layer
var subLayerOptions = {
sql: "SELECT * FROM ne_10m_populated_places_simple",
cartocss: "#ne_10m_populated_places_simple{marker-fill: #F84F40; marker-width: 8; marker-line-color: white; marker-line-width: 2; marker-clip: false; marker-allow-overlap: true;}"
}
in which a SQL is added by default, which you can use to determine which points you want to show at the beginning.
And then it adds several buttons in the interface that lets you apply different filters to your map. This will internally change the SQL applied to the layers so that the map is updated. In CartoDB.js, this is performed with the setSQL function of a layer, in which you can change the data that is being rendered.
I also have here another example for filtering with SQL in CartoDB.js. It follows the same idea: create some buttons and when the click event is triggered in them, it calls to layer.setSQL(sql_here). For the record, the whole source code is:
<!DOCTYPE html>
<html>
<head>
<style>
html, body, #map {
height: 90%;
padding: 0;
margin: 0;
}
</style>
<link rel="stylesheet" href="http://libs.cartocdn.com/cartodb.js/v3/3.15/themes/css/cartodb.css" />
</head>
<body>
<div id="map"></div>
<button type="button" id="button-basemap1" onclick="setButton1();"> Button 1 (Spain)</button>
<button type="button" id="button-basemap2" onclick="setButton2();"> Button 2 (Area > 5000)</button>
<!-- include cartodb.js library -->
<script src="http://libs.cartocdn.com/cartodb.js/v3/3.15/cartodb.js"></script>
<script>
var dataLayer;
function setButton1(){
//filtering by country name
dataLayer.setSQL("select * from european_countries_e where name = 'Spain'");
//changing the css too
dataLayer.setCartoCSS("#european_countries_e{ polygon-fill: #CCC; polygon-opacity: 0.8; line-color: #FFF; line-width: 1; line-opacity: 0.5; } ");
}
function setButton2(){
//filtering by area
dataLayer.setSQL("select * from european_countries_e where area > 5000");
//changing the css, not mandatory!
dataLayer.setCartoCSS("#european_countries_e{ polygon-fill: #FFFFB2; polygon-opacity: 0.8; line-color: #FFF; line-width: 1; line-opacity: 0.5; } #european_countries_e [ area <= 1638094] { polygon-fill: #B10026; } #european_countries_e [ area <= 55010] { polygon-fill: #E31A1C; } #european_countries_e [ area <= 34895] { polygon-fill: #FC4E2A; } #european_countries_e [ area <= 12890] { polygon-fill: #FD8D3C; } #european_countries_e [ area <= 10025] { polygon-fill: #FEB24C; } #european_countries_e [ area <= 9150] { polygon-fill: #FED976; } #european_countries_e [ area <= 5592] { polygon-fill: #FFFFB2; } ");
}
function main() {
var map = new L.Map('map', {
zoomControl: false,
center: [43, 0],
zoom: 3
});
//Adding the basemap here
var basemap = L.tileLayer('http://{s}.basemaps.cartocdn.com/light_all/{z}/{x}/{y}.png', {
attribution: '© <a href="http://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors, © <a href="http://cartodb.com/attributions">CartoDB</a>'
}).addTo(map);
cartodb.createLayer(map, {
//change your username below
user_name: 'documentation',
type: 'cartodb',
sublayers: [{
//change your sql below
sql: "SELECT e.cartodb_id, e.area, w.subregion, w.un, e.the_geom, e.the_geom_webmercator FROM european_countries_e e LEFT JOIN world_borders w ON e.iso_2_code = w.iso2",
//change your cartocss below. you can copy it from the CartoDB Editor and remove the line breaks.
cartocss: "#european_countries_e{ polygon-fill: #FFFFB2; polygon-opacity: 0.8; line-color: #FFF; line-width: 1; line-opacity: 0.5; } #european_countries_e [ area <= 1638094] { polygon-fill: #B10026; } #european_countries_e [ area <= 55010] { polygon-fill: #E31A1C; } #european_countries_e [ area <= 34895] { polygon-fill: #FC4E2A; } #european_countries_e [ area <= 12890] { polygon-fill: #FD8D3C; } #european_countries_e [ area <= 10025] { polygon-fill: #FEB24C; } #european_countries_e [ area <= 9150] { polygon-fill: #FED976; } #european_countries_e [ area <= 5592] { polygon-fill: #FFFFB2; }"
}]
})
.addTo(map)
.done(function(layer) {
//here I store the layer in the variable, so that we can query it outside this function
dataLayer = layer.getSubLayer(0);
})
.error(function(err) {
console.log(err);
});
}
window.onload = main;
</script>
</body>
</html>
