Now that you have a lot of data into your geospatial database, we should take a little bit of time to discuss how to manage it and keep it running smoothly. \u00a0This can make the difference between requests timing out and having data be returned almost instantaneously.<\/p>\n
There are a large number of sources online that will go over how to configure PostgreSQL for maximum performance. \u00a0The PostgreSQL team themselves provide such documentation at the <\/span>Tuning Your PostgreSQL Server<\/a>\u00a0wiki page and a list of many techniques at their <\/span>Performance Optimization<\/a> wiki page.<\/span><\/p>\n For the PostGIS side of things, Boundless has a workshop page titled Tuning Postgres for Spatial<\/a> that provides some information on configuring for spatial operations. \u00a0The PostGIS team also has some tips that can be found at this<\/a> link.<\/p>\n Another tool is the pgtune<\/a> utility. \u00a0This is a command line tool that lets you specify what you will be using your database for and what type of machine it is running on. \u00a0It then will give you several options that you can put in the PostgreSQL configuration files.<\/p>\n In general, the main things you should consider are creating indices, vacuuming your database to remove unused space, keeping your database statistics up to date, and clustering your database so that data that is spatially near each other are physically near each other on disk.<\/p>\n To perform the following commands, open a command prompt and run the command:<\/p>\n You should then see output similar to the following:<\/p>\n If you followed the commands in this series, your database tables should already have a spatial index based on the geom column that lets PostGIS only return data that you have requested. \u00a0To see this, first run the command<\/p>\n This will give you a list of all of the tables in your database. \u00a0On my system, I get the output below:<\/p>\n To get further details on a table, including the indices, run the following:<\/p>\n The geospatial index here is the us_zip_code_areas_geom_gist\u00a0<\/em> while the standard primary key index is us_zip_code_areas_pkey<\/em>.<\/p>\n When you create a new index, or perform a large number of inserts, updates, or deletes, you generally need to update the database’s statistics on that table as well as clear out any unused space. \u00a0Pick one of your tables and run the following command,\u00a0noting that depending on the size of your database (especially with US_Roads, for example), this command can take a while to complete:<\/p>\n This command tells PostgreSQL to clean up any unused space and update statistics on the us_zip_code_areas<\/em> table. \u00a0These statistics are used internally by the database when it runs user queries on the data.<\/p>\n The next thing you should do is cluster the data. \u00a0Clustering is an operation that is performed against a database index and it physically places data that is close to each other in the index in the same area on the hard drive. \u00a0For geospatial data, this can make a huge difference, as the database does not have to scan multiple areas on the hard drive to return data that your GIS may request. \u00a0To cluster data on the geospatial index, run the following command next, again noting that this could take a while to finish depending on the size of your database and your hardware:<\/p>\n Once finished, run the table describe command (\\d<\/em>) again:<\/p>\n As you can see, the geom index now has the world CLUSTER behind it, denoting that this is the index that you clustered on. \u00a0It will generally make more sense for geospatial databases to cluster on the geospatial index as that will ensure data that is near each other in the real world is near each other on disk.<\/p>\n However, now that you have moved data around on disk, you again need to update the database statistics. \u00a0You will not need to vacuum<\/em> it again, as the cluster<\/em> command does this itself as it rearranges the data.<\/p>\n So there you are. \u00a0Running these commands on your data tables, in addition to tuning your database itself, can make a huge performance difference over simply accepting the default options.<\/p>\n Next time we will go over backing up your database and then head on to loading data again, such as the USGS<\/a> Geographic Names Information System (GNIS)<\/a>, the National Geospatial-Intelligence Agency’s<\/a> Geonames<\/a>, and OpenStreetMap<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":" Now that you have a lot of data into your geospatial database, we should take a little bit of time to discuss how to manage it and keep it running smoothly. \u00a0This can make the difference between requests timing out … Continue reading Table Maintenance<\/h4>\n
psql -d Census_2013<\/pre>\n
[bmaddox@girls ~]$ psql -d Census_2013<\/strong> \r\npsql (9.2.7)\r\nType \"help\" for help.\r\n\r\nCensus_2013=#<\/pre>\n
\\dt<\/pre>\n
Census_2013=# \\dt<\/strong>\r\n List of relations\r\n Schema | Name | Type | Owner \r\n--------+-----------------------------------------+-------+---------\r\n public | county_outlines | table | bmaddox\r\n public | spatial_ref_sys | table | bmaddox\r\n public | state_outlines | table | bmaddox\r\n public | us_113_congress_districts | table | bmaddox\r\n public | us_alaska_native_regional_corporations | table | bmaddox\r\n public | us_area_landmarks | table | bmaddox\r\n public | us_area_water | table | bmaddox\r\n public | us_census_block_groups | table | bmaddox\r\n public | us_coastlines | table | bmaddox\r\n public | us_combined_new_england_city_town_areas | table | bmaddox\r\n public | us_combined_statistical_areas | table | bmaddox\r\n public | us_elementary_school_districts | table | bmaddox\r\n public | us_indian_alaska_hawaii_native_areas | table | bmaddox\r\n public | us_indian_tribal_subdivisions | table | bmaddox\r\n public | us_linear_water | table | bmaddox\r\n public | us_metro_micropolitan_statistical_areas | table | bmaddox\r\n public | us_military_areas | table | bmaddox\r\n public | us_new_england_city_town_areas | table | bmaddox\r\n public | us_new_england_city_town_divisions | table | bmaddox\r\n public | us_primary_roads | table | bmaddox\r\n public | us_primary_secondary_roads | table | bmaddox\r\n public | us_rails | table | bmaddox\r\n public | us_roads | table | bmaddox\r\n public | us_secondard_school_districts | table | bmaddox\r\n public | us_state_legislative_lower | table | bmaddox\r\n public | us_state_legislative_upper | table | bmaddox\r\n public | us_tribal_block_groups | table | bmaddox\r\n public | us_unified_school_districts | table | bmaddox\r\n public | us_urban_areas_2010 | table | bmaddox\r\n public | us_zip_code_areas | table | bmaddox\r\n(30 rows)\r\n\r\nCensus_2013=#<\/pre>\n
Census_2013=# \\d us_zip_code_areas<\/strong>\r\n Table \"public.us_zip_code_areas\"\r\n Column | Type | Modifiers \r\n\r\n------------+-----------------------------+---------------------------------------------------------\r\n--------\r\n gid | integer | not null default nextval('us_zip_code_areas_gid_seq'::re\r\ngclass)\r\n zcta5ce10 | character varying(5) | \r\n geoid10 | character varying(5) | \r\n classfp10 | character varying(2) | \r\n mtfcc10 | character varying(5) | \r\n funcstat10 | character varying(1) | \r\n aland10 | double precision | \r\n awater10 | double precision | \r\n intptlat10 | character varying(11) | \r\n intptlon10 | character varying(12) | \r\n geom | geometry(MultiPolygon,4269) | \r\nIndexes:\r\n \"us_zip_code_areas_pkey\" PRIMARY KEY, btree (gid)\r\n \"us_zip_code_areas_geom_gist\" gist (geom)\r\n\r\nCensus_2013=#<\/pre>\nCensus_2013=# vacuum analyze us_zip_code_areas;<\/strong>\r\nVACUUM\r\nCensus_2013=#<\/pre>\n
Census_2013=# cluster us_zip_code_areas using us_zip_code_areas_geom_gist ;<\/strong>\r\nCLUSTER\r\nCensus_2013=#<\/pre>\n
Census_2013=# \\d us_zip_code_areas<\/strong>\r\n Table \"public.us_zip_code_areas\"\r\n Column | Type | Modifiers \r\n\r\n------------+-----------------------------+---------------------------------------------------------\r\n--------\r\n gid | integer | not null default nextval('us_zip_code_areas_gid_seq'::re\r\ngclass)\r\n zcta5ce10 | character varying(5) | \r\n geoid10 | character varying(5) | \r\n classfp10 | character varying(2) | \r\n mtfcc10 | character varying(5) | \r\n funcstat10 | character varying(1) | \r\n aland10 | double precision | \r\n awater10 | double precision | \r\n intptlat10 | character varying(11) | \r\n intptlon10 | character varying(12) | \r\n geom | geometry(MultiPolygon,4269) | \r\nIndexes:\r\n \"us_zip_code_areas_pkey\" PRIMARY KEY, btree (gid)\r\n \"us_zip_code_areas_geom_gist\" gist (geom) CLUSTER\r\n\r\nCensus_2013=#<\/pre>\nCensus_2013=# analyze us_zip_code_areas ;<\/strong>\r\nANALYZE\r\nCensus_2013=#<\/pre>\n