a2bardeals Posted March 8, 2007 Share Posted March 8, 2007 I am working on a geocoding program that will calculate distance between two addresses and I think I have found a way to do it. However, the data I could find is from the US census website. It's called TIGER/Line data and its in a wierd format. I have found one site that kind of explains how to do it but I can't get it to work the way they want. Basically, they have a PHP script that is supposed to read the data into MySQL which dosen't seem to work for me (gives parse errors and such). I am desperatly in need of finding a way to use this TIGER/Line Data to geocode a certain county. To fully undertand this problem you may need to read the article. http://blog.tooleshed.com/?p=16 Any ideas on getting this data into a MySQL format? Quote Link to comment https://forums.phpfreaks.com/topic/41833-geocoding-with-mysql/ Share on other sites More sharing options...
fenway Posted March 8, 2007 Share Posted March 8, 2007 Doesn't MySQL have GIS support? Quote Link to comment https://forums.phpfreaks.com/topic/41833-geocoding-with-mysql/#findComment-203008 Share on other sites More sharing options...
a2bardeals Posted March 9, 2007 Author Share Posted March 9, 2007 I don't know what GIS means... but I made a few hacks to the provided code on that article and loaded the data in. Now the second page to the article explains how to pull the data and calculate distance but when i try their geocode.php script i get all kinds of parse errors. Maybe because I'm using PHP 4.1.2 and they wrote it for 5 or something but now I am really stuck. I can post the code for anyone interested in helping a distraught student. Quote Link to comment https://forums.phpfreaks.com/topic/41833-geocoding-with-mysql/#findComment-203077 Share on other sites More sharing options...
artacus Posted March 9, 2007 Share Posted March 9, 2007 I don't know what TIGER data is. But what you want to calculate distances is the Lat Lon of each point. Can TIGER be converted somehow to lat lon? You could just register an account w/ google and use their geocoding api. But if your a student, you probably aren't allowed that option. Quote Link to comment https://forums.phpfreaks.com/topic/41833-geocoding-with-mysql/#findComment-203097 Share on other sites More sharing options...
richardw Posted March 10, 2007 Share Posted March 10, 2007 GIS stands for "geographic information system" Take a look at ESRI's site for more info on GIS. The TIGER format and GIS are compatible as wierd as the format may seem. www.esri.com/ GIS software can easily calculate distance, but it has been too many years since I worked with GIS to formulate an interface to extract and manipluate the data in MySQL. Artacus' suggestion as to lat long with google may be the most straight forward route, but knowing what GIS is may help you solve your problem. If you have not solved it yet, please post your code as it will definitley be helpful. best (thirty plus years since a distraught stutent... and then there was no GIS to worry about) Quote Link to comment https://forums.phpfreaks.com/topic/41833-geocoding-with-mysql/#findComment-204361 Share on other sites More sharing options...
a2bardeals Posted March 11, 2007 Author Share Posted March 11, 2007 basically I've loaded the data from 2 counties into a table using the method in the article posted above. Now I can not figure out how to: 1. Parse the user inputed address. e.g. 3004 N. Main St. would break up into 4 parts the street number (3004) the direction (N) the street (main) and the type (St.) 2. Then search the database for the lat-long of the address. Finally: 3. Calculate the distance from a set lat long (aka a store location). Quote Link to comment https://forums.phpfreaks.com/topic/41833-geocoding-with-mysql/#findComment-205054 Share on other sites More sharing options...
artacus Posted March 11, 2007 Share Posted March 11, 2007 Well like I said, google maps will do 1 and 2 for you. After you have the lat/long you need to use the haversine or Great-circle formula to calculate the distance. http://en.wikipedia.org/wiki/Great-circle_distance Quote Link to comment https://forums.phpfreaks.com/topic/41833-geocoding-with-mysql/#findComment-205083 Share on other sites More sharing options...
a2bardeals Posted March 12, 2007 Author Share Posted March 12, 2007 dosen't google have fairly strict terms on using thier API for commercial purposes though? Plus...I would like to have this done all "in-house" so that I am not relying on anyone elses services or connections. Quote Link to comment https://forums.phpfreaks.com/topic/41833-geocoding-with-mysql/#findComment-205568 Share on other sites More sharing options...
richardw Posted March 12, 2007 Share Posted March 12, 2007 They may have a strict policy for commercial purposes, but for testing, you can get one for "localhost". I was able to get the data uploaded for my City, the query results are weaker than I would like, so I am reviewing the underling data. I may have to use the search with the LIKE function. When I did locate a street address it was accurate or when I used it to calculate distances between 2 lat lon it als0 was accurate. The issue was that not as many streets showed up. Quote Link to comment https://forums.phpfreaks.com/topic/41833-geocoding-with-mysql/#findComment-205709 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.