Skip to content

Instantly share code, notes, and snippets.

Alexander Salveson Nossum alexanno

View GitHub Profile
View index.html
http://www.webatlas.no/mediakart/testbuild_v2/embed/kart.htm
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
You can’t perform that action at this time.