Re: Constraint: string length must be 32 chars

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Constraint: string length must be 32 chars
Date: 2010-10-17 14:01:52
Message-ID: AANLkTinCE2HNd+FnVEcbbpw1p8hLN3iSb_xewRt3QDFJ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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>
';

}

?>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2010-10-17 14:33:38 Re: Constraint: string length must be 32 chars
Previous Message Mike Christensen 2010-10-17 13:32:31 Need some help setting up pgAgent