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:

2 Dec 2008

ohloh la!

I just discovered ohloh.net after reading Marting Fowler latest post.

Ohloh display informations about open source projects: commits, timelines, contributors, languages etc...

Amazing how they managed to extract valuable informations for internet/svn/projects and how they presented it in a meaningful way!

I had lots of fun finding people I know:

Or project I've worked with:

This can help see if a project is under active development or not and who is really working on, not just claiming to be a contributor ;)