0

I have the following sql code that works within sql however i am wanting to be able to perform the query via cfml template passing to it the radius and the long / lat of current location which needs to be converted into a geography type first

DECLARE @point geography = geography::Point([-27.5819482,153.3136523], 4326);

SELECT *, @point.STDistance(a.placeOfInterest) as distance FROM placesOfInterest_GeoFences a WHERE @point.STDistance(a.placeOfInterest) <= [100500]

Can anyone point me in the right direction so that my cfml template process the necessary code to then output the records that meet the query conditions. the values in [] are needing to be passed to the template dynamically each time variables

Look forward to any advise / assistance

  • What happened when you tried passing the coordinate values into the cfquery? – Leigh Apr 23 '16 at 22:55
  • that where i get stuck how in cf do i create the equivalent to the above code as i need to convert th long / lat into the geography value there is my challnge –  Apr 24 '16 at 12:04
  • i have since found this and applied it to my database and the results are as i need however its not benefiting fro the geography column time i have in the table if i queries the geography the query would be considerably faster especially as records in the table move move to 100,000 '+ –  Apr 24 '16 at 12:10
  • Not sure I understand the current issue. Since it sounds like you solved the original problem (and there are no existing "answers" on this thread) it would be okay to update your question and title to describe the *new* problem you are having. Also, please update the tags to include your DBMS. – Leigh Apr 24 '16 at 17:45
  • yes i can pass my lng lat of current location into the query to compare against lng lat of points of interest within x distance in a poi table however its not effient. The records in the poi table have lng and lat columns but also a column of geography type that represents the lng / lat in geography structure. In order to optimise the query i need to be able to convert the lng and lat of current location being passed to the cfml template into geography type so the query will find the matching geography type value a lot faster than comparing ln / lat of poi table & current location –  Apr 25 '16 at 11:31
  • (Edit) I do not have an answer, but in the original sql it looks like lng / lat are just numbers. What is the issue with passing a numeric variable ie `geography::Point([, .... ]` instead of hard coded numbers? Also, not everyone reads comments, so please put these details [*in the question*](http://stackoverflow.com/posts/36810983/edit) and include your specific DBMS tag (sql server, mySQL, oracle, etcetera), not just generic "sql". – Leigh Apr 25 '16 at 15:30

0 Answers0