random psql commands

random psql commands #PostGIS

--connect to "specific_database"
psql -d specific_database;

--alter table, change name of table and move to different schema
alter table schema_name.table_name
rename to new_table_name
set schema name_of_new_schema;

--end a psq command
\q

--select all from "schema.table", limit, order
select * from schema_name.table_name
order by column_name;
limit 10;

--update attributes of one feature based on another if they share the same spatial location
UPDATE table1 SET some_field1 = (SELECT "some_field2" FROM table2 WHERE ST_Within(table1.the_geom,table2.the_geom));

--select by geographic location
SELECT location,owner FROM lighting, atlasgrid WHERE ST_Within(lighting.the_geom,atlasgrid.the_geom);

--simple query
SELECT * FROM atlasgrid AS selection WHERE grid_id = 'B2'

--location + simple query
SELECT * FROM lighting, atlasgrid WHERE atlasgrid.grid_id ='B2' AND ST_Within(lighting.the_geom,atlasgrid.the_geom) order by pge_id;

--create a table based on a query
CREATE TABLE gridb2 AS SELECT * FROM atlasgrid WHERE grid_id = 'B2';

--create a table based on a spatial query
--the result of this in a table, but not a spatial table (it lacks the_geom column)
CREATE TABLE lightsinb2 AS SELECT location,owner FROM lighting, atlasgrid WHERE atlasgrid.grid_id ='B2' AND ST_Within(lighting.the_geom,atlasgrid.the_geom) order by pge_id;

--create a spatial table based on a spatial query
--the result of this in a spatial table (contains the_geom column).  since both the lighting and atlasgrid tables have a column called 'the_geom' it is necessary to specify which one you want (e.g. lighting.the_geom)
CREATE TABLE lightsinb2b AS SELECT location,owner,lighting.the_geom FROM lighting, atlasgrid WHERE atlasgrid.grid_id ='B2' AND ST_Within(lighting.the_geom,atlasgrid.the_geom) order by pge_id;

--ST_Union as a method for increasing procss speeds
--not exactly sure how to use yet
SELECT ST_Union(the_geom) FROM atlasgrid;

--always run vacuum analyze after bulk insert
VACUUM ANALYZE VERBOSE lighting;

--more complex spatial query
--see challenge example at:
--http://www.gistutor.com/postgresqlpostgis/10-intermediate-postgresqlpostgis-tutorials/75-understanding-postgis-spatial-queries.html
SELECT ST_Union(ST_Intersection(ST_Buffer(r.the_geom,50000),s.the_geom)) 
FROM usa_rivers as r, usa_states as s 
WHERE ST_Intersects(r.the_geom, s.the_geom) AND s.region = 'West' 
GROUP BY state