[postgis-users] a couple of procedural questions for populating tables programmatically

[Mr. Puneet Kishor]

I have a couple of procedural questions. I am populating my table with point 
and cell geometries programmatically. First question: When I INSERT values 
other than the geometry in rows and then UPDATE the geometry in a second 
process, everything works --

   $sth = $dbh-prepare(qq{INSERT INTO base.cells (x, y) VALUES (?, ?)});

   for (@a) {
   $sth-execute($a[0], $a[b]);
   }

   $sth = $dbh-prepare(qq{
   UPDATE base.new_cells 
   SET 
   the_point = GeomFromText('POINT(' || x || ' ' || y || ')', 2163),
   the_cell  = GeomFromText(
   'POLYGON((' || 
   (x - $csmid) || ' ' || (y - $csmid) || ',' || 
   (x + $csmid) || ' ' || (y - $csmid) || ',' || 
   (x + $csmid) || ' ' || (y + $csmid) || ',' || 
   (x - $csmid) || ' ' || (y + $csmid) || ',' || 
   (x - $csmid) || ' ' || (y - $csmid) ||  '))', 
   2163
   )
   });
   $sth-execute;

However, if I try to do the above in one attempt like so --

   $sth = $dbh-prepare(qq{INSERT INTO base.cells (x, y, the_point, the_cell) 
VALUES (?, ?, ?, ?)});

   for (@a) {
   $sth-execute(
   $a[0], 
   $a[b], 
   GeomFromText('POINT(' || $a[0] || ' ' || $a[y] || ')', 2163),
   GeomFromText(
   'POLYGON((' || 
   (x - $csmid) || ' ' || (y - $csmid) || ',' || 
   (x + $csmid) || ' ' || (y - $csmid) || ',' || 
   (x + $csmid) || ' ' || (y + $csmid) || ',' || 
   (x - $csmid) || ' ' || (y + $csmid) || ',' || 
   (x - $csmid) || ' ' || (y - $csmid) ||  '))', 
   2163
   )
   );
   }

I get an error saying the method GeomFromText doesn't exist. Would like to 
understand the reason behind this.

Second question: I am doing the following to set the SRID (besides specifying 
it in the UPDATE step above).

   INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, 
f_geometry_column, coord_dimension, srid, type)
   VALUES ('', 'base', 'cells', 'the_point', 2, 2163, 'POINT');
   INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, 
f_geometry_column, coord_dimension, srid, type)
   VALUES ('', 'base', 'cells', 'the_cell', 2, 2163, 'POLYGON');

Is the above correct and sufficient?

Puneet.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] a couple of procedural questions for populating tables programmatically

[Mike Toews]

On 9 March 2011 03:07, Mr. Puneet Kishor punk.k...@gmail.com wrote:

 I have a couple of procedural questions. I am populating my table with point 
 and cell geometries programmatically. First question: When I INSERT values 
 other than the geometry in rows and then UPDATE the geometry in a second 
 process, everything works --

   $sth = $dbh-prepare(qq{INSERT INTO base.cells (x, y) VALUES (?, ?)});

   for (@a) {
       $sth-execute($a[0], $a[b]);
   }

   $sth = $dbh-prepare(qq{
       UPDATE base.new_cells
       SET
           the_point = GeomFromText('POINT(' || x || ' ' || y || ')', 2163),
           the_cell  = GeomFromText(
               'POLYGON((' ||
               (x - $csmid) || ' ' || (y - $csmid) || ',' ||
               (x + $csmid) || ' ' || (y - $csmid) || ',' ||
               (x + $csmid) || ' ' || (y + $csmid) || ',' ||
               (x - $csmid) || ' ' || (y + $csmid) || ',' ||
               (x - $csmid) || ' ' || (y - $csmid) ||  '))',
               2163
           )
   });
   $sth-execute;

 However, if I try to do the above in one attempt like so --

   $sth = $dbh-prepare(qq{INSERT INTO base.cells (x, y, the_point, the_cell) 
 VALUES (?, ?, ?, ?)});

   for (@a) {
       $sth-execute(
           $a[0],
           $a[b],
           GeomFromText('POINT(' || $a[0] || ' ' || $a[y] || ')', 2163),

If you want to avoid precision loss from double-text conversions, try
setting the_point constructor with:
ST_SetSRID(ST_MakePoint($a[0], $a[y]), 2163)
(or is $a[y] really $a[b]? Why is $a[0] not $a[x]? I'm not sure)

           GeomFromText(
               'POLYGON((' ||
               (x - $csmid) || ' ' || (y - $csmid) || ',' ||
               (x + $csmid) || ' ' || (y - $csmid) || ',' ||
               (x + $csmid) || ' ' || (y + $csmid) || ',' ||
               (x - $csmid) || ' ' || (y + $csmid) || ',' ||
               (x - $csmid) || ' ' || (y - $csmid) ||  '))',
               2163

There is easily a problem somewhere here. I'm not sure how exactly x,
y, and $csmid are used here, but they can't refer to the SQL columns
x or y yet, since this is an INSERT statement. A similar binary
method to above can be combined with ST_Expand:

ST_Expand(ST_SetSRID(ST_MakePoint($a[0], $a[y]), 2163), $csmid)


 I get an error saying the method GeomFromText doesn't exist. Would like to 
 understand the reason behind this.

Your full error message possibly says something like
ERROR:  function geomfromtext(unknown) does not exist
because the datatype your are passing to GeomFromText is not text, and
another function for any other datatype does not exist.

 Second question: I am doing the following to set the SRID (besides specifying 
 it in the UPDATE step above).

   INSERT INTO geometry_columns (f_table_catalog, f_table_schema, 
 f_table_name, f_geometry_column, coord_dimension, srid, type)
   VALUES ('', 'base', 'cells', 'the_point', 2, 2163, 'POINT');
   INSERT INTO geometry_columns (f_table_catalog, f_table_schema, 
 f_table_name, f_geometry_column, coord_dimension, srid, type)
   VALUES ('', 'base', 'cells', 'the_cell', 2, 2163, 'POLYGON');

 Is the above correct and sufficient?

Looks correct, but it only needs to be done once, when you make the
table/columns. Don't insert this after each UPDATE step above.

-Mike
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users