We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

Mysql 5.7 - Geospatial columns and models with 'as' queries

Hi there,

How can we retrieve geom Point objects from a MySQL 5.7 table for our models? At the moment I just get a garbled string? In raw sql I would use something like 'select ASTEXT(location) as location' so I'm also unsure how we would use the 'as' in the SQL statement in models.

Do we have to use raw sql? If so do I need to populate the model objects automatically? Should I create a static function my object to do this? e.g static function findWithGeom()

Also with regards to inserts for geom data I've seen it written that we need to use DB\RawValue but that the docs also specifiy we shouldn't use this for user entered data to avoid sql injection attacks. If I'm allowing users to enter locations how can we get over this? It looks like I won't be able to use the save() method of a model but again have to use raw sql? Something like

insert into location(location) VALUES(Point(:lon, :lat)) and bind the values that way?

Has anyone got a full working example of this? I really want to keep my code tidy and not have raw sql/PHQL in my controllers to retrieve this items purely because they have geom data. With mobile apps and apis these days it strikes me as something people would want to do more of, so I would really appreciate a fuller example.

I tried using the column map to explicitly create lat/lon properties as below but it doesn't work even though it doesn't error

    public function columnMap()
    {
        // Keys are the real names in the table and
        // the values their names in the application
        return array(
            'id' => 'id',
            'location' => 'location',
            'X(location)'       => 'lon',
            'Y(location)' => 'lat'
        );
    }

thanks



79.0k

That is non standard SQL syntax/feature, i.e. too specific to a certain RDBMS (MySQL) and thus not supported by ORM which tries to be as supportive as possible in terms of different RDBMS out there.

Your best shot is to use https://docs.phalcon.io/en/latest/api/Phalcon_Db_Adapter_Pdo_Mysql.html


public query (unknown $sqlStatement, [unknown $bindParams], [unknown $bindTypes]) inherited from Phalcon\Db\Adapter\Pdo

Sends SQL statements to the database server returning the success state. Use this method only when the SQL statement sent to the server is returning rows


12.2k

Thanks, though the docs say to only use this method when returning rows. So should this not be used for insert? I guess my other concern is validating user input for the lat/lon especially as PHQL seems to want me to specifically bind a 'location' variable as that's the name of the field whereas I created placeholder for the lat and lon as part of a Point(:lon:, :lat:) in the actual SQL statement. As the actual field 'locaiton' didn't exist PHQL throws an error. At the moment I'm using a validator to validate the lat/lon values and then just creating a model similar to though.

$place = new Place(); $place->setLocation(DBRawValue($lon, $lat)...etc

$place->save()

This seems to work this way. But with PHQL I can't get it to work at all. I'm just wondering what the best approach is for the insert

thanks



79.0k
edited Jul '16

For inserts:

public execute (unknown $sqlStatement, [unknown $bindParams], [unknown $bindTypes]) inherited from Phalcon\Db\Adapter\Pdo

Sends SQL statements to the database server returning the success state. Use this method only when the SQL statement sent to the server doesn’t return any rows


79.0k

You can also build your Geospatial insert query with:

public boolean insertAsDict (string $table, array $data, [array $dataTypes]) inherited from Phalcon\Db\Adapter

Inserts data into a table using custom RBDM SQL syntax