<font color="#006600"><font size="2"><font face="courier new,monospace">Kevin,</font></font></font><div><font color="#006600"><font size="2"><font face="courier new,monospace"><br></font></font></font></div><div><font color="#006600"><font size="2"><font face="courier new,monospace">Thanks a lot. I did figure out that Rules doesn't work with "COPY FROM". And the triggers are much faster. Normally it takes like 20 min to insert the data and update the spatial points. With triggers it is reduced now to 7 mins. </font></font></font></div>
<div><font color="#006600"><font size="2"><font face="courier new,monospace"><br></font></font></font></div><div><font color="#006600"><font size="2"><font face="courier new,monospace">Thanks Again.</font></font></font></div>
<div><font color="#006600"><font size="2"><font face="courier new,monospace"><br></font></font></font></div><div><font color="#006600"><font size="2"><font face="courier new,monospace">-Sairam <br></font></font></font><br>
<div class="gmail_quote">On Mon, Dec 6, 2010 at 7:38 PM, Kevin Neufeld <span dir="ltr"><<a href="http://kneufeld.ca">kneufeld.ca</a>@<a href="http://gmail.com">gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div bgcolor="#ffffff" text="#000000">
So after some testing, it looks like the COPY command bypasses any
RULEs placed on the target table, so disregard my RULE suggestion.
I'm sure you can still use the TRIGGER suggestion, that's up to
you. Is it faster? Do a test and find out.<br>
<br>
Initial setup (create a 1,000,000 row sample csv file):<br>
<tt> create temp table sample as<br>
select <br>
(random()::double precision * 360) - 180 AS lon,<br>
(random()::double precision * 180) - 90 AS lat<br>
from generate_series(1, 1000000);<br>
<br>
copy sample TO E'D:\\Program
Files\\PostgreSQL\\9.0\\data\\sample.csv' CSV;<br>
</tt><br>
<br>
Trigger approach: (9906ms)<tt><br>
CREATE TABLE foo (lon double precision, lat double precision, pt
geometry);<br>
<br>
CREATE OR REPLACE FUNCTION foo_insert_tr() RETURNS TRIGGER AS <br>
$body$<br>
BEGIN<br>
NEW.pt := ST_SetSRID(ST_MakePoint(NEW.lon, NEW.lat), 4326);<br>
RETURN NEW;<br>
END;<br>
$body$ LANGUAGE plpgsql;<br>
<br>
CREATE TRIGGER foo_insert_tr BEFORE INSERT OR UPDATE ON foo<br>
FOR EACH ROW EXECUTE PROCEDURE foo_insert_tr();<br>
<br>
COPY foo (lon, lat) FROM E'D:\\Program
Files\\PostgreSQL\\9.0\\data\\sample.csv' CSV;</tt><tt><br>
-- 9656 ms</tt><br>
<tt> <br>
ANALYZE foo;</tt><tt><br>
-- 250ms</tt><br>
<br>
<br>
COPY / UPDATE approach: (27531 ms)<br>
<tt>CREATE TABLE foo (lon double precision, lat double precision, pt
geometry);<br>
<br>
COPY foo (lon, lat) FROM E'D:\\Program
Files\\PostgreSQL\\9.0\\data\\sample.csv' CSV;</tt><tt><br>
-- 3625 ms</tt><br>
<tt> <br>
-- An update is an expensive operation ... it's like a DELETE
followed by an INSERT <br>
-- and you still have to deal with the dead space (it'll make
your table twice as<br>
-- big physically)<br>
UPDATE foo SET pt = ST_SetSRID(ST_MakePoint(lon, lat), 4326);</tt><tt><br>
-- 18609 ms</tt><br>
<tt> <br>
VACUUM FULL ANALYZE foo;</tt><tt><br>
-- 5297 ms.</tt><br>
<br>
<br>
Cheers,<br><font color="#888888">
-- Kevin</font><div><div></div><div class="h5"><br>
<br>
<br>
On 12/6/2010 12:06 PM, Sairam Krishnamurthy wrote:
<blockquote type="cite"><font color="#006600"><font size="2"><font face="courier new,monospace">Hey Kevin,</font></font></font>
<div><font color="#006600"><font size="2"><font face="courier
new,monospace"><br>
</font></font></font></div>
<div><font color="#006600"><font size="2"><font face="courier
new,monospace">Thanks for your reply. Will this be faster
than updating the spatial points after loading the lat/lon
values? I will sure try this out in a while. But if this
is going to slower that that things wont be easier for me.
What is your opinion?</font></font></font></div>
<div><font color="#006600"><font size="2"><font face="courier
new,monospace"><br>
</font></font></font></div>
<div><font color="#006600"><font size="2"><font face="courier
new,monospace">Thanks,</font></font></font></div>
<div>
<font color="#006600"><font size="2"><font face="courier
new,monospace">Sairam<br>
</font></font></font><br>
<div class="gmail_quote">On Mon, Dec 6, 2010 at 12:00 PM, Kevin
Neufeld <span dir="ltr"><<a href="http://kneufeld.ca" target="_blank">kneufeld.ca</a>@<a href="http://gmail.com" target="_blank">gmail.com</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0pt 0pt 0pt 0.8ex;border-left:1px solid rgb(204, 204, 204);padding-left:1ex">
<div 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
<div>
<div><br>
<br>
<br>
On 12/6/2010 8:43 AM, Sairam Krishnamurthy wrote: </div>
</div>
<blockquote type="cite">
<div>
<div> <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 cols="72">Thanks,
Sairam Krishnamurthy
+1 612 859 8161
</pre>
</div>
</div>
<pre><fieldset></fieldset>
_______________________________________________
postgis-users mailing list
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<br>
</div>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br>
</blockquote>
</div>
<br>
</div>
<pre><fieldset></fieldset>
_______________________________________________
postgis-users mailing list
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</div></div></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>