Jump to content

mo

Members
  • Posts

    139
  • Joined

  • Last visited

    Never

Profile Information

  • Gender
    Not Telling

mo's Achievements

Member

Member (2/5)

0

Reputation

  1. Here are my current results. I have logic in my PHP to loop through the result and remove duplicates but that is a sloppy way of removing the duplicates and since the cities are not in order, the logic does not always work. city region distance Ascending -------------------------------------------- Milpitas CA 0 San Jose CA 4.05605867292861 San Jose CA 4.27379165682789 San Jose CA 4.72803292240825 San Jose CA 5.10657017304142 Fremont CA 5.37310902047679 Santa Clara CA 5.86235168930009 San Jose CA 6.31906928616225 Alviso CA 6.57871062331007 Fremont CA 6.7615683089475
  2. I am using the below script which works fine to query all regions in a specified radius of my input latitude and longitude. However my database has duplicate city/state pairs due to different zip codes and the query returns duplicate cities. I tried to use distinct but since the query has to be ordered by distance, I could not get this to work. Any suggestions. Note: Table contains columns city, region(state), country, zip, latitude and longitude. SELECT `city`, `region`, 3956 * 2 * ASIN(SQRT(POWER(SIN(( 37.4404 - abs(`latitude`)) * pi()/180 / 2),2) + COS(37.4404 * pi()/180 ) * COS(abs(`latitude`) * pi()/180) * POWER(SIN((-121.8705 - `longitude`) * pi()/180 / 2), 2) )) as distance FROM `locationdb` having distance < 20 ORDER BY distance limit 10
  3. Ok. Well this is a minor issue and the visitor will be able to manually change their location. In regard to my initial question and I guess based on your feed back. It would be a good idea to start with a master database of IP addresses by location and maintain the database when the site encounters a new IP. I think I answered my own question. I will review the various free IP geolocation databases out there and build my master tables.
  4. So what is the best method for determining a visitors location in your opinion?
  5. I need some input on my design. I am creating a site similar to foursquare and I am working on my geo-location classes and logic. Option 1: I was going to use the ipinfodb website's API to gather location data for a visitor's IP address and save the returned data in a database table. This way I could always check the table first (to avoid the API overhead) than use the API if no result was found. I figure eventually my database table will be almost 100% and the API will barely be used. Option 2: My other option is the same only I start with a huge table of IP address and location data that I downloaded for free from various data gathering sites and use the API as needed to update the table with missing entries. I guess both options are the same and will work the same. My main issue is that when a visitor performs a search for locations, I do not just want o show locations in their city (which I obtain from their IP). I want to show searches from nearby cities, this is where the idea to initially start off with a huge database table came from. Since I would need to have a table which contained all US cities and their nearby cities within a specific radius. This is the design that I am having trouble with. I could use the API at ws.geonames.org to get the nearby cities and build a database table as I go just like option 1. I guess I'm just looking on some feedback on if this is the way that this is normally done or should I go another route.
  6. I got it working using the following. Although I have to go state by state to keep the query from timing out. UPDATE `cityzip` AS a SET `latitude` = (SELECT DISTINCT `latitude` FROM `ip_geolocation` WHERE UPPER(`city`) = UPPER(a.`city`) AND UPPER(`region_name_short`) = UPPER(a.`state`) limit 1) WHERE a.`state` = 'AK' AND a.`latitude` = '';
  7. Thanks for all the feedback. I actually downloaded a free zip code list and don't want to pay for a list just to have the lat and lon. I will try a join and if this does not work, I will copy table 2 and remove unneeded columns.
  8. If I use in for both city and state, wouldn't that return random results?
  9. I have 2 tables described below. I am trying to take latitude and longitude from table B and update table A. I could just use table B but it is huge, 1Mil+ records and even though indexed, queries are slow. Table A: (cityzip) ID | zip_code | city | state | latitude | longitude ------------------------------------------------------------ 1 | 30281 | Atlanta | GA | | ... ------------------------------------------------------------ Table B: (ip_geolocation) goecode_id | ip | country_code | country_name | region_name | city | postal_code | latitude | longitude ---------------------------------------------------------------------------------------------------------------------------------------------- 0 | 555555555 | USA | United States | GA | Atlanta | 30281 | 34.000 | -45.099 ... ----------------------------------------------------------------------------------------------------------------------------------------------- UPDATE `cityzip` AS a SET `latitude` = (SELECT DISTINCT `latitude` FROM `ip_geolocation` WHERE UPPER(`city`) = UPPER(a.`city`) AND UPPER(`region_name`) = UPPER(a.`state`)) WHERE a.`latitude` = ''; Error #1242 - Subquery returns more than 1 row
  10. Does anyone know of a free or cheap API for local area searches. I need to search for local business by location and category. I was using Yahoo and it was working great until I realized that it was for non-commercial use. I also tried Yelp but it sucks. I am looking at the Google API but the documentation and examples are so convoluted that it is annoying and I have given up. I'm looking for a REST API that returns XML or JSON.
  11. I do not have the position, I want to read the array with a key value. I am looping through a main array and printing values. I have a multidimensional array which has additional values that I need to read based on the current key of my main loop. Here is my current code for the loop, which is not working. $venue_key equals the key value of my main loop. foreach($attributes as $attribute) { foreach($attribute[$venue_key] as $key => $value) { echo $key.'->'.$value; } }
  12. I have a array as follows and I want to look by key where the key is 'ABCD', 'EFGH', etc.. I am using foreach but it is not working. How can I accomplish this? Array ( [0] => Array ( [ABCD] => Array ( [venue_id] => 1003 [has_dining] => X [table_count] => 0 [serves_alcohol] => X ) ) [1] => Array ( [EFGH] => Array ( [venue_id] => 1003 [has_dining] => X [table_count] => 0 [serves_alcohol] => X ) ) )
  13. I have successfully used the Yahoo local search API and received results in XML format. I am trying to use PHPs Simple XML to parse the data into an array so I can loop through and print specific elements. I cannot get to specific elements and I have read several threads online. Below is some code and below that is the XML. I am trying to get to the elements like title, address, etc. $url = 'http://local.yahooapis.com/LocalSearchService/V3/localSearch?appid=<my yahoo api key>&category=Casinos&query=blackjack&zip=95035&radius=20&results=10'; $xml = file_get_contents($url); $phpobject = simplexml_load_string($xml); foreach($phpobject->attributes() as $name=>$attr) { $res[$name]=$attr; } <ResultSet xsi:schemaLocation="urn:yahoo:lcl http://local.yahooapis.com/LocalSearchService/V3/LocalSearchResponse.xsd" totalResultsAvailable="48" totalResultsReturned="10" firstResultPosition="1"> − <ResultSetMapUrl> http://maps.yahoo.com/broadband/?q1=Milpitas%2C+CA+95035&tt=poker&tp=1 </ResultSetMapUrl> − <Result id="34179399"> <Title>Bay Area Poker Chip</Title> <Address>1274 Piper Dr</Address> <City>Milpitas</City> <State>CA</State> <Phone>(408) 263-8611</Phone> <Latitude>37.412359</Latitude> <Longitude>-121.891794</Longitude> − <Rating> <AverageRating>5</AverageRating> <TotalRatings>1</TotalRatings> <TotalReviews>1</TotalReviews> <LastReviewDate>1198993183</LastReviewDate> − <LastReviewIntro> I bought 100% real clay chips from here and obsolutely love it. I had a poker game that night and needed chips and a table. They had everything in stock and saved me time to look around. Great products and great service and I highly recommend this place to anybody </LastReviewIntro> </Rating> <Distance>1.33</Distance> − <Url> http://local.yahoo.com/info-34179399-bay-area-poker-chip-milpitas </Url> − <ClickUrl> http://local.yahoo.com/info-34179399-bay-area-poker-chip-milpitas </ClickUrl> − <MapUrl> http://maps.yahoo.com/maps_result?q1=1274+Piper+Dr+Milpitas+CA&gid1=34179399 </MapUrl> <BusinessUrl>http://bayareapokerchip.com/</BusinessUrl> <BusinessClickUrl>http://bayareapokerchip.com/</BusinessClickUrl> − <Categories> <Category id="96928631">Graphic Design</Category> </Categories> </Result> − <Result id="44540748"> <Title>Bay Area Poker Chip</Title> <Address>519 Montague Expy</Address> <City>Milpitas</City> <State>CA</State> <Phone>(408) 263-8611</Phone> <Latitude>37.409925</Latitude> <Longitude>-121.89489</Longitude> − <Rating> <AverageRating>NaN</AverageRating> <TotalRatings>0</TotalRatings> <TotalReviews>0</TotalReviews> <LastReviewDate/> <LastReviewIntro/> </Rating> <Distance>1.48</Distance> − <Url> http://local.yahoo.com/info-44540748-bay-area-poker-chip-milpitas </Url> − <ClickUrl> http://local.yahoo.com/info-44540748-bay-area-poker-chip-milpitas </ClickUrl> − <MapUrl> http://maps.yahoo.com/maps_result?q1=519+Montague+Expy+Milpitas+CA&gid1=44540748 </MapUrl> <BusinessUrl>http://www.bayareapokerchip.com/</BusinessUrl> <BusinessClickUrl>http://www.bayareapokerchip.com/</BusinessClickUrl> − <Categories> <Category id="96925976">Sporting Goods</Category> <Category id="96935378">Games</Category> </Categories> </Result> − <Result id="67710717"> <Title>Poker Da Vinci</Title> <Address>51 E Campbell Ave</Address> <City>Campbell</City> <State>CA</State> <Phone>(408) 866-4446</Phone> <Latitude>37.287157</Latitude> <Longitude>-121.94942</Longitude> − <Rating> <AverageRating>NaN</AverageRating> <TotalRatings>0</TotalRatings> <TotalReviews>0</TotalReviews> <LastReviewDate/> <LastReviewIntro/> </Rating> <Distance>10.40</Distance> − <Url> http://local.yahoo.com/info-67710717-poker-da-vinci-campbell </Url> − <ClickUrl> http://local.yahoo.com/info-67710717-poker-da-vinci-campbell </ClickUrl> − <MapUrl> http://maps.yahoo.com/maps_result?q1=51+E+Campbell+Ave+Campbell+CA&gid1=67710717 </MapUrl> <Categories/> </Result> − <Result id="21599986"> <Title>Bay 101 Banquet Facilities</Title> <Address>1801 Bering Dr</Address> <City>San Jose</City> <State>CA</State> <Phone>(408) 451-8888</Phone> <Latitude>37.373044</Latitude> <Longitude>-121.912125</Longitude> − <Rating> <AverageRating>4</AverageRating> <TotalRatings>2</TotalRatings> <TotalReviews>2</TotalReviews> <LastReviewDate>1200040214</LastReviewDate> − <LastReviewIntro> If youre looking for high limit action Bay 101 is the place to go in San Jose. You can regularly find limit games up to $80-$160 in their spacious high ceiling room. </LastReviewIntro> </Rating> <Distance>4.13</Distance> − <Url> http://local.yahoo.com/info-21599986-bay-101-banquet-facilities-san-jose </Url> − <ClickUrl> http://local.yahoo.com/info-21599986-bay-101-banquet-facilities-san-jose </ClickUrl> − <MapUrl> http://maps.yahoo.com/maps_result?q1=1801+Bering+Dr+San+Jose+CA&gid1=21599986 </MapUrl> <BusinessUrl>http://bay101.com/</BusinessUrl> <BusinessClickUrl>http://bay101.com/</BusinessClickUrl> − <Categories> <Category id="96929273">Casinos</Category> </Categories> </Result> − <Result id="21401964"> <Title>Thwart Poker Incorporated</Title> <Address>525 Lincoln Ave</Address> <City>Palo Alto</City> <State>CA</State> <Phone>(650) 329-9627</Phone> <Latitude>37.444049</Latitude> <Longitude>-122.151513</Longitude> − <Rating> <AverageRating>NaN</AverageRating> <TotalRatings>0</TotalRatings> <TotalReviews>0</TotalReviews> <LastReviewDate/> <LastReviewIntro/> </Rating> <Distance>14.09</Distance> − <Url> http://local.yahoo.com/info-21401964-thwart-poker-incorporated-palo-alto </Url> − <ClickUrl> http://local.yahoo.com/info-21401964-thwart-poker-incorporated-palo-alto </ClickUrl> − <MapUrl> http://maps.yahoo.com/maps_result?q1=525+Lincoln+Ave+Palo+Alto+CA&gid1=21401964 </MapUrl> − <Categories> <Category id="96926908">Internet Access Providers</Category> <Category id="96932773">Computer Business Solutions</Category> </Categories> </Result> − <Result id="21574436"> <Title>Blinky's Can't Say Lounge</Title> <Address>1031 Monroe St</Address> <City>Santa Clara</City> <State>CA</State> <Phone>(408) 985-7201</Phone> <Latitude>37.348893</Latitude> <Longitude>-121.94816</Longitude> − <Rating> <AverageRating>5</AverageRating> <TotalRatings>3</TotalRatings> <TotalReviews>3</TotalReviews> <LastReviewDate>1228667811</LastReviewDate> − <LastReviewIntro> Good drinks good prices good people. Who could ask for more..... </LastReviewIntro> </Rating> <Distance>6.39</Distance> − <Url> http://local.yahoo.com/info-21574436-blinky-s-can-t-say-lounge-santa-clara </Url> − <ClickUrl> http://local.yahoo.com/info-21574436-blinky-s-can-t-say-lounge-santa-clara </ClickUrl> − <MapUrl> http://maps.yahoo.com/maps_result?q1=1031+Monroe+St+Santa+Clara+CA&gid1=21574436 </MapUrl> − <Categories> <Category id="96926057">All Bars, Pubs, & Clubs</Category> <Category id="96926063">Bars & Pubs</Category> </Categories> </Result> − <Result id="21619987"> <Title>Barbara of Pauline's Cake Decorating Supplies</Title> <Address>1093 Malone Rd</Address> <City>San Jose</City> <State>CA</State> <Phone>(408) 978-9740</Phone> <Latitude>37.293702</Latitude> <Longitude>-121.889876</Longitude> − <Rating> <AverageRating>4.5</AverageRating> <TotalRatings>8</TotalRatings> <TotalReviews>8</TotalReviews> <LastReviewDate>1268173462</LastReviewDate> − <LastReviewIntro> I love this store. No matter what it is i am looking for weather it is some thing i remember from my child hood or some thing i saw in a magazine and i need it i know i can find it there. Yes they are a little rough around the edges but that is only because they are trying to get it across to you what will work and what will not. This is an old school store with people who know their stuff and are only trying to help you get the best end result possible. I am 46 years old and i took my class from Barbara when i first came out of high school and believe me she will not try to up sale you or tell you some thing is going to work if it will not. Go in expecting to be overwhelmed by stock and know you are going to get the best prices in town. Take the time to get to know the employees and the store and you will always go back to Paulines. I travel all the way from Livermore to get stuff i know i can only find there. Thanks for all your many years of being in business </LastReviewIntro> </Rating> <Distance>9.52</Distance> − <Url> http://local.yahoo.com/info-21619987-barbara-of-pauline-s-cake-decorating-supplies-san-jose </Url> − <ClickUrl> http://local.yahoo.com/info-21619987-barbara-of-pauline-s-cake-decorating-supplies-san-jose </ClickUrl> − <MapUrl> http://maps.yahoo.com/maps_result?q1=1093+Malone+Rd+San+Jose+CA&gid1=21619987 </MapUrl> − <Categories> <Category id="96928859">Wedding Supplies & Services</Category> </Categories> </Result> − <Result id="21602089"> <Title>Carey David Incorporated</Title> <Address>1624 Remuda Ln</Address> <City>San Jose</City> <State>CA</State> <Phone>(408) 453-7843</Phone> <Latitude>37.372385</Latitude> <Longitude>-121.908305</Longitude> − <Rating> <AverageRating>NaN</AverageRating> <TotalRatings>0</TotalRatings> <TotalReviews>0</TotalReviews> <LastReviewDate/> <LastReviewIntro/> </Rating> <Distance>4.13</Distance> − <Url> http://local.yahoo.com/info-21602089-carey-david-incorporated-san-jose </Url> − <ClickUrl> http://local.yahoo.com/info-21602089-carey-david-incorporated-san-jose </ClickUrl> − <MapUrl> http://maps.yahoo.com/maps_result?q1=1624+Remuda+Ln+San+Jose+CA&gid1=21602089 </MapUrl> <BusinessUrl>http://dc-enterprises.com/</BusinessUrl> <BusinessClickUrl>http://dc-enterprises.com/</BusinessClickUrl> − <Categories> <Category id="96928508">Clothing Wholesalers</Category> <Category id="96928509">Clothing Manufacturers</Category> <Category id="96931030">Apparel Manufacturing</Category> </Categories> </Result> − <Result id="30246475"> <Title>Royal Casino Parties</Title> <Address>943 Berryessa Rd, #6</Address> <City>San Jose</City> <State>CA</State> <Phone>(408) 213-0904</Phone> <Latitude>37.363563</Latitude> <Longitude>-121.886341</Longitude> − <Rating> <AverageRating>NaN</AverageRating> <TotalRatings>0</TotalRatings> <TotalReviews>0</TotalReviews> <LastReviewDate/> <LastReviewIntro/> </Rating> <Distance>4.71</Distance> − <Url> http://local.yahoo.com/info-30246475-royal-casino-parties-san-jose </Url> − <ClickUrl> http://local.yahoo.com/info-30246475-royal-casino-parties-san-jose </ClickUrl> − <MapUrl> http://maps.yahoo.com/maps_result?q1=943+Berryessa+Rd%2C+%236+San+Jose+CA&gid1=30246475 </MapUrl> <BusinessUrl>http://www.royalcasinoparties.com/</BusinessUrl> <BusinessClickUrl>http://www.royalcasinoparties.com/</BusinessClickUrl> − <Categories> <Category id="96925812">Entertainment Production</Category> <Category id="96930362">Gambling</Category> </Categories> </Result> − <Result id="25247620"> <Title>Bay Casino Gaming, Casino Party Rentals</Title> <Address/> <City>San Jose</City> <State>CA</State> <Phone>(408) 747-1977</Phone> <Latitude>37.338475</Latitude> <Longitude>-121.885794</Longitude> − <Rating> <AverageRating>NaN</AverageRating> <TotalRatings>0</TotalRatings> <TotalReviews>0</TotalReviews> <LastReviewDate/> <LastReviewIntro/> </Rating> <Distance>6.44</Distance> − <Url> http://local.yahoo.com/info-25247620-bay-casino-gaming-casino-party-rentals-san-jose </Url> − <ClickUrl> http://local.yahoo.com/info-25247620-bay-casino-gaming-casino-party-rentals-san-jose </ClickUrl> − <MapUrl> http://maps.yahoo.com/maps_result?q1=+San+Jose+CA&gid1=25247620 </MapUrl> <BusinessUrl>http://www.baycasinogaming.com/</BusinessUrl> <BusinessClickUrl>http://www.baycasinogaming.com/</BusinessClickUrl> − <Categories> <Category id="96925812">Entertainment Production</Category> <Category id="96925814">Party Rentals</Category> </Categories> </Result> </ResultSet>
  14. Perfect, this is exactly what I wanted to know. I will change the table accordingly. I am in the design phase and have not started coding but I want to make sure my tables are designed correctly before I start coding. I am using MySQL with PHP 5. Now I just need to determine when to use ENUM and when to use foreign key tables. I like foreign key tables so that they can be used in drop down list and ENUM when I know that I will not need the values in a drop down but I do not know what is the best practice. Thanks...
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.