GeoNames Forum

Hi,

First of all great site and loads of really useful info. Just wanted to know if the following would be possible using the webservice/text file dump and if so how I should go about it.

I have a database of properties for sale (UK) each property obviously has the postcode in the ms access database.

On the website, I would like users to type in a placename (UK town) and then have the option of selecting a radius i.e within 10 miles. From here the results will display only the properties that match this criteria.

Is this possible? if so could you point me in the right direction to get started.

I would really appreciate any information possible, thanks in advance

James

Ok I have got this far:

Have managed to convert all my properties to include X, Y coordinates, Longitude and Latitude

and a list of town names along with their respective X, Y coordinates, Lonitude and Latitude.

Now just need to find a way of typing a town name and quering the respective long/lat with that of each property within a certain x miles.

Feels like I have got well underway for someone who is pretty clueless with this sort of stuff, any guidance on the next step would be greatly appreciated.

James

Hi James

Anonymous wrote:

Now just need to find a way of typing a town name and quering the respective long/lat with that of each property within a certain x miles.

1. getting lat/lng for user input :
You can use our webservice to get the lat/lng for your users input :
http://www.geonames.org/export/geonames-search.html
This is probably easier then implementing your own search on your own database.

2. getting each property within a certain x miles
Check your database documentation for spatial queries. Most spatial query operators will have index operators for 'rectangular' boxes and you will have to use a box for your query and an additional clause to limit the radius.
The lat/lng are usually in WGS84 and your input in miles. There are countless ways to calculate the distance, which one you want to take depends on how much time you want to spend and how accurate the result needs to be. The easiest way is a rule of thumb (a minute of latitude is approximately 1.15 miles)
http://nationalatlas.gov/articles/mapping/a_latlong.html

and the most accurate are trigonometric formulas :
http://en.wikipedia.org/wiki/Haversine_formula
http://www.movable-type.co.uk/scripts/LatLong.html

Marc

Hi Marc,

Thanks for your reply, much appreciated, i have been looking into this further and in reference to your point 2:

I am using a MySQL database and have referred to their documentation and found the bit in the manual about spacial extensions - all way over my head if I am honest!

They list data values as GEOMETRY, POINT, LINESTRING, POLYGON - I think I need to somehow convert long and lats for both my properties and the placenames into POINTS is this correct? to then be able to calculate my initial query of showing properties within [x] distance of [placename].

If so I have to understand how to install these spatial extensions and then I guess create a new field in both tables called 'Point' which these new extensions will automatically populate.

Could you give me feedback on this or if there are any simple tutorials / code examples etc as I am a bit stumped to say the least. If this query is however outside the scope of this forum, just let me know and I will plod slowly onwards.

Thanks again. James.

Hi James

I am normally using postgres. Mysql has fewer gis functionality.

As mysql does not seem to have a distance operator, you don't need the spatial extension for the distance part. You only need if you have a large table and want to use a spatial index.

For the index you need a field of type Point in your table and update it with your existing data :

UPDATE myTable SET Coord = PointFromText(CONCAT('POINT(',myTable.DLong,' ',myTable.DLat,')'));

Then you create your spatial index :
CREATE SPATIAL INDEX sp_index ON mytable(coord);

In the query you use the functions MBRContains(g1,g2) or MBRWithin(g1,g2) with a rectangular bounding box around the search point. You will use a bounding box larger than the distance to reduce the number of matching rows.

Now you have used the index, but you still have too many rows and need a further restriction for the 'fine tuning'.

Links :

Documentation :
http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html

Distance :
http://forums.mysql.com/read.php?23,40909,43079

Hope this helps.

Marc

Marc, thankyou very much for your help!

Mi Marc, an issue has resulted in me moving databased to Microsoft SQL server express editon. can the same be done for that i.e in your last post?

Hi James

I fear SQL Server does not have inbuilt spatial support. Maybe this paper can help you :

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/TblValFuncSQL.asp

If your database is not too big, you may not need spatial suport and just use two number columns for lat/lng.

Marc

marc wrote:

...
As mysql does not seem to have a distance operator, you don't need the spatial extension for the distance part. You only need if you have a large table and want to use a spatial index.

For the index you need a field of type Point in your table and update it with your existing data :
...
Now you have used the index, but you still have too many rows and need a further restriction for the 'fine tuning'.

What a coincidence, I just published a new version of vivirama that includes a "nearby" feature that uses this technique. See for example
http://www.vivirama.com/spain/cataluna/barcelona

xavi

xavi wrote:

... See for example
http://www.vivirama.com/spain/cataluna/barcelona

xavi

hi Xavi,

that at sight damage! , would be possible to offer the own and natural name of Catalunya instead of Cataluna? is some problem not to use it?
Surely that this error can be repaired!

Thank Xavi

abat sise

First, I have to clarify that I'm not using regions from Geonames because of this
http://forum.geonames.org/gforum/posts/list/59.page#149

Unfortunately, in the database of regions that I'm using, the names for some regions like Catalunya are not in the language of that region. Probably I should have changed this immediately (I'm from this region!), but I didn't do it, and now that I've already spreaded the URL address with the other name, if I change the address, then I have to provide a way to handle the requests for the old name in a graceful way, and I haven't done that yet (up to now I've given more priority to other things). But I plan to change it, I'll let you know when it's done.

On the other hand, although I don't have any problem in discussing these vivirama issues here, I guess that it's not very appropriate (because this is to discuss about Geonames), so if you want to add anything, maybe you should send me a private message.

Thanks!

abat sise wrote:

would be possible to offer the own and natural name of Catalunya instead of Cataluna?

just let you know that this has been fixed in vivirama now (sorry for using this public forum, but I didn't find a way to contact privately with abat sise).

xavi

Ok, over a month on and I have got absolutely nowhere. Have decided to use PostgreSQL as it seems to be the best for GIS stuff.

Could someone please help me get started, I have downloaded it and tried following some examples on the internet all of which fail.

Basically I want a site as mentioned at top to type in placename and then within (x) miles and properties matching this to be dispalyed.

All I have got is postcodes of the properties.

Can anyone goive me a step by step approach.??? PLEASE?

I guess the abovementioned rule of thumb should be sufficient for your needs. If you only have postal codes of the properties you cannot have exact accuracy anyhow.

1. get the lat/lng for your properties (you can use the geonames webservice for this) and store it in your database. You add two new numerical columns to your table.

2. implement a search form on your site and get the lat/lng for the placename entered by the user. (you can again use the geonames webservice)

3. convert the miles into degrees with the rule of thumb (1 degree = 1.15 miles).

4. create a sql statement and search for all properties with a bounding box. lat/lng plus-mins radius x.

5. display list of properties to user

hope this helps

Marc

Thanks for the reply Marc,

IU am using dreamweaver and under the webservices panel it asks for:

URL of the WDSL file
and Proxy generator (currently .NET VB)

I have tried entering the url of the webservice http://ws.geonames.org/postalCodeSearch? plus loads of other variant but it keeps coming up with an error - cannot read wdsl file

Any ideas what I am doing wrong?

Hi

geonames is using REST type webservices as REST webservices are much simpler to deal with than SOAP webservices for example. The REST webservices don't have a WSDL.
I don't know dreamweaver, so I don't know whether it has a REST option in the webservice panel. It should not matter, however, as REST services are really easy to use and will manage without the help of dreamweaver.

Marc

can anyone help me request this in a simple asp page? still very confused

Did you already have a look at the json example?

http://www.geonames.org/maps/json-googlemaps-example.html

Marc

Hi MArc, yes I have seen that but its not what I am after, I dont need to show any maps.

Just enter place name and it stores the lon/lat of the first result (country = GB, max rows =1) in the form so that I can query my database

using PHP geoclass.php may be of use...

I tended to get the information, convert it to a point distance from a central location (North, East from a set location) then use distance calculation in postgres to get a true radius. This was my own personal preference.