PostGIS Cheat Sheet

README.md

PostGIS Cheat Sheet

Reverse geo-lookup

datathon=# SELECT zip FROM tmp WHERE st_contains(geom, ST_GeomFromText('POINT(-122.4246 37.80145)'));
  zip
-------
 94109
(1 row)

datathon=#

Performance of Rverse geo-lookup

datathon=# EXPLAIN ANALYZE SELECT zip FROM tmp WHERE st_contains(geom, ST_GeomFromText('POINT(-122.4246 37.80145)'));
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tmp  (cost=206.32..8518.90 rows=2024 width=6) (actual time=0.217..0.333 rows=1 loops=1)
   Recheck Cond: (geom && '01010000006C787AA52C9B5EC08AB0E1E995E64240'::geometry)
   Filter: _st_contains(geom, '01010000006C787AA52C9B5EC08AB0E1E995E64240'::geometry)
   Rows Removed by Filter: 2
   ->  Bitmap Index Scan on gis_geom  (cost=0.00..205.81 rows=6071 width=0) (actual time=0.176..0.176 rows=3 loops=1)
         Index Cond: (geom && '01010000006C787AA52C9B5EC08AB0E1E995E64240'::geometry)
 Total runtime: 0.406 ms
(7 rows)

datathon=#

HOWTO.md

How To

Enable PostGIS Extensions

For more information se official postgis site

-- Enable PostGIS (includes raster)
CREATE EXTENSION postgis;

-- Enable Topology
CREATE EXTENSION postgis_topology;

-- fuzzy matching needed for Tiger
CREATE EXTENSION fuzzystrmatch;

-- Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder;

Import data using shp2psql

oot@apollocrawler:~# time shp2pgsql -c ./zip_poly.shp public.gis_us_zip | psql -h apollocrawler.com -v  -d datathon -U datathon  > /dev/null
Shapefile type: Polygon
Postgis type: MULTIPOLYGON[2]
Password for user datathon:

real    1m10.654s
user    0m28.983s
sys     0m3.657s
root@apollocrawler:~#

postgis.sql

-- Create index
-- CREATE INDEX gis_geom ON tmp USING gist(geom);

-- Lookup
-- SELECT po_name FROM tmp WHERE st_contains(geom, ST_GeomFromText('POINT(-122.4246 37.80145)', 4326))

-- Find wrong geoms
-- select *, ST_IsValidReason(geom) from tmp where ST_IsValid(geom) = false

-- ALTER TABLE my_table ALTER COLUMN geom  SET DATA TYPE geometry(MultiPolygon) USING ST_Multi(geom);
    
-- Fix wrong geoms
-- UPDATE tmp SET geom = ST_SimplifyPreserveTopology(geom, 0.0001) WHERE ST_IsValid(geom) = false;

SELECT zip FROM tmp WHERE st_contains(geom, ST_GeomFromText('POINT(-122.4246 37.80145)'));