[postgis-users] Loading spatial point from text file

[Kevin Neufeld]

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 list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20101206/04daa7b8/attachment.html>