SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 40 of 40
  1. #26
    SitePoint Zealot prashidi's Avatar
    Join Date
    Mar 2001
    Posts
    118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe you can post those values somewhere on hotscripts or something similar to that. I doubt that the person who started this thread is seeking the information but it would be good to have it somewhere just in case someone else does.

  2. #27
    SitePoint Addict zoordaan's Avatar
    Join Date
    Feb 2001
    Location
    NYC/Texas
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Everah, could you make those files available as a zip etc.?

  3. #28
    SitePoint Enthusiast Everah's Avatar
    Join Date
    Mar 2004
    Location
    East Bay Area, CA, USA
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry for not getting back to you sooner, I thought this thread died.

    Quote Originally Posted by zoordaan
    Everah, could you make those files available as a zip etc.?
    Sure, what would you like? After looking at my files, I have a really accurate datafile (52,600 US Zipcodes with lats, longs, states, GMT offsets) but they are nowhere near SQL ready, so you will have to either edit the file or write a script to get them into a table. I also have a flatfile with just cities and zips. I have another datafile that is SQL ready, but it only has zips, lats and longs and is only 42,000 lines, so I am not sure how accurate it is. I also have a MySQL command line that will pull all records in a client table that are within a proximity to a supplied zip code using spherical surface distance of the Earth.

    Just let me know. And again, sorry for not checking this out sooner.

  4. #29
    SitePoint Addict zoordaan's Avatar
    Join Date
    Feb 2001
    Location
    NYC/Texas
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Probably the first option, and I'll just write something to carve it up however I want it. And the mysql command line too.

    I use winrar 3.2 for compressed files so a zip or rar is good.

    Thanks

    As for the thread dying, I've been interested in this topic for a few years so it's in my subscribed threads and I always check back whenever someone posts to it

  5. #30
    SitePoint Enthusiast Everah's Avatar
    Join Date
    Mar 2004
    Location
    East Bay Area, CA, USA
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by zoordaan
    Probably the first option, and I'll just write something to carve it up however I want it. And the mysql command line too.

    I use winrar 3.2 for compressed files so a zip or rar is good.

    Thanks

    As for the thread dying, I've been interested in this topic for a few years so it's in my subscribed threads and I always check back whenever someone posts to it

    OK, give me a couple of days. The file I have that is the most accurate is now in two formats; MySQL AND flat text. The MySQL ready version is formatted to insert with my table name. I can run a dump to get the table structure and zip data all in one file (but again, it will have my table name in it). How do you want it?

  6. #31
    SitePoint Addict zoordaan's Avatar
    Join Date
    Feb 2001
    Location
    NYC/Texas
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The mysql with the dump structure is fine.

  7. #32
    SitePoint Enthusiast Everah's Avatar
    Join Date
    Mar 2004
    Location
    East Bay Area, CA, USA
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Here it is...

    Quote Originally Posted by zoordaan
    The mysql with the dump structure is fine.
    OK, go to this link:
    http://www.purelyauto.com/zipcodes/p...p_codes.sql.gz

    The file is about 970K gzipped. Extracted and installed it is about 3.5 MB.

    Good luck!

    PS If anyone is looking for a goodwill gesture to offer for this data, my automotive forum just went up and could use some members
    Last edited by Everah; Jun 12, 2004 at 14:34.

  8. #33
    SitePoint Addict zoordaan's Avatar
    Join Date
    Feb 2001
    Location
    NYC/Texas
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. I've downloaded the zip file.

  9. #34
    Web-coding NINJA! silver trophy beetle's Avatar
    Join Date
    Jul 2002
    Location
    Dallas, TX
    Posts
    2,900
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I wrote a class for this almost two years ago. I can't find all the code right now, but I managed to dig up the SQL query I used from an old post I made here.
    PHP Code:
    $sql "
        SELECT
            
    $this->zipCol 
        FROM
            
    $this->zipTable 
        WHERE (    
            POW((69.172*(
    $this->lonCol-\"$this->lon\")*cos($this->lat/57.3)),2) +
            POW((69.172*(
    $this->latCol-\"$this->lat\")),2)
            ) < (
    $this->radius*$this->radius)
        "

    As you can see, I use quite a few member variables, but you should be able to put your own data in there.

    This is a sql query that performs the arithmetic of the haversine forumla - something you absolutely need when doing lat/lon stuff.
    beetle a.k.a. Peter Bailey
    blogs: php | prophp | security | design | zen | software
    refs: dhtml | gecko | prototype | phpdocs | unicode | charsets
    tools: ide | ftp | regex | ffdev




  10. #35
    SitePoint Member
    Join Date
    Jun 2004
    Location
    Bangkok, Thailand
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Everah
    OK, go to this link:
    http://www.purelyauto.com/zipcodes/p...p_codes.sql.gz

    The file is about 970K gzipped. Extracted and installed it is about 3.5 MB.

    Good luck!

    PS If anyone is looking for a goodwill gesture to offer for this data, my automotive forum just went up and could use some members
    Hey thanks!

    I lurk these forums a lot looking for code help and today they provided me with a free zip database that I thought was going to be impossible to find. I thought I'd register and say thanks for the help

    p.s. - I'd help by joining the auto forum but I have absolutely no interest in automotive stuff so I don't think it'd do much good sorry.

  11. #36
    SitePoint Zealot mrWoot's Avatar
    Join Date
    Jan 2004
    Location
    Wisconsin
    Posts
    131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wonderful. Just wonderful. I was wondering if anyone has a full script done? I'm almost done with mine.

  12. #37
    SitePoint Zealot mrWoot's Avatar
    Join Date
    Jan 2004
    Location
    Wisconsin
    Posts
    131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by beetle
    I wrote a class for this almost two years ago. I can't find all the code right now, but I managed to dig up the SQL query I used from an old post I made here.
    PHP Code:
    $sql "
        SELECT
            
    $this->zipCol 
        FROM
            
    $this->zipTable 
        WHERE (    
            POW((69.172*(
    $this->lonCol-\"$this->lon\")*cos($this->lat/57.3)),2) +
            POW((69.172*(
    $this->latCol-\"$this->lat\")),2)
            ) < (
    $this->radius*$this->radius)
        "

    As you can see, I use quite a few member variables, but you should be able to put your own data in there.

    This is a sql query that performs the arithmetic of the haversine forumla - something you absolutely need when doing lat/lon stuff.
    I figured out what everything was except $this->lonCol and $this->latCol. What are those?

  13. #38
    Web-coding NINJA! silver trophy beetle's Avatar
    Join Date
    Jul 2002
    Location
    Dallas, TX
    Posts
    2,900
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Those are column names in the DB that hold the necessary latitude and longitude coordinates.
    beetle a.k.a. Peter Bailey
    blogs: php | prophp | security | design | zen | software
    refs: dhtml | gecko | prototype | phpdocs | unicode | charsets
    tools: ide | ftp | regex | ffdev




  14. #39
    SitePoint Enthusiast Everah's Avatar
    Join Date
    Mar 2004
    Location
    East Bay Area, CA, USA
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Taking the Zip Code data down...

    Hi everyone. I hope the zipcode data has been helpful. I am going to be taking the data down tonight. There has been an awful lot of folks downloading the thing, so I believe I am going to take it a step further and package a locator. I am finishing up a class right now that will search the database for clients that are in a proximity to a user. That is what I was looking for originally.

    Can I get an opinion? If this data was sold at a fair price (say $10), would you have considered buying it? All responses are welcome. Thanks for your feedback.

  15. #40
    SitePoint Enthusiast DmS's Avatar
    Join Date
    Jan 2004
    Location
    Stockholm, Sweden
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I did this recently, try this out.
    Zipcodes, + PHP/MySQL queries
    http://www.ozoneasylum.com/21781

    Cheers/Dan
    { knowledge is what remains once you forget what you learned }
    Home: DmSProject Tutorials: GurusNetwork
    Committed at:
    OzoneAsylum + Blog


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •