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