Apex Code Development You need to sign in to do that
Don't have an account?
Need help in converting dynamic query into direct query?
String queryString =
'SELECT Id 'FROM Account ' +
'WHERE DISTANCE(GeoLocation__c, GEOLOCATION('+lat1+','+lon1+'), \'mi\') < ' + Integer.valueOf(SelectedWithin) ;
Here lat1, lon1, selectedWithin are the variables.
Can you help me in converting this dynamic query into direct query?
'SELECT Id 'FROM Account ' +
'WHERE DISTANCE(GeoLocation__c, GEOLOCATION('+lat1+','+lon1+'), \'mi\') < ' + Integer.valueOf(SelectedWithin) ;
Here lat1, lon1, selectedWithin are the variables.
Can you help me in converting this dynamic query into direct query?
I just found "Limitations on Location-Based SOQL Queries":
Location-based queries are supported in SOQL in Apex and in the SOAP and REST APIs with the following limitations:
DISTANCE and GEOLOCATION are supported in WHERE and ORDER BY clauses in SOQL, but not in GROUP BY or SELECT.
DISTANCE supports only the logical operators > and <, returning values within (<) or beyond (>) a specified radius.
When using the GEOLOCATION function in SOQL queries, the geolocation field must precede the latitude and longitude coordinates. For example, DISTANCE(warehouse_location__c, GEOLOCATION(37.775,-122.418), 'km') works but DISTANCE(GEOLOCATION(37.775,-122.418), warehouse_location__c, 'km') doesn’t work.
Apex bind variables aren’t supported for the DISTANCE or GEOLOCATION functions. The following queries won’t work.
1 double latitude = 10;
2 double longitude = 20;
3 double distance = 30;
4 List<List<Account>> accountLocations = [SELECT ID, Name, BillingLatitude, BillingLongitudeFROM Account where DISTANCE(BillingAddress,GEOLOCATION(:latitude,:longitude),'mi') < :distance];
1 Location loc = Location.newInstance(90.0, 90.0);
2 List<Account> accountIds = [SELECT Id FROM Account WHERE DISTANCE(BillingAddress, :loc) <100];
Use a database query like this one instead.
1 double latitude = 10;
2 double longitude = 20;
3 double distance = 30;
4 Database.query('SELECT ID, Name, Location__latitude__s,Location __longitude__s FROM Party__c where '+ 'DISTANCE(Location__c,GEOLOCATION('+latitude+','+longitude+' ),\'mi\') < '+distance);
See “Compound Field Considerations and Limitations” in the SOAP API Developer’s Guide for additional details.
All Answers
You can do like this.
String lattitude = {
get { return lattitude;}
set { lattitude = value;}
}
string longitude = {
get{return longitude;}
set (longitude = value;}
}
lattitude = lat1;
longitude = lot1;
String queryString =
'SELECT Id 'FROM Account ' +
'WHERE DISTANCE(GeoLocation__c, GEOLOCATION('lattitude,longitude'), \'mi\') < ' + Integer.valueOf(SelectedWithin) ;
This is a static query.
Thanks
Rishav
I need to query directly on Account object without using "database.Query(queryString)"
eg:
lAccount = [Select id from Account where.....need to add condition as per my post];
oh i didn't get your point !
To make direct query with variable see this link
https://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_variables.htm
Thanks
list<Account> accList = [SELECT Id FROM Account WHERE DISTANCE(GeoLocation__c, GEOLOCATION(lat1,lon1), mi) < Integer.valueOf(SelectedWithin)];
try this.
-- Praveen Murugesan
I just found "Limitations on Location-Based SOQL Queries":
Location-based queries are supported in SOQL in Apex and in the SOAP and REST APIs with the following limitations:
DISTANCE and GEOLOCATION are supported in WHERE and ORDER BY clauses in SOQL, but not in GROUP BY or SELECT.
DISTANCE supports only the logical operators > and <, returning values within (<) or beyond (>) a specified radius.
When using the GEOLOCATION function in SOQL queries, the geolocation field must precede the latitude and longitude coordinates. For example, DISTANCE(warehouse_location__c, GEOLOCATION(37.775,-122.418), 'km') works but DISTANCE(GEOLOCATION(37.775,-122.418), warehouse_location__c, 'km') doesn’t work.
Apex bind variables aren’t supported for the DISTANCE or GEOLOCATION functions. The following queries won’t work.
1 double latitude = 10;
2 double longitude = 20;
3 double distance = 30;
4 List<List<Account>> accountLocations = [SELECT ID, Name, BillingLatitude, BillingLongitudeFROM Account where DISTANCE(BillingAddress,GEOLOCATION(:latitude,:longitude),'mi') < :distance];
1 Location loc = Location.newInstance(90.0, 90.0);
2 List<Account> accountIds = [SELECT Id FROM Account WHERE DISTANCE(BillingAddress, :loc) <100];
Use a database query like this one instead.
1 double latitude = 10;
2 double longitude = 20;
3 double distance = 30;
4 Database.query('SELECT ID, Name, Location__latitude__s,Location __longitude__s FROM Party__c where '+ 'DISTANCE(Location__c,GEOLOCATION('+latitude+','+longitude+' ),\'mi\') < '+distance);
See “Compound Field Considerations and Limitations” in the SOAP API Developer’s Guide for additional details.