Useful commands

  • make symlinks on Windows.  Paritcularly useful for tying in cloud storage.  Usually my "To Directory" is some easily accessible location in my documents or high up in the hiearchy on C or D.  From directory is usually cloud storage.  That way I do not need to worry about which cloud storage service I'm actually using.
    • mklink /j "To Directory" "From Directory"
  • as part of mac python uninstall, remove symlinks in usr/bin using grep, awk, and xargs
    • ls -lt | grep Python.framework/Versions/2.7 | awk '{print $9}' | sudo xargs rm
  • to discover/add all unversioned files within a working copy
    • svn add * --force
  • the commnand below seemed like proper syntax, but output seemed distorted ... perhaps it naiively reprojects without adjusting the datum?  Was doing this in prep for gdal2tiles, but that must have handled reprojection implicitly, because output was already adjusted??
    • gdalwarp Delaware State Plane to "Google" Spherical/Web Mercator

      • gdalwarp -s_srs "EPSG:26957" -t_srs "EPSG:900913" C:\temp\newark_imagery_2010.jp2 C:\temp\newark_imagery_2010-proj.jp2
  • PHP to a parent folder path
    • $file = $dirname($_SERVER['PATH_TRANSLATED']) . 'somefilename';
  • Postgres delete duplicates (keep one distinct)
    • use the hidden ctid tuple field
    • DELETE FROM master WHERE ctid NOT IN (SELECT MAX(output.ctid) FROM master AS output GROUP BY output.osm_id, output.osm_version)
  • Update table based on values from another (postgres)'
    • UPDATE to_table SET to_column = from_column from from_table where to_table.primary_key= from_table.foreign_key;
  • Example of the same in sqlite
    • UPDATE uri_downloads
    • SET uri_downloads.external_id  = 1 WHERE EXISTS (SELECT  * FROM downloads WHERE uri_downloads.external_id = downloads.name and downloads.class = 'facultystaff')
    • or
    • UPDATE pa_congress
    • SET layer_id  = (
    • SELECT layer_id FROM tbl_layer
    • WHERE table_row_id = pa_congress.ROWID 
    • AND table_name = 'pa_congress');
  •  Postgis to shapefile/Shapefile to Postgis
      • cd to postgres bin and use the following commands:
      • pgsql2shp -f PATHTOSHAPEFILEOUTPUT -h HOSTNAME -p PORT(5432) -P PASSWORD -u USERNAME -g the_geom DATABASENAME SCHEMA.TABLE
      • shp2pgsql.exe" -c -D -s SRID PATHTOSHAPEFILE SCHEMA.TABLE | psql -U USERNAME -W -d DBNAME














  • OSM to PGSQL
    • osm2pgsql.exe -u -d DATABASENAME -E 3395 -s -S FULLPATHTODEFAULT.STYLE -U DBUSERNAME -W -H DBHOST -P 5432 -O pgsql FULLPATHTOOSMFILE
      • Here is a statments which will create a shapefile and kml from a postgis database in ogr (variables in caps):
        • ogr2ogr -sql "SQL STATEMENT" -f "ESRI Shapefile" PG:"dbname='campusdata_141' host='DB NAME' port='5432' user='USER' password='PASSWORD'" PATH_TO_DESTINATION_FILE.shp
        • ogr2ogr -sql "select centroid from structures" -f "KML" PATH_TO_DESTINATION_FILE.kml PG:"dbname='campusdata_141' host='DB NAME' port='5432' user='USER' password='PASSWORD'"
        • ... not really sure why source comes first in shapefile example and last in kml example ..
    • Linux/Centos
      • Find text in files: grep -rnw 'directory' -e "pattern"
    • Mysql command line
      • show databases;
      • use fooDatabase;
      • show tables;
      • show create table fooTable;
      • ### for insert statements, must do in shell (cmd) ###
      • mysqldump -t -u fooUser -pfooPassword fooDatabase fooTable
    • Sqlite
      • Select from one into another: INSERT INTO Destination SELECT * FROM Source;
    Subscribe to: Posts (Atom)