How do I create an Accessor for a POINT data colum

Question

I've got a user_sessions table that has a column named "geo_location", it's a POINT column that stores the latitude and longitude values for a user's current location, or NULL if it's not available.

When I create a Model in Laravel that binds to that table it only works when the geo_location field is hidden completely. Otherwise it throws a JSON error because it's not properly querying for the separate X and Y values in the geo_location column.

Is there a way that I can create an Accessor in my Laravel Model that can manipulate the data before it gets displayed so that I can include it in my results?

Do I need to modify my UserSessions controller and add a get() function to just use raw SQL instead?

Answer 1

If you are using PostGreSQL + PostGIS, this is how I did it for L4.1

location is of type geometry(POINT), created using a raw sql query in the migration table

Table:

 DB::statement("ALTER TABLE places ADD COLUMN location GEOMETRY(POINT, 4326)");

Model:

 class Place extends Eloquent{
     //mass assignment of fillable field
     #fillable
     protected $fillable = array('name', 'attribute', 'location');
     // if you want to include your POINT data as a JSON attribute
     #append
     protected $append = array('location');
     // the DB:raw statement is particular to PostGreSQL + PostGIS, a native function of PostGIS
     // basically, find the id of the referred place
     // and run the PostGIS function that turns the geometry type to wkt text.
     #accessor
     public function getLocationAttribute(){
         $id =  $this->attributes['id'];
         $wkt = DB::table('places')->find( $id, array(DB::raw('ST_AsText(location) AS location')));
         $location = $wkt->location;
         return $location;
     }

 }

a sample output using REST looks like this:

{domain}/place/{1}

 {
  id: 1,
  name: "Yellowstone",
  created_at: "2014-05-19 08:19:51",
  updated_at: "2014-05-19 08:19:51",
  location: "POINT(121.1 14.4)"
 }

Note:

using a default accessor

  $this->attributes['location']

returns a Hexadecimal Object, rather than a string. so i opted to use a raw query with a PostGIS function.

 {
   id: 1,
   name: "Yellowstone",
   created_at: "2014-05-19 08:19:51",
   updated_at: "2014-05-19 08:19:51",
   location: "0101000020E61000006666666666465E40CDCCCCCCCCCC2C40"
 }

From the WKT, i hope you can easily return the longitude / latitude using native php scripts. :-)

I hope this gives you an idea on how to create the accessor.