Jump to content


Photo

looking for options...parse address from DB to Map form???


  • Please log in to reply
18 replies to this topic

#1 slashpine

slashpine
  • Members
  • PipPipPip
  • Advanced Member
  • 70 posts

Posted 29 August 2006 - 07:22 PM

Not sure if this is kosher or not...?

I have a database of local business listings... without having to hand code every record (over 50k)...I would like to add a link to a map of the address that coincides with each individual record...

Can this be done with a single script?

could it possibly be done using an html form grabbed from the source code of a free map provider (and hiding the fields) that executes a script that grabs the record from the DB and parses the streed address and the zip code ...this would leave me a "map" button rather than a (text) hyperlink..

any comments or suggestions welcome...

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 29 August 2006 - 08:03 PM

You need to know four things

the x,y coordinates (lat, long) of the business
the coordinates of top-left of the map
the coordinates of bottom-right of the map
the dimensions of the map on the page

Once you know those you can scale the coordinates and place the business in the correct place on the map
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 slashpine

slashpine
  • Members
  • PipPipPip
  • Advanced Member
  • 70 posts

Posted 29 August 2006 - 08:09 PM

Thanks for the reply...

What I have in the database in corresponding columns= 'street address' | 'zip code'

do I have any options using this data?

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 29 August 2006 - 08:12 PM

You need to get hold of a database of lat/longs for zipcodes. I don't know if there arae any free ones out there - try Google
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 29 August 2006 - 08:55 PM

What I have in the database in corresponding columns= 'street address' | 'zip code'

do I have any options using this data?


Yes. Yahoo Maps or Google Maps. Either of them conditionally allow the use of their maps.  If you visit one of the addresses in your database you'll see how map services construct their URLs and then you can use your database info to construct specific URLs for each of the businesses that link to the right part of the right map.
Legend has it that reading the manual never killed anyone.
My site

#6 slashpine

slashpine
  • Members
  • PipPipPip
  • Advanced Member
  • 70 posts

Posted 29 August 2006 - 08:59 PM

What I have in the database in corresponding columns= 'street address' | 'zip code'

do I have any options using this data?


Yes. Yahoo Maps or Google Maps. Either of them conditionally allow the use of their maps.  If you visit one of the addresses in your database you'll see how map services construct their URLs and then you can use your database info to construct specific URLs for each of the businesses that link to the right part of the right map.


Thanks...

I see what you mean...can I use php to query the database and  parse the address and zip  as variables and displays the dynamioc URL ?

#7 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 29 August 2006 - 09:43 PM

can I use php to query the database and  parse the address and zip  as variables and displays the dynamioc URL ?


Exactly so. I've used that on real estate sites to provide a direct link to the right part of Yahoo Maps from a clickable image on my client's site.
Legend has it that reading the manual never killed anyone.
My site

#8 slashpine

slashpine
  • Members
  • PipPipPip
  • Advanced Member
  • 70 posts

Posted 30 August 2006 - 12:34 AM

ok I think I got a handle on it but....The "address" record I have is a usually a street number and then a street name etc... all the URLs for the map sites have +'s between the number and the street name... the way I thought to call the data "...{$data['address']}..."  I don't know how to seperate the number and the street name in the string from that column with a +

?

#9 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 30 August 2006 - 12:42 AM

How you do that really depends on how the address in your database is formatted, but for the street address str_replace() is the function you'll need.

Below is a working example I used where addr1 was a street address, addr2 was the zip, and the country was presumed to be US.

    $url = "http://us.rd.yahoo.com/maps/us/insert/Tmap/extmap/*-http://maps.yahoo.com/maps_result?addr=";
    $url.= str_replace(" ","+",$query_data['addr1']);
    $url.= "&csz=";
    $url1 = str_replace(",","%2C",$query_data['addr2']);
    $url.= str_replace(" ","+",$url1). "&country=us";
    echo "<a href='". $url. "'><img src='images/maplink.gif' width='89' height='17' alt=''/></a>";

Legend has it that reading the manual never killed anyone.
My site

#10 slashpine

slashpine
  • Members
  • PipPipPip
  • Advanced Member
  • 70 posts

Posted 30 August 2006 - 12:54 AM

Thanks fopr the example...I will give it  ashot later tonight or tomorrow...

thanks again for the replies

#11 slashpine

slashpine
  • Members
  • PipPipPip
  • Advanced Member
  • 70 posts

Posted 30 August 2006 - 01:43 AM

no error problems but something is not right with the URL...

using your  snippet...

I get the following

http://maps.yahoo.co...sz=3&country=us

regardless of the query results...

any ideas?


#12 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 30 August 2006 - 01:57 AM

