<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
You could create a BEFORE INSERT TRIGGER on your table that modifies
the records you are inserting by populating the spatial column using
the NEW.long and NEW.lat values.<br>
<br>
Alternatively, you mentioned that you didn't even want long/lat in
the database at all. In that case, as part of your loading script,
you could try to:<br>
- create a temp dummy table as the target for your COPY command<br>
- place a RULE on the dummy table to redirects INSERTs into a table
for all your data<br>
- run the COPY and drop the dummy table.<br>
<br>
i.e., this works.<br>
<br>
<tt>CREATE TABLE foo (pt geometry);<br>
CREATE TEMP TABLE foo_tmp (long double precision, lat double
precision);<br>
<br>
CREATE RULE foo_tmp_insert_rule AS <br>
ON INSERT TO foo_tmp<br>
DO INSTEAD<br>
INSERT INTO foo (pt) VALUES (ST_MakePoint(NEW.long, NEW.lat));<br>
<br>
-- You would use your COPY command here to insert records<br>
INSERT INTO foo_tmp (long, lat) VALUES (-56.57647, -64.7647);<br>
DROP TABLE foo_tmp CASCADE;<br>
<br>
SELECT ST_AsText(pt) FROM foo;<br>
st_astext<br>
---------------------------<br>
POINT(-56.57647 -64.7647)<br>
(1 rows)<br>
</tt><br>
<br>
Cheers,<br>
Kevin<br>
<br>
<br>
On 12/6/2010 8:43 AM, Sairam Krishnamurthy wrote:
<blockquote cite="mid:4CFD1296.3000707@gmail.com" type="cite">
<meta http-equiv="content-type" content="text/html;
charset=ISO-8859-1">
<font face="Courier New, Courier, monospace">All,<br>
<br>
I have a test file that contains the data to the loaded to the
spatial
table I have. The table structure is
<lat,lon,data1,data2,spatialPoint>. Sample data in the txt
file :
<-64.7647, -56.57647, 1234548, 1221312>. <br>
<br>
So I want to load the table from this text file. I use the COPY
query
below. <br>
<br>
<b>COPY "table_name" (lat,lon,data1,data2) FROM 'FILE' DELIMITER
','</b><br>
<br>
But the problem is I am not able to update the spatialPoint
using copy
query. So for now I am loading the lat,lon,data1,data2 fields
and then
update the spatialPoint using a separate query similar to one
below:<br>
<br>
<b>UPDATE "table_name" SET "spatialPoint" =
ST_SetSRID(ST_MakePoint(lat,lon),4326) WHERE "spatialPoint" IS
NULL</b><br>
<br>
My question is, is there a way to avoid the second query so that
I can
also load the spatialPoint in the COPY query?<br>
<br>
Also I initial dint want to have lat,lon in the table and have
only the
spatialPoint field. Because of the above problem I was forced to
have
lat,lon fields in the table. Someway to achieve this will help
me a lot.<br>
</font><br>
<br>
<pre class="moz-signature" cols="72">Thanks,
Sairam Krishnamurthy
+1 612 859 8161
</pre>
<pre wrap="">
<fieldset class="mimeAttachmentHeader"></fieldset>
_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<br>
</body>
</html>