Notes from Spain and Spanish Forum Learn REAL Spanish now!  

Go Back   Notes from Spain and Spanish Forum > The Rastro > Geek's Corner

Notices

Reply
 
Thread Tools Display Modes
Old 24th May 2008, 07:46 PM   #1
MrMark
Convert to vinos
 
MrMark's Avatar
 
Join Date: Oct 2007
Location: London
Posts: 317
Default More GIS

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?
MrMark is offline   Reply With Quote
Old 24th May 2008, 08:32 PM   #2
eldeano
He's the most tip top
 
eldeano's Avatar
 
Join Date: Nov 2006
Location: UK
Posts: 3,889
Default

Any chance you could put that into English?
eldeano is offline   Reply With Quote
Old 24th May 2008, 08:45 PM   #3
ValenciaSon
Hero Forero
 
ValenciaSon's Avatar
 
Join Date: Mar 2006
Location: Silver Spring, Maryland, USA
Posts: 4,915
Default

Quote:
Originally Posted by MrMark View Post
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 and will bring this question to work and seek an answer from the techies.
ValenciaSon is offline   Reply With Quote
Old 24th May 2008, 10:16 PM   #4
MrMark
Convert to vinos
 
MrMark's Avatar
 
Join Date: Oct 2007
Location: London
Posts: 317
Default

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

Quote:
My wife is an account executive at ESRI 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....
MrMark is offline   Reply With Quote
Old 25th May 2008, 02:35 AM   #5
lwjl
Forero
 
lwjl's Avatar
 
Join Date: Feb 2007
Location: Silver Spring, MD
Posts: 16
Default GIS

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
lwjl is offline   Reply With Quote
Old 25th May 2008, 02:47 AM   #6
richardksa
Jedi Forero
 
richardksa's Avatar
 
Join Date: Mar 2006
Location: Madrid
Posts: 1,848
Default

Will this help? Or any of the GEO CALC programs.
richardksa is offline   Reply With Quote
Old 25th May 2008, 10:38 AM   #7
MrMark
Convert to vinos
 
MrMark's Avatar
 
Join Date: Oct 2007
Location: London
Posts: 317
Default

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)!
MrMark is offline   Reply With Quote
Old 25th May 2008, 01:22 PM   #8
tad
virtual idiot
 
tad's Avatar
 
Join Date: Nov 2006
Location: palmers green
Posts: 2,400
Default

I've got some pretty good ones in the back of my AA members handbook -pm me if you're interested.
tad is offline   Reply With Quote
Old 25th May 2008, 01:30 PM   #9
MrMark
Convert to vinos
 
MrMark's Avatar
 
Join Date: Oct 2007
Location: London
Posts: 317
Default

Quote:
Originally Posted by tad View Post
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.
MrMark is offline   Reply With Quote
Old 25th May 2008, 08:20 PM   #10
deecree
Errant in Forolandia
 
Join Date: Mar 2006
Location: Kobol
Posts: 1,043
Default

Quote:
Originally Posted by MrMark View Post
... 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.
deecree is offline   Reply With Quote
Old 26th May 2008, 02:32 AM   #11
gastephen
..es que yo no estoy loco
 
gastephen's Avatar
 
Join Date: Mar 2007
Location: Gales
Posts: 1,932
Default

Quote:
Originally Posted by MrMark View Post
However, I can console myself with calculating the distance between my home and Madrid (787 miles, or 1267 kilometres)!
Nice one!

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

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 is offline   Reply With Quote
Old 26th May 2008, 02:34 AM   #12
gastephen
..es que yo no estoy loco
 
gastephen's Avatar
 
Join Date: Mar 2007
Location: Gales
Posts: 1,932
Default

Quote:
Originally Posted by MrMark View Post
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...
gastephen is offline   Reply With Quote
Old 26th May 2008, 12:29 PM   #13
MrMark
Convert to vinos
 
MrMark's Avatar
 
Join Date: Oct 2007
Location: London
Posts: 317
Talking

Quote:
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??) No, enough of my Dr-Evil fantasies...now, where's that cat..
MrMark is offline   Reply With Quote
Old 26th May 2008, 12:44 PM   #14
eldeano
He's the most tip top
 
eldeano's Avatar
 
Join Date: Nov 2006
Location: UK
Posts: 3,889
Default

Quote:
Originally Posted by MrMark View Post
...now, where's that cat..
Over here, reading all this geeky stuff and understanding very little.
eldeano is offline   Reply With Quote
Old 26th May 2008, 01:04 PM   #15
gastephen
..es que yo no estoy loco
 
gastephen's Avatar
 
Join Date: Mar 2007
Location: Gales
Posts: 1,932
Default

Quote:
Originally Posted by MrMark View Post
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.

Quote:
We should form a sinister ultra-secret organisation to take over the world (Ben-elDeana??) 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).
gastephen is offline   Reply With Quote
Old 26th May 2008, 11:33 PM   #16
lwjl
Forero
 
lwjl's Avatar
 
Join Date: Feb 2007
Location: Silver Spring, MD
Posts: 16
Default

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
lwjl is offline   Reply With Quote
Old 5th June 2008, 02:15 PM   #17
ValenciaSon
Hero Forero
 
ValenciaSon's Avatar
 
Join Date: Mar 2006
Location: Silver Spring, Maryland, USA
Posts: 4,915
Default More GIS

Here's a tool that those with a web site which occasionally focuses on locations in a country, may find useful.
ValenciaSon is offline   Reply With Quote
Old 8th June 2008, 11:36 AM   #18
MrMark
Convert to vinos
 
MrMark's Avatar
 
Join Date: Oct 2007
Location: London
Posts: 317
Default

Nice one ValenciaSon. Mola mucho...
MrMark is offline   Reply With Quote
Old 10th June 2008, 01:49 PM   #19
ValenciaSon
Hero Forero
 
ValenciaSon's Avatar
 
Join Date: Mar 2006
Location: Silver Spring, Maryland, USA
Posts: 4,915
Default

Quote:
Originally Posted by MrMark View Post
Nice one ValenciaSon. Mola mucho...
I'm glad que te molio.
ValenciaSon is offline   Reply With Quote
Old 22nd June 2008, 05:36 PM   #20
ValenciaSon
Hero Forero
 
ValenciaSon's Avatar
 
Join Date: Mar 2006
Location: Silver Spring, Maryland, USA
Posts: 4,915
Default

Habeis visto o leido esto?
ValenciaSon is offline   Reply With Quote
Reply

Bookmarks
Learn REAL Spanish now!

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT +1. The time now is 01:07 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.