Skip to content

Instantly share code, notes, and snippets.

View knutole's full-sized avatar

Knut Ole Sjøli knutole

13 followers · 12 following
View GitHub Profile
@knutole
knutole / gist:7fb09dcf3d4d1b730173
Created October 24, 2014 18:23
egypt.geojson
Loading
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
@knutole
knutole / postgis query
Created January 12, 2015 16:08
SELECT row_to_json(fc)
FROM (
SELECT ST_AsGeoJSON(ST_Intersection(lg.geometry, bbox.geom))::json As geometry,
(
SELECT row_to_json(t)
FROM (
SELECT name, type, admin_level
) t
)
As properties
@knutole
knutole / postgis query 4326
Created January 12, 2015 16:58
SELECT row_to_json(fc)
FROM (
SELECT ST_AsGeoJSON(ST_Transform(ST_Intersection(lg.geometry, bbox.geom), 4326))::json As geometry,
(
SELECT row_to_json(t)
FROM (
SELECT name, type, admin_level
) t
)
@knutole
knutole / postgis query without filter
Created January 13, 2015 14:25
SELECT row_to_json(fc)
FROM (
SELECT ST_AsGeoJSON(ST_Transform(ST_Intersection(lg.geometry, bbox.geom), 4326))::json As geometry,
(
SELECT row_to_json(t)
FROM (
SELECT name, type, admin_level
) t
)
As properties
@knutole
knutole / gist:5b7713849ccce50e31de
Created January 21, 2015 15:48
inefficient query
SELECT row_to_json(fc) FROM ( SELECT ST_AsGeoJSON(ST_Transform(ST_Intersection(ST_MakeValid(lg.geometry), bbox.geom), 4326))::json As geometry, ( SELECT row_to_json(t) FROM ( SELECT "name","type","area") t ) As properties FROM osm_waterareas As lg, (SELECT ST_Transform(ST_MakeEnvelope(-7.031250000000009,36.59788913307019,-5.625000000000013,37.718590325588146, 4326),3857) as geom) as bbox WHERE ST_Intersects(ST_MakeValid(lg.geometry), bbox.geom) AND (area > 12500000) ) fc;
@knutole
knutole / gist:cf8b7567c71e6aa6d4cb
Created January 21, 2015 16:04
indexes?
Indexes:
"ne_bathymetry_pkey" PRIMARY KEY, btree (gid)
"ne_bathymetry_geom_idx" gist (geometry)
"ne_bathymetry_geom_idx1" gist (geometry)
"ne_bathymetry_geom_idx10" gist (geometry)
"ne_bathymetry_geom_idx2" gist (geometry)
"ne_bathymetry_geom_idx3" gist (geometry)
"ne_bathymetry_geom_idx4" gist (geometry)
"ne_bathymetry_geom_idx5" gist (geometry)
"ne_bathymetry_geom_idx6" gist (geometry)
@knutole
knutole / gist:edbb24ab7eadc7f5c1d2
Created January 21, 2015 16:08
whole shebang
List of relations
Schema | Name | Type | Owner
--------+-------------------------------+-------+----------
public | ne_bathymetry | table | osm
public | ne_urban_areas | table | osm
public | osm_admin | table | osm
public | osm_aeroways | table | osm
public | osm_amenities | table | osm
public | osm_barrierpoints | table | osm
public | osm_barrierways | table | osm
@knutole
knutole / gist:5dca257d530cf50827ea
Created January 30, 2015 10:19
SELECT row_to_json(fc) FROM (
SELECT ST_AsGeoJSON(ST_Transform(ST_Intersection(lg.geometry, bbox.geom), 4326))::json As geometry, (
SELECT row_to_json(t) FROM (
SELECT "name","type","area","z_order") t ) As properties FROM osm_landusages As lg, (
SELECT ST_Transform(ST_MakeEnvelope(-2.8125,38.82259097617711,25.312499999999986,57.326521225217064, 4326),3857)
as geom) as bbox
WHERE ST_Intersects(lg.geometry, bbox.geom)
AND (area > 200000000) ) fc;
@knutole
knutole / gist:e3bb5b4ffdbcb54bee70
Created January 30, 2015 12:39
EXPLAIN ANALYZE VERBOSE SELECT ST_AsGeoJSON(ST_Intersection(lg.geometry, bbox.geom)) As geometry, ( SELECT rowea","z_order") t ) As properties FROM osm_landusages As lg, (SELECT ST_Transform(ST_MakeEnvelope(-2.8125,38.822590974, 4326),3857) as geom) as bbox WHERE ST_Intersects(lg.geometry, bbox.geom) AND (area > 200000000) ;
@knutole
knutole / gist:472da807de087eeca047
Created January 30, 2015 13:46
SELECT row_to_json(fc) FROM (
SELECT ST_AsGeoJSON(ST_Transform(ST_Intersection(lg.geometry, bbox.geom), 4326))::json As geometry, (
SELECT row_to_json(t) FROM (
SELECT "name","type","area","z_order") t ) As properties FROM osm_landusages As lg, (
SELECT ST_Transform(ST_MakeEnvelope(-2.8125,38.82259097617711,25.312499999999986,57.326521225217064, 4326),3857) as geom) as bbox
WHERE ST_Intersects(lg.geometry, bbox.geom)
AND (area > 200000000) ) fc;