exploring point data in postgis

points.sql

/*

in a terminal enter a database with psql that is postgis enabled

 $ psql <database name>

now you should be in the psql interpreter (aka prompt)
here you can run sql queries interactively and explore the results
*/

-- create a table
CREATE TABLE test(gid serial PRIMARY KEY, geom geometry);

-- check out the metadata for the table
\d test

-- add some points to it
INSERT INTO test(geom) values (ST_MakePoint(0,0));
INSERT INTO test(geom) values (ST_MakePoint(1,1));
INSERT INTO test(geom) values (ST_MakePoint(2,2));

-- count the number of rows (aka features)
select count(*) from test;

-- ask postgis about the geometry type of each row
select ST_GeometryType(geom) from test;

-- query the geometries and display the geometry of each row in the Well Known Text format
select ST_AsText(geom) from test;

-- aggregate all rows into a single row + multipoint
select ST_AsText(ST_Collect(geom)) from test;

-- now create another table of points with some attributes
CREATE TABLE test2(gid serial PRIMARY KEY, geom geometry, type varchar);
INSERT INTO test2(geom,type) values (ST_MakePoint(0,0), 'oak');
INSERT INTO test2(geom,type) values (ST_MakePoint(1,1), 'redwood');
INSERT INTO test2(geom,type) values (ST_MakePoint(2,2), 'redwood');
INSERT INTO test2(geom,type) values (ST_MakePoint(3,3), 'redwood');
INSERT INTO test2(geom,type) values (ST_MakePoint(4,4), 'madrona');
INSERT INTO test2(geom,type) values (ST_MakePoint(5,5), 'madrona');
INSERT INTO test2(geom,type) values (ST_MakePoint(6,6), 'madrona');
INSERT INTO test2(geom,type) values (ST_MakePoint(7,7), 'madrona');

-- try aggregating all rows into a single row + multipoint but you'll hit an error!
select ST_AsText(ST_Collect(geom)),type from test2;
-- will get an error like 'ERROR:  column "test2.type" must appear in the GROUP BY clause or be used in an aggregate function'
-- this is because collapsing the "type" column into a single valye is not possible. They are different trees!
-- So we can either drop the attributes or, if we still want to be able to keep them but benefit from the multipoint efficiencies we can group by:

select ST_AsText(ST_Collect(geom)),type from test2 group by type;

-- The above "group by" does the magic to collapse as many similiar rows as possible into groups of similiar trees:

/*
st_astext          |  type   
-----------------------------+---------
 MULTIPOINT(0 0)             | oak
 MULTIPOINT(4 4,5 5,6 6,7 7) | madrona
 MULTIPOINT(1 1,2 2,3 3)     | redwood
(3 rows)
*/