[postgis-users] insert into a geometry field from 4gl

[Fay Du]

Emily:
Thank you very much. It works. For my data, load from file takes 1 min,
update takes 1 min. When I used cursor to insert one by one, it takes 7
minues.
Fay

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Emily Gouge
Sent: Monday, February 27, 2006 2:56 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] insert into a geometry field from 4gl


 > I have a new problem related the previous problem. I found the
 > performance of insert into database by using cursor is not good.
If you wrap the queries around a transaction block ("begin" and "commit"
statements) the performance 
may improve.

As an answer to your question, you could try loading the geometry into a
text field and then 
converting it to a geometry using a single query in the database:

1.  LOAD FROM input_file DELIMITER "|" INSERT INTO gps_log (uid,
geom_text)
with a file that looks like:
10ABC|POINT(1870391 320462)

2.  run a single update command in the database:
update gps_log set geom_pt = geomfromtext(geom_text, -1);

Emily


Fay Du wrote:
> Hi All:
> I have a new problem related the previous problem. I found the
> performance of insert into database by using cursor is not good. I
want
> to create a pipe delimited text file than using Load from command to
do
> it.
> 
> LOAD FROM input_file DELIMITER "|" INSERT INTO gps_log (uid, Geom_pt)
> 
> 
> The text file is like this:
> 10ABC|geomfromtext('POINT(1870391 320462)',-1)
> 
> I got an error, because geom._pt is a geometry field.
> 
> Can I use text file to load geometry field directly? I really want to
do
> this. Load data from text file into my table (including the time to
> generate pipe delimited text file) is 6 times faster than loading data
> by using cursor.
> 
> Many, many thanks for any help.
> Fay
> 
> 
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> Markus Schaber
> Sent: Thursday, February 23, 2006 3:20 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] insert into a geometry field from 4gl
> 
> Hi, Fay,
> 
> Fay Du wrote:
> 
>> INSERT INTO gps_log (uid, geom_pt) values ('test1',
>> geomfromtext('POINT(1870391 320462)',-1));
> 
> This works fine.
>> LET sql_stmt = "INSERT INTO gps_log  (uid,geom_pt) Values (?,?)"
>>    PREPARE insert_gps_tb FROM sql_stmt
>>
>> LET Uid="test"
>>
>> LET gps_point = "geomfromtext('POINT(1870391 320462)',-1)"
>>
>> EXECUTE insert_gps_tb USING uid, gps_point
> 
> This inserts the whole "geomfromtext('POINT(1870391 320462)',-1)" as
> textual geometry representation.
> 
> Try this:
> 
> LET sql_stmt =
>   "INSERT INTO gps_log  (uid,geom_pt) Values (?,geomFromText(?,-1))"
> PREPARE insert_gps_tb FROM sql_stmt
> LET Uid="test"
> LET gps_point = "'POINT(1870391 320462)'"
> EXECUTE insert_gps_tb USING uid, gps_point
> 
> HTH,
> Markus

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users