Ben Mearns: Recent Queries, Commands, and Code

Public Github Repos

kml-priority: KML LOD Render Priority (Nested Regions) with PHP/Postgres
csv2gft: Bash .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>

Recent Queries, Commands, and Code

No comments: