3 Dec 2008

Insert Polygon into Postgis

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:

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 
    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
    You forget the last coordinates, you need to repeate the first point twice:
    POLYGON((down_left, down_right, up_right, up_left, down_left))
  • geometry contains non-closed rings
    UPDATE my_table 
    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
    That's because the finishing point (0,10) is not the same as the starting point (0,0)
  • 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

Technorati tags:

5 comments:

Anonymous said...

Hello this is Sébastien Vidon, remember ? Soltim's teammate during gastro epidemy in toulouse ... speak fluent english as I can but not as well as you do ... did not know toulouse turned english now ... good luck !

Benjamin Francisoud said...

Hi Sébastien Sure I remember ;)
If you want to keep in touch you can contact me if you want ;)
http://benjamin.francisoud.googlepages.com/

Aurélien said...

and i have another problem : http://forums.mysql.com/read.php?23,272683,272683#msg-272683
can you help me ?

Benjamin Francisoud said...

sorry I don't have mysql installed at the moment...
I took a look at your problem but I don't have a clue...

Aurélien said...

tanks you. i find my problem. i have to close the polygon by put the first point in last.
see you soon