Playing with PostGIS
I’m working on some stuff for work that involves integrating spatial data (both vector and raster), census microdata, and census tables. Neat stuff. PostgresSQL and PostGIS look like they’ll be extremely useful but I’ve just gotten started learning. I also had the opportunity to contribute a minor bugfix to PostGIS 2.0. Hopefully it won’t be the last one.
For someone coming from a traditional SQL background, the geometry functions take some getting used to. I’ve spent more time in MySQL than anything else, but I tend to think in the Transact-SQL dialect used by Sybase and MS SQL Server, particularly for join syntax. It’s pleasant that PostgreSQL supports a similar join syntax!
I wrote up some simple queries, to play with the spatial functions and to see if they behave the way I expect. I offer them here with no guarantee of usefulness, just a hope that they might prove to be a good starting point for the next adventurer.
This is using PostGIS 2.0-SVN as checked out from the svn repository on Jan 5th, 2012, but none of these functions should be PostGIS 2.0 specific. Once I start playing with the raster stuff, that may no longer be true.
# PostGIS notes: create table my_geom (id serial NOT NULL PRIMARY KEY, name varchar(20)); select AddGeometryColumn('public', 'my_geom', 'my_polygons', -1, 'POLYGON', 2); insert into my_geom(name, my_polygons) values ('square1', ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))')); insert into my_geom(name, my_polygons) values ('square2', ST_GeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))')); # approximate an approximation of a circle of radius 1, centered at 1,1 insert into my_geom(name, my_polygons) values ('circle1', ST_Buffer(ST_GeomFromText('POINT(1 1)'), 1)); # what did we just insert? select id, name, ST_AsText(my_polygons) from my_geom g1; # and are they valid? select name, ST_IsValid(my_polygons) from my_geom; # how big are they? select name, ST_Area(my_polygons), ST_Perimeter(my_polygons) from my_geom; # do square1 and square2 intersect? select ST_Intersects(g1.my_polygons, g2.my_polygons) from my_geom g1, my_geom g2 where g1.name = 'square1' and g2.name = 'square2'; # What's the intersection between the two squares? (should be the same as square1) select ST_AsText(ST_Intersection(g1.my_polygons, g2.my_polygons)) from my_geom g1, my_geom g2 where g1.name = 'square1' and g2.name = 'square2'; # Is it really the same as square1? select ST_Equals(g1.my_polygons, ST_Intersection(g1.my_polygons, g2.my_polygons)) from my_geom g1, my_geom g2 where g1.name = 'square1' and g2.name = 'square2'; # What's the symmetric difference? select ST_AsText(ST_SymDifference(g1.my_polygons, g2.my_polygons)) from my_geom g1, my_geom g2 where g1.name = 'square1' and g2.name = 'square2'; # What is the area of the difference (should be 3) select ST_Area(ST_SymDifference(g1.my_polygons, g2.my_polygons)) from my_geom g1, my_geom g2 where g1.name = 'square1' and g2.name = 'square2'; # What is the area of the difference between square1 and the other two shapes? select g2.name, ST_Area(ST_SymDifference(g1.my_polygons, g2.my_polygons)) from my_geom g1, my_geom g2 where g1.name = 'square1' and g2.name != 'square1'; drop table my_geom;
