Recent Queries, Commands, and Code

Public Github Repos
  • kml-priorityKML LOD Render Priority (Nested Regions) with PHP/Postgres
  • csv2gftBash .sh for routine updating of a Google Fusion Table based on a MMDDYY csv file, with sanity checks

Command snippets
  • php log/write to file with serialization:
    $content = var_export($DATA_TO_LOG, TRUE);
    
    $file = dirname( __FILE__ ) . '\filename';
    
    file_put_contents($file, $content);
    
  • to do a spatial join/update in sqlite:
    UPDATE measurement
    SET
          state_abbr = (SELECT states.STATE_ABBR
                                FROM states
                                WHERE ST_INTERSECTS(measurement.geometry,states.geometry)=1);   
    
  • sudo ls -Rlta | grep -i python | gedit
  • /* selects non active duplicate records from table1 based on another table and two id's */
    select * from table1 where idfield not in (select idfield from table2) and idfield2 in (select idfield2 from (select idfield2, count(idfield2) as a from table1 group by idfield2) as sq where a > 1)
  • to create a diff file with gnuwin32 diff (ignorance whitespace differences): diff -uwi C:\FILE1PATH C:\FILE2PATH > C:\PATCHFILEPATH.patch
  • To join two tables as a view based on a field containing a delimited value (to be converted to an array) in postgres
    
    CREATE OR REPLACE VIEW downloads_report AS 
     SELECT download_recd."user", download_recd.firstname, download_recd.lastname, download_recd.email, download_recd.campusdeptname, download_recd.ticket, download_recd.timedate, download_recd.persontype, download_recd.studentclassification, download_recd.software_id, tbl_geodownload.id, tbl_geodownload.name
       FROM download_recd, tbl_geodownload
      WHERE  cast(tbl_geodownload.id as text) = ANY (regexp_split_to_array(download_recd.software_id,'|'));
    
    ALTER TABLE downloads_report OWNER TO postgres;
    
  • to add a twitter share button to blogger, add this to the template inside the "post" includable, which is in the Blog1 Widget :
     <a class='twitter-share-button' data-count='horizontal' data-dnt='true' data-lang='en' data-related='bmearns:GIS from U. of Delaware and Philly' data-via='bmearns' expr:data-text='data:post.title' expr:data-url='data:post.canonicalUrl' href='https://twitter.com/share' rel='nofollow'>Tweet</a>  
     <script>!function(d,s,id){var js,fjs=d.getElementsByTagName(s)[0],p=/^http:/.test(d.location)?&#39;http&#39;:&#39;https&#39;;if(!d.getElementById(id)){js=d.createElement(s);js.id=id;js.src=p+&#39;://platform.twitter.com/widgets.js&#39;;fjs.parentNode.insertBefore(js,fjs);}}(document, &#39;script&#39;, &#39;twitter-wjs&#39;);</script>  
    

No comments: