Search Postgresql Archives

Re: Constraint: string length must be 32 chars

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello again,

I have 1 more question please:

how do you select the "x" and "y" parts of a point data type?

Can't find it in
http://www.postgresql.org/docs/8.4/interactive/functions-geometry.html

For example, I have this table with a "pos" column:

snake=> \d gps
                  Table "public.gps"
 Column |            Type             |   Modifiers
--------+-----------------------------+---------------
 id     | bytea                       |
 stamp  | timestamp without time zone | default now()
 pos    | point                       | not null
Check constraints:
    "gps_id_check" CHECK (length(id) = 16)

snake=> select encode(id,'hex') as id, pos from gps;
                id                |   pos
----------------------------------+----------
 0cc175b9c0f1b6a831c399e269772661 | (51,7)
 0cc175b9c0f1b6a831c399e269772661 | (51,7)
 92eb5ffee6ae2fec3ad71c777531578f | (51,7)
 92eb5ffee6ae2fec3ad71c777531578f | (51.3,7)
(4 rows)

but my problem is, that I'd prefer to fetch
the x part of pos as "lat" and y part as "lon",
so I can print them in my php script (source below).

Thank you
Alex

<?php

$id  = trim($_REQUEST['id']);
$lat = strtr(trim($_REQUEST['lat']), ',', '.');
$lon = strtr(trim($_REQUEST['lon']), ',', '.');

if (preg_match('/^[a-fA-F0-9]{32}$/', $id) &&
    preg_match('/^[+-]?[0-9.]+$/', $lat) &&
    preg_match('/^[+-]?[0-9.]+$/', $lon)) {

        try {
                $db = new PDO('pgsql:host=/tmp', 'user', 'password');
                $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

                $insert = $db->prepare("insert into gps (id, pos)
values (decode(?, 'hex'), point(?, ?))");
                $insert->execute($id, $lat, $lon);

                $select = $db->prepare("select encode(id, 'hex') as
id, stamp, pos from gps");
                $select->execute();

                header('Content-Type: text/xml; charset=utf-8');
                print '<?xml version="1.0"?><gps>';
                while ($row = $select->fetch(PDO::FETCH_ASSOC)) {
                        printf('<pos id="%s" lat="%f" lon="%f" />',
$row['id'], $row['lat'], $row['lon']);
                }
                print '</gps>';
        } catch (Exception $e) {
                print 'Database problem: ' . $e->getMessage();
        }

} else {
        header('Content-Type: text/html; charset=utf-8');
        print '<html>
<body>
<form method="post">
<p>Id: <input type="text" name="id" size=32 maxlength=32 /></p>
<p>Latitude: <input type="text" name="lat" /></p>
<p>Longitude: <input type="text" name="lon" /></p>
<p><input type="submit" value="Save" /></p>
</form>
</body>
</html>
';

}

?>

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux