Custom Query with geocode and keywords

Wappler General
,
#1

I am struggling for a solution to this. I need to return location and keyword search from a table. The fields I am searching are varchar. The problem is a custom query does not allow for wild card contains or like. I Have searched the forum and have seen others with this issue but really no solutions to my issue. I have it working separately but when I join the two to one search I get no returns without a wild card on the keywords any Ideas or help would be appreciated. Also maybe there is a better solution to this?

Screenshot 2021-11-12 224832
Screenshot 2021-11-12 2248321247×890 28.2 KB

#2

I’m not quite sure what you mean about joining the two to one search, but this is how I do LIKE clauses for mysql/mariadb, in case that is what is broken.

LIKE concat('%', :P1, '%')

PS. if you have a very large dataset, there’s another way to do the distance search that will speed things up. Let me know if that is an issue for you.

1 Like
#3

That worked sorta, it returned values but not the right searched criteria. For example it returned the locations with in the radius but ignored the search value.

I changed it to the code below. But it seems to be ignoring the distance is <= :P3

SELECT
	*, 
	ROUND((
			6371 * acos(
				cos(
					radians( :P1 )) * cos(
					radians( lat )) * cos(
					radians( lng ) - radians(:P2 )) + sin(
					radians( :P1 )) * sin(
				radians( lat )))),
	( 2 )) AS distance
FROM
	view_JobSearch
WHERE
(distance <= :P3 and
	view_JobSearch.Job_Title LIKE concat('%', :P4, '%') or
	view_JobSearch.Job_Description  LIKE concat('%', :P4, '%') or
	view_JobSearch.skills  LIKE concat('%', :P4, '%'))
ORDER BY
	distance ASC
    LIMIT :P6,:P7;
#4

I think the issue is that you are attempting to use an alias (distance) in the WHERE clause which I don’t believe is allowed.

The way I handle this is to create a function that returns the distance and then compare that against the max distance. Mine is for miles, thus the 3959 instead of 6371. lat/lng are the values from the row being evaluated and pnt_lat/png_lng is the center point using radians, you are evaluating against.

You can just replace mine with your desired function.

Creating the function (done once in your db manager).

create
    function getDistance(lat float, lng float, pnt_lat float, pnt_lng float) returns float
BEGIN

    DECLARE dist FLOAT;
    SET dist =
                acos(sin(pnt_lat) * sin(radians(lat)) +
                     cos(pnt_lat) * cos(radians(lat)) * cos(radians(lng) - pnt_lng)) * 3959;

    RETURN dist;

END;

Then use it in your WHERE clause

WHERE getDistance(res1.lat, res1.lng, @radLat, @radLng) <= @max_distance

I should add that before I perform this function against all rows, I do a subquery that narrows the results down to a square region – basically a box instead of a circle. This can really increase performance on large datasets.

2 Likes
#5

That worked. Thank you so much for your help.

1 Like