![\"pg_logo\"](\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2012\/06\/PostgreSQL_logo.3colors.120x120.png\"){kind=logo}
<\/p>\n<\/p>\n
In preparing for an upcoming Datathon<\/a>, a column of data in PostgreSQL Psql extension Select stats on non-zero data<\/p>\n <\/p>\n So, recall that a natural log e<\/em> of 1.0 is 0; a natural log of 116 is slightly over 4.75; a natural log of a number less than 1 is a negative number. To simplify the range for visualization, add a float column called data<\/i>, set the new data<\/i> column to the natural log of (intersection_density_sqkm + 1);<\/em> use a simple multiply-then-divide technique to limit the precision to two digits (screenshot from an IPython Notebook<\/a><\/strong> session using psycopg2<\/a><\/strong>).<\/p>\nnumeric<\/code> format needed formatting for presentation. “Intersection Count” intersection_density_sqkm<\/code> is a count of street intersections per unit area – a quick way to measure density of the built environment. A table of grid cells (covering the nine-county San Francisco Bay Area) that the column comes from consists of roughly 814,000 cells. How to quickly characterize the data contents? Use SQL and the psql quantile<\/strong> extension to look at ranges, with and without the zeroes.
\n <\/p>\n\r\nSELECT \r\n min(intersection_density_sqkm), \r\n quantile(intersection_density_sqkm,ARRAY[0.02,0.25,0.5,0.75,0.92]), \r\n max(intersection_density_sqkm) \r\n\r\n FROM uf_singleparts.ba_intersection_density_sqkm\r\n<\/pre>\n
\n
\r\n-[ RECORD 1 ]-----------------------------------------------------------------------------\r\nmin | 0.0\r\nquantile | {0.0, 0.0, 0.683937...,3.191709...,25.604519...}\r\nmax | 116.269430...\r\n<\/pre>\n<\/blockquote>\nquantile<\/code> takes as arguments a column name and an ARRAY for N<\/em> positional elements by percentage, e.g. above<\/em><\/p>\n
\nHow Many Gridcells Have Non-zero Data ?<\/h4>\n
\r\nselect count(*) from ba_intersection_density_sqkm;\r\ncount => 814439\r\n\r\nselect count(*) from ba_intersection_density_sqkm where intersection_density_sqkm <> 0;\r\ncount => 587504\r\n<\/pre>\n
\r\n SELECT \r\n min(intersection_density_sqkm), \r\n quantile(intersection_density_sqkm,ARRAY[0.02,0.25,0.5,0.75,0.92]), \r\n max(intersection_density_sqkm) \r\n\r\n FROM uf_singleparts.ba_intersection_density_sqkm\r\n\r\n where intersection_density_sqkm <> 0;\r\n<\/pre>\n
\n
\r\n-[ RECORD 1 ]-----------------------------------------------------------------------------\r\nmin | 0.227979...\r\nquantile | {0.227979...,0.455958...,1.367875...,7.751295...,31.461139...}\r\nmax | 116.269430...\r\n<\/pre>\n<\/blockquote>\n
\nand, what does the high-end of the range look like ? Use SQL for a quick visual inspection for either outliers or smooth transitions:<\/p>\n\r\nSELECT intersection_density_sqkm \r\nFROM ba_intersection_density_sqkm \r\n ORDER BY intersection_density_sqkm desc limit 12;\r\n\r\n intersection_density_sqkm \r\n---------------------------\r\n 116.2694300518134736\r\n 115.5854922279792768\r\n 115.3575129533678764\r\n 115.1295336787564760\r\n 114.9015544041450756\r\n 114.9015544041450756\r\n 114.4455958549222792\r\n 113.7616580310880824\r\n 112.6217616580310892\r\n 112.6217616580310892\r\n 112.1658031088082884\r\n 112.1658031088082884\r\n<\/pre>\n
![\"ipython_notebook](\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2014\/08\/ipynb_sql_update.png\")
<\/a><\/p>\n![\"geoserv_css_style_inters\"](\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2014\/08\/geoserv_css_style_inters.png\")
<\/a><\/p>\n