Reverse Geocoding with it loaded into MySQL database?

[David Roth]

How is reverse geocoding done with allCountries.txt loaded into a MySQL database?

When you search for a latitude and longitude in order to get back a country code, 

where is the data or formula(?) to specify the range of the lat/lon data points so it 

returns back the correct country code for any lat/lon searched on? Or am I missing another piece of data that is available

from Geonames to do this? Thanks in advance!

David Roth

[Daniel Lo Nigro]

You need to use MySQL's spatial extensions. There's a question on StackOverflow (http://stackoverflow.com/questions/1006654/fastest-distance-lookup-given-latitude-longitude) that may help you. I've never done it with MySQL but essentially you need to sort by distance from a provided lat/lon point.

[David Roth]

Hi Daniel.

Thanks so much for your helpful reply.

If I understand this correctly, the method would be to find the nearest point, such as a point of interest, and from that determine which country the inputted lat/lon is in? This sounds interesting, but I'm wondering if there are enough data points for this to work. For example, if a given lat/lon were on the border, the nearest Point of Interest or Place might be across the border in another country.

I can't help from wondering how Geonames does this through the API. Does it have the lat/lon borders of each country, and then a lat/lon is inputted it determines by range which country it is in? If so, that data appears to not be included for download, or perhaps I missed it.

David Roth

--
You received this message because you are subscribed to the Google Groups "GeoNames" group.
To view this discussion on the web visit https://groups.google.com/d/msg/geonames/-/LZx9IO06E3gJ.
To post to this group, send email to geon...@googlegroups.com.
To unsubscribe from this group, send email to geonames+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/geonames?hl=en.

[Daniel Lo Nigro]

Yeah, I didn't think about lat/lon on the border. I couldn't see any data for bounding areas in the main "geoname" table. 

I can't help from wondering how Geonames does this through the API. Does it have the lat/lon borders of each country, and then a lat/lon is inputted it determines by range which country it is in?

Are you talking about the findNearbyPlaceName API call? If so, it says:

Result : returns the closest populated place for the lat/lng query as xml document.

Which to me implies that it just does finds the nearest point, not taking place/country borders into account.

[David Roth]

I wasn't specifically thinking about the API call to findNearbyPlaceName, since I thought that would be available in the database to download. But, yes, it likely doesn't know if the nearest place is within the same country or not if running your own database to do Reverse Geocoding.

Thanks for looking into the existence of border data. I suspect releasing the border data (for lack of a better term) would be giving too much away. Since there are so few even APIs available to do this, it might be a huge effort to collect the border data. I was thinking about this, it would be difficult to determine because borders don't follow any sort of pattern. The lat/lon would have to be traced along the border for each country I'd think. Now each country might have that data available, but it would have to be collected and then software written to take advantage of it.

[Denis Arnaud]

Hi David and Daniel,

AFAIK, Geonames uses a polygon-based model internally, as well as PostGIS (spatial extensions for PostgreSQL). The polygon specifications are not available publicly. For such definitions, you may consider having a look at Expedia Affiliate Network (EAN) database; see my post of May 2012 for more details: https://groups.google.com/forum/#!topic/geonames/yBIc5zM2wVA.

Kind regards

Denis

[Norman E. Avila G.]

With a spatial query over country polygons?