Re: Constraint: string length must be 32 chars
| От | Alexander Farber |
|---|---|
| Тема | Re: Constraint: string length must be 32 chars |
| Дата | |
| Msg-id | AANLkTinCE2HNd+FnVEcbbpw1p8hLN3iSb_xewRt3QDFJ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Constraint: string length must be 32 chars (Harald Fuchs <hari.fuchs@gmail.com>) |
| Ответы |
Re: Constraint: string length must be 32 chars
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| Список | pgsql-general |
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> '; } ?>
В списке pgsql-general по дате отправления: