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

[Mike Toews]

On 9 March 2011 03:07, Mr. Puneet Kishor <punk.kish at 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