CODEWITHSUNDEEP
sqlspatialpostgispostgresql
Attila O.
Attila O.

Reputation: 16615

Write raw SQL for PostGIS

I'm trying to input some data into a PostgreSQL 8.4 database with a PostGIS template. I'm unable to UPDATE polygons:

> UPDATE my_table SET coords = POINT(1, 1)
UPDATE 0 1

> UPDATE my_table SET box = POLYGON(((1, 1), (2, 3), (3, 3), (1, 1)))
ERROR:  function polygon(record) does not exist

> UPDATE my_table SET box = POLYGON((1, 1), (2, 3), (3, 3), (1, 1))
ERROR:  function polygon(record, record, record, record) does not exist

> UPDATE my_table SET box = POLYGON(1, 1, 2, 3, 3, 3, 1, 1)
ERROR:  function polygon(numeric, numeric, numeric, numeric, numeric, numeric, numeric, numeric) does not exist

> UPDATE my_table SET box = ((1, 1), (2, 3), (3, 3), (1, 1))
ERROR:  column "box" is of type polygon but expression is of type record

How do I insert a polygon? Note that the data already exists in the table, with NULL fields in place of the spatial data. I need to UPDATE, not INSERT, but that shouldn't make a difference.

Upvotes: 0

Views: 956

Answers (2)

amercader
amercader

Reputation: 4540

You should use the Geometry constructors to load new geometries in your table, specifically the St_GeomFromText function:

UPDATE my_table SET box = ST_GeomFromText('POLYGON ((1 1), (2 3), (3 3), (1 1))');

The geometry is defined in the WKT (Well-Known Text) format.

Upvotes: 2

rfusca
rfusca

Reputation: 7705

Try:

UPDATE my_table SET box = '((1, 1), (2, 3), (3, 3), (1, 1))'::polygon;

To my knowledge, most geometric types in general need the quotes.

Upvotes: 1

Related Questions