Process mammals with Postgres+PostGIS

README.md

1. Install Postgres.app
2. Add /Applications/Postgres.app/Contents/Versions/9.4/bin 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.

1. CREATE TABLE grouped_species (id integer, species text, geom geometry);
2. SELECT DISTINCT species_na FROM terrestrial_mammals;

For each in result...
INSERT INTO grouped_species (select species_na, ST_Union(geom) from terrestrial_mammals where species_na = each GROUP BY species_na)`