Mapping from a database

1. Mapping from a database Peterson

2. Linux • PHP and MySQL were largely developed under Linux • open source operating system based on UNIX • Linus Torvalds – • “Hello everybody out there! I'm doing a (free) operating system … (just a hobby, won't be big and professional) for 386(486) AT clones. This has been brewing since april, and is starting to get ready.I'd like any feedback.”

3. PHP and MySQL • Most Linux installation procedures include the option of installing PHP and MySQL. • Administration of online databases is done through phpMyAdmin • Available through the cPanel • Purpose of exercises: • Develop familiarity with PHP • Input and query MySQL tables

4. PHP Example 1

5. PHP example 2

6. PHP example 3

7. PHP example 4

8. PHP Example 5

9. PHP Example 6

10. PHP Example 7

11. PHP Example 8

12. cPanel

13. MySQL is used to define new tables

15. SQL

17. Browse

18. Structure

19. SQL

20. Search

21. mysql_connect.php

22. All Capitals

23. //connection to database and querying <?php include 'mysql_connect.php'; $table = "us_capitals"; $sql = "SELECT name, usstate, population, Y(location), X(location) FROM $table"; $res = mysql_query($sql); if($res){ $num = mysql_num_rows($res); while($row = mysql_fetch_array($res)){ // contents one row of database $lat = $row['X(location)']; $lon = $row['Y(location)']; $name = $row['name']; $state = $row['usstate']; $pop = $row['population']; // create marker echo 'var myLatlng = new google.maps.LatLng('.$lat.','.$lon.');'; echo 'var marker = new google.maps.Marker({position: myLatlng, map: map, title: "'.$name.', Population: '.$pop.'", icon: image, shadow: shadow});'; // info message will be attached echo 'attachMessage(marker, "'.$name.'", "'.$state.'", "'.$pop.'");'; } } else echo "alert('SQL-Error getting data from database...')"; mysql_close($conn); ?>

24. North of Omaha / less than 500,000

25. //connection to database and querying <?php include 'mysql_connect.php'; $table = "us_capitals"; $sql = "SELECT name, usstate, population, Y(location), X(location) FROM $table WHERE X(location) > 41.25 AND population < 500000"; $res = mysql_query($sql);

26. Select within box

27. <?php //define coordinates and draw rectangle to map include 'mysql_connect.php'; $table = "us_capitals"; $sql =" SELECT name, Y(location), X(location), usstate, population FROM us_capitals WHERE Intersects( location, GeomFromText( 'POLYGON((40 -96, 44 -96, 44 -70, 40 -70, 40 -96))' ) ) ";

28. DROP TABLE IF EXISTS cities; create table cities ( city VARCHAR(30), location GEOMETRY NOT NULL, SPATIAL INDEX(location), PRIMARY KEY (city) ); INSERT INTO cities (city, location) VALUES ("Omaha", GeomFromText('POINT(41.25 -96)')); INSERT INTO cities (city, location) VALUES ("Atlanta", GeomFromText('POINT(33.755 -84.39)')); INSERT INTO cities (city, location) VALUES ("Lincoln", GeomFromText('POINT(40.809722 -96.675278)')); DROP TABLE IF EXISTS dl_airports; create table dl_airports ( city VARCHAR(30), airport VARCHAR(30), code VARCHAR(3), FOREIGN KEY (city) REFERENCES cities(city), PRIMARY KEY (code) ); INSERT INTO dl_airports (city, airport, code) VALUES ("Omaha","OmahaEppley Airfield", "OMA"); INSERT INTO dl_airports (city, airport, code) VALUES ("Atlanta","Hartsfield-Jackson International Airport", "ATL"); INSERT INTO dl_airports (city, airport, code) VALUES ("Lincoln","Municipal Airport", "LNK"); DROP TABLE IF EXISTS dl_routes; create table dl_routes ( airportCode VARCHAR(3), destinationCode VARCHAR(3), FOREIGN KEY (airportCode) references dl_airports(code), FOREIGN KEY (destinationCode) references dl_airports(code) ); INSERT INTO dl_routes (airportCode, destinationCode) VALUES ("OMA","ATL"); INSERT INTO dl_routes (airportCode, destinationCode) VALUES ("OMA","DET"); INSERT INTO dl_routes (airportCode, destinationCode) VALUES ("OMA","MEM"); Part of the SQL code for entering three tables that provide city location, airport information, and airline connections.

29. Flight Routes

30. <?php include 'mysql_connect.php'; $sql = "SELECT cDep.city AS departure , X( cDep.location ) AS latDep , Y( cDep.location ) AS lngDep, cDst.city AS destination , X( cDst.location ) AS latDest, Y( cDst.location ) AS lngDest FROM ( ( ( dl_routes AS rt INNER JOIN dl_airports AS ap1 ON rt.airportcode = ap1.code ) INNER JOIN dl_airports AS ap2 ON rt.destinationcode = ap2.code ) INNER JOIN cities AS cDep ON cDep.city = ap1.city_name ) INNER JOIN cities AS cDst ON cDst.city = ap2.city_name"; $res = mysql_query($sql); if($res){ $num = mysql_num_rows($res); while($row = mysql_fetch_array($res)){ echo "var flightPlanCoordinates = [ new google.maps.LatLng(".$row['latDep'].", ".$row['lngDep']."), new google.maps.LatLng(".$row['latDest'].", ".$row['lngDest'].") ]; flightPath = new google.maps.Polyline({ path: flightPlanCoordinates, strokeColor: \"#FF0000\", strokeOpacity: 0.8, geodesic: true, strokeWeight: 1 }); flightPath.setMap(map);"; } } else echo "alert('SQL-Error getting data from database...')"; mysql_close($conn); ?>

32. Selected routes

33. $sql = "SELECT cDep.city AS departure, X( cDep.location ) AS latDep, Y( cDep.location ) AS lngDep, cDst.city AS destination, X( cDst.location ) AS latDest, Y( cDst.location ) AS lngDest FROM ( ( ( dl_routes AS rt INNER JOIN dl_airports AS ap1 ON rt.airportcode = ap1.code ) INNER JOIN dl_airports AS ap2 ON rt.destinationcode = ap2.code ) INNER JOIN cities AS cDep ON cDep.city = ap1.city_name ) INNER JOIN cities AS cDst ON cDst.city = ap2.city_name WHERE cDep.city = 'Omaha'"; $res = mysql_query($sql);

34. create table ne_counties ( strokecolor VARCHAR(7), strokewidth INT(5), strokeopacity FLOAT(5), fillcolor VARCHAR(7), fillopacity FLOAT(5), popdata INT(15), name VARCHAR(30), geom GEOMETRY NOT NULL, SPATIAL INDEX(geom) ); INSERT INTO ne_counties (strokecolor, strokewidth , strokeopacity , fillcolor, fillopacity , popdata, name, geom) VALUES ("#008800",1,1.0,"#FFCC00",0.06674,33185,"county", GeomFromText('POLYGON((40.698311157 -98.2829258865,40.698311157 -98.2781218448,40.3505519215 -98.2781218448,40.3500181391 -98.3309663027,40.3504184759 -98.3344358884,40.3504184759 -98.7238301514,40.6413298855 -98.7242304882,40.6897706386 -98.7244973794,40.6989783851 -98.7243639338,40.6991118307 -98.7214281306,40.6985780482 -98.686198492,40.698311157 -98.2829258865, 40.698311157 -98.2829258865))')); INSERT INTO ne_counties (strokecolor, strokewidth , strokeopacity , fillcolor, fillopacity , popdata, name, geom) VALUES ("#008800",1,1.0,"#FFCC00",0.01334,6931,"county", GeomFromText('POLYGON((41.9149346991 -98.2956032185,42.0888143169 -98.2954697729,42.0888143169 -98.3004072602,42.3035282886 -98.3005407058,42.4369738893 -98.300140369,42.4377745629 -97.8344152223,42.2326686746 -97.8352158959,42.0897484361 -97.8346821135,42.0347688486 -97.8341483311,41.9164026008 -97.8332142119,41.9152015904 -98.0647423292,41.9149346991 -98.2956032185, 41.9149346991 -98.2956032185))')); MySQL commands, attributes, and coordinates for placing two county polygons for Nebraska into a MySQL database.

35. Nebraska county polygons

36. //connection to database and querying <?php include 'mysql_connect.php'; $sql = "SELECT popdata, AsText(ExteriorRing(geom)) AS linestring FROM ne_counties;"; $res = mysql_query($sql); // first statement -> get all polygons if($res){ // loop through all polygons while($row = mysql_fetch_array($res)){ $linestring = $row['linestring']; //points of current county polygon $pop = $row['popdata']; //population of current county //linestring includes all points of current polygon LINESTRING(40.698311157 -98.2829258865,40.69831115...) //delete not needed characters $clearedLS = str_replace(array("LINESTRING(" , "," , ")"),array(""," ",""),$linestring); //parse the string in PHP and store coordinates in array $coordinates = explode(" ", $clearedLS); //read coordinates from array by pair

37. //and finally create javascript GM latitude/longitude objects for polygons echo "varpts = new Array();"; $j=0; for ($i =0; $i<count($coordinates); $i=$i+2){ echo "pts[".$j."] = new google.maps.LatLng(".$coordinates[$i].",".$coordinates[$i+1].");"; $j++; } echo "polygon = new google.maps.Polygon({ paths: pts, strokeColor: '#0000FF', strokeOpacity: 1.0, strokeWeight: 0.5, fillColor: '#0000FF', fillOpacity: 0.4 });"; echo "polygon.setMap(map);"; } } else echo "alert('SQL-Error getting data from database...')"; mysql_close($conn); ?>

38. Nebraska county population

39. // Find the min and max population values for the 93 counties after doing a non-linear transformation using the log function varmin=100000000; var max=-100000000; for (vari = 0; i < 93; i++) { // Log the data to deal with counties with large populations popdata[i] = Math.log(popdata[i]) if (popdata[i] < min) { min=popdata[i] } if (popdata[i] > max) { max=popdata[i] } } // Find the range and compute the opacities for each polygon var range = max-min // compute an opacity as a range of the data values opacities = new Array () for (vari = 0; i < 93; i++) { opacities[i] = 1-((max - popdata[i]) / range) }

40. Counties with less than 50,000 $sql = "SELECT popdata, AsText(ExteriorRing(geom)) AS linestring FROM ne_counties WHERE popdata < 50000;"; $res = mysql_query($sql);

41. <?php include 'mysql_connect.php'; $sql = "SELECT popdata, AsText( ExteriorRing( geom ) ) AS geom FROM ne_counties WHERE Intersects( GeomFromText( 'POINT(41.25 -96)' ) , geom ) OR Intersects( GeomFromText( 'POINT(40.809722 -96.675278)' ) , geom ) OR Intersects( GeomFromText( 'POINT(40.700833 -99.081111)' ) , geom ) "; $res = mysql_query($sql);