Skip to content

Instantly share code, notes, and snippets.

@pdbartsch
Last active February 16, 2017 15:03
Show Gist options
  • Save pdbartsch/4329690 to your computer and use it in GitHub Desktop.
Save pdbartsch/4329690 to your computer and use it in GitHub Desktop.
random psql commands
--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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment