More GIS

[MrMark]

I've recently started looking into using spatial data calculations on Mysql (not the best-endowed database in this respect, but never mind). Without using WKT data I can find a distance between 2 points using trig calculations for given latitude and longitude co-ordinates. I can find these co-ords for each post code I give easily enough from Multimap, for example, and then save the data as a radian in decimal data type fields- eg DECIMAL(9,6).
Now the GIS pages I've perused seem to indicate that using WKT (well known text format) allows you to calculate distances using built-in gis formulas (now available even in Mysql). However I can't find anywhere (spent several hours on google) that converts the long/lat elements easily to WKT, or gives the WKT data for relevant zipcode/postcode. Does anyone know if such a site or tool exists?

[eldeano]

Any chance you could put that into English? :cool:

[ValenciaSon]

I've recently started looking into using spatial data calculations on Mysql (not the best-endowed database in this respect, but never mind). Without using WKT data I can find a distance between 2 points using trig calculations for given latitude and longitude co-ordinates. I can find these co-ords for each post code I give easily enough from Multimap, for example, and then save the data as a radian in decimal data type fields- eg DECIMAL(9,6).
Now the GIS pages I've perused seem to indicate that using WKT (well known text format) allows you to calculate distances using built-in gis formulas (now available even in Mysql). However I can't find anywhere (spent several hours on google) that converts the long/lat elements easily to WKT, or gives the WKT data for relevant zipcode/postcode. Does anyone know if such a site or tool exists?My wife is an account executive at ESRI (http://www.esri.com/) and will bring this question to work and seek an answer from the techies.

[MrMark]

Any chance you could put that into English? :cool:
Well we are in the Geeks section Eldeano!!!!:rolleyes:

My wife is an account executive at ESRI (http://www.esri.com/) and will bring this question to work and seek an answer from the techies.

Wow, muchas gracias, ValenciaSon - that would be cool!
I have to admit I've found out how to calculate distances between 2 places (assuming I can access their latitude and longitude in radians), by using the cos, acos and sin functions. From there I can query my database to find all banks or restaurants etc that are within a defined radius (assuming I have their details input into database). No need for the WKT data. But I suspect I'll need to know how to get this data as my knowledge develops....

[lwjl]

The GIS executive here. I have to say: this is the first time on this forum I've heard a GIS issue mentioned. It's kind of fun for me, actually. Despite my long history with GIS, I'm not familiar with the WKT format. I'm aware that ESRI (the company for which I work) has based products can consume RSS feeds so it would seem plausible that our products could consume something similar. I'll check on Tuesday (Monday is a national holiday here in the US). I applaud your trig calculations. There are a bunch of ESRI ArcGIS tools that automate distance calculations and buffers, as well as, routing, network analysis, thematic analysis, statistical analysis, 3D analysis, etc. right out of the box. Is your project for fun or work-related? Here's a "What is GIS" link, if this is helpful:

http://www.gis.com/whatisgis/index.html

I'll look for your answer next week. But if you want more overview info, let me know what you're trying to build and I'll see what I can come up with.

Cheers.

Leslie

[richardksa]

Will this (http://www.bluemarblegeo.com/products/calculator.php)help? Or any of the GEO CALC programs.

[MrMark]

Thanks for the feedback all. I've been able to calculate distances between different places I've recorded on my mysql database. So long as I have the longitude and latitude values I can use the trig functions available in mysql to calculate the distance (either in miles or kilometres). The reason I was after the WKT data was because there is a new datatype (POINT) and Mysql now uses new functions to manipulate this Point data, and WKT data is used to populate the Point fields. But, as I can already do my calculations on the latitude and longitude values, I no longer need to import the WKT data into my database.
I will be investigating other aspects of GIS, so thanks all for the informative links. I doubt very much though I'll be in any position soon to purchase software tools. The jobs market in the UK is as bad as it's ever been over the last 30 years (well like many I know, I was always able to get work in the 80s and 90s; not so now). However, I can console myself with calculating the distance between my home and Madrid (787 miles, or 1267 kilometres)!

[tad]

I've got some pretty good ones in the back of my AA members handbook -pm me if you're interested. ;)

[MrMark]

I've got some pretty good ones in the back of my AA members handbook -pm me if you're interested. ;)

Jeje, yes of course, just knowing distances between places is pretty "static" information.

However the advantage of having the data in a database format is this. You could record tapas bars, lauderettes, or specialist shops etc. Then you can run a query to find all those in a certain category that were within 3 or 30 miles (or 800 kilometres or whatever criteria you wanted to give). Of course in a sense it's still a bit pointless for a home database, but I suspect it's how some commercial companies do their "Find your closest store" facility on their website.

[deecree]

... but I suspect it's how some commercial companies do their "Find your closest store" facility on their website.

Don't tell Tad that! He was happy thinking little pixies in the computer did all the work.
You've really gone and spoilt it for him now. :(

[gastephen]

However, I can console myself with calculating the distance between my home and Madrid (787 miles, or 1267 kilometres)!

Nice one! :thumbs-up:

I assume, of course, that your trig calculations are formulated for distances over the surface of an oblate spheroid...:eek: ;D

And, by the way, if you are ever tempted to use the Google Maps API to geocode UK postcodes, don't bother: they are only accurate down to the postcode district (or was it the area? - I can't remember). You can, however, use the Google Ajax Search API instead to get results accurate down to the unit code.

Cheers, G.

[gastephen]

but I suspect it's how some commercial companies do their "Find your closest store" facility on their website.

They are probably using QAS Nearest or something similar...

[MrMark]

They are probably using QAS Nearest or something similar...
Well yes, but how does the software tool QAS Nearest calculate the distance??? Anyway thanks for your other info Gastephen - very interesting stuff. And thanks again to the other input from other posters on this thread. I feared I wasn't going to get much feedback on this specialist subject - just shows it never pays to underestimate the geekdom here! We should form a sinister ultra-secret organisation to take over the world (Ben-elDeana??) :o No, enough of my Dr-Evil fantasies...now, where's that cat..

[eldeano]

...now, where's that cat..

Over here, reading all this geeky stuff and understanding very little.

[gastephen]

Well yes, but how does the software tool QAS Nearest calculate the distance???

The Quick Address component uses Royal Mail / Ordnance Survey data to geocode postcodes and then does the distance calculations. I undestand that it can also takes into account major obstacles, such as large bodies of water, in the calculations. My point was that commerical sites are more likely to use off-the-shelf components such as this, rather than re-invent the code.


We should form a sinister ultra-secret organisation to take over the world (Ben-elDeana??) :o No, enough of my Dr-Evil fantasies...now, where's that cat..Hasn't the AISB already done that? (didn't anyone notice Adam Weishaupt taking Washington's place?). Eye in the pyramid and all that (just look at the back of a dollar bill).

[lwjl]

well all this GIS talk is fun for me though not as fun as about a hundred other things. who would have thought GIS would have made it to the forum...

In the US (and elsewhere I'm sure), there are several notable companies that sell address databases against which to geocode (link to a lat/long). The commercial sites almost always use these. And depending upon the tool and the quality of the data, users can geocode from the centroid of the postal zipcode to submeter. Obviously the data is better in urban areas where there is more data and where there is less change over time (less need for refresh). There is also a lot of free gov't (not so good) data available. ESRI posts a lot of free data too. You get what you pay for. Users can purchase business databases with regular refresh (quarterly, yearly)... Cheers...

Leslie

[ValenciaSon]

Here's a tool (http://google-latlong.blogspot.com/2008/05/google-earth-meet-browser.html) that those with a web site which occasionally focuses on locations in a country, may find useful.

[MrMark]

Nice one ValenciaSon. Mola mucho...:)

[ValenciaSon]

Nice one ValenciaSon. Mola mucho...:)I'm glad que te molio.

[ValenciaSon]

Habeis visto o leido esto (http://www.elmundo.es/navegante/2008/05/20/tecnologia/1211301808.html)?

[gastephen]

Habeis visto o leido esto (http://www.elmundo.es/navegante/2008/05/20/tecnologia/1211301808.html)?

Me gustó este enlace (http://weburbanist.com/2007/06/23/5-surreal-google-street-view-images-interesting-errors-glitches-and-mistakes/) en el artículo. Un poco siniestro, ¿no? Agujeros negros al fin de la calle, y todo eso...

[gastephen]

Thanks for the feedback all. I've been able to calculate distances between different places I've recorded on my mysql database. So long as I have the longitude and latitude values I can use the trig functions available in mysql to calculate the distance (either in miles or kilometres)...

Hi MrMark - it's been a while since you were posting on this topic, but I thought you might be interested - if you don't already know, that is - about the functions available in the Google Maps API.

To evaluate as-the-crow-flies distances from one location to another you can use the distanceFrom() method of the GLatLng class.

However, something that I have been using recently and that is more useful for certain applications is the GDirections class. You can use its asynchronous load() method with a query involving either textual location descriptions or lat/long coordinates. This will return route information, including the distance by road between two points.

I've recently written a demo prototype dealer locator (http://ppnet.loyaltylogistix.com/demo/locator/locator.html) based on this. This expects a UK postcode as input, and will rank a number of fixed sites in order of road distance. You can also get directions and view the suggested route on the map.

BTW, there is documentation about the API available here (http://code.google.com/apis/maps/documentation/index.html).

Cheers, G.

[MrMark]

Good info there, thx Gastephen. I'm afraid I don't have much time to follow this at the moment, as I've just started a new contract, but I hope to get back to this at some point.:thumbs-up: