WGS84 • WGS84 has a Spatial Reference ID of 4326 • It basically approximates the earths surface as the mean sea level* • ESRI - Mean Sea Level, GPS, and the Geoid
file format called Shape (.shp) • shp files are fairly ubiquitous and lots of boundary data, etc exists in this format • shp2pgsql takes shape files as input and output a pg table with data inserts •shp2pgsql regions.shp | psql talkdb
and aggregates them into one large boundary with no intersecting regions • SELECT provence, ST_Union(county) FROM counties GROUP BY provence • SELECT provence, ST_Area(ST_Union(county)) FROM counties GROUP BY provence
geometry a shares any portion of space with geometry b • ST_Extent(set) - Returns a bounding box which encloses all supplied geometries • ST_Within(a,b) - Returns true if geometry a is completely inside geometry b
especially where longitude, latitude (x, y) are concerned • Not all functions support the geography data type so you many needs to ST_Transform them to geometries
Well Known Binary (WKB) format • SELECT location, ST_AsText(location) FROM hospitals 0101000020E6100000613D187... POINT(-‐6.3788 53.2911) 0101000020E610000054D10E6... POINT(-‐6.2559 53.3327)
in from KML and GeoJSON, you can output to it also • SELECT ST_AsKML(location), ST_AsGeoJSON(location) FROM hospitals <Point><coordinates>-‐6.3788,53.2911</coordinates></Point> {"type":"Point","coordinates":[-‐6.3788,53.2911]}"
for working with hex encoded WKB data • GeoRuby is a very useful library for converting to and from it in Ruby include GeoRuby::SimpleFeatures x = Geometry.from_hex_ewkb(raw) raw = x.as_hex_ewkb ! • See also PPyGIS (Python), GeoTools (Java)
the other way • Exports a table or query to an ESRI Shape file complete with associated attributes •pgsql2shp -f output.shp talkdb hospitals! •pgsql2shp -f output.shp talkdb \ ”SELECT name, location FROM hospitals”