18  Mapping  from  a  database  

Mapping  in  the  Cloud  Peterson  

 

Linux  

•  PHP  and  MySQL  were  largely  developed  under  Linux    – open  source  operaAng  system  based  on  UNIX    

•  Linus  Torvalds  –    – “Hello  everybody  out  there!  I'm  doing  a  (free)  operaAng  system  …  (just  a  hobby,  won't  be  big  and  professional)  for  386(486)  AT  clones.  This  has  been  brewing  since  april,  and  is  starAng  to  get  ready.I'd  like  any  feedback.”    

PHP  and  MySQL  

•  Most  Linux  installaAon  procedures  include  the  opAon  of  installing  PHP  and  MySQL.    

•  AdministraAon  of  online  databases  is  done  through  phpMyAdmin    – Available  through  the  cPanel  

•  Purpose  of  exercises:  – Develop  familiarity  with  PHP  –  Input  and  query  MySQL  tables  

PHP  example  1  

Text forwindow header

/n” addsline break“

Text writtenout in body

Result:  HTML  wriZen  by  PHP  This  was  wriZen  in  HTML  from  a  PHP  script    

PHP  example  2  

PHP  example  3  

Text string

Concatenate string,number and text

Write $msg

Format withthousandsseparators

Write $msgagain

Result:  A  screen  with  a  1024  x  768  resoluAon  has  786432  pixels.  A  screen  with  a  1024  x  768  resoluAon  has  786,432  pixels.    

PHP  example  4  

Set initialvalue of $j

Loop five timesAdd $jto itself Write out the

value of j

Result:  The  value  of  j  is:  20  The  value  of  j  is:  40  The  value  of  j  is:  80  The  value  of  j  is:  160  The  value  of  j  is:  320    

PHP  example  5  

Set initialvalue of $j

Loop five timesAdd $jto itself Write out the

value of j

Result:  The  value  of  myCount  is:  0  The  value  of  myCount  is:  10  The  value  of  myCount  is:  20  The  value  of  myCount  is:  30  The  value  of  myCount  is:  40  

PHP  example  6  

Values of variables

If / else statement

Result:  20  is  bigger  than  10    

PHP  Example  6  

Get random numberbetween 0 and 10

Write random numberif greater than 5

Write random numberif less than 5

Add 1000 randomnumber results

Average of 1000random numbers

PHP  Example  7  

PHP Date example

Text forwindow header

Font andtext color

Writes date as:“03/25/12”

Writes date as:Sat Mar 10 17:16:18 MST 2012

cPanel  

MySQL  is  used  to  define  new  tables  

SQL  create table us_capitals ( name VARCHAR(30) PRIMARY KEY, usstate VARCHAR(5), population INT(10), location Point NOT NULL, SPATIAL INDEX(location));

INSERT INTO us_capitals (name, usstate, population, location) VALUES ( 'Montgomery', 'AL', 469268 , GeomFromText('POINT(32.377447 -86.300942)'));INSERT INTO us_capitals (name, usstate, population, location) VALUES ( 'Juneau', 'AK', 30987 , GeomFromText('POINT(58.302197 -134.410467)'));INSERT INTO us_capitals (name, usstate, population, location) VALUES ( 'Phoenix', 'AZ', 4039182 , GeomFromText('POINT(33.448097 -112.097094)'));......INSERT INTO us_capitals (name, usstate, population, location) VALUES ( 'Charleston', 'WV', 305526 , GeomFromText('POINT(38.333056 -81.613889)'));INSERT INTO us_capitals (name, usstate, population, location) VALUES ( 'Madison', 'WI', 543022 , GeomFromText('POINT(43.074444 -89.384722)'));INSERT INTO us_capitals (name, usstate, population, location) VALUES ( 'Cheyenne', 'WY', 85384 , GeomFromText('POINT(41.140278 -104.819722)'));

Create new MySQLtable with these fields Insert data

into fields

Lat and long putinto OGC point

Browse  

Structure  

SQL  

Search  

mysql_connect.php  

All  Capitals  

North  of  Omaha  /  less  than  500,000  

Select  within  box  

DROP  TABLE  IF  EXISTS  ciAes;      create  table  ciAes  (  

 city  VARCHAR(30),    locaAon  GEOMETRY  NOT  NULL,    SPATIAL  INDEX(locaAon),    PRIMARY  KEY  (city)  

);  INSERT  INTO  ciAes  (city,  locaAon)  VALUES  ("Omaha",  GeomFromText('POINT(41.25  -‐96)'));  INSERT  INTO  ciAes  (city,  locaAon)  VALUES  ("Atlanta",  GeomFromText('POINT(33.755  -‐84.39)'));  INSERT  INTO  ciAes  (city,  locaAon)  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  ciAes(city),    PRIMARY  KEY  (code)  

);  INSERT  INTO  dl_airports  (city,  airport,  code)  VALUES  ("Omaha","Omaha  Eppley  Airfield",  "OMA");  INSERT  INTO  dl_airports  (city,  airport,  code)  VALUES  ("Atlanta","Hartsfield-‐Jackson  InternaAonal  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),    desAnaAonCode  VARCHAR(3),    FOREIGN  KEY  (airportCode)  references  dl_airports(code),    FOREIGN  KEY  (desAnaAonCode)  references  dl_airports(code)  

);  INSERT  INTO  dl_routes  (airportCode,  desAnaAonCode)  VALUES  ("OMA","ATL");  INSERT  INTO  dl_routes  (airportCode,  desAnaAonCode)  VALUES  ("OMA","DET");  INSERT  INTO  dl_routes  (airportCode,  desAnaAonCode)  VALUES  ("OMA","MEM");    

Part  of  the  SQL  code  for  entering  three  tables  that  provide  city  

locaAon,  airport  informaAon,  and  airline  connecAons.    

Flight  Routes  

Flight Routes in a Relational Database

cities table dl_airports dl_routes

dep destcity airp codecity lat longOMA ATL

Input as point

Inner Join #1 Omaha ATL

Inner Join #2 Omaha Atlanta

Inner Join #3

Inner Join #4

41.25, -96 Atlanta

41.25, -96 33.75, -84.39

Joining(relational process)

Selected  routes  

create  table  ne_counAes  (    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_counAes  (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_counAes  (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,  aZributes,  and  coordinates  for  placing  two  county  

polygons  for  Nebraska  into  a  MySQL  database.    

Nebraska  county  polygons  

Nebraska  county  populaAon  

CounAes  with  less  than  50,000  

CounAes  with  less  than  50,000  

SelecAon  of  counAes  by  points  

SelecAon  of  

counAes  by  points