I hope that you take this challenge :)
I need experienced programmer for solve this problem and which can move us forward.
The videoscreen from our page [url removed, login to view] and you will see the proces off loading the results... you can try by yourself if you want on live page... the new results must be much faster. now on site is turn off cache + other compress function which support better loading of content.... the changes will in mysql databses, the page only ilustrate speed which is critical slow (or searching POi one geo)
[url removed, login to view]
A. - list all queries with geographical data
B. - "replicate" (but dont move !!!) all geographical data to one table with geographical data and implement stored procedure to get them (return list of IDs by CENTER, DISTANCE)
- need build SPATIAL INDEX - in separate tables (stored procedure is used to access the data)
- stored procedure input parameters: city coordinates + range
output: list of IDs of the hotels (POIs) within the range
C. - modify existing queries to use stored procedure instead of SQL sub-clauses
Yes, wee are stuck. My programmer report is:
I tried the logic given in PDF from Page 25, and I got an error "ERROR 1367 (22007): Illegal non geometric 'aswkb(a@0)' value found during parsing error." then I tried another solution with different distance function, it also didn't worked. then I tried the method mentioned here [url removed, login to view] but it also didn't worked for me. I don't know what can be the issue, it was returning no data for me. The only sql statement that returned some result was with out use of Spatial index, the sql statement that worked was this:
3959 * acos (
cos ( radians(51.36364590032256) )
* cos( radians( latitude ) )
* cos( radians( longitude ) - radians(12.996535354492153) )
+ sin ( radians(51.36364590032256) )
* sin( radians( latitude ) )
) AS dist, title from `aj3_iproperty` WHERE `state` = 1 HAVING dist < 50 order by dist
where 51.36364590032256, 12.996535354492153 are latitude and longitude for Dahlen (Sachsen).
we are using:
Joomla 3.5.1 + about mysql: Server: Localhost via UNIX socket Server type: Percona Server Server version: 5.6.29-76.2-log - Percona Server (GPL), Release 76.2, Revision ddf26fe Protocol version: 10 User:
Hello I can help you with this, I'm very good with SQL and Databases. If you are interested please take a look at my feedbacks and let me know. Regards Jose Mendoza.