[irr1449]

I’ve added a “geo” field to the topics table that contains latitude and longitude data for each topic. The topics table now contains lat/lng from different cities all over the US.

I’ve modified forum.php to take latitude and longitude GET parameters to use as a “center.” Then I select topics within range of X miles and order by distance from “center.”

To hack this in I wrote a new function in classes.php called generate_geo_topic_sql. This worked enough so that I can test my idea, but its not a long term solution.

So now I need to somehow integrate this query into generate_topic_sql. I still want to retain the original functionality, but just create a different view if I’m using a “center” point.

This is the query. You can not run multiple statements at once in PHP so each of these queries need to be executed separately.

SET @center = GeomFromText(‘POINT(” . $lng . ” ” . $lat . “)’);

SET @radius = 1;

SET @bbox = CONCAT(‘POLYGON((‘,X(@center) – @radius, ‘ ‘, Y(@center) – @radius, ‘,’,X(@center) + @radius, ‘ ‘, Y(@center) – @radius, ‘,’,X(@center) + @radius, ‘ ‘, Y(@center) + @radius, ‘,’,X(@center) – @radius, ‘ ‘, Y(@center) + @radius, ‘,’,X(@center) – @radius, ‘ ‘, Y(@center) – @radius, ‘))’);

Then this was the “hack” query that I wrote to get it to work. I dumped the SQL from the original generate_topic_sql and grafted on what I needed.

SELECT t.*,SQRT(POW( ABS( X(t.geo) – X(@center)), 2) + POW( ABS(Y(t.geo) – Y(@center)), 2 )) AS distance FROM bb_topics AS t WHERE t.topic_status = ‘0’ AND t.topic_sticky != ‘2’ AND t.forum_id = ‘” . $this->query . “‘ AND Intersects( t.geo, GeomFromText(@bbox) ) AND SQRT(POW( ABS( X(t.geo) – X(@center)), 2) + POW( ABS(Y(t.geo) – Y(@center)), 2 )) < @radius ORDER BY distance ASC LIMIT 3

The problem is that paging does not work (and a few other things) in my hackjob function. I need to find a way to integrate this into the regular function so that I can maintain all the functionality.

Any suggestions?