What is best way to store spatial or point data in database?

Hi, I worked with postgresql for a while and every time I define my spatial data as 'point' datatype to insert, get and use it easily and fast, for calculate distance and use other postgresql geo functions. Now I define point data like before, but when i want to get point data with eloquent or want to export my table that has point datatype column, show me a lot of incorrect values like this : :�m½�?@)��R.K@

But when i define latitude and longitude as decimal(11, 8) everything is fine and work properly. But which way is correct? which is best and optimized? I'm working on an enterprise project and it's important for me to write the best code.

lostdreamer_nl

7 years ago

Best Answer

Level 53

since mysql and postgresql both have spatial indexing, I'd go for geometry fields with a spatial index on it.

But as the DB will save it in a binary format, you'll have to do some other magic to get the text versions in your result after querying.

Something like this:

    protected $geometry = ['coordinates'];
    protected $geometryAsText = true;

    public function newQuery($excludeDeleted = true)
    {
        if (!empty($this->geometry) && $this->geometryAsText === true)
        {
            $raw = '';
            foreach ($this->geometry as $column)
            {
                $raw .= 'AsText(`' . $this->table . '`.`' . $column . '`) as `' . $column . '`, ';
            }
            $raw = substr($raw, 0, -2);

            return parent::newQuery($excludeDeleted)->addSelect('*', DB::raw($raw));
        }

2 likes

Please or to participate in this conversation.