tokumine

## window_rank.sql
-- this SQL can be used to calculate the rank of a given user in a game,
-- and the names/scores of those just above and below him.
-- This is useful in online games or citizen science projects where you
-- just want to see the 'proximity' of other users around you, not the entire global rank

-- I want to find the rank and score for user_3, and other users 3 above and 3 below.
WITH global_rank AS (
  SELECT name, score, rank() OVER (ORDER BY score DESC) FROM scores
)
SELECT * FROM global_rank

## n-tile.sql
-- To calculate flexible quantile ranges in postgresql, for example to calculate n equal
-- frequency buckets for your data for use in a visualisation (such as binning for a
-- choropleth map), you can use the following SQL:

-- this functions returns 6 equal frequency bucket ranges for my_column.
SELECT ntile, avg(my_column) AS avgAmount, max(my_column) AS maxAmount, min(my_column) AS minAmount
FROM (SELECT my_column, ntile(6) OVER (ORDER BY my_column) AS ntile FROM my_table) x
GROUP BY ntile ORDER BY ntile

-- more on the ntile() function and windowing here:

## mvk_Panasonic_TX-L42E30B_TX-L37E30B_TX-L32E30B
There's something wrong with the Panasonic TX-L42E30B, TX-L37E30B and TX-L32E30B TVs not recognising certain MKV container specific settings.

There is nothing actually wrong with the x264 encoded video and audio streams - the solution is to correctly re-mux the contents of the MKV container into a compatible MKV container while avoiding a time consuming re-encode.

I have found 2 solutions (on OSX) to the problem that don't require re-encoding.

1) Repackage the MKV as a mp4 using mp4tools (http://www.emmgunn.com/mp4tools/mp4toolshome.html). This works, but is unable to handle MKVs with DTS encoded audio streams on my machine.

2) Repackage the MKV as an MKV using mkvtools beta (http://www.emmgunn.com/mkvtools/mkvtoolshome.html). This has worked flawlessly on all the broken MKVs I've tried. mkvtools also can split MKVs into FAT32 compatible chunks for playback from USB sticks and so on.

## zoom_dependent_carto.md

      
              1 file
            
          
              3 forks
            
          
              0 comments
            
          
              1 star
            
          
                tokumine
                / zoom_dependent_carto.md
            
            
              Created
              December 9, 2011 16:27
            
              
                zoom dependent Carto on CartoDB
              
          
    This is an example of zoom dependent Carto.
For this demo, we will style 2 tiles from the same dataset:

Zoom level 6: https://vizzuality.cartodb.com/tiles/sql_statement/6/31/21.png
Zoom level 8: https://vizzuality.cartodb.com/tiles/sql_statement/8/125/83.png

Our style will make the polygons default to blue, but at zoom level 8 the polygons will be yellow:
Our Carto stylesheet for this is:

  
## style.css
#sql_statement{
  polygon-fill:#1F78B4;
}

## sequel.rspec
If you're running sequel models and rspec, make sure you run rake db:test:prepare before you execute your tests otherwise you'll get cryptic

NoMethodError:
       undefined method

on all your model attributes

Seems obvious once you know, but terrible error message.

## crazysql.sql
SELECT intersects.band, (CASE WHEN within.avg IS NULL OR within.area IS NULL THEN intersects.avg ELSE (((intersects.avg * intersects.area)   (within.avg * within.area)) / (intersects.area   within.area)) END) AS total FROM ( SELECT band, avg((ST_SummaryStats(ST_AsRaster((intersection).geom, scalex, scaley, NULL, NULL, ARRAY['32BSI'], ARRAY[(intersection).val]))).mean), SUM(area) AS area FROM ( SELECT band, (ST_Intersection(the_geom, rast, band)) AS intersection, ST_ScaleX(rast) AS scalex, ST_ScaleY(rast) AS scaley, ST_Area(the_geom) AS area FROM forest_intact, ( SELECT ST_GeomFromText('MULTIPOLYGON(((18.511962890625 5.386335689520536,14.864501953125 2.141834969768584,18.775634765625 0.0769042737833478,22.510986328125 -0.5383221578577078,26.114501953125 0.8239462091017685,26.553955078125 3.3269862108134998,22.423095703125 6.085935520826564,18.511962890625 5.386335689520536)))',4326) AS the_geom) foo, ( SELECT 1 AS band UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) bar WHERE ST_Intersects(rast, the_

## gist:1253540
rvm use 1.9.2
Using /Users/simon/.rvm/gems/ruby-1.9.2-p290
time bin/rspec -l 1179 spec/models/table_spec.rb

Finished in 2.85 seconds
1 example, 0 failures

real	0m12.487s
user	0m8.829s
sys	0m0.951s

## gist:1252442
The aim here:

1. upgrade postgres
2. then upgrade postgis

For [1], we'll use pg_upgrade to migrate our data.

on OSX, postgres is installed in usr/local using a symlinked pgsql pointing to pgsql-9.0

1. brew install postgresql (9.1.1 = https://github.com/fragility/homebrew/blob/pgsql/Library/Formula/postgresql.rb)

## LearnPython
1. tutorial in the python.org page
2. essential python reference  (dave beazly)
3. module documentation on python.org
	-- this SQL can be used to calculate the rank of a given user in a game,
	-- and the names/scores of those just above and below him.
	-- This is useful in online games or citizen science projects where you
	-- just want to see the 'proximity' of other users around you, not the entire global rank

	-- I want to find the rank and score for user_3, and other users 3 above and 3 below.
	WITH global_rank AS (
	SELECT name, score, rank() OVER (ORDER BY score DESC) FROM scores
	)
	SELECT * FROM global_rank
	-- To calculate flexible quantile ranges in postgresql, for example to calculate n equal
	-- frequency buckets for your data for use in a visualisation (such as binning for a
	-- choropleth map), you can use the following SQL:

	-- this functions returns 6 equal frequency bucket ranges for my_column.
	SELECT ntile, avg(my_column) AS avgAmount, max(my_column) AS maxAmount, min(my_column) AS minAmount
	FROM (SELECT my_column, ntile(6) OVER (ORDER BY my_column) AS ntile FROM my_table) x
	GROUP BY ntile ORDER BY ntile

	-- more on the ntile() function and windowing here:
	There's something wrong with the Panasonic TX-L42E30B, TX-L37E30B and TX-L32E30B TVs not recognising certain MKV container specific settings.

	There is nothing actually wrong with the x264 encoded video and audio streams - the solution is to correctly re-mux the contents of the MKV container into a compatible MKV container while avoiding a time consuming re-encode.

	I have found 2 solutions (on OSX) to the problem that don't require re-encoding.

	1) Repackage the MKV as a mp4 using mp4tools (http://www.emmgunn.com/mp4tools/mp4toolshome.html). This works, but is unable to handle MKVs with DTS encoded audio streams on my machine.

	2) Repackage the MKV as an MKV using mkvtools beta (http://www.emmgunn.com/mkvtools/mkvtoolshome.html). This has worked flawlessly on all the broken MKVs I've tried. mkvtools also can split MKVs into FAT32 compatible chunks for playback from USB sticks and so on.
	If you're running sequel models and rspec, make sure you run rake db:test:prepare before you execute your tests otherwise you'll get cryptic

	NoMethodError:
	undefined method

	on all your model attributes

	Seems obvious once you know, but terrible error message.
	rvm use 1.9.2
	Using /Users/simon/.rvm/gems/ruby-1.9.2-p290
	time bin/rspec -l 1179 spec/models/table_spec.rb

	Finished in 2.85 seconds
	1 example, 0 failures

	real 0m12.487s
	user 0m8.829s
	sys 0m0.951s
	The aim here:

	1. upgrade postgres
	2. then upgrade postgis

	For [1], we'll use pg_upgrade to migrate our data.

	on OSX, postgres is installed in usr/local using a symlinked pgsql pointing to pgsql-9.0

	1. brew install postgresql (9.1.1 = https://github.com/fragility/homebrew/blob/pgsql/Library/Formula/postgresql.rb)
	1. tutorial in the python.org page
	2. essential python reference (dave beazly)
	3. module documentation on python.org