function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Mohit Bansal6Mohit Bansal6 

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?
Best Answer chosen by Mohit Bansal6
Mohit Bansal6Mohit Bansal6
Praveen

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

RishavRishav
Hiii Mohit,
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
Mohit Bansal6Mohit Bansal6
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];
RishavRishav

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

praveen murugesanpraveen murugesan
Hi Mohit,

list<Account> accList = [SELECT Id  FROM Account WHERE DISTANCE(GeoLocation__c, GEOLOCATION(lat1,lon1), mi) < Integer.valueOf(SelectedWithin)];

try this.

-- Praveen Murugesan
Mohit Bansal6Mohit Bansal6
Praveen

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.
This was selected as the best answer