Charmatzis

object IngestImage extends App{


val _spark = SparkSession
    .builder()
    .config("spark.serializer", "org.apache.spark.serializer.KryoSerializer")
    .config("spark.kryo.registrator", "geotrellis.spark.io.kryo.KryoRegistrator")

Charmatzis

import geotrellis.proj4.{LatLng, WebMercator}
import geotrellis.raster.{ArrayTile, DoubleCellType, IntConstantNoDataCellType, MutableArrayTile, RasterExtent, Tile}
import geotrellis.raster.render.ColorRamps
import geotrellis.raster.resample.Max
import geotrellis.spark._
import geotrellis.spark.io._
import geotrellis.spark.io.index._
import geotrellis.spark.io.s3._
import geotrellis.spark.pyramid._
import geotrellis.spark.tiling._

Charmatzis

Introduction

This is a help page for settinh up Zeppelin with geotrellis and geotrellis-spark-sql and continue with analysis.

Steps

Prestep

Set properties for EMR cluster

Charmatzis

SELECT
  CASE 
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016'     
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017' 

Charmatzis

USE MyDatabase
GO

EXEC sp_spaceused N'User.ContactInfo'
GO

Charmatzis

WITH dataCTE
AS
(	
	SELECT allocated_page_page_id, next_page_page_id, previous_page_page_id
	FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.TEMP_POS_TERR'),1, NULL, 'DETAILED')
	WHERE is_allocated = 1
	AND page_type_desc = 'DATA_PAGE'
)
,pageCTE
AS

Charmatzis

select  index_id
  ,     index_type_desc
  ,     index_depth
  ,     index_level
  ,     page_count
  ,     record_count
from sys.dm_db_index_physical_stats (DB_ID(),
OBJECT_ID('Table_Name'), DEFAULT, DEFAULT, 'DETAILED')
go

Charmatzis

Reading tiles using RIO

<GDAL_WMS>
    <Service name="TMS">
	<ServerUrl>https://api.mapbox.com/v4/mapbox.satellite/${z}/${x}/${y}.jpg?access_token=******</ServerUrl> <!-- Map -->
    </Service>
    <DataWindow>
        <UpperLeftX>-20037508.34</UpperLeftX>
 20037508.34

Charmatzis

SELECT c.conrelid::regclass AS "table",
       /* list of key column names in order */
       string_agg(a.attname, ',' ORDER BY x.n) AS columns,
       pg_catalog.pg_size_pretty(
          pg_catalog.pg_relation_size(c.conrelid)
       ) AS size,
       c.conname AS constraint,
       c.confrelid::regclass AS referenced_table
FROM pg_catalog.pg_constraint c
   /* enumerated key column numbers per foreign key */

Charmatzis

 -- Unused Indexes
 select
	indexrelname as index_name,
	idx_scan,
	idx_tup_read,
	idx_tup_fetch
from
	pg_stat_all_indexes
where
	idx_scan = 0