PHP + MySQL – Best solutions for finding points in a polygon

We will actually be doing this all in MySQL, the title is a bit misleading but in my defense this is still the best way to find all the points in a given polygon using PHP + MySQL we just simply don’t use PHP.

An overview of what we will do goes like this: First we will need to create a MySQL point in polygon function. Secondly we will need to format our table properly so that this function is able to run efficiently on our table.Then finally we need to build a query that uses that function and selects the proper results.

In this particular case we will be working with geographical coordinates lat,lng – this tutorial could also be used in the application of non geographical x,y coordinates.

1. Here is the Mysql Point In Polygon function aka (GISWhithin). Run the below code in MySQL to create this function:

DELIMITER //

CREATE FUNCTION GISWithin(pt POINT, mp MULTIPOLYGON) RETURNS INT(1) DETERMINISTIC
BEGIN

DECLARE str, xy TEXT;
DECLARE x, y, p1x, p1y, p2x, p2y, m, xinters DECIMAL(16, 13) DEFAULT 0;
DECLARE counter INT DEFAULT 0;
DECLARE p, pb, pe INT DEFAULT 0;

SELECT MBRWithin(pt, mp) INTO p;
IF p != 1 OR ISNULL(p) THEN
RETURN p;
END IF;

SELECT X(pt), Y(pt), ASTEXT(mp) INTO x, y, str;
SET str = REPLACE(str, 'POLYGON((','');
SET str = REPLACE(str, '))', '');
SET str = CONCAT(str, ',');

SET pb = 1;
SET pe = LOCATE(',', str);
SET xy = SUBSTRING(str, pb, pe - pb);
SET p = INSTR(xy, ' ');
SET p1x = SUBSTRING(xy, 1, p - 1);
SET p1y = SUBSTRING(xy, p + 1);
SET str = CONCAT(str, xy, ',');

WHILE pe > 0 DO
SET xy = SUBSTRING(str, pb, pe - pb);
SET p = INSTR(xy, ' ');
SET p2x = SUBSTRING(xy, 1, p - 1);
SET p2y = SUBSTRING(xy, p + 1);
IF p1y < p2y THEN SET m = p1y; ELSE SET m = p2y; END IF;
IF y > m THEN
IF p1y > p2y THEN SET m = p1y; ELSE SET m = p2y; END IF;
IF y <= m THEN
IF p1x > p2x THEN SET m = p1x; ELSE SET m = p2x; END IF;
IF x <= m THEN
IF p1y != p2y THEN
SET xinters = (y - p1y) * (p2x - p1x) / (p2y - p1y) + p1x;
END IF;
IF p1x = p2x OR x <= xinters THEN
SET counter = counter + 1;
END IF;
END IF;
END IF;
END IF;
SET p1x = p2x;
SET p1y = p2y;
SET pb = pe + 1;
SET pe = LOCATE(',', str, pb);
END WHILE;

RETURN counter % 2;

END;

DELIMITER ;

2. Setting up the database table

So the next thing we need to do is create or format a table that has a “Points” Column that column should have a spacial index on it. In this example I will call that column LngLatCoords.

IMPORTANT NOTE: the column is named LngLat not LatLng because Longitude corresponds with the X axis and Latitude corresponds with Y axis ,this can be confusing because things like google maps will give you Lat,Lng coordinates – so you would need to flip this around in order to get corresponding X,Y coordinates. If you are working with non geographical points perhaps XYCoords is better name.

Assuming you have a table with Lat,Lng columns you will need to take those to columns and create one LngLatCoords column. Here is the query for that.


ALTER TABLE mytable ADD coords LngLatCoords;

UPDATE myTable SET LngLatCoords = GeometryFromText( CONCAT( 'POINT(', Lng, ' ', Lat, ')' ) );

CREATE SPATIAL INDEX sx_mytable_coords ON mytable(LatLngCoords);
 

3. The query for getting points

Lets say this is our polygon points in format “lng lat,lng lat” format.

$polygon = " -89 36, -90 35,-84 35, -81 36, -89 36";

IMPORTANT:

– first and last point must be the same for this GISWithin function to work.

– polygon format is “lng lat,lng lat” – you will pry have to reformat from “lat lng, lat lng”.

– Mysql Polygon(()) is enclosed by two brackets ”((” not just one “(“.

So the query to get all the points within that polygon looks like this:

SELECT *  from mytable where GISWithin(LngLatPoint, GeomFromText('Polygon(( -89 36, -90 35,-84 35, -81 36, -89 36))'));

If you have done everything successfully this query will get all the Lng Lat points that you have in your table that fall within Tennessee. Pretty cool huh!

Thanks to Gert Serneels for the GISWithin function found here.

Creating a user session class in PHP

Managing user sessions in PHP applications should be made simple using PHP’s built in “session_start”. However after working with this function you might recognize it’s not the cleanest solution and may not scale well.

I recently built a simple user session class that I believe is a much cleaner/scalable solution. This class uses Memcache as storage engine as appose to flat files. This gives us the ability to manage user session across multiple servers. Using a database to store user sessions would also be a fine solutions. I would love some feedback from other developers on pro’s and con’s on this class.

A few things that I think make this such a good solutions for managing user sessions. First we use a the Singleton pattern so this class will only be instantiated once. Meaning for every php process we hit data storage once for retrieval. The second part of this is the __destruct which (called when the PHP process ends) saves our object to storage only once.

a few things to note:

this class is bare bones to show the main components. It would be ideal to put some other methods such as “authorizeUser”,”isUserAuthorized”, etc ….
as you can see this class has dependency on another class “Memcache\Connnection”. Ive attached that class so you could use it if you have a memcached server. If not you could create another datastorage class to use – it simply needs two methods : get($key), set($key,$value,$time_to_store).

<?php

include "Connection.php";

use Memcache\Connection;

class User
{

    protected static $instance; 

    protected $unique_key; 

    public $first_name;
    public $last_name;

    protected function __construct() { 
        $this->init_session();
    } 
    
     
    /*
     * we save this user session when this php process is finishing
     */ 
    function  __destruct(){
        $this->save(); 
    }


    private function __clone(){ } 


    public static function getInstance() 
    { 
        if (!self::$instance) 
        { 
            //see if we have a user session for this user,
            //if so we return that if not we construct
            if(is_object($user=self::getUserSession())){
                return  $user;
            }else{
                return new User(); 
            }
        }

        return self::$instance; 
    } 



    /*
     * fetches user function from memcache this is a serialized object so we 
     * just need to unserialze and should be godo
     * 
     * returns the user object or false if it does not exhist
     * 
     */ 
    protected static function getUserSession()
    {
        $user_session=$_COOKIE['user_sess'];
    
        if(!$user_session){
            return false;
        }

        $cache = Connection::getInstance();
        return $cache->get($user_session);  
        
    }
   
    /* 
     * 
     * if user does not have a session we need to give them a session cookie
     * and set the memcached object
     *  i will hash a unique time value (consider security implications)
     *
     *  returns true on success false otherwise
     *
     */ 
    protected function init_session() {
        $this->unique_key=uniqid("session_cookie");
        $this->unique_key=hash("md5",$this->unique_key);
        
        setcookie("user_sess",$this->unique_key,time()+(60*60)); 


        $cache = Connection::getInstance();
        return $cache->set($this->unique_key,$this,60*60);  

    
    }

    /*
     *
     * saves current object memache, can be called everytime something in here is set
     * should consider way to only call this when we wrapping up script
     */
    protected function save(){
        $cache = Connection::getInstance();
        return $cache->set($this->unique_key,$this,60*60);  
    } 
    

    
}

Database Connection Class – Singleton

Everyone needs a db connection class – here is mine. Feel free to use, or provide feedback.

<?php

namespace Database;

class Connection
{
    
    protected static $hostname = "host";
    protected static $username = "username";
    protected static $password = "pass@"; 
    protected static $db_name  = "db_name"; 
  
    protected static $conn;
    protected static $instance; 

    protected function __construct($db_name=null) { 
         
        self::$conn = mysql_connect(self::$hostname, self::$username, self::$password);
        
        if (!self::$conn)
        { 
            die('Critical Stop Error: Database Error<br />' . mysql_error());
        }
        //use default db or overide with db_name
        if($db_name==null)
        {
               $db_name=self::$db_name;
        }
      $selected_db =  mysql_select_db($db_name, self::$conn);
        if(!$selected_db){
            die('Cant select database');
        }   
    } 


    //no cloning aloud 
    private function __clone()
    {
    } 

    public static function getInstance($db_name=null) 
    { 
        if (!self::$instance) 
        { 
        self::$instance = new Connection($db_name); 
        } 

        return self::$instance; 
    } 
    
    /*
     * $param string $sql
     * @param bool, $return_last_insert_id
     * return:
     * see php mysql_query,
     * note: if $return_last_insert_id is true - will return autoincrement if it exhists, 0 if it does not, false if query didnt work
     *
     */
    public function query($sql, $return_last_insert_id = false)
    {
        $query = mysql_query($sql, self::$conn) OR die("Error: could not run query:".mysql_error());
        
        if($return_last_insert_id){
                return  mysql_insert_id(); 
        }

        return $query;
      
    }

    public function fetchRow($sql)
    {
        $query = mysql_query($sql, self::$conn) OR die("Error: could not run query:".mysql_error());
        
        $row = mysql_fetch_row($query);
        return $row;
    }

    
     
    public function fetchAssoc($sql)
    {
        $result = mysql_query($sql, self::$conn) OR die("Error: could not run query:".mysql_error());
        
        $all_results = array();
        while($row=mysql_fetch_assoc($result))
        {
            $all_results[]=$row;
        
        } 
        return $all_results;
    }

   

}

?>

Base Model Class for simple ORM

Here is a simple ORM class that I like to use in smaller projects.


<?php
    
namespace Model\Base;
use Database\Connection;

abstract class Basemodel
{
    /*
     * insert row into db,
     * inserts any properties of the object that are set as columns
     * note: does not insert any auto increment fields
     * note: sets the object to the values inserted 
     */ 
    public function insert()
    {
        $connection = Connection::getInstance();        
        $insert_columns='';
        $insert_values='';
            
        foreach($this->columns as $column=>$properties){
            if(isset($this->$column) && @!$properties['auto_increment']){
                $insert_columns .= "`".$column. "`,";
                $insert_values .= "'".$this->$column. "',";
            }
        }    
        
          //remore trailing (,)
         $insert_columns=substr($insert_columns,0,-1);
         $insert_values=substr($insert_values,0,-1);

        $sql= "INSERT INTO `".$this->table_name."` (".$insert_columns. ")  VALUES  (".$insert_values.") ";

        $last_insert_id=$connection->query($sql,true);

        //so if this table has an auto increment field we need to set it
        //note: most tables do and usually they are primary key 
        foreach($this->columns as $column=>$properties){
            if(@$properties['auto_increment']){
                    $this->$column= $last_insert_id;
            }
        }    

       return true; 

    
    }

    /*
     * fetches by primary key,
     * @return true - on success | false - on failure
     * sets the propperties of this object 
     *
     * note: does not support multiple column primary keys
     *
     */  
    public function fetchRow($primary_key_id){
         $query= "Select * from `".$this->table_name."` where ".$this->primary_key." = ".$primary_key_id ." limit 1";


         $connection = Connection::getInstance();        
         $results=$connection->fetchAssoc($query);

         if(!$results){
            return false; 
         }
            
         foreach($results[0] as $key=>$val){
            $this->$key=$val;
         } 

             return true;

    }
    public function fetchAssoc($query){
         $connection = Connection::getInstance();        
         $results=$connection->fetchAssoc($query);
            return $results;
    }
        
    /*
     * fetches Row where something is the case 
     * set params on myself
     */  
    public function fetchRowWhere($where){
         $query= "Select * from `".$this->table_name."` where ".$where." limit 1";

         $connection = Connection::getInstance();        
         $results=$connection->fetchAssoc($query);

         if(!$results){
            return false; 
         }
            
         foreach($results[0] as $key=>$val){
            $this->$key=$val;
         } 

             return true;

    }


   
    /*
     * dynamicly set properties
     * open to setting anything?? - could cause saving issues?? 
     * this magic method so can dynamicly set properties
     */
    public function __set($key,$value){
        $this->$key= $value; 
    }

    /*
     * this updates a row, clients should just call save
     */   
    public function update(){
        
        //so here we get the primary key name
        $pkey_name=$this->primary_key;

        //if its not set that means this is new row, should not be updating but insterting, throw errror 
        if(!isset($this->$pkey_name)){
            throw new Exception("Cannot update a row w/o primary key"); 

        }

        
        $update_sets=''; 

        foreach($this->columns as $column=>$properties){
            if(isset($this->$column) && !@$properties['auto_increment']){
                $update_sets.="`".$column. "`= '".$this->$column."',";

            }
        }    
        
        $update_sets=substr($update_sets,0,-1);

        $where_clause= "`".$this->primary_key."` = ".$this->$pkey_name;

        $sql= "Update `".$this->table_name."` SET  ".$update_sets. "  WHERE  ".$where_clause;


        $connection = Connection::getInstance();        
        $connection->query($sql);

       return true; 

    }


    /*
     * inserts or updates a row depending on if the primary key isset
     * return true on success false on failure
     */ 
    public function save(){
        $pkey_name=$this->primary_key;
        if(isset($this->$pkey_name)){
            return  $this->update(); 
        }else{
            return $this->insert();
        }   

    }
}

Memcache Connection Class

<?php

namespace Memcache;

class Connection
{
    
  protected  $servers = array('localhost');
    
  public static  $instance; 
 
  protected static  $memcache;
    
  private function __construct()
  {
    if(empty($this->servers))
    {
        throw new exception("No memcache servers to connect to");
    }  
    
    //actually using memcached
    self::$memcache= new \Memcached(); 
    
    foreach($this->servers as $server)
    {
        self::$memcache->addServer($server,'11211');
    }
  
  }

   
  public static function getInstance()
    {
    
        if(self::$instance === null)
        {
          self::$instance = new Connection();
        } 
        return self::$instance;
    }



  public function get($key)
  {
    return self::$memcache->get($key);
  
  }

  public function set($key,$var,$expire =0)
  {
        return self::$memcache->set($key,$var,$expire =0);    
  }
 
  public function add($key,$var,$expire =0)
  {
        return self::$memcache->add($key,$var,$expire =0);    
  }

}

SEO TIPS – Getting an accurate keyword monthly search count

I recently ran a seo campaign on a particular keyword that Google Adwords told me it was getting 14 million monthly global searches.  So when I got on the second page in the search results for the keyword I was pretty happy until I checked my traffic and was only getting a few visitors trickling in.  Stupid Adwords  was off by 13,999,500 visitors.

TO GET REAL MONTHLY SEARCHES NUMBERS DO THIS

Create a google adwords campain  only using the one keyword you want to get information on. Be sure to put brackets around it so it must be an exact match. EX [my search keyword]

Set your cost per click really high, so google will be sure to show your add when someone searches the keyword. Run the campaign for a day or to then look at how many impressions you got per day. Extrapolating that to a month can give you a fairly accurate results on how many searches that keyword is getting.

You also might want to make your add not very relevant to the keyword so people don’t click on it and cost you money.

for example if i was targeting the keyword “high heel shoes” 

An add like this would work:

Mexican Jumping Beans
Your like beans don’t you?
youre a dirty little jumping bean
www.jumpingbeans.com

 

note: this technique is more accurate for keywords that are not getting massive amounts of monthly searches.

Absolute vs Relative Truth – The Big Puzzle

I had a conversation with a friend about absolute vs relative truth, not much came of it, but a few nights ago I could not sleep and ended just thinking about it most the night. This is generally the type of stuff I keep to myself,  however I decided to share.


I’ve noticed that my stance or thinking on a philosophical or any topic is derived from first thinking of a situation then applying that situation to what I consider a thought framework or stance on a topic. Often I draw a conclusion, based on situations without registering that I did so.

For example:
How do you think about relative vs absolute truth in the context of this situation?
“What is 2 + 2? “

What about this situation ?
“That is a beautiful girl”

What about this situation ?
“Old hag or young lady?”
http://www.moillusions.com/2006/05/young-lady-or-old-hag.html

The last example is where I initially formed my bases for understanding absolute vs relative truths. It shows a picture in which some see an young lady and some see an old hag. Class rooms have been shown this picture and argue over what she is, after some debate the picture is explained to them and they see the other side of the women in the picture.

Applying this situation to my thinking I thought: well each person has a relative view of the picture, but when they see the other side they are enlightened and understand the picture is an optical illusion. Knowing that the picture is an optical illusion and having seen both sides they now know the absolute truth about the picture.

My initial conclusion was then that:

There seems to be both an absolute truth and a relative truths.

This conclusion rubbed me the wrong way, as this “absolute truth” seemed a little to easy to come by . So I thought about another situation.

What if three men look at the sky two men say the sky is blue and one man who was born color blind says that the sky is  grey. One day one of the  ”blue sky” men goes color blind. He has now seen the sky in both a blue and grey, and has a richer understanding of the sky color than the two other  men.

This thought drew me to a bit different conclusion about absolute and relative truths; as the man who had seen the sky as both grey and blue still does not have the absolute truth about the sky color, he has not seen the sky through the eyes of a bat, or through the eyes of man who can only see some colors.

My new conclusion was that:

There is an absolute truth that is unobtainable by humans, so we think in relative truths that have a nearness or farness from the absolute truth.

I liked this framework of thought, but as I piped various situations through it , the framework seemed to fail or at least be greatly lacking. The one situation that it really seemed to lack to explain the most was the most trivial in nature.

“Was it Owen Wilson or Luke Wilson that starred in the movie Wedding Crashers?”

I say Owen, my moron friend says Luke. There seemed to be such an absolute correctness here, no relative proximity to the correctness,  there is no spinning this one, this was entirely and absolutely true – “Owen Wilson starred in Wedding Crashers.”

Thinking about this expanded my understanding greatly. I found that in most of the previous situations I was thinking about there was generally an entirely different interpretation of a truth, a different paradigm in which people viewed a situation that led them to draw different yet relatively true conclusions. The paradigm that the color blind man lives in leads him to believe the sky is grey, the paradigm in which the man who can see color lives in leads him to believe the sky is blue.

In the Wedding Crashers situation my friend and I are within the exact same paradigm of thought – we did not interpret anything in reguards to the sitution differently. We both had the same interpretation of what a “Star” in the movie was, we both speak English,  we both were speaking about the same movie – ultimately our interpretation was the same.

This understanding began to lead me to understand how utterly far away we are from the “absolute truth”. The only reason we were able to have this argument is because we have some framework or paradigm of thinking. The understanding of this human paradigm and our complete depravity of knowledge was I think my biggest leap in understanding of all.

From that understanding I reworked my model of truths to that of a puzzle, a very big puzzle. A completed puzzle would be absolute truth.

As we live our life, we put down pieces on our puzzle that give us a better view of the absolute truth. The curious thing about a puzzle is that until it is one hundred percent complete we do not know what it is, and can only make statements as to what we see on the puzzle. Each of our puzzles looks very different, yet humans have all done some work on the bottom right corner of the puzzle.

So when a little child sees Santa Clause on the puzzle, that child says look it’s Santa, after putting a few more pieces down that child sees a different picture of Santa as being a fun character we created for a Christmas.

A boy who sees a girl and says she is beautiful, gets to know her a little (putting a few more pieces down on his puzzle) and says she is ugly. Then later gets stuck in a class with her (puts a few more pieces down on his puzzle) and thinks she the most beautiful thing in the world and marries her.

A religious man who says there is a God looks at his puzzle and sees a God. An atheist looks at his puzzle and says there is no God. That same atheist has a child and his puzzle changes drastically now he sees a God. The religious man discovers new information that puts more pieces on his puzzle and now the picture of God he saw turns into “Opium of the people”.

A man argues with a friend about who is the star of the movie, both men see a very similar puzzle. One man put a few pieces down on his puzzle that said Luke Wilson stared in Wedding Crashers because he looked at IMDB the other put pieces on his puzzle when folks said “look it’s Owen Wilson in that movie”. Both men’s puzzles grew after discussing the issue. We both now understood that for a brief period IMDB had put that Luke Wilson had stared in wedding crashers. Our puzzles became aligned, but still grossly incomplete. We did not know that on the day IMDB made this mistake – Jennifer a part time employee who had put the information up had done so on purpose after flirting with a boy in a class she was taking by claiming it was indeed Luke not Owen who stared in weeding crashers – knowing full well it was Owen. We did not know the that the boy thinking this prank was so clever began to see something in the girl he had missed before and ultimately grew to love and marry her.


Ultimately the thinking of truths as a puzzle will be greatly lacking, truth in this world is not a puzzle – it is what it is.  Thinking about it this way however provides me with a better framework of thought than I have ever used in the passed.

Initially I thought that as individuals our goal is to expand our puzzle as best we can, continually seeking to get a better picture of the whole puzzle. Now I’m not so sure, I believe we choose most of the puzzle pieces we put down. Some pieces could show us a picture that could ruin our lives, other pieces may gives us the most wonderful lives possible. It seems we can not complete the puzzle, so the best option then would seem to be to try our best to put down those pieces that form a beautiful picture, even if they are not the whole picture.

I am however missing something about humans or myself, because I am adamantly determined to expand my puzzle no matter the impact to the well being of my life.  And I believe that as humans we have in the past and will continue to expand our puzzle no matter the consequence. So that one day we will be able to answer the question “Where did we come from?”  as we now answer the question “What is 2 +2?” . 

See my other rantings including Sam Harris, David Pizarro , Tamler Sommers – very bad wizards.

Reverse Engineer Database With Doctrine & Symfony

My beef with symfony/Doctrine combo is it’s tuff to make changes to your database and the update your Entities. To appease my anger I wrote little PHP script that lets you make changes to your database and then update your entities. Basically this just reverse engineers your database as explained here – now you can avoid having to follow those instruction and just run this php code after making a database change.

NOTE: You will need to change the first three variables to fit your application.

 <?php
 $path="/home/webhostingweaver_remake/src/Ecom/ModelBundle/Resources/config/doctrine"; //change me
 $bundle= "Ecom"; //change me
 $namespace="Ecom\\ModelBundle\\Entity\\"; //change me
 
 
 $command="php app/console doctrine:mapping:convert xml $path --from-database --force";
 
 system($command);
 
 $dir = new DirectoryIterator($path);
 foreach ($dir as $fileinfo) {
     if (!$fileinfo->isDot()) {
         $file=$path."/".$fileinfo->getFilename();
         $contents=file_get_contents($file);
         $contents= str_replace("entity name=\"","entity name=\"".$namespace,$contents);
         file_put_contents($file,$contents);
     }
 }
 
 //generate entities
 system("php app/console doctrine:generate:entities $bundle"); 
 
 
 ?>

Displaying code in html

Displaying various types of code on an HTML web page is always an annoying problem, especially when you are using a editor of some sort to create the post/page on the backend. My solutions (which I think is the best) is to store the code in regular html bad characters and all (“<”, “>”). And then any code that your write inside a post/page put inside the “


” tags. Then when outputting your content use this little handy function that run’s PHP’s htmlspecialchars only on what’s inside your “

” tags:

function html_encode_pre_tags($content){                                                                             
 $line = preg_replace_callback(                                                                                       
         '/<pre>(.*?)<\/pre>/s',                                                                                      
         function ($matches) {                                                                                        
             return "<pre>".htmlspecialchars($matches[1])."</ pre>;";                                                   
         },                                                                                                           
         $content                                                                                                     
     );                                                                                                               
 return $line;                                                                                                        
 } 

Setting up SSH keys

1. Generate pub/private key one client in ~/.ssh/ : ssh-keygen -t rsa
2. Append public key on client to servers authorized key file: ~/.ssh/authorized_keys
3. On client add private key to keychain: ssh-add -K ~/.ssh/privateKey.txt

note: on ubuntu to add key to ~/.ssh/config:

IdentityFile ~/.ssh/keyfile.txt