knutole’s gists

knutole / gist:7fb09dcf3d4d1b730173

Created October 24, 2014 18:23

egypt.geojson

Sorry, something went wrong. Reload?

Sorry, we cannot display this file.

Sorry, this file is invalid so it cannot be displayed.

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 / 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 / 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 / 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 / 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 / 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 / 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 / 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 / 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;

	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

Knut Ole Sjøli knutole