Example #1
0
// 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
}
Example #2
0
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 #3
0
/* 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

}
Example #4
0
// 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
}
Example #5
0
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

}
Example #6
0
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
}
Example #7
0
// 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
}