// Return GeoRecords by Level slug
func getGeosByLevSlug(conf CONFIG, levslug string, filter_key string) []byte {
hash := cache.MakeHash("gGByLS:" + levslug + filter_key)
c := getFromCache(conf.REDIS_CONN, hash)
if len(c) != 0 {
log.Println("Serving getData from cache: ", "gGbyLS:"+levslug)
return c
}
var (
id int
geoKey string
slug string
name string
)
cleaned_slug, err := sanitize(levslug, "[-\\w]+")
if err != nil {
r := []byte("405")
return r
}
cleaned_filter, err := sanitize(filter_key, "[-\\w\\d]+")
if err != nil {
//r:=[]byte("405")
//return r
cleaned_filter = ""
}
db, err := getDB(conf)
if err != nil {
log.Println("Error trying to call getDB")
r := []byte("500")
return r
}
defer db.Close()
query := "SELECT profiles_georecord.id, profiles_georecord.geo_id, profiles_georecord.slug, profiles_georecord.name FROM profiles_geolevel FULL JOIN profiles_georecord ON profiles_georecord.level_id = profiles_geolevel.id WHERE profiles_geolevel.slug=$1"
if cleaned_filter != "" {
query += " AND profiles_georecord.geo_id ILIKE '%" + cleaned_filter + "%'"
}
rows, err := db.Query(query, cleaned_slug)
if err != nil {
log.Println("Error runnning query: %s", query)
r := []byte("500")
return r
}
defer rows.Close()
data := map[string]interface{}{} // this will be the object that wraps everything
results := []interface{}{}
for rows.Next() {
err := rows.Scan(&id, &geoKey, &slug, &name)
if err == nil {
jrow := make(map[string]interface{})
jrow["id"] = id
jrow["geoKey"] = geoKey
jrow["slug"] = slug
jrow["name"] = name
results = append(results, jrow)
}
}
data["objects"] = &results
j, err := json.Marshal(data)
if len(results) != 0 {
putInCache(conf.REDIS_CONN, hash, j, conf.CACHE_EXPIRE)
}
return j
}
func getPointOverlays(conf CONFIG) []byte {
/*
get all enabled PointOverlay (point shapefile overlays)
*/
hash := cache.MakeHash("maplayers")
c := getFromCache(conf.REDIS_CONN, hash)
if len(c) != 0 {
log.Println("Serving getPointOverlays from cache")
return c
}
db, err := getDB(conf)
if err != nil {
log.Println("Error trying to call getDB")
r := []byte("500")
return r
}
defer db.Close()
var (
name string
shapefile_id string
image string
label string
geom string
)
query := "SELECT maps_pointoverlay.name, maps_pointoverlay.shapefile_id, maps_pointoverlayicon.image FROM maps_pointoverlay, maps_pointoverlayicon WHERE maps_pointoverlayicon.id = maps_pointoverlay.icon_id AND maps_pointoverlay.available_on_maps = TRUE"
rows, err := db.Query(query)
if err != nil {
log.Println("Error runnning query: getPointOverlays")
r := []byte("500")
return r
}
defer rows.Close()
data := map[string]interface{}{}
for rows.Next() {
err := rows.Scan(&name, &shapefile_id, &image)
if err == nil {
rows, err := db.Query("SELECT label, ST_ASGeoJSON(geom) AS geom FROM maps_pointmapfeature WHERE source_id = " + shapefile_id)
if err != nil {
log.Println("Error runnning query: getPointOverlays")
r := []byte("500")
return r
}
defer rows.Close()
results := []interface{}{}
for rows.Next() {
err := rows.Scan(&label, &geom)
if err == nil {
properties := make(map[string]interface{})
properties["label"] = label
properties["image"] = "/media/" + image
geom := jsonLoads(geom)
geom["properties"] = &properties
results = append(results, geom)
}
}
data[name] = results
}
}
j, err := json.Marshal(data)
putInCache(conf.REDIS_CONN, hash, j, conf.CACHE_EXPIRE)
return j
}
/* Example:
IN query
Find geoms of geolevel 6 that are in geom 419. lev=N or *
http://127.0.0.1:8080/shp/q/?geoms=419&lev=6&q=IN
OF query
Find geoms of geolevel 1 that contain geoms 380
http://127.0.0.1:8080/shp/q/?geoms=380&lev=1&q=OF
*/
func getGeoQuery(conf CONFIG, geoms_ids string, geo_lev_id string, query_type string) []byte {
hash := cache.MakeHash("gGQ:" + geoms_ids + geo_lev_id + query_type)
c := getFromCache(conf.REDIS_CONN, hash)
if len(c) != 0 {
log.Println("Serving getData from cache: ", "gGQ:"+geoms_ids+geo_lev_id+query_type)
return c
}
cleaned_geoms, err := sanitize(geoms_ids, "[0-9,\\*]+")
if err != nil {
r := []byte("405")
return r
}
cleaned_geo_lev, err := sanitize(geo_lev_id, "[0-9,]+") // supports mulitple levels
if err != nil {
r := []byte("405")
return r
}
cleaned_query_type, err := sanitize(query_type, "(IN|OF)")
if err != nil {
r := []byte("405")
return r
}
// also make sure the sanitized query_Type is IN or OF
if cleaned_query_type != "IN" && cleaned_query_type != "OF" {
r := []byte("405")
return r
}
var (
geomId int
geosId int
geoKey string
label string
slug string
)
var geom_query string
where_clause := " WHERE profiles_georecord.level_id IN (" + cleaned_geo_lev + ") AND "
if cleaned_query_type == "IN" {
// find geoms contained in this geom
geom_query = "WITH targ_levs AS (SELECT maps_polygonmapfeature.id as geom_id, maps_polygonmapfeature.geo_key, ST_SimplifyPreserveTopology(maps_polygonmapfeature.geom, 0.001) AS geom, profiles_georecord.id as id, profiles_georecord.slug, profiles_georecord.name as label FROM maps_polygonmapfeature, profiles_georecord" + where_clause + "maps_polygonmapfeature.geo_key=profiles_georecord.geo_id), targ_geom AS (SELECT id, geo_key, ST_SimplifyPreserveTopology(geom, 0.001) AS geom FROM maps_polygonmapfeature WHERE id IN (" + cleaned_geoms + ") LIMIT 1) SELECT DISTINCT ON (targ_levs.geo_key) targ_levs.id, targ_levs.geom_id, targ_levs.geo_key, targ_levs.label, targ_levs.slug FROM targ_levs, targ_geom WHERE ST_Area(ST_Intersection(targ_levs.geom, targ_geom.geom)) > ST_Area(targ_levs.geom)/2 ORDER BY targ_levs.geo_key"
} else if cleaned_query_type == "OF" {
// find geoms that contain geom
geom_query = "WITH targ_levs AS (SELECT maps_polygonmapfeature.id as geom_id, maps_polygonmapfeature.geo_key, ST_SimplifyPreserveTopology(maps_polygonmapfeature.geom, 0.001) AS geom, profiles_georecord.id as id, profiles_georecord.slug, profiles_georecord.name as label FROM maps_polygonmapfeature, profiles_georecord" + where_clause + "maps_polygonmapfeature.geo_key=profiles_georecord.geo_id), targ_geom AS (SELECT id, geo_key, ST_SimplifyPreserveTopology(geom, 0.001) AS geom FROM maps_polygonmapfeature WHERE id IN (" + cleaned_geoms + ") LIMIT 1) SELECT DISTINCT ON (targ_levs.geo_key) targ_levs.id, targ_levs.geom_id, targ_levs.geo_key, targ_levs.label, targ_levs.slug FROM targ_levs, targ_geom WHERE ST_Area(ST_Intersection(targ_levs.geom, targ_geom.geom)) > ST_Area(targ_geom.geom)/2 ORDER BY targ_levs.geo_key"
}
//TODO: We tend to always run querires like this, why not abstract it
db, err := getDB(conf)
if err != nil {
log.Println("Error trying to call getDB")
r := []byte("500")
return r
}
defer db.Close()
stmt, err := db.Prepare(geom_query)
if err != nil {
log.Println("Error preparing query: ", geom_query)
r := []byte("405")
return r
}
defer stmt.Close()
rows, err := stmt.Query()
if err != nil {
log.Println("Error running query ", geom_query)
log.Println(err)
r := []byte("405")
return r
}
data := map[string]interface{}{} // this will be the object that wraps everything
results := []interface{}{}
for rows.Next() {
jrow := make(map[string]interface{})
err := rows.Scan(&geosId, &geomId, &geoKey, &label, &slug)
if err == nil {
jrow["id"] = geosId
jrow["geom_id"] = geomId
jrow["geoKey"] = geoKey
jrow["label"] = label
jrow["slug"] = slug
results = append(results, jrow)
} else {
log.Println("Error in row: %s ", err)
}
}
data["objects"] = &results
j, err := json.Marshal(data)
if len(results) != 0 {
putInCache(conf.REDIS_CONN, hash, j, conf.CACHE_EXPIRE)
}
return j
}
// Get Geoms by a list of geometry ids
func getGeomsById(geoms_ids string, conf CONFIG) []byte {
/*
geoms_ids is a comma delimited string
*/
hash := cache.MakeHash("shp:" + geoms_ids)
c := getFromCache(conf.REDIS_CONN, hash)
if len(c) != 0 {
log.Println("Serving getData from shp cache:", geoms_ids)
return c
}
var (
geom string
geo_key string
label string
)
cleaned_geos, err := sanitize(geoms_ids, "[0-9,\\*]+")
if err != nil {
r := []byte("405")
return r
}
db, err := getDB(conf)
if err != nil {
log.Println("Error trying to call getDB")
r := []byte("500")
return r
}
defer db.Close()
rows, err := db.Query("SELECT geo_key, label, ST_AsGeoJSON(geom) FROM maps_polygonmapfeature WHERE id IN (" + cleaned_geos + ")")
if err != nil {
log.Println("Error runnning query: getGeomsById")
r := []byte("500")
return r
}
defer rows.Close()
data := map[string]interface{}{} // this will be the object that wraps everything
results := []interface{}{}
for rows.Next() {
err := rows.Scan(&geo_key, &label, &geom)
if err == nil {
properties := make(map[string]interface{})
properties["geo_key"] = geo_key
properties["label"] = label
geom := jsonLoads(geom)
geom["properties"] = properties
results = append(results, geom)
}
}
data["objects"] = &results
j, err := json.Marshal(data)
if len(results) != 0 {
putInCache(conf.REDIS_CONN, hash, j, conf.CACHE_EXPIRE)
}
return j
}
func getGeomsByGeosId(geos_ids string, conf CONFIG) []byte {
/*
geos_ids is a comma delimited string of IDS found in the profiles_georecord_table
*/
hash := cache.MakeHash("shp:" + geos_ids)
c := getFromCache(conf.REDIS_CONN, hash)
if len(c) != 0 {
log.Println("Serving getData from shp cache:", geos_ids)
return c
}
var (
geos_id int
geos_name string
geos_slug string
geo_key string
geom string
)
cleaned_geos, err := sanitize(geos_ids, "[0-9,\\*]+")
if err != nil {
r := []byte("405")
return r
}
db, err := getDB(conf)
if err != nil {
log.Println("Error trying to call getDB")
r := []byte("500")
return r
}
defer db.Close()
base_query := "SELECT profiles_georecord.geo_id, profiles_georecord.name, profiles_georecord.slug, profiles_georecord.geo_id, ST_ASGeoJSON(maps_polygonmapfeature.geom) as geom FROM profiles_georecord, maps_polygonmapfeature WHERE profiles_georecord.id IN(" + cleaned_geos + ") AND profiles_georecord.geo_id = maps_polygonmapfeature.geo_key"
rows, err := db.Query(base_query)
if err != nil {
log.Println("Error runnning query: getGeomsByGeosId")
r := []byte("500")
return r
}
defer rows.Close()
data := map[string]interface{}{} // this will be the object that wraps everything
results := []interface{}{}
for rows.Next() {
err := rows.Scan(&geos_id, &geos_name, &geos_slug, &geo_key, &geom)
if err == nil {
properties := make(map[string]interface{})
properties["geo_key"] = geo_key
properties["label"] = geos_name
properties["slug"] = geos_slug
geom := jsonLoads(geom)
geom["properties"] = &properties
results = append(results, geom)
}
}
data["objects"] = &results
j, err := json.Marshal(data)
if len(results) != 0 {
putInCache(conf.REDIS_CONN, hash, j, conf.CACHE_EXPIRE)
}
return j
}
func getDataGeoJson(ind string, time string, raw_geos string, conf CONFIG) []byte {
// Join indicator data with shapefiles geoms
hash := cache.MakeHash("gdgj:" + ind + time + raw_geos)
c := getFromCache(conf.REDIS_CONN, hash)
if len(c) != 0 {
log.Println("Serving getData from cache gdgj: ", ind+time+raw_geos)
return c
}
var (
//indicator_id int
indicator_slug string
display_title string
geography_id int
geography_name string
//geography_slug string
geometry_id sql.NullInt64
value_type string
time_key string
number sql.NullFloat64
percent sql.NullFloat64
moe sql.NullFloat64
numerator sql.NullFloat64
numerator_moe sql.NullFloat64
f_number sql.NullString
f_percent sql.NullString
f_moe sql.NullString
f_numerator sql.NullString
f_numerator_moe sql.NullString
geom sql.NullString
)
/* SANITIZING INPUTS */
cleaned_geos, err := sanitize(raw_geos, "[0-9,\\*]+")
if err != nil {
r := []byte("405")
return r
}
data := map[string]interface{}{} // this will be the object that wraps everything
db, err := getDB(conf)
if err != nil {
log.Println("Error trying to call getDB")
r := []byte("500")
return r
}
defer db.Close()
var base_query = "SELECT profiles_flatvalue.indicator_slug, profiles_flatvalue.display_title, profiles_flatvalue.geography_id, profiles_flatvalue.geography_name, profiles_flatvalue.geometry_id, profiles_flatvalue.value_type, profiles_flatvalue.time_key, profiles_flatvalue.number, profiles_flatvalue.percent, profiles_flatvalue.moe, profiles_flatvalue.numerator, profiles_flatvalue.numerator_moe, profiles_flatvalue.f_number, profiles_flatvalue.f_percent, profiles_flatvalue.f_moe, profiles_flatvalue.f_numerator, profiles_flatvalue.f_numerator_moe, ST_AsGeoJSON(maps_polygonmapfeature.geom) AS geom FROM profiles_flatvalue LEFT OUTER JOIN maps_polygonmapfeature ON (profiles_flatvalue.geography_geo_key = maps_polygonmapfeature.geo_key) WHERE profiles_flatvalue.indicator_slug = $1 AND profiles_flatvalue.time_key= $2"
var query string
// we need to support getting * geos or specific ones via thier ids also we need to be able to join on a geom
if cleaned_geos == "*" {
query = base_query
} else {
query = base_query + " AND profiles_flatvalue.geography_id IN (" + cleaned_geos + ")"
}
stmt, err := db.Prepare(query)
if err != nil {
log.Println("Error runnning query: getDataGeoJson")
r := []byte("500")
return r
}
defer stmt.Close()
rows, err := stmt.Query(ind, time)
if err != nil {
log.Println("Error runnning query: getDataGeoJson")
r := []byte("500")
return r
}
results := []interface{}{}
metachan := make(chan []interface{}) // get the meta data
go getMetaData(metachan, db, ind)
data["related"] = <-metachan
for rows.Next() {
jrow := make(map[string]interface{})
err := rows.Scan(&indicator_slug, &display_title, &geography_id, &geography_name, &geometry_id, &value_type, &time_key, &number, &percent, &moe, &numerator, &numerator_moe, &f_number, &f_percent, &f_moe, &f_numerator, &f_numerator_moe, &geom)
if err == nil {
if geom.Valid {
jrow = jsonLoads(geom.String)
} else {
jrow["coordinates"] = nil
}
properties := make(map[string]interface{})
properties["label"] = geography_name
properties["geography_id"] = geography_id
values := make(map[string]interface{})
values["indicator_slug"] = indicator_slug
values["value_type"] = value_type
values["time_key"] = time_key
if number.Valid {
values["number"] = number.Float64
} else {
values["number"] = nil
}
if value_type != "i" {
if percent.Valid {
values["percent"] = percent.Float64
} else {
values["percent"] = nil
}
} else {
values["percent"] = nil
}
if moe.Valid {
values["moe"] = moe.Float64
} else {
values["moe"] = nil
}
if f_number.Valid {
values["f_number"] = f_number.String
} else {
values["f_number"] = nil
}
if value_type != "i" {
if numerator.Valid {
values["numerator"] = numerator.Float64
} else {
values["numerator"] = nil
}
if f_numerator.Valid {
values["f_numerator"] = f_numerator.String
} else {
values["f_numerator"] = nil
}
if numerator_moe.Valid {
values["numerator_moe"] = numerator_moe.Float64
} else {
values["numerator_moe"] = nil
}
if f_numerator.Valid {
values["f_numerator_moe"] = f_numerator_moe.String
} else {
values["f_numerator_moe"] = nil
}
if f_percent.Valid {
values["f_percent"] = f_percent.String
} else {
values["f_percent"] = nil
}
} else {
values["f_percent"] = nil
}
if f_moe.Valid {
values["f_moe"] = f_moe.String
} else {
values["f_moe"] = nil
}
jrow["properties"] = &properties
jrow["values"] = &values
results = append(results, jrow)
}
}
data["objects"] = &results
j, err := json.Marshal(data)
if len(results) != 0 {
putInCache(conf.REDIS_CONN, hash, j, conf.CACHE_EXPIRE)
}
return j
}
// Indicator Api Handler. Returns values only
func getData(ind string, time string, raw_geos string, conf CONFIG) []byte {
hash := cache.MakeHash(ind + time + raw_geos)
c := getFromCache(conf.REDIS_CONN, hash)
if len(c) != 0 {
log.Println("Serving getData from cache: ", ind+time+raw_geos)
return c
}
var (
indicator_slug string
display_title string
geography_id int
geography_name string
//geography_slug string
geometry_id sql.NullInt64
value_type string
time_key string
number sql.NullFloat64
percent sql.NullFloat64
moe sql.NullFloat64
numerator sql.NullFloat64
numerator_moe sql.NullFloat64
f_number sql.NullString
f_percent sql.NullString
f_moe sql.NullString
f_numerator sql.NullString
f_numerator_moe sql.NullString
)
/* SANITIZING INPUTS */
cleaned_geos, err := sanitize(raw_geos, "[0-9,\\*]+")
if err != nil {
r := []byte("405")
return r
}
data := map[string]interface{}{} // this will be the object that wraps everything
db, err := getDB(conf)
if err != nil {
log.Println("Error trying to call getDB")
r := []byte("500")
return r
}
defer db.Close()
var base_query string = "SELECT indicator_slug, display_title, geography_id, geography_name, geometry_id, value_type, time_key, number, percent, moe, numerator,numerator_moe, f_number, f_percent, f_moe, f_numerator, f_numerator_moe FROM profiles_flatvalue WHERE indicator_slug = $1 AND time_key= $2"
var query string
// we need to support getting * geos or specific ones via thier ids
if cleaned_geos == "*" {
query = base_query
} else {
query = base_query + "AND geography_id IN (" + cleaned_geos + ")"
}
stmt, err := db.Prepare(query)
if err != nil {
log.Println("Error preparing query %s in getData", query)
}
defer stmt.Close()
rows, err := stmt.Query(ind, time)
if err != nil {
log.Println("Error running query %s in getData", query)
}
results := []interface{}{}
metachan := make(chan []interface{}) // get the meta data
go getMetaData(metachan, db, ind)
data["related"] = <-metachan
for rows.Next() {
jrow := make(map[string]interface{})
err := rows.Scan(&indicator_slug, &display_title, &geography_id, &geography_name, &geometry_id, &value_type, &time_key, &number, &percent, &moe, &numerator, &numerator_moe, &f_number, &f_percent, &f_moe, &f_numerator, &f_numerator_moe)
if err != nil {
log.Fatal(err)
}
jrow["indicator_slug"] = indicator_slug
jrow["display_title"] = display_title
jrow["geography_id"] = geography_id
jrow["geography_name"] = geography_name
jrow["value_type"] = value_type
jrow["time_key"] = time_key
if number.Valid {
jrow["number"] = number.Float64
} else {
jrow["number"] = nil
}
if value_type != "i" {
if percent.Valid {
jrow["percent"] = percent.Float64
} else {
jrow["percent"] = nil
}
} else {
jrow["percent"] = nil
}
if moe.Valid {
jrow["moe"] = moe.Float64
} else {
jrow["moe"] = nil
}
if f_number.Valid {
jrow["f_number"] = f_number.String
} else {
jrow["f_number"] = nil
}
if value_type != "i" {
if numerator.Valid {
jrow["numerator"] = numerator.Float64
} else {
jrow["numerator"] = nil
}
if f_numerator.Valid {
jrow["f_numerator"] = f_numerator.String
} else {
jrow["f_numerator"] = nil
}
if numerator.Valid {
jrow["numerator_moe"] = numerator.Float64
} else {
jrow["numerator_moe"] = nil
}
if f_numerator.Valid {
jrow["f_numerator_moe"] = f_numerator.String
} else {
jrow["f_numerator_moe"] = nil
}
if f_percent.Valid {
jrow["f_percent"] = f_percent.String
} else {
jrow["f_percent"] = nil
}
} else {
jrow["f_percent"] = nil
}
if f_moe.Valid {
jrow["f_moe"] = f_moe.String
} else {
jrow["f_moe"] = nil
}
results = append(results, jrow)
}
data["objects"] = &results
j, err := json.Marshal(data)
if len(results) != 0 {
putInCache(conf.REDIS_CONN, hash, j, conf.CACHE_EXPIRE)
}
return j
}