GeoNames Forum

Can someone suggest a query for MySQL that would take RSS title and description and return lat and long. I think I'll end up using full-text, which is fine with me. I'm having some trouble reverse engineering the service without help.

Something like:

DECLARE _title Varchar(100);
DECLARE _descr varchar(500);
SELECT latitude, longitude FROM geonames WHERE
MATCH(name, alternatenames) AGAINST (_title & ' ' & _descr);

I would use the web service, but I'm making too many calls per day since we're attempting to convert thousands of newspaper rss feeds to georss and store the feed items in our DB.

I don't think it is possible to achieve useful results with a single sql select. The GeoNames RSStoGeoRSS is an application with thousands of line of code.

Marc

I am not trying to accomplish -everything- the RSStoGeoRSS application accomplishes. Let's say for instance I only want to get lat and long for a single string made up of words like that which would appear in a news article.

STRING --> lat & long (if found)

I thought it might not be as simple as one select statement. How many would you say? Can you give me an idea of what the process is like? Any chance I can have some code/sql examples? I am not even daunted by thousands of lines of code... I'm adventurous and properly motivated.

The impetus for my request is a problem that we've been having with the RSS2GeoRSS web service lately. I thought it might be because we were hitting geonames too much at one time. Every couple of hours, we scour the US's news sources (thousands) and, in turn, run them all through geonames' RSS2GeoRSS converter before we store them in our DB. About 3 months ago, we started seeing strange characters showing up in the titles and descriptions. Most browsers were translating them as a simple question mark (?). I traced the process that saves a feed to our DB and I found that the feed was clean before geonames. When we got the feed back from geonames, it contained strange characters. It didn't happen all the time. In fact, it only seems to happen when we process a large batch of feeds at once.

So, I set out to recreate the RSS2GeoRSS process on my end to fix the problem with strange characters. If you have a way to fix the problem with the web server, I don't need to re-create the process. If not, I need to be able to geocode rss feeds on my own servers.

Any help will be greatly appreciated.

Marc,

Can you give me an update on the answer to my last question?

Byron

Under heavy load I would expect poor performance or no answer at all, I don't think that heavy load will cause encoding problems. More likely it seems is that when you process a large number of feeds you are also more likely to have some feeds with encoding problems. So I assume the problem is somewhere else and not related to server load.

Marc