Hey Kevin, 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?
Thanks, Sairam On Mon, Dec 6, 2010 at 12:00 PM, Kevin Neufeld <kneufeld...@gmail.com>wrote: > 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. > > 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: > - create a temp dummy table as the target for your COPY command > - place a RULE on the dummy table to redirects INSERTs into a table for all > your data > - run the COPY and drop the dummy table. > > i.e., this works. > > CREATE TABLE foo (pt geometry); > CREATE TEMP TABLE foo_tmp (long double precision, lat double precision); > > CREATE RULE foo_tmp_insert_rule AS > ON INSERT TO foo_tmp > DO INSTEAD > INSERT INTO foo (pt) VALUES (ST_MakePoint(NEW.long, NEW.lat)); > > -- You would use your COPY command here to insert records > INSERT INTO foo_tmp (long, lat) VALUES (-56.57647, -64.7647); > DROP TABLE foo_tmp CASCADE; > > SELECT ST_AsText(pt) FROM foo; > st_astext > --------------------------- > POINT(-56.57647 -64.7647) > (1 rows) > > > Cheers, > Kevin > > > > On 12/6/2010 8:43 AM, Sairam Krishnamurthy wrote: > > All, > > 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>. > > So I want to load the table from this text file. I use the COPY query > below. > > *COPY "table_name" (lat,lon,data1,data2) FROM 'FILE' DELIMITER ','* > > 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: > > *UPDATE "table_name" SET "spatialPoint" = > ST_SetSRID(ST_MakePoint(lat,lon),4326) WHERE "spatialPoint" IS NULL* > > My question is, is there a way to avoid the second query so that I can also > load the spatialPoint in the COPY query? > > 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. > > > > Thanks, > Sairam Krishnamurthy > +1 612 859 8161 > > > _______________________________________________ > postgis-users mailing > listpostgis-us...@postgis.refractions.nethttp://postgis.refractions.net/mailman/listinfo/postgis-users > > > > _______________________________________________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > >
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
