Skip to content

Instantly share code, notes, and snippets.

Alexander Salveson Nossum alexanno

Block or report user

Report or block alexanno

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
View cartodb-json-flattener.sql
SELECT *,
(select string_agg(value->>'tekstfelt',', ') a from
json_array_elements(tekstfelt::json) m) tekstfelt_streng
FROM mellomlag m
View postgis2geojson.sql
SELECT row_to_json(fc)
FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
FROM (SELECT 'Feature' As type
, ST_AsGeoJSON(lg.geom, 4)::json As geometry
, row_to_json((SELECT l FROM (SELECT fylkesnr, navn) As l
)) As properties
FROM sandbox.fylker As lg ) As f ) As fc;
View index.html
<!doctype><html><head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<title>Odyssey.js Scroll</title>
<meta name="description" content="">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="icon" type="image/x-icon" href="http://cartodb.github.io/odyssey.js/sandbox/favicon.png">
<link rel="icon" type="image/png" href="http://cartodb.github.io/odyssey.js/sandbox/favicon.png">
View gist:c72bb93a2fd000411cb6
<?xml version="1.0" encoding="UTF-8"?>
<jmeterTestPlan version="1.2" properties="2.8" jmeter="2.13 r1665067">
<hashTree>
<TestPlan guiclass="TestPlanGui" testclass="TestPlan" testname="build-web-test-plan" enabled="true">
<stringProp name="TestPlan.comments"></stringProp>
<boolProp name="TestPlan.functional_mode">false</boolProp>
<boolProp name="TestPlan.serialize_threadgroups">false</boolProp>
<elementProp name="TestPlan.user_defined_variables" elementType="Arguments" guiclass="ArgumentsPanel" testclass="Arguments" testname="User Defined Variables" enabled="true">
<collectionProp name="Arguments.arguments"/>
</elementProp>
@alexanno
alexanno / table size postgres.sql
Created Oct 4, 2015
list table size postgres
View table size postgres.sql
SELECT
table_name,
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
table_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
View scraping.js
s = document.createElement("script");
s.src = "https://code.jquery.com/jquery-2.1.4.min.js";
s.type = "application/javascript";
document.head.appendChild(s);
$("tr a").each(function(key,elem){
elem.target="_blank";
elem.click();
})
@alexanno
alexanno / .sh
Created Jun 29, 2015
Import N50 postgis dump
View .sh
#pgadmin works pretty well
#schema only
pg_restore --host localhost --port 5432 --username "alexanno" --dbname "test" --role "alexanno" --no-password --no-owner --no-privileges --no-tablespaces --schema-only --schema n50 --verbose "/Users/alexanno/Downloads/Kartdata_Norge_UTM33_N50_PostGIS/n50_custom.backup
#data only
pg_restore --host localhost --port 5432 --username "alexanno" --dbname "alexanno" --role "alexanno" --no-password --data-only --schema n50 --verbose "/Users/alexanno/Downloads/Kartdata_Norge_UTM33_N50_PostGIS/n50_custom.backup
@alexanno
alexanno / island_contours_postgis_n50.sql
Last active Aug 29, 2015
island contours in postgis + n50
View island_contours_postgis_n50.sql
select ogc_fid, geometri from n50.n50_begrensningskurve a
where st_touches(a.geometri, (select geometri from n50.n50_begrensningskurve where ogc_fid=2680783))
------------ http://boundlessgeo.com/2010/07/network-walking-in-postgis/
WITH RECURSIVE walk_network(ogc_fid, geom) AS (
SELECT ogc_fid, geom FROM sandbox.justoya WHERE ogc_fid=2625722
UNION ALL
SELECT n.ogc_fid, n.geom
FROM sandbox.justoya n, walk_network w
WHERE ST_DWithin(ST_EndPoint(w.geom),ST_StartPoint(n.geom),0.1)
)
@alexanno
alexanno / greatcircle_postgis
Created Jun 27, 2015
Create great circles in postgis / cartodb.
View greatcircle_postgis
http://gis.stackexchange.com/questions/84443/what-is-this-postgis-query-doing-to-show-great-circle-connections/84444#84444
The examples given, and the previous answer all depend on taking the line into a planar projection and then segmentizing. As of PostGIS 2.1 (which I think CartoDB is on) you can do a general-purpose job of it using ST_Segmentize(geography), so like this:
SELECT
ST_Segmentize(
ST_MakeLine(
ST_SetSRID(ST_MakePoint(-71.060316, 48.432044),4326),
pt
)::geography
@alexanno
alexanno / gist:789fedb9e4378d4dd0c6
Created Jun 3, 2015
git https:// instead of git://
View gist:789fedb9e4378d4dd0c6
git config --global url."https://".insteadOf git://
You can’t perform that action at this time.