block by jczaplew 53ea2b483b61d7e8a1aa

Process mammals with Postgres+PostGIS

  1. Install
  2. Add /Applications/ to your $PATH
  3. createdb mammals
  4. psql -U you -d mammals -c 'CREATE EXTENSION postgis'
  5. psql -U you -d mammals -c 'CREATE EXTENSION postgis_topology'
  6. shp2pgsql -s 4326 -W "latin1" ~/Downloads/TERRESTRIAL_MAMMALS/TERRESTRIAL_MAMMALS.shp | psql -U you mammals (this will take about a minute or two)
  7. psql -U you mammals
  8. UPDATE terrestrial_mammals SET geom = ST_MakeValid(geom); (Took about half an hour for me)
  9. CREATE TABLE grouped_families AS SELECT family_nam, ST_Union(geom) AS geom FROM terrestrial_mammals GROUP BY family_nam; (takes ~ 30 seconds)
  10. VACUUM ANALYZE grouped_families;
  11. CREATE TABLE grouped_species AS SELECT species_na, ST_Union(geom) AS geom FROM terrestrial_mammals GROUP BY species_na;
  12. VACUUM ANALYZE grouped_species;

–create index sIndex on terrestrial_mammals USING gist (geom); create index ON terrestrial_mammals (family_nam); create index on terrestrial_mammals (species_na); You can also install PGAdminIII to more easily view the data.