How to use ST_GeomFromText with PG::prepare? (Ruby)

Question

I am having trouble with the syntax relate to PG::prepare, ST_GeomFromText, and POINT.

This works:

sql = %Q%insert into #{table_name} (latlong, place_name) values (ST_GeomFromText('POINT(0 0)', 4326), $1 )%

conn.prepare('statement1', sql)

This does not work:

sql = %Q%insert into #{table_name} (latlong, place_name) values (ST_GeomFromText('POINT($1 $2)', 4326), $3 )%

conn.prepare('statement1', sql)

Error:

in `prepare': ERROR:  could not determine data type of parameter $1 (PG::IndeterminateDatatype)

Answer 1

Instead of this:

sql = %Q%insert into #{table_name} (latlong, place_name) values (ST_GeomFromText('POINT($1 $2)', 4326), $3 )%
conn.prepare('statement1', sql)

Try this:

sql = %Q%insert into #{table_name} (latlong, place_name) values (ST_GeomFromText($1, 4326))%
conn.prepare('statement1', sql)

The POINT(long lat) string is represented as a single value, $1 in this example.

CONN.exec_prepared( 'statement1', [ "POINT(long lat)", foobar ]

The whole issue occurs when PostGIS tries to parse your above statement, it tries to create a geometry with $1 $2 which simply doesn't work and gives you the error you are are now familiar with. Instead pass the WKT (well known text) POINT(long lat) in as a single value after your statement is prepared.