Skip to content

Instantly share code, notes, and snippets.

Avatar

Ochieng' Steve jometho

View GitHub Profile
View Register JtsModule in Spring Boot to enable Serialization and Deserialization of Features and FeatureCollection
/**
- Spring Boot uses Jackson by default for serializing and deserializing request and response objects in your REST APIs.
- Jackson cannot deserialize GeoJson by default.
- So we need this library solve the issue: https://github.com/bedatadriven/jackson-datatype-jts.
Withut it Jackson will fail to desirialize due to recursive calls in the Feature and FeatureCollection properties
- To register JtsModule , you can either do a configuration class and annotate it with @Configuration then
add the JtsModule @Bean inside together with the registration in the ObjctMapper
or do this in your Main class.
**/
@jometho
jometho / gist:c35ca3b264c2cb75172f7e26074fcc2f
Created Feb 26, 2019
Excel VBA function to Google Geocode
View gist:c35ca3b264c2cb75172f7e26074fcc2f
Function GoogleGeocode(address As String) As String
Dim strAddress As String
Dim strQuery As String
Dim strLatitude As String
Dim strLongitude As String
strAddress = URLEncode(address)
'Assemble the query string
strQuery = "http://maps.googleapis.com/maps/api/geocode/xml?"
@jometho
jometho / handling_duplicates_in_postgresql.sql
Created May 24, 2018
Handling duplicates records in postgresql
View handling_duplicates_in_postgresql.sql
--Given table coverage with field name which is guaranteed uniqueness
-- You can select duplicated records with this "sequel" on the table
select coverage.name, count(*) from catalog.coverage GROUP BY coverage.name
HAVING count(*) > 1;
--Same scenario now say you want to delete the redundant tables and remain with only one record for each
--You can use this SQL, where name is the unique column
--ctid is a postgresql system columns for the physical row location of a record
View postgis_geojson.php
<?php
header('Content-Type: application/json; charset=UTF-8');
/**
* PostGIS to GeoJSON
* Query a PostGIS table or view and return the results in GeoJSON format, suitable for use in OpenLayers, Leaflet, etc.
*
* @param string $geotable The PostGIS layer name *REQUIRED*
* @param string $geomfield The PostGIS geometry field *REQUIRED*
* @param string $srid The SRID of the returned GeoJSON *OPTIONAL (If omitted, EPSG: 4326 will be used)*
* @param string $fields Fields to be returned *OPTIONAL (If omitted, all fields will be returned)* NOTE- Uppercase field names should be wrapped in double quotes
View postgres_queries_and_commands.sql
-- show running queries (pre 9.2)
SELECT procpid, age(query_start, clock_timestamp()), usename, current_query
FROM pg_stat_activity
WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;
-- show running queries (9.2)
SELECT pid, age(query_start, clock_timestamp()), usename, query
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
@jometho
jometho / postgres-cheatsheet.md
Created Apr 9, 2018 — forked from Kartones/postgres-cheatsheet.md
PostgreSQL command line cheatsheet
View postgres-cheatsheet.md

PSQL

Magic words:

psql -U postgres

Some interesting flags (to see all, use -h):

  • -E: will describe the underlaying queries of the \ commands (cool for learning!)
  • -l: psql will list all databases and then exit (useful if the user you connect with doesn't has a default database, like at AWS RDS)
@jometho
jometho / SimpleJSON.java
Last active Feb 20, 2018 — forked from sheharyarn/SimpleJSON.java
Simple Java Class to convert Lists/Maps to JSON and vice versa
View SimpleJSON.java
public class SimpleJSON {
public static Object toJSON(Object object) throws JSONException {
if (object instanceof HashMap) {
JSONObject json = new JSONObject();
HashMap map = (HashMap) object;
for (Object key : map.keySet()) {
json.put(key.toString(), toJSON(map.get(key)));
}
return json;
@jometho
jometho / csv_splitter.py
Created May 19, 2016 — forked from palewire/csv_splitter.py
A Python CSV splitter
View csv_splitter.py
import os
def split(filehandler, delimiter=',', row_limit=10000,
output_name_template='output_%s.csv', output_path='.', keep_headers=True):
"""
Splits a CSV file into multiple pieces.
A quick bastardization of the Python CSV library.
Arguments:
View geo.js
var pg = require('pg');
var conString = "postgres://username:password@hostname.rds.amazonaws.com:5432/database"; //TODO: point to RDS instance
exports.bbox = function(req, res) {
var client = new pg.Client(conString);
client.connect();
var crsobj = {"type": "name","properties": {"name": "urn:ogc:def:crs:EPSG:6.3:4326"}};
var idformat = "'" + req.params.id + "'";
idformat = idformat.toUpperCase();
var query = client.query("select st_asgeojson(st_envelope(shape)) as geojson from ne_countries where iso_a3 = " + idformat + ";");