【数据库】Brisbane WiFi Hot Spots_MySQL吧

1樓巨大八爪鱼 2015-5-24 10:04

-- phpMyAdmin SQL Dump
-- version 4.0.10
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 24, 2015 at 12:01 PM
-- Server version: 5.1.73-0ubuntu0.10.04.1
-- PHP Version: 5.3.2-1ubuntu4.28

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `arslanbar_debug`
--

-- --------------------------------------------------------

--
-- Table structure for table `WiFiHotSpots`
--

CREATE TABLE IF NOT EXISTS `WiFiHotSpots` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(50) NOT NULL,
`Address` varchar(200) NOT NULL,
`Suburb` varchar(50) NOT NULL,
`PostalCode` int(11) DEFAULT NULL,
`Latitude` varchar(30) NOT NULL,
`Longitude` varchar(30) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=56 ;

--
-- Dumping data for table `WiFiHotSpots`
--

INSERT INTO `WiFiHotSpots` (`ID`, `Name`, `Address`, `Suburb`, `PostalCode`, `Latitude`, `Longitude`) VALUES
(1, '7th Brigade Park, Chermside', 'Delaware St', 'Chermside', NULL, '-27.37893', '153.04461'),
(2, 'Annerley Library Wifi', '450 Ipswich Road', 'Annerley', 4103, '-27.5094228', '153.033321'),
(3, 'Ashgrove Library Wifi', '87 Amarina Avenue', 'Ashgrove', 4060, '-27.4439462', '152.987098'),
(4, 'Banyo Library Wifi', '284 St. Vincents Road', 'Banyo', 4014, '-27.3739664', '153.078323'),
(5, 'Booker Place Park', 'Birkin Rd & Sugarwood St', 'Bellbowrie', NULL, '-27.56353', '152.89372'),
(6, 'Bracken Ridge Library Wifi', 'Corner Bracken and Barrett Street', 'Bracken Ridge', 4017, '-27.3179726', '153.037873'),
(7, 'Brisbane Botanic Gardens', 'Mt Coot-tha Rd', 'Toowong', NULL, '-27.47724', '152.97599'),
(8, 'Brisbane Square Library Wifi', 'Brisbane Square, 266 George Street', 'Brisbane', 4000, '-27.4709117', '153.022459'),
(9, 'Bulimba Library Wifi', 'Corner Riding Road & Oxford Street', 'Bulimba', 4171, '-27.4520308', '153.062824'),
(10, 'Calamvale District Park', 'Formby & Ormskirk Sts', 'Calamvale', NULL, '-27.62152', '153.03665'),
(11, 'Carina Library Wifi', 'Corner Mayfield Road & Nyrang Street', 'Carina', 4152, '-27.4916931', '153.091212'),
(12, 'Carindale Library Wifi', 'The Home and Leisure Centre, Corner Carindale Street & Banchory Court, Westfield Carindale Shopping Centre', 'Carindale', 4152, '-27.5047592', '153.100396'),
(13, 'Carindale Recreation Reserve', 'Cadogan and Bedivere Sts', 'Carindale', NULL, '-27.497', '153.11105'),
(14, 'Chermside Library Wifi', '375 Hamilton Road', 'Chermside', 4032, '-27.3856032', '153.034902'),
(15, 'City Botanic Gardens Wifi', 'Alice Street', 'Brisbane City', NULL, '-27.47561', '153.03005'),
(16, 'Coopers Plains Library Wifi', '107 Orange Grove Road', 'Coopers Plains', 4108, '-27.5651050', '153.040318'),
(17, 'Corinda Library Wifi', '641 Oxley Road', 'Corinda', 4075, '-27.5388023', '152.980909'),
(18, 'D.M. Henderson Park', 'Granadilla St', 'MacGregor', NULL, '-27.57745', '153.07603'),
(19, 'Einbunpin Lagoon', 'Brighton Rd', 'Sandgate', NULL, '-27.31947', '153.06822'),
(20, 'Everton Park Library Wifi', '561 South Pine Road', 'Everton park', 4053, '-27.4053336', '152.990420'),
(21, 'Fairfield Library Wifi', 'Fairfield Gardens Shopping Centre, 180 Fairfield Road', 'Fairfield', 4103, '-27.5090903', '153.025970'),
(22, 'Forest Lake Parklands', 'Forest Lake Bld', 'Forest Lake', NULL, '-27.62020', '152.96625'),
(23, 'Garden City Library Wifi', 'Garden City Shopping Centre, Corner Logan and Kessels Road', 'Upper Mount Gravatt', 4122, '-27.5624422', '153.080918'),
(24, 'Glindemann Park', 'Logan Rd', 'Holland Park West', NULL, '-27.52552', '153.06923'),
(25, 'Grange Library Wifi', '79 Evelyn Street', 'Grange', 4051, '-27.4253119', '153.017472'),
(26, 'Gregory Park', 'Baroona Rd', 'Paddington', NULL, '-27.467', '152.99981'),
(27, 'Guyatt Park', 'Sir Fred Schonell Dve', 'St Lucia', NULL, '-27.49297', '153.00187'),
(28, 'Hamilton Library Wifi', 'Corner Racecourt Road and Rossiter Parade', 'Hamilton', 4007, '-27.4379013', '153.064222'),
(29, 'Hidden World Park', 'Roghan Rd', 'Fitzgibbon', NULL, '-27.3397170', '153.034981'),
(30, 'Holland Park Library Wifi', '81 Seville Road', 'Holland Park', 4121, '-27.5229228', '153.072292'),
(31, 'Inala Library Wifi', 'Inala Shopping centre, Corsair Ave', 'Inala', 4077, '-27.5982857', '152.973521'),
(32, 'Indooroopilly Library Wifi', 'Indrooroopilly Shopping centre, Level 4, 322 Moggill Road', 'Indooroopilly', 4068, '-27.4976428', '152.973647'),
(33, 'Kalinga Park', 'Kalinga St', 'Clayfield', NULL, '-27.40666', '153.05217'),
(34, 'Kenmore Library Wifi', 'Kenmore Village Shopping Centre, Brookfield Road', 'Kenmore', 4069, '-27.5059285', '152.938643'),
(35, 'King Edward Park (Jacob''s Ladder)', 'Turbot St and Wickham Tce', 'Brisbane', NULL, '-27.46589', '153.02406'),
(36, 'King George Square', 'Ann & Adelaide Sts', 'Brisbane', NULL, '-27.46843', '153.02422'),
(37, 'Mitchelton Library Wifi', '37 Helipolis Parada', 'Mitchelton', 4053, '-27.4170416', '152.978340'),
(38, 'Mt Coot-tha Botanic Gardens Library Wifi', 'Administration Building, Brisbane Botanic Gardens (Mt Coot-tha), Mt Coot-tha Road', 'Toowong', 4066, '-27.4752990', '152.976041'),
(39, 'Mt Gravatt Library Wifi', '8 Creek Road', 'Mt Gravatt', 4122, '-27.5385548', '153.080262'),
(40, 'Mt Ommaney Library Wifi', 'Mt Ommaney Shopping Centre, 171 Dandenong Road', 'Mt Ommaney', 4074, '-27.5482419', '152.937809'),
(41, 'New Farm Library Wifi', '135 Sydney Street', 'New Farm', 4005, '-27.4673657', '153.049584'),
(42, 'New Farm Park Wifi', 'Brunswick Street', 'New Farm', NULL, '-27.47046,', '153.05223'),
(43, 'Nundah Library Wifi', '1 Bage Street', 'Nundah', 4012, '-27.4012590', '153.058375'),
(44, 'Oriel Park', 'Cnr of Alexandra & Lancaster Rds', 'Ascot', NULL, '-27.42928', '153.05768'),
(45, 'Orleigh Park', 'Hill End Tce', 'West End', NULL, '-27.48995', '153.00326'),
(46, 'Post Office Square', 'Queen & Adelaide Sts', 'Brisbane', NULL, '-27.46735', '153.02735'),
(47, 'Rocks Riverside Park', 'Counihan Rd', 'Seventeen Mile Rocks', NULL, '-27.54153', '152.95913'),
(48, 'Sandgate Library Wifi', 'Seymour Street', 'Sandgate', 4017, '-27.3206052', '153.070492'),
(49, 'Stones Corner Library Wifi', '280 Logan Road', 'Stones Corner', 4120, '-27.4980357', '153.043655'),
(50, 'Sunnybank Hills Library Wifi', 'Sunnybank Hills Shopping Centre, Corner Compton and Calam Roads', 'Sunnybank Hills', 4109, '-27.6109253', '153.055070'),
(51, 'Teralba Park', 'Pullen & Osborne Rds', 'Everton Park', NULL, '-27.40286', '152.98059'),
(52, 'Toowong Library Wifi', 'Toowon Village Shopping Centre, Sherwood Road', 'Toowong', 4066, '-27.4850511', '152.992509'),
(53, 'West End Library Wifi', '178 - 180 Boundary Street', 'West End', 4101, '-27.4824507', '153.012076'),
(54, 'Wynnum Library Wifi', 'Wynnum Civic Centre, 66 Bay Terrace', 'Wynnum', 4178, '-27.4424489', '153.173196'),
(55, 'Zillmere Library Wifi', 'Corner Jennings Street and Zillmere Road', 'Zillmere', 4034, '-27.3601423', '153.040789');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

2樓巨大八爪鱼 2015-5-24 10:04

来源：http://data.brisbane.qld.gov.au/index.php/dataset/wifi-hot-spots/

3樓巨大八爪鱼 2015-5-24 10:06

【资料：以下为Suburb与PostalCode分离语句】
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Annerley', `PostalCode` = 4103 WHERE `id` = 2;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Ashgrove', `PostalCode` = 4060 WHERE `id` = 3;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Banyo', `PostalCode` = 4014 WHERE `id` = 4;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Bracken Ridge', `PostalCode` = 4017 WHERE `id` = 6;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Brisbane', `PostalCode` = 4000 WHERE `id` = 8;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Bulimba', `PostalCode` = 4171 WHERE `id` = 9;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Carina', `PostalCode` = 4152 WHERE `id` = 11;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Carindale', `PostalCode` = 4152 WHERE `id` = 12;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Chermside', `PostalCode` = 4032 WHERE `id` = 14;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Coopers Plains', `PostalCode` = 4108 WHERE `id` = 16;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Corinda', `PostalCode` = 4075 WHERE `id` = 17;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Everton park', `PostalCode` = 4053 WHERE `id` = 20;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Fairfield', `PostalCode` = 4103 WHERE `id` = 21;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Upper Mount Gravatt', `PostalCode` = 4122 WHERE `id` = 23;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Grange', `PostalCode` = 4051 WHERE `id` = 25;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Hamilton', `PostalCode` = 4007 WHERE `id` = 28;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Holland Park', `PostalCode` = 4121 WHERE `id` = 30;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Inala', `PostalCode` = 4077 WHERE `id` = 31;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Indooroopilly', `PostalCode` = 4068 WHERE `id` = 32;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Kenmore', `PostalCode` = 4069 WHERE `id` = 34;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Mitchelton', `PostalCode` = 4053 WHERE `id` = 37;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Toowong', `PostalCode` = 4066 WHERE `id` = 38;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Mt Gravatt', `PostalCode` = 4122 WHERE `id` = 39;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Mt Ommaney', `PostalCode` = 4074 WHERE `id` = 40;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'New Farm', `PostalCode` = 4005 WHERE `id` = 41;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Nundah', `PostalCode` = 4012 WHERE `id` = 43;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Sandgate', `PostalCode` = 4017 WHERE `id` = 48;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Stones Corner', `PostalCode` = 4120 WHERE `id` = 49;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Sunnybank Hills', `PostalCode` = 4109 WHERE `id` = 50;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Toowong', `PostalCode` = 4066 WHERE `id` = 52;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'West End', `PostalCode` = 4101 WHERE `id` = 53;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Wynnum', `PostalCode` = 4178 WHERE `id` = 54;
UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = 'Zillmere', `PostalCode` = 4034 WHERE `id` = 55;

4樓巨大八爪鱼 2015-5-24 10:06

【资料：以下为Suburb与PostalCode分离用的PHP】
<?php
include_once("../../includes/conn.php");
include_once("../../includes/functions.php");
include_once("../../includes/data.php");
include_once("__CONFIG__.php");
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<?php
$sql = "SELECT `ID`, `Suburb` FROM `arslanbar_debug`.`WiFiHotSpots` ORDER BY `ID` ASC";
$rs = mysql_query($sql);
while ($row = mysql_fetch_row($rs)) {
   $arr = explode(",", $row[1]);
   if (count($arr) > 1) {
       $sql = "UPDATE `arslanbar_debug`.`WiFiHotSpots` SET `Suburb` = '{$arr[0]}', `PostalCode` = {$arr[1]} WHERE `id` = {$row[0]};";
       echo $sql."<br />";
   }
}
?>
</body>
</html>

5樓巨大八爪鱼 2015-5-24 10:10

Import has been successfully finished, 11 queries executed. ()

6樓巨大八爪鱼 2015-5-24 10:12

<?php include 'header.inc'; ?>
   <form>
       Search Type:
       <select name="type">
           <option value="suburb" selected="selected">Suburb</option>
           <option value="postalcode">Postal Code</option>
           <option value="geolocation">Geographical Location</option>
       </select>
   </form>
   <form name="searchbasedonsuberb" action="results.php" method="get">
       Suburb:
          <select name="suburb">
           <option value=1>Test</option>
       </select>
       <br />
       <input type="submit" value="Search" />
   </form>
   <form name="searchbasedonpostalcode" action="results.php" method="get" style="display:none">
       Postal Code:
          <input name="postalcode" type="text"/>
       <br />
       <input type="submit" value="Search" />
   </form>
   <form name="searchbasedongeolocation" action="results.php" method="get" style="display:none">
       Latitude:
       <input name="latitude" type="text"/>
       <br />
       Longitude:
       <input name="longitude" type="text"/>
       <br />
       <input type="submit" value="Search" />
   </form>
<?php include 'footer.inc'; ?>

回復帖子