PostGIS Basics

Transcript

1. Absolute Basics
   of PostGIS ...the first few hours after installation...
2. Overview • What is PostGIS • Getting data into PostGIS • Basic everyday GIS queries • Getting data out of PostGIS
3. Disclaimer / About • I’m a Ruby / C# / NodeJS / Java developer • I’m interested in apps with spatial aspects • I’m a grumpy coder and a scuba diver
4. PostWTF A set of geographic extensions for pgSQL • Spatial Data Types • Spatial Functions • Spatial Indexing
5. It’s basically a set of tools that helps you do stuff with geo* data in PostgreSQL...
6. Spatial Data Types ...three primitive elements...
7. Point • Cartesian (x, y, z) coordinates • Represent locations such as: • Buildings, Bus Stops, Bike Stations, etc • Commonly GPS latitude and longitude • POINT(-6.23709945 53.3411573)
8. LineString • Multiple points connected by straight lines • Represent thing such as: • Roads, Routes, Pipes, Cables, etc • Can be used to indicate a direction • LINESTRING(30 10, 10 30, 40 40)
9. Polygon • Multiple points which enclose an area • Represent boundaries such as: • Property, Town, County, Country, etc • POLYGON(
 (30 10, 10 20, 20 40, 40 40, 30 10))
10. Multipart Each of the three primitives may be grouped together to form a larger collection 1.MultiPoint 2.MultiLineString 3.MultiPolygon
11. Coordinate Reference Systems ...in far too little detail...
12. The world is not flat...
13. It’s not a sphere either!
14. It’s not even an ellipsoid
15. A Best Fit Model
16. A Global Best Fit • GPS uses an ellipsoid called 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
17. Input
18. Creating a PostGIS Database 1.createdb postgis0 -T template0! 2.psql postgis0 -f postgis.sql! 3.psql postgis0 -f spatial_ref_sys.sql! 4.createdb postgis1 -T postgis0! 5.createdb talkdb -T postgis1
19. CREATE TABLE CREATE TABLE hospitals
 (
 id serial NOT NULL,
 CONSTRAINT pk_hospitals PRIMARY KEY (id),
 location geography NOT NULL,
 location geometry(POINT, 4326) NOT NULL,
 name text NOT NULL
 );
20. INSERT INTO INSERT INTO hospitals (name, location) VALUES ('Adelaide and Meath, Tallaght',
 ST_POINT(-6.3788, 53.2911)), ('Mullingar Regional',
 ST_GeogFromText('POINT(-7.3497 53.5346)'));
21. From KML INSERT INTO hospitals (name, location)
 VALUES (E'St James\'s',
 ST_GeomFromKML(
 '<Point><coordinates>
 -6.2953, 53.3401
 </coordinates></Point>'));
22. From GeoJSON INSERT INTO hospitals (name, location)
 VALUES (E'St James\'s',
 ST_GeomFromGeoJSON('{
 “type”:“Point”,
 “coordinates”:[-6.2953, 53.3401]
 }'));
23. shp2pgsql • ESRI is a commercial organisation which defined a 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
24. ogr2ogr • It’s kind of like a rosetta stone for GIS • Supports KML, GeoJSON, MapInfo Tab, ... • Part of GDAL package: 1.brew install gdal --with-postgres! • We’re obviously interested in pg: 2.ogr2ogr -f "PostgreSQL" PG:"dbname=talkdb” locations.kml
25. Spatial Functions
26. SELECT name, ST_Distance(location,
 ST_Point(-6.23709945, 53.3411573)::geography)
 FROM hospitals ORDER BY 2 ST_Distance Na#onal  Maternity,  Holles  Street 632.4519021 Royal  Victoria  Eye  and  Ear 1,570.022099 Rotunda 2,151.375497 … Bantry  General 286,158.2834
27. SELECT name FROM hospitals
 WHERE ST_DWithin(location,
 ST_Point(-6.23709945, 53.3411573)::geography, 
 10000) ST_DWithin Royal  Victoria  Eye  and  Ear St  James's Coombe  Women's … 13  rows  out  of  48  returned
28. ST_Union & ST_Area • ST_Union takes a set of polygons 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
29. Too Many to List • ST_Intersects(a,b) - Returns true if 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
30. Function Caveats • Watch out for the order of arguments, 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
31. Spatial Indexes
32. USING GiST(geom) • CREATE INDEX gx_regions_geom ON regions USING GiST(geom); • Performance gains such as the previously mentioned ST_DWithin • Not all functions can use indexes though
33. Output
34. SELECT FROM • PostGIS returns GIS data in hex encoded, Well Known Binary (WKB) format • SELECT location, ST_AsText(location) FROM hospitals 0101000020E6100000613D187... POINT(-­‐6.3788  53.2911) 0101000020E610000054D10E6... POINT(-­‐6.2559  53.3327)
35. KML and GeoJSON • Just as you can read data 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]}"
36. In an Application • Various libraries exist in multiple languages 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)
37. pgsql2shp • Just like the previously mentioned shp2pgsql but going 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”
38. Questions ...with little guarantee of a definitive answer...