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>
';
}
?>
