I was trying to insert a polygon (I think it's the same as a bbox but not sure yet) into a postgis table. But I only found scaterred examples where the columns declaration was missing... So here's a concret example.
The SQL:
Where '4326' is the standard/normal/usual coordinnates system: longitude/latitude. You can do the following to get more info about '4326':
To check if everything is working you can do:
It should return 't' for 'true'.
The SQL:
SELECT AddGeometryColumn( 'my_table', 'geo_bbox', 4326, 'POLYGON', 2);
UPDATE my_table
SET geo_bbox=GeomFromText('POLYGON((0 0,4 0,4 4,0 4,0 0))',4326)
WHERE id='my_pk';
Where '4326' is the standard/normal/usual coordinnates system: longitude/latitude. You can do the following to get more info about '4326':
select * from spatial_ref_sys where SRID=4326;
To check if everything is working you can do:
SELECT ST_Contains(geo_bbox, GeomFromText('POINT(2 2)',4326))
FROM my_table
WHERE clef='my_pk';
It should return 't' for 'true'.
Errors I've bumped into and their "gotchas":
- parse error - invalid geometry
UPDATE my_table
SET geo_bbox=GeomFromText('POLYGON(0 0,4 0,4 4,0 4,0 0)',4326)
WHERE clef='my_pk';
ERROR: parse error - invalid geometry
CONTEXT: SQL function "geomfromtext" statement 1
You forget the double '((' and '))'. There is a double '((' because Polygon can be describe like this: POLYGON((first_shape), (second_shape)) - geometry contains non-closed rings
UPDATE my_table
You forget the last coordinates, you need to repeate the first point twice:
SET geo_bbox=GeomFromText('POLYGON((0 0,4 0,4 4,0 4))',4326)
WHERE clef='my_pk';
ERROR: geometry contains non-closed rings
CONTEXT: SQL function "geomfromtext" statement 1
POLYGON((down_left, down_right, up_right, up_left, down_left)) - geometry contains non-closed rings
UPDATE my_table
That's because the finishing point (0,10) is not the same as the starting point (0,0)
SET geo_bbox=GeomFromText('POLYGON((0 0,4 0,4 4,0 4,0 10))',4326)
WHERE clef='my_pk';
ERROR: geometry contains non-closed rings
CONTEXT: SQL function "geomfromtext" statement 1 - other: invalid geometry
I also got an other way to get an "invalid geometry" but I don't remember how to reproduce it... postgis does some validation if your coordinates are not valid.
Postgis official documentation: Creating object syntaxe