I know my code works. All I did was to go to one of the addresses in my database (manually entered at Yahoo), then zoom to whatever scale I wanted etc.  There's a link on the map page that says 'copy this to add this map to your site' ... or something like that ... which leads you to a text box with the precise html code for a link to that address on that map.

Then I just worked backwards from that html code to determine how I could use my own real database information to construct a similar link to an address from the database.  It always worked.

I notice that your map URL is different from the one I used. Maybe if you follow the process I've outlined above you'll be able to get your stuff working.
Legend has it that reading the manual never killed anyone.
My site

#13 slashpine

slashpine
  • Members
  • PipPipPip
  • Advanced Member
  • 70 posts

Posted 30 August 2006 - 02:24 AM

that is the page/ page I end up on when I used your code...

#14 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 30 August 2006 - 02:33 AM

Post one of your addresses (change the streeet number if you want), identifying which database fields each part represents and post an example of the database query that retrieves the data.
Legend has it that reading the manual never killed anyone.
My site

#15 slashpine

slashpine
  • Members
  • PipPipPip
  • Advanced Member
  • 70 posts

Posted 18 September 2006 - 12:13 AM

I think I have finally gotten this script to work...however I have encountered  a problem with one database that does not have complete zip code records for all records... 

can the author or anyone else help me so in place of a ZIP code the script will parse (the address and)  the "city"  with the state being a constant similar to the way the county is in the example below??

[code]$url.= str_replace(" ","+",$data['address']);
    $url.= "&amp;csz=";
    $url1 = str_replace(",","%2C",$data['zip']);
    $url.= str_replace(" ","+",$url1). "&amp;country=us";[/code]

I can see enough to replace $data 'zip'  with $data'city'.... but I am not sure about the State (GA)

TIA


edited I got this by changing zip to city and adding ",GA+ to 

    $url.= str_replace(" ","+",$url1). "[color=red],GA+[/color]&amp;country=us";


#16 slashpine

slashpine
  • Members
  • PipPipPip
  • Advanced Member
  • 70 posts

Posted 18 September 2006 - 01:06 AM

How you do that really depends on how the address in your database is formatted, but for the street address str_replace() is the function you'll need.

Below is a working example I used where addr1 was a street address, addr2 was the zip, and the country was presumed to be US.

    $url = "http://us.rd.yahoo.com/maps/us/insert/Tmap/extmap/*-http://maps.yahoo.com/maps_result?addr=";
    $url.= str_replace(" ","+",$query_data['addr1']);
    $url.= "&amp;csz=";
    $url1 = str_replace(",","%2C",$query_data['addr2']);
    $url.= str_replace(" ","+",$url1). "&amp;country=us";
    echo "<a href='". $url. "'><img src='images/maplink.gif' width='89' height='17' alt=''/></a>";


Thanks for your previous help...I have this scritp working both with zip codes and city/state parameters...

Now is there anything that can be done about addresses that characters like # 3 or #A

Thanks in advance

#17 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 18 September 2006 - 01:44 AM

The only thing that comes to mind is to go to maps.yahoo.com and enter one of those 'problem' addresses to see exactly what URL Yahoo constructs to find the right map, etc. or how it handles (or ignores) things that look like apartment numbers, and then work backwards from there.  That's how I solved the problem I had in the first place and now both us know it's possible and produces a pretty reasonable result (after a few aspirin and some trial and error).
Legend has it that reading the manual never killed anyone.
My site

#18 slashpine

slashpine
  • Members
  • PipPipPip
  • Advanced Member
  • 70 posts

Posted 18 September 2006 - 02:34 AM

ok thanks give that approach a shot and will post any progress...

your efforts are very much appreciated... the snippet works great...

With some trial and errors I have managed to make it work very well for me...

I have noticed that if the user has been using the new (beta) version of Yahoo maps (ala google) it works just as well and URL in the script works with both version of Yahoo Maps...depending on what the  user's browsers has been set to use...

Thanks again... I suppose some day I will have to go with geo coding etc.. but for now your script works great with my current databases...

thumbs up and hats off to you...

#19 slashpine

slashpine
  • Members
  • PipPipPip
  • Advanced Member
  • 70 posts

Posted 19 September 2006 - 01:56 PM

I just wanted to post this solution to the "#" character problem for anyone else that may encounter the glitch...

for some reason the script or the URI would not accept (symbol) characters...in this case the # sign...

After messing with the application and seeing how the map host handled the problem (as advised by 'AndyB') I surmised that changing the # to the word "Unit" solved the problem...

I used an UPDATE-Replace query to change all the records in the database

All is well... thanks for all the great help and suggestions...




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users